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

Re: multiple threads inserting into the same table

From: Brian Cox <brian(dot)cox(at)ca(dot)com>
To: "David Wilson [david(dot)t(dot)wilson(at)gmail(dot)com]" <david(dot)t(dot)wilson(at)gmail(dot)com>
Cc: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: multiple threads inserting into the same table
Date: 2009-03-23 21:05:03
Message-ID: 49C7F97F.2000100@ca.com (view raw or flat)
Thread:
Lists: pgsql-performance
David Wilson [david(dot)t(dot)wilson(at)gmail(dot)com] wrote:

> How many indexes are there on ts_stats_transset_user_daily?
10:
create index ts_stats_transet_user_daily_DayIndex on 
ts_stats_transet_user_daily (ts_day);
create index ts_stats_transet_user_daily_HourIndex on 
ts_stats_transet_user_daily (ts_hour);
create index ts_stats_transet_user_daily_LastAggregatedRowIndex on 
ts_stats_transet_user_daily (ts_last_aggregated_row);
create index ts_stats_transet_user_daily_MonthIndex on 
ts_stats_transet_user_daily (ts_month);
create index ts_stats_transet_user_daily_StartTimeIndex on 
ts_stats_transet_user_daily (ts_interval_start_time);
create index ts_stats_transet_user_daily_TranSetIncarnationIdIndex on 
ts_stats_transet_user_daily (ts_transet_incarnation_id);
create index ts_stats_transet_user_daily_TranSetIndex on 
ts_stats_transet_user_daily (ts_transet_id);
create index ts_stats_transet_user_daily_UserIncarnationIdIndex on 
ts_stats_transet_user_daily (ts_user_incarnation_id);
create index ts_stats_transet_user_daily_UserIndex on 
ts_stats_transet_user_daily (ts_user_id);
create index ts_stats_transet_user_daily_WeekIndex on 
ts_stats_transet_user_daily (ts_week);
create index ts_stats_transet_user_daily_YearIndex on 
ts_stats_transet_user_daily (ts_year);


> Are these rows being inserted in individual insert statements, or are
> they batched in some fashion?
individual insert stmts in a single transaction.

> What's the disk/cpu activity on your system look like?
The app is using 100% CPU and I haven't figured out why, but the insert 
threads are generally doing socket reads. But they can't be completely
blocked as 1 thread is doing a read in one thread dump and is doing 
processing (preparing for another insert) in a later thread dump. So,
it looks as if the inserts are taking a l-o-n-g time.

Here's the output of vmstat and iostat. I've never looked at this info
before, so I'm not sure what it says.

[root(at)rdl64xeoserv01 log]# vmstat
procs -----------memory---------- ---swap-- -----io---- --system-- 
----cpu----
  r  b   swpd   free   buff  cache   si   so    bi    bo   in    cs us 
sy id wa
  1  0      0 9194740  58676 20980264    0    0     8    21    1     2 
2  0 98  0
[root(at)rdl64xeoserv01 log]# iostat
Linux 2.6.9-42.ELsmp (rdl64xeoserv01.ca.com)    03/23/2009

avg-cpu:  %user   %nice    %sys %iowait   %idle
            1.71    0.00    0.09    0.02   98.18

Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
sda              10.46       126.23       343.38  304224943  827588034
sda1              0.00         0.00         0.00       1428         58
sda2             57.73       126.23       343.37  304221426  827576144
sda3              0.00         0.00         0.00       1073          0


pgsql-performance by date

Next:From: Tom LaneDate: 2009-03-23 22:57:34
Subject: Re: multiple threads inserting into the same table
Previous:From: Brian CoxDate: 2009-03-23 20:52:06
Subject: Re: multiple threads inserting into the same table

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