MySQL Forums
Forum List  »  Performance

large set of Insert causing sluggiesh performance?
Posted by: Bharat Satyanarayan
Date: August 25, 2006 10:39AM

We are logging the all the users action into a MYISAM table.

My Biggest customer, has around 400-500 users, and on an average we have 1000 - 1500 queries /sec being fired.

now when ever we turn on user action logging, the application seems to become sluggish. can this be because of this? i mean because of huge number of writes, mysql is spending more time in writing to disk and hence everything else is getting slow.
some information -

i am using insert delayed for these insert.

create table syntax:

CREATE TABLE `logxxx` (
`id` int(11) unsigned NOT NULL auto_increment,
`userid` int(11) unsigned NOT NULL default '0',
`aid` int(11) unsigned NOT NULL default '0',
`xmldata` text,
`status` enum('logged','parsed','failed') NOT NULL default 'logged',
`df` tinyint(3) unsigned NOT NULL default '0',
`timestamp` timestamp(14) NOT NULL,
`comments` text,
`syn` enum('no','yes') NOT NULL default 'no',
`optionid` tinyint(2) NOT NULL default '0',
`usrsactionid` tinyint(2) NOT NULL default '0',
`pid` int(11) NOT NULL default '0',
`Iid` int(11) NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `eid` (`aid`,`status`,`df`),
KEY `pid` (`pid`)
) TYPE=MyISAM DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC

with logging enabled:

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 4080272 94856 2870704 0 0 10 21 67 119 5 1 92 1



iostat -x

avg-cpu: %user %nice %sys %iowait %idle
5.50 0.01 1.44 0.63 92.42

Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util
cciss/c0d0 0.12 12.98 0.28 1.72 11.87 122.58 5.94 61.29 67.21 0.09 41.11 0.68 0.14
cciss/c1d0 0.15 7.59 11.69 19.58 152.78 217.41 76.39 108.71 11.84 0.30 9.66 1.63 5.10
cciss/c1d1 0.04 0.00 0.00 0.00 0.32 0.00 0.16 0.00 146.13 0.00 8.08 1.45 0.00


with out logging:

vmstat=

procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
r b swpd free buff cache si so bi bo in cs us sy id wa
0 0 0 4480816 89752 2661568 0 0 10 16 55 93 5 1 93 1


iostat -x

Linux 2.6.9-5.ELsmp
avg-cpu: %user %nice %sys %iowait %idle
5.08 0.01 1.28 0.64 93.00

Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util
cciss/c0d0 0.12 5.61 0.28 1.34 11.85 58.57 5.92 29.29 43.53 0.04 26.02 0.61 0.10
cciss/c1d0 0.15 6.89 11.31 17.97 144.09 198.87 72.05 99.44 11.71 0.27 9.19 1.78 5.22
cciss/c1d1 0.04 0.00 0.00 0.00 0.33 0.00 0.16 0.00 146.13 0.00 9.64 2.03 0.00


of course at the same time, they had some RAM problem, net work etc.. all these could have caused the sluggish ness.

but more specifically , i am looking is for, how to determine, that whether this process actually blocks or not?

thanks
bharat

Options: ReplyQuote


Subject
Views
Written By
Posted
large set of Insert causing sluggiesh performance?
1848
August 25, 2006 10:39AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.