pg.ddx.io pgsql-performance@postgresql.org mailing list archive help / color / mirror / Atom feed From: Brian Cox <brian.cox@ca.com>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: multiple threads inserting into the same table
Date: Mon, 23 Mar 2009 12:34:03 -0700
Message-ID: <49C7E42B.9070002@ca.com> (raw )
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
------------------------------------------------------------------+---------+-------------------------------
<IDLE> in transaction |
15147 | 2009-03-23 12:08:31.604433-07
<IDLE> |
15382 |
select current_query, procpid, xact_start from pg_stat_activity; |
15434 | 2009-03-23 12:10:38.913764-07
<IDLE> |
15152 |
<IDLE> |
15150 |
<IDLE> |
15156 |
<IDLE> in transaction |
15183 | 2009-03-23 12:09:50.864992-07
<IDLE> in transaction |
15186 | 2009-03-23 12:10:07.955838-07
<IDLE> |
15188 |
<IDLE> |
15192 |
<IDLE> in transaction |
15193 | 2009-03-23 12:10:07.955859-07
<IDLE> 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
view thread (9+ messages) latest in thread
Message-ID: <49C7E42B.9070002@ca.com>
Permalink: ../49C7E42B.9070002@ca.com/
Also on: postgresql.org/message-id/49C7E42B.9070002@ca.com
copy link · copy postgr.es
reply 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: multiple threads inserting into the same table
In-Reply-To: <49C7E42B.9070002@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