multiple threads inserting into the same table - Mailing list pgsql-performance
| From | Brian Cox |
|---|---|
| Subject | multiple threads inserting into the same table |
| Date | |
| Msg-id | 49C7E42B.9070002@ca.com Whole thread Raw |
| Responses |
Re: multiple threads inserting into the same table
Re: multiple threads inserting into the same table |
| List | pgsql-performance |
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
pgsql-performance by date: