multiple threads inserting into the same table
От | Brian Cox |
---|---|
Тема | multiple threads inserting into the same table |
Дата | |
Msg-id | 49C7E42B.9070002@ca.com обсуждение исходный текст |
Ответы |
Re: multiple threads inserting into the same table
(David Wilson <david.t.wilson@gmail.com>)
Re: multiple threads inserting into the same table (Tom Lane <tgl@sss.pgh.pa.us>) |
Список | 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 по дате отправления: