Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 7C916635D55 for ; Mon, 23 Mar 2009 16:36:41 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 03364-01-9 for ; Mon, 23 Mar 2009 16:36:34 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from smtp2.ca.com (smtp2.ca.com [141.202.248.13]) by mail.postgresql.org (Postfix) with ESMTP id B7ACD633755 for ; Mon, 23 Mar 2009 16:34:29 -0300 (ADT) Received: from athena.ca.com ([130.200.164.17]) by smtp2.ca.com with Microsoft SMTPSVC(6.0.3790.3959); Mon, 23 Mar 2009 15:34:27 -0400 Message-ID: <49C7E42B.9070002@ca.com> Date: Mon, 23 Mar 2009 12:34:03 -0700 From: Brian Cox User-Agent: Thunderbird 2.0.0.19 (X11/20081209) MIME-Version: 1.0 To: "pgsql-performance@postgresql.org" Subject: multiple threads inserting into the same table Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-OriginalArrivalTime: 23 Mar 2009 19:34:27.0835 (UTC) FILETIME=[61374CB0:01C9ABEE] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200903/331 X-Sequence-Number: 33253 The application log shows that 99652 rows are being inserted into relation ts_stats_transet_user_daily. 5 threads are doing the inserts. The schema is lengthy, but it has a synthetic primary key (ts_id int8 not null) and the following constraints: alter table ts_stats_transet_user_daily add constraint FK8ED105ED9DADA24 foreign key (ts_transet_id) references ts_transets; alter table ts_stats_transet_user_daily add constraint K8ED105ED545ADA6D foreign key (ts_user_id) references ts_users; This relation currently has 456532 rows and is not partitioned. The inserts have been going on now for almost 1 hour -- not exactly speedy. Here's what I find on the postgres side: cemdb=> select current_query, procpid, xact_start from pg_stat_activity; current_query | procpid | xact_start ------------------------------------------------------------------+---------+------------------------------- in transaction | 15147 | 2009-03-23 12:08:31.604433-07 | 15382 | select current_query, procpid, xact_start from pg_stat_activity; | 15434 | 2009-03-23 12:10:38.913764-07 | 15152 | | 15150 | | 15156 | in transaction | 15183 | 2009-03-23 12:09:50.864992-07 in transaction | 15186 | 2009-03-23 12:10:07.955838-07 | 15188 | | 15192 | in transaction | 15193 | 2009-03-23 12:10:07.955859-07 in transaction | 15194 | 2009-03-23 12:08:59.940101-07 (12 rows) cemdb=> select c.oid,c.relname,l.pid,l.mode,l.granted from pg_class c join pg_locks l on c.oid=l.relation order by l.mode; oid | relname | pid | mode | granted ----------+-----------------------------+-------+------------------+--------- 26493289 | ts_users_pkey | 15183 | AccessShareLock | t 26493267 | ts_transets_pkey | 15186 | AccessShareLock | t 1259 | pg_class | 15434 | AccessShareLock | t 26493289 | ts_users_pkey | 15147 | AccessShareLock | t 10969 | pg_locks | 15434 | AccessShareLock | t 26493267 | ts_transets_pkey | 15193 | AccessShareLock | t 26493289 | ts_users_pkey | 15194 | AccessShareLock | t 2662 | pg_class_oid_index | 15434 | AccessShareLock | t 26493267 | ts_transets_pkey | 15194 | AccessShareLock | t 26493289 | ts_users_pkey | 15193 | AccessShareLock | t 26493267 | ts_transets_pkey | 15147 | AccessShareLock | t 26493289 | ts_users_pkey | 15186 | AccessShareLock | t 26493267 | ts_transets_pkey | 15183 | AccessShareLock | t 2663 | pg_class_relname_nsp_index | 15434 | AccessShareLock | t 26472890 | ts_stats_transet_user_daily | 15147 | RowExclusiveLock | t 26472890 | ts_stats_transet_user_daily | 15186 | RowExclusiveLock | t 26472890 | ts_stats_transet_user_daily | 15193 | RowExclusiveLock | t 26472890 | ts_stats_transet_user_daily | 15194 | RowExclusiveLock | t 26472890 | ts_stats_transet_user_daily | 15183 | RowExclusiveLock | t 26473252 | ts_users | 15194 | RowShareLock | t 26472508 | ts_transets | 15183 | RowShareLock | t 26472508 | ts_transets | 15193 | RowShareLock | t 26473252 | ts_users | 15193 | RowShareLock | t 26473252 | ts_users | 15183 | RowShareLock | t 26472508 | ts_transets | 15147 | RowShareLock | t 26473252 | ts_users | 15186 | RowShareLock | t 26472508 | ts_transets | 15186 | RowShareLock | t 26473252 | ts_users | 15147 | RowShareLock | t 26472508 | ts_transets | 15194 | RowShareLock | t (29 rows) cemdb=> select c.oid,c.relname,l.pid,l.mode,l.granted from pg_class c join pg_locks l on c.oid=l.relation order by l.pid; oid | relname | pid | mode | granted ----------+-----------------------------+-------+------------------+--------- 26493289 | ts_users_pkey | 15147 | AccessShareLock | t 26473252 | ts_users | 15147 | RowShareLock | t 26493267 | ts_transets_pkey | 15147 | AccessShareLock | t 26472508 | ts_transets | 15147 | RowShareLock | t 26472890 | ts_stats_transet_user_daily | 15147 | RowExclusiveLock | t 26472890 | ts_stats_transet_user_daily | 15150 | RowExclusiveLock | t 26493289 | ts_users_pkey | 15150 | AccessShareLock | t 26493267 | ts_transets_pkey | 15150 | AccessShareLock | t 26472508 | ts_transets | 15150 | RowShareLock | t 26473252 | ts_users | 15150 | RowShareLock | t 26472890 | ts_stats_transet_user_daily | 15186 | RowExclusiveLock | t 26473252 | ts_users | 15186 | RowShareLock | t 26493267 | ts_transets_pkey | 15186 | AccessShareLock | t 26472508 | ts_transets | 15186 | RowShareLock | t 26493289 | ts_users_pkey | 15186 | AccessShareLock | t 26472890 | ts_stats_transet_user_daily | 15193 | RowExclusiveLock | t 26493289 | ts_users_pkey | 15193 | AccessShareLock | t 26473252 | ts_users | 15193 | RowShareLock | t 26472508 | ts_transets | 15193 | RowShareLock | t 26493267 | ts_transets_pkey | 15193 | AccessShareLock | t 26493267 | ts_transets_pkey | 15194 | AccessShareLock | t 26472508 | ts_transets | 15194 | RowShareLock | t 26493289 | ts_users_pkey | 15194 | AccessShareLock | t 26472890 | ts_stats_transet_user_daily | 15194 | RowExclusiveLock | t 26473252 | ts_users | 15194 | RowShareLock | t 1259 | pg_class | 15434 | AccessShareLock | t 2663 | pg_class_relname_nsp_index | 15434 | AccessShareLock | t 2662 | pg_class_oid_index | 15434 | AccessShareLock | t 10969 | pg_locks | 15434 | AccessShareLock | t (29 rows) cemdb=> select c.oid,c.relname,l.pid,l.mode,l.granted from pg_class c join pg_locks l on c.oid=l.relation order by c.relname; oid | relname | pid | mode | granted ----------+-----------------------------+-------+------------------+--------- 1259 | pg_class | 15434 | AccessShareLock | t 2662 | pg_class_oid_index | 15434 | AccessShareLock | t 2663 | pg_class_relname_nsp_index | 15434 | AccessShareLock | t 10969 | pg_locks | 15434 | AccessShareLock | t 26472890 | ts_stats_transet_user_daily | 15150 | RowExclusiveLock | t 26472890 | ts_stats_transet_user_daily | 15193 | RowExclusiveLock | t 26472890 | ts_stats_transet_user_daily | 15194 | RowExclusiveLock | t 26472890 | ts_stats_transet_user_daily | 15192 | RowExclusiveLock | t 26472890 | ts_stats_transet_user_daily | 15186 | RowExclusiveLock | t 26472508 | ts_transets | 15193 | RowShareLock | t 26472508 | ts_transets | 15186 | RowShareLock | t 26472508 | ts_transets | 15194 | RowShareLock | t 26472508 | ts_transets | 15192 | RowShareLock | t 26472508 | ts_transets | 15150 | RowShareLock | t 26493267 | ts_transets_pkey | 15192 | AccessShareLock | t 26493267 | ts_transets_pkey | 15194 | AccessShareLock | t 26493267 | ts_transets_pkey | 15150 | AccessShareLock | t 26493267 | ts_transets_pkey | 15186 | AccessShareLock | t 26493267 | ts_transets_pkey | 15193 | AccessShareLock | t 26473252 | ts_users | 15150 | RowShareLock | t 26473252 | ts_users | 15194 | RowShareLock | t 26473252 | ts_users | 15186 | RowShareLock | t 26473252 | ts_users | 15193 | RowShareLock | t 26473252 | ts_users | 15192 | RowShareLock | t 26493289 | ts_users_pkey | 15186 | AccessShareLock | t 26493289 | ts_users_pkey | 15192 | AccessShareLock | t 26493289 | ts_users_pkey | 15193 | AccessShareLock | t 26493289 | ts_users_pkey | 15194 | AccessShareLock | t 26493289 | ts_users_pkey | 15150 | AccessShareLock | t (29 rows) Any ideas as to what is happening here would be appreciated. Thanks, Brian