Re: multiple threads inserting into the same table

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