Skip site navigation (1) Skip section navigation (2)

multiple threads inserting into the same table

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 (view raw or flat)
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

Responses

pgsql-performance by date

Next:From: David WilsonDate: 2009-03-23 19:49:34
Subject: Re: multiple threads inserting into the same table
Previous:From: Robert HaasDate: 2009-03-23 17:15:20
Subject: Re: Need help with one query

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group