Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 422E3632C45 for ; Mon, 23 Mar 2009 18:05:47 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 26723-07 for ; Mon, 23 Mar 2009 18:05:39 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from smtp1.ca.com (smtp1.ca.com [141.202.248.21]) by mail.postgresql.org (Postfix) with ESMTP id A73956322F0 for ; Mon, 23 Mar 2009 18:05:42 -0300 (ADT) Received: from athena.ca.com ([130.200.164.17]) by smtp1.ca.com with Microsoft SMTPSVC(6.0.3790.3959); Mon, 23 Mar 2009 17:05:34 -0400 Message-ID: <49C7F97F.2000100@ca.com> Date: Mon, 23 Mar 2009 14:05:03 -0700 From: Brian Cox User-Agent: Thunderbird 2.0.0.19 (X11/20081209) MIME-Version: 1.0 To: "David Wilson [david.t.wilson@gmail.com]" CC: "pgsql-performance@postgresql.org" Subject: Re: multiple threads inserting into the same table Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-OriginalArrivalTime: 23 Mar 2009 21:05:40.0363 (UTC) FILETIME=[1F18CDB0:01C9ABFB] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200903/338 X-Sequence-Number: 33260 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