Re: multiple threads inserting into the same table

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