Alexander Pace (0faf9cfe) at 27 Mar 23:01
Apply 1 suggestion(s) to 1 file(s)
Alexander Pace (2b9e9eb8) at 27 Mar 23:01
Apply 1 suggestion(s) to 1 file(s)
Alexander Pace (986f17a8) at 27 Mar 23:01
Apply 1 suggestion(s) to 1 file(s)
Alexander Pace (53654693) at 27 Mar 22:51
Apply 1 suggestion(s) to 1 file(s)
Alexander Pace (ac7df550) at 27 Mar 22:49
Apply 1 suggestion(s) to 1 file(s)
Alexander Pace (88773f1c) at 27 Mar 22:47
Apply 1 suggestion(s) to 1 file(s)
Alexander Pace (0d21b346) at 27 Mar 22:47
Apply 1 suggestion(s) to 1 file(s)
yep, good call. this was copy-paste detritus.
@robert.bruntz @ryan.fisher @stuart.anderson @philippe.grassia
I've been watching at the dqsegdb server upgrade from a 30,000ft perspective. Unless I'm looking in the wrong place, is this the right folder that contains the database schemas that are used for the production database?
If so, and unless I'm not seeing it, or unless it's been implemented and not in version control, it looks like none of the tables are indexed. Is that the case?
If so, then having an unindexed table with multiple users making queries is 100% the cause of seeing increased and unsustainable load on your database. Early on in O3 and before O4a, there were a few corner cases where users would hit GraceDB with unindexed queries which would bring the service to crawl. But a few lines of SQL fixed the problem.
Everyone's busy, but I can spare some cycles to help out if need be; just let me know.
Alexander Pace (dd03cb3f) at 27 Mar 14:36
install vim in the container
Alexander Pace (575308b4) at 27 Mar 14:32
add ssm label and documentation
Alexander Pace (0c515f33) at 27 Mar 01:44
removed import-time queries
test 1: fetch all 55669 exposed superevent logs that the unauthenticated user can see, based just on querying for permissions. Here's the sql and the EXPLAIN ANALYZE
for guardian.shortcuts.get_objects_for_user
:
gracedb=> explain analyze SELECT "superevents_log"."id", "superevents_log"."created", "superevents_log"."issuer_id", "superevents_log"."filename", "superevents_log"."file_version", "superevents_log"."comment", "superevents_log"."N", "superevents_log"."superevent_id" FROM "superevents_log" WHERE ("superevents_log"."id" IN (SELECT (U0."content_object_id")::bigint AS "obj_pk" FROM "superevents_loguserobjectpermission" U0 INNER JOIN "auth_permission" U2 ON (U0."permission_id" = U2."id") WHERE (U0."user_id" = 40 AND U2."content_type_id" = 35 AND U2."codename" IN ('view_log'))) OR "superevents_log"."id" IN (SELECT (U0."content_object_id")::bigint AS "obj_pk" FROM "superevents_loggroupobjectpermission" U0 INNER JOIN "auth_group" U1 ON (U0."group_id" = U1."id") INNER JOIN "auth_user_groups" U2 ON (U1."id" = U2."group_id") INNER JOIN "auth_permission" U4 ON (U0."permission_id" = U4."id") WHERE (U2."user_id" = 40 AND U4."codename" IN ('view_log') AND U4."content_type_id" = 35))) ORDER BY "superevents_log"."created" DESC, "superevents_log"."N" DESC LIMIT 21;
Limit (cost=16123.40..16125.85 rows=21 width=79) (actual time=139.976..140.225 rows=21 loops=1)
-> Gather Merge (cost=16123.40..50539.20 rows=294972 width=79) (actual time=139.975..140.221 rows=21 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=15123.37..15492.09 rows=147486 width=79) (actual time=131.472..131.479 rows=17 loops=3)
Sort Key: superevents_log.created DESC, superevents_log."N" DESC
Sort Method: top-N heapsort Memory: 32kB
Worker 0: Sort Method: top-N heapsort Memory: 32kB
Worker 1: Sort Method: top-N heapsort Memory: 34kB
-> Parallel Seq Scan on superevents_log (cost=1660.20..11146.92 rows=147486 width=79) (actual time=65.531..125.634 rows=18556 loops=3)
Filter: ((hashed SubPlan 1) OR (hashed SubPlan 2))
Rows Removed by Filter: 138762
SubPlan 1
-> Nested Loop (cost=0.00..8.48 rows=1 width=8) (actual time=0.045..0.047 rows=0 loops=3)
Join Filter: (u0.permission_id = u2.id)
-> Seq Scan on auth_permission u2 (cost=0.00..8.47 rows=1 width=4) (actual time=0.025..0.041 rows=1 loops=3)
Filter: ((content_type_id = 35) AND ((codename)::text = 'view_log'::text))
Rows Removed by Filter: 297
-> Seq Scan on superevents_loguserobjectpermission u0 (cost=0.00..0.00 rows=1 width=8) (actual time=0.004..0.004 rows=0 loops=3)
Filter: (user_id = 40)
SubPlan 2
-> Nested Loop (cost=0.70..1651.71 rows=1 width=8) (actual time=0.085..36.913 rows=55669 loops=3)
Join Filter: (u0_1.permission_id = u4.id)
-> Seq Scan on auth_permission u4 (cost=0.00..8.47 rows=1 width=4) (actual time=0.014..0.039 rows=1 loops=3)
Filter: (((codename)::text = 'view_log'::text) AND (content_type_id = 35))
Rows Removed by Filter: 297
-> Nested Loop (cost=0.70..1642.82 rows=33 width=8) (actual time=0.070..27.152 rows=55669 loops=3)
-> Nested Loop (cost=0.28..5.70 rows=1 width=8) (actual time=0.028..0.030 rows=1 loops=3)
Join Filter: (u1.id = u2_1.group_id)
Rows Removed by Join Filter: 2
-> Index Only Scan using public_auth_user_groups_user_id1_idx on auth_user_groups u2_1 (cost=0.28..4.30 rows=1 width=4) (actual time=0.019..0.021 rows=1 loops=3)
Index Cond: (user_id = 40)
Heap Fetches: 0
-> Seq Scan on auth_group u1 (cost=0.00..1.18 rows=18 width=4) (actual time=0.007..0.007 rows=3 loops=3)
-> Index Only Scan using superevents_loggroupobje_group_id_permission_id_c_634ea615_uniq on superevents_loggroupobjectpermission u0_1 (cost=0.42..1080.42 rows=55670 width=12) (actual time=0.041..17.173 rows=55669 loops=3)
Index Cond: (group_id = u1.id)
Heap Fetches: 0
Planning Time: 0.920 ms
Execution Time: 140.654 ms
And the same for fast_get_objects_for_user
:
gracedb=> explain analyze SELECT "superevents_log"."id", "superevents_log"."created", "superevents_log"."issuer_id", "superevents_log"."filename", "superevents_log"."file_version", "superevents_log"."comment", "superevents_log"."N", "superevents_log"."superevent_id" FROM "superevents_log" INNER JOIN "superevents_loggroupobjectpermission" ON ("superevents_log"."id" = "superevents_loggroupobjectpermission"."content_object_id") WHERE ("superevents_loggroupobjectpermission"."group_id" = 4 AND "superevents_loggroupobjectpermission"."permission_id" = 129) ORDER BY "superevents_log"."created" DESC, "superevents_log"."N" DESC LIMIT 21;
Limit (cost=12692.43..12694.88 rows=21 width=79) (actual time=93.652..94.799 rows=21 loops=1)
-> Gather Merge (cost=12692.43..18131.58 rows=46618 width=79) (actual time=93.651..94.795 rows=21 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=11692.41..11750.68 rows=23309 width=79) (actual time=84.658..84.661 rows=14 loops=3)
Sort Key: superevents_log.created DESC, superevents_log."N" DESC
Sort Method: top-N heapsort Memory: 33kB
Worker 0: Sort Method: top-N heapsort Memory: 32kB
Worker 1: Sort Method: top-N heapsort Memory: 29kB
-> Parallel Hash Join (cost=2044.28..11063.96 rows=23309 width=79) (actual time=8.956..78.911 rows=18556 loops=3)
Hash Cond: (superevents_log.id = superevents_loggroupobjectpermission.content_object_id)
-> Parallel Seq Scan on superevents_log (cost=0.00..8503.48 rows=196648 width=79) (actual time=0.008..20.385 rows=157319 loops=3)
-> Parallel Hash (cost=1752.91..1752.91 rows=23309 width=4) (actual time=7.808..7.809 rows=18556 loops=3)
Buckets: 65536 Batches: 1 Memory Usage: 2720kB
-> Parallel Index Only Scan using superevents_loggroupobje_group_id_permission_id_c_634ea615_uniq on superevents_loggroupobjectpermission (cost=0.42..1752.91 rows=23309 width=4) (actual time=0.038..2.850 rows=18556 loops=3)
Index Cond: ((group_id = 4) AND (permission_id = 129))
Heap Fetches: 0
Planning Time: 0.325 ms
Execution Time: 94.839 ms
(19 rows)
Alexander Pace (0a86cf68) at 26 Mar 20:48
speed up unauthenticated access of public data products
Alexander Pace (57654c4f) at 26 Mar 20:42
Alexander Pace (57654c4f) at 26 Mar 20:40
speed up unauthenticated access to superevents and files
... and 5 more commits
Alexander Pace (cba89ba1) at 26 Mar 17:28
fix outdated link to old monitor dashboard
Alexander Pace (ab8cea28) at 26 Mar 17:23
change domain of helpdesk email