Skip site navigation (1) Skip section navigation (2)

"slow" queries

From: Brian Cox <brian(dot)cox(at)ca(dot)com>
To: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: "slow" queries
Date: 2009-03-01 02:51:32
Message-ID: 49A9F834.8060100@ca.com (view raw or flat)
Thread:
Lists: pgsql-performance
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

Responses

pgsql-performance by date

Next:From: Robert HaasDate: 2009-03-01 03:15:17
Subject: Re: "slow" queries
Previous:From: Alexander StauboDate: 2009-02-28 16:20:22
Subject: Re: Bad plan for nested loop + limit

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group