From: Brian Cox <brian.cox@ca.com>
To: David Wilson [david.t.wilson@gmail.com] <david.t.wilson@gmail.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: multiple threads inserting into the same table
Date: Mon, 23 Mar 2009 14:05:03 -0700
Message-ID: <49C7F97F.2000100@ca.com> (raw)
David Wilson [david.t.wilson@gmail.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@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@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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: brian.cox@ca.com, david.t.wilson@gmail.com
Subject: Re: multiple threads inserting into the same table
In-Reply-To: <49C7F97F.2000100@ca.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox