Skip to content

Instantly share code, notes, and snippets.

@dmzoneill

dmzoneill/AA active queries.txt

Last active January 25, 2023 19:45
Show Gist options
  • Save dmzoneill/cbc75e502dbe2b78651f68d6320b714a to your computer and use it in GitHub Desktop.
Save dmzoneill/cbc75e502dbe2b78651f68d6320b714a to your computer and use it in GitHub Desktop.
Download ZIP
AA active queries
Raw
AA active queries.txt
postgres=> SELECT pid, now() - pg_stat_activity.query_start AS duration, query, state, datname
FROM pg_stat_activity;
pid | duration | query | state | datname
-------+-----------------+------------------------------------------------------------------------------------------------------------------------+--------+----------------
5779 | 00:00:00 | SELECT pid, now() - pg_stat_activity.query_start AS duration, query, state, datname +| active | postgres
| | FROM pg_stat_activity; | |
14358 | 00:00:07.786144 | SELECT 1 | idle | rdsadmin
17140 | 00:00:00.187546 | COMMIT | idle | rdsadmin
32302 | 00:38:27.349656 | +| active | tenant_7729085
| | WITH scoped_events_cte AS ( +| |
| | SELECT z.cluster_id, z.job_id, z.host_name, +| |
| | y.created AS created, y.created::date AS created_date, z.created AS event_created, z.failed, +| |
| | z.changed, z.event, z.task AS task, z.task_action AS task_action, y.status, y.model, +| |
| | y.name, y.org_name, z.id AS event_id, COALESCE(z.duration, 0) AS duration, s.failures, s.dark +| |
| | FROM ( +| |
| | SELECT id, name, cluster_id, created, status, org_name, model FROM unified_jobs_fk +| |
| | WHERE unified_jobs_fk.created >= ('2022-08-24'::date)::timestamp AND +| |
| | unified_jobs_fk.created <= ('2022-08-29'::date)::date + interval '1 day - 1 microsecond' AND+| |
| | unified_jobs_fk.model = 'job' +| |
| | ) y +| |
| | JOIN job_events_fk z +| |
| | ON z.job_id = y.id AND +| |
| | z.cluster_id = y.cluster_id AND +| |
| | z.host_name IS NOT NULL AND +| |
| | z.event = ANY(ARRAY['runner_on_ok', 'runner_on_failed', 'runner_on_unreachable', +| |
| | 'runner_on_skipped', 'runner_retry']) +| |
| | LEFT JOIN ( +| |
| | -- If there are more summaries for one event, choose the last one (edge case, usually 1:1) +| |
| | SELECT DISTINCT ON (cluster_id, job_id, host_id) cluster_id, job_id, host_id, +| |
| | failures, dark +| |
| | FROM job_host_summary +| |
| | ORDER by cluster_id, job_id, host_id, event_id DESC +| |
| | ) s +| |
| | ON z.cluster_id = s.cluster_id AND +| |
| | z.job_id = s.job_id AND +| |
| | z.host_name = s.host_id +| |
| | ) +| |
| | INSERT INTO "host_event_explorer_rollup" +| |
| | ( +| |
| | "cluster_id", +| |
| | "event_id", +| |
| | "host_id", +| |
| | "job_id", +| |
| | "created", +| |
| | "event_created", +| |
| | "duration", +| |
| | "changed", +| |
| | "task_id", +| |
| | "task_action_id", +| |
| | "status" +| |
| | ) +| |
| | | |
8841 | 00:23:35.259691 | +| active | tenant_7729085
| | insert into job_events_fk +| |
| | ( +| |
| | system_id, +| |
| | cluster_id, +| |
| | id, +| |
| | created, +| |
| | uuid, +| |
| | parent_uuid, +| |
| | event, +| |
| | task_action, +| |
| | resolved_role_id, +| |
| | resolved_action_id, +| |
| | failed, +| |
| | changed, +| |
| | playbook, +| |
| | play, +| |
| | task, +| |
| | role, +| |
| | job_id, +| |
| | host_id, +| |
| | host_name, +| |
| | start, +| |
| | "end", +| |
| | duration, +| |
| | warnings, +| |
| | deprecations, +| |
| | job_created, +| |
| | modified, +| |
| | playbook_on_stats +| |
| | ) +| |
| | (select +| |
| | a.system_id, +| |
| | a.cluster_id, +| |
| | a.id, +| |
| | a.created, +| |
| | a.uuid, +| |
| | a.parent_uuid, +| |
| | a.event, +| |
| | b.id as task_action, +| |
| | r_role.id as resolved_role_id, +| |
| | r_action.id as resolved_action_id, +| |
| | a.failed, +| |
| | a.changed, +| |
| | c.id as playbook, +| |
| | d.id as play, +| |
| | e.id as task, +| |
| | g.id as role, +| |
| | a.job_id, +| |
| | a.host_id, +| |
| | f.id as host_name, +| |
| | a.start, +| |
| | a.end, +| |
| | a.duration, +| |
| | h.id_list AS warnings, +| |
| | i.id_list AS deprecations, +| |
| | a.job_created, +| |
| | a.modified, +| |
| | a.playbook_on_stats +| |
| | from job_events_fk_tmp a +| |
| | left join task_action_map_tmp b on a.task_action = b.tmp_id +| |
| | left join playbook_map_tmp c on a.playbook = c.tmp_id +| |
| | left join play_map_tmp d on a.play = d.tmp_id +| |
| | left join task_map_tmp e on a.task = e.tmp_id +| |
| | left join host_map_tmp f on a.host_name = f.tmp_id +| |
| | left join role_map_tmp g on a.role = g.tmp_id +| |
| | | |
27485 | 00:15:34.215167 | +| active | tenant_7729085
| | insert into job_events_fk +| |
| | ( +| |
| | system_id, +| |
| | cluster_id, +| |
| | id, +| |
| | created, +| |
| | uuid, +| |
| | parent_uuid, +| |
| | event, +| |
| | task_action, +| |
| | resolved_role_id, +| |
| | resolved_action_id, +| |
| | failed, +| |
| | changed, +| |
| | playbook, +| |
| | play, +| |
| | task, +| |
| | role, +| |
| | job_id, +| |
| | host_id, +| |
| | host_name, +| |
| | start, +| |
| | "end", +| |
| | duration, +| |
| | warnings, +| |
| | deprecations, +| |
| | job_created, +| |
| | modified, +| |
| | playbook_on_stats +| |
| | ) +| |
| | (select +| |
| | a.system_id, +| |
| | a.cluster_id, +| |
| | a.id, +| |
| | a.created, +| |
| | a.uuid, +| |
| | a.parent_uuid, +| |
| | a.event, +| |
| | b.id as task_action, +| |
| | r_role.id as resolved_role_id, +| |
| | r_action.id as resolved_action_id, +| |
| | a.failed, +| |
| | a.changed, +| |
| | c.id as playbook, +| |
| | d.id as play, +| |
| | e.id as task, +| |
| | g.id as role, +| |
| | a.job_id, +| |
| | a.host_id, +| |
| | f.id as host_name, +| |
| | a.start, +| |
| | a.end, +| |
| | a.duration, +| |
| | h.id_list AS warnings, +| |
| | i.id_list AS deprecations, +| |
| | a.job_created, +| |
| | a.modified, +| |
| | a.playbook_on_stats +| |
| | from job_events_fk_tmp a +| |
| | left join task_action_map_tmp b on a.task_action = b.tmp_id +| |
| | left join playbook_map_tmp c on a.playbook = c.tmp_id +| |
| | left join play_map_tmp d on a.play = d.tmp_id +| |
| | left join task_map_tmp e on a.task = e.tmp_id +| |
| | left join host_map_tmp f on a.host_name = f.tmp_id +| |
| | left join role_map_tmp g on a.role = g.tmp_id +| |
| | | |
32418 | 00:37:43.164398 | +| active | tenant_7729085
| | insert into job_events_fk +| |
| | ( +| |
| | system_id, +| |
| | cluster_id, +| |
| | id, +| |
| | created, +| |
| | uuid, +| |
| | parent_uuid, +| |
| | event, +| |
| | task_action, +| |
| | resolved_role_id, +| |
| | resolved_action_id, +| |
| | failed, +| |
| | changed, +| |
| | playbook, +| |
| | play, +| |
| | task, +| |
| | role, +| |
| | job_id, +| |
| | host_id, +| |
| | host_name, +| |
| | start, +| |
| | "end", +| |
| | duration, +| |
| | warnings, +| |
| | deprecations, +| |
| | job_created, +| |
| | modified, +| |
| | playbook_on_stats +| |
| | ) +| |
| | (select +| |
| | a.system_id, +| |
| | a.cluster_id, +| |
| | a.id, +| |
| | a.created, +| |
| | a.uuid, +| |
| | a.parent_uuid, +| |
| | a.event, +| |
| | b.id as task_action, +| |
| | r_role.id as resolved_role_id, +| |
| | r_action.id as resolved_action_id, +| |
| | a.failed, +| |
| | a.changed, +| |
| | c.id as playbook, +| |
| | d.id as play, +| |
| | e.id as task, +| |
| | g.id as role, +| |
| | a.job_id, +| |
| | a.host_id, +| |
| | f.id as host_name, +| |
| | a.start, +| |
| | a.end, +| |
| | a.duration, +| |
| | h.id_list AS warnings, +| |
| | i.id_list AS deprecations, +| |
| | a.job_created, +| |
| | a.modified, +| |
| | a.playbook_on_stats +| |
| | from job_events_fk_tmp a +| |
| | left join task_action_map_tmp b on a.task_action = b.tmp_id +| |
| | left join playbook_map_tmp c on a.playbook = c.tmp_id +| |
| | left join play_map_tmp d on a.play = d.tmp_id +| |
| | left join task_map_tmp e on a.task = e.tmp_id +| |
| | left join host_map_tmp f on a.host_name = f.tmp_id +| |
| | left join role_map_tmp g on a.role = g.tmp_id +| |
| | | |
32424 | 00:37:42.048307 | +| active | tenant_7729085
| | insert into job_events_fk +| |
| | ( +| |
| | system_id, +| |
| | cluster_id, +| |
| | id, +| |
| | created, +| |
| | uuid, +| |
| | parent_uuid, +| |
| | event, +| |
| | task_action, +| |
| | resolved_role_id, +| |
| | resolved_action_id, +| |
| | failed, +| |
| | changed, +| |
| | playbook, +| |
| | play, +| |
| | task, +| |
| | role, +| |
| | job_id, +| |
| | host_id, +| |
| | host_name, +| |
| | start, +| |
| | "end", +| |
| | duration, +| |
| | warnings, +| |
| | deprecations, +| |
| | job_created, +| |
| | modified, +| |
| | playbook_on_stats +| |
| | ) +| |
| | (select +| |
| | a.system_id, +| |
| | a.cluster_id, +| |
| | a.id, +| |
| | a.created, +| |
| | a.uuid, +| |
| | a.parent_uuid, +| |
| | a.event, +| |
| | b.id as task_action, +| |
| | r_role.id as resolved_role_id, +| |
| | r_action.id as resolved_action_id, +| |
| | a.failed, +| |
| | a.changed, +| |
| | c.id as playbook, +| |
| | d.id as play, +| |
| | e.id as task, +| |
| | g.id as role, +| |
| | a.job_id, +| |
| | a.host_id, +| |
| | f.id as host_name, +| |
| | a.start, +| |
| | a.end, +| |
| | a.duration, +| |
| | h.id_list AS warnings, +| |
| | i.id_list AS deprecations, +| |
| | a.job_created, +| |
| | a.modified, +| |
| | a.playbook_on_stats +| |
| | from job_events_fk_tmp a +| |
| | left join task_action_map_tmp b on a.task_action = b.tmp_id +| |
| | left join playbook_map_tmp c on a.playbook = c.tmp_id +| |
| | left join play_map_tmp d on a.play = d.tmp_id +| |
| | left join task_map_tmp e on a.task = e.tmp_id +| |
| | left join host_map_tmp f on a.host_name = f.tmp_id +| |
| | left join role_map_tmp g on a.role = g.tmp_id +| |
| | | |
8759 | 00:24:08.328467 | +| active | tenant_7729085
| | insert into job_events_fk +| |
| | ( +| |
| | system_id, +| |
| | cluster_id, +| |
| | id, +| |
| | created, +| |
| | uuid, +| |
| | parent_uuid, +| |
| | event, +| |
| | task_action, +| |
| | resolved_role_id, +| |
| | resolved_action_id, +| |
| | failed, +| |
| | changed, +| |
| | playbook, +| |
| | play, +| |
| | task, +| |
| | role, +| |
| | job_id, +| |
| | host_id, +| |
| | host_name, +| |
| | start, +| |
| | "end", +| |
| | duration, +| |
| | warnings, +| |
| | deprecations, +| |
| | job_created, +| |
| | modified, +| |
| | playbook_on_stats +| |
| | ) +| |
| | (select +| |
| | a.system_id, +| |
| | a.cluster_id, +| |
| | a.id, +| |
| | a.created, +| |
| | a.uuid, +| |
| | a.parent_uuid, +| |
| | a.event, +| |
| | b.id as task_action, +| |
| | r_role.id as resolved_role_id, +| |
| | r_action.id as resolved_action_id, +| |
| | a.failed, +| |
| | a.changed, +| |
| | c.id as playbook, +| |
| | d.id as play, +| |
| | e.id as task, +| |
| | g.id as role, +| |
| | a.job_id, +| |
| | a.host_id, +| |
| | f.id as host_name, +| |
| | a.start, +| |
| | a.end, +| |
| | a.duration, +| |
| | h.id_list AS warnings, +| |
| | i.id_list AS deprecations, +| |
| | a.job_created, +| |
| | a.modified, +| |
| | a.playbook_on_stats +| |
| | from job_events_fk_tmp a +| |
| | left join task_action_map_tmp b on a.task_action = b.tmp_id +| |
| | left join playbook_map_tmp c on a.playbook = c.tmp_id +| |
| | left join play_map_tmp d on a.play = d.tmp_id +| |
| | left join task_map_tmp e on a.task = e.tmp_id +| |
| | left join host_map_tmp f on a.host_name = f.tmp_id +| |
| | left join role_map_tmp g on a.role = g.tmp_id +| |
| | | |
8737 | 00:24:22.489294 | SELECT pg_advisory_lock_shared(793312423285730774); | idle | tenants
28099 | 00:00:04.642055 | UPDATE messages set processing_state = 'not_found' +| active | tenants
| | WHERE request_id='2d049a7d8a0b41509b547b5f125b0547'; +| |
| | | |
8730 | 00:24:22.588533 | SELECT pg_advisory_lock_shared(793312423285730774); | idle | tenants
27335 | 00:16:18.414008 | SELECT pg_advisory_lock_shared(793312423285730774); | idle | tenants
32513 | | | |
32511 | | | |
32512 | | | |
32517 | | | |
32514 | | | |
(18 rows)
postgres=>
postgres=>
postgres=>
postgres=>
postgres=>
postgres=>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment