| From: | Brian Cox <brian(dot)cox(at)ca(dot)com> |
|---|---|
| To: | "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> |
| Subject: | multiple threads inserting into the same table |
| Date: | 2009-03-23 19:34:03 |
| Message-ID: | 49C7E42B.9070002@ca.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | 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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | David Wilson | 2009-03-23 19:49:34 | Re: multiple threads inserting into the same table |
| Previous Message | Robert Haas | 2009-03-23 17:15:20 | Re: Need help with one query |