From: Brian Cox <brian.cox@ca.com>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: "slow" queries
Date: Sat, 28 Feb 2009 18:51:32 -0800
Message-ID: <49A9F834.8060100@ca.com> (raw)
Actually, they're all deadlocked. The question is why?
Here's a brief background. The ts_defects table is partitioned by
occurrence date; each partition contains the rows for 1 day. When the
data gets old enough, the partition is dropped. Since the correct
partition can be determined from the occurrence date, there is no
trigger: inserts are done directly into the correct partition. Multiple
threads may be inserting into a partition at the same time. The thread
that checks for old data to be dropped runs at 00:30 each night. It also
creates the partition for the next day.
Below is the output from:
select xact_start,query_start,substring(current_query from 0 for 40)
from pg_stat_activity order by xact_start;
run at 18:40 on 28 Feb 2009 (i.e. these queries have been running for
> 6 hours). The 1st select is not on any of the ts_defect partitions
nor is the CREATE VIEW. The SELECT's shown last are not (directly)
generated by the java program that is running the drop table, inserts,
the 1st select and the CREATE VIEW.
Thanks for your ideas,
Brian
2009-02-28 00:30:00.01572-08 | 2009-02-28 00:30:00.015758-08 | drop
table ts_defects_20090225
2009-02-28 00:30:00.693353-08 | 2009-02-28 00:30:00.69337-08 | select
transetdef0_.ts_id as ts1_85_0_,
2009-02-28 00:30:01.875671-08 | 2009-02-28 00:30:01.875911-08 | insert
into ts_defects_20090228 (ts_id,
2009-02-28 00:30:01.875673-08 | 2009-02-28 00:30:01.875911-08 | insert
into ts_defects_20090228 (ts_id,
2009-02-28 00:30:01.875907-08 | 2009-02-28 00:30:01.87611-08 | insert
into ts_defects_20090228 (ts_id,
2009-02-28 00:30:01.87615-08 | 2009-02-28 00:30:01.876334-08 | insert
into ts_defects_20090228 (ts_id,
2009-02-28 00:30:01.87694-08 | 2009-02-28 00:30:01.877153-08 | insert
into ts_defects_20090228 (ts_id,
2009-02-28 00:30:01.876952-08 | 2009-02-28 00:30:01.877171-08 | insert
into ts_defects_20090228 (ts_id,
2009-02-28 00:30:01.876965-08 | 2009-02-28 00:30:01.87716-08 | insert
into ts_defects_20090228 (ts_id,
2009-02-28 00:30:01.877267-08 | 2009-02-28 00:30:01.877483-08 | insert
into ts_defects_20090228 (ts_id,
2009-02-28 00:30:01.877928-08 | 2009-02-28 00:30:01.878101-08 | insert
into ts_defects_20090228 (ts_id,
2009-02-28 00:30:06.822733-08 | 2009-02-28 00:30:06.822922-08 | insert
into ts_defects_20090228 (ts_id,
2009-02-28 01:01:00.95051-08 | 2009-02-28 01:01:00.950605-08 | CREATE
VIEW TranSetGroupSlaPerformanceA
2009-02-28 09:12:33.181039-08 | 2009-02-28 09:12:33.181039-08 | SELECT
c.oid, c.relname, pg_get_userbyi
2009-02-28 09:19:47.335621-08 | 2009-02-28 09:19:47.335621-08 | SELECT
c.oid, c.relname, pg_get_userbyi
2009-02-28 10:52:36.638467-08 | 2009-02-28 10:52:36.638467-08 | SELECT
c.oid, c.relname, pg_get_userbyi
2009-02-28 11:01:05.023126-08 | 2009-02-28 11:01:05.023126-08 | SELECT
c.oid, c.relname, pg_get_userbyi
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: brian.cox@ca.com
Subject: Re: "slow" queries
In-Reply-To: <49A9F834.8060100@ca.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox