> please post the complete outputs of > > SHOW PROCESSLIST; > > and > > SHOW INNODB STATUS > > during such CPU peak. > > > Daniel > > Best regards, > > Heikki Tuuri > Innobase Oy > Foreign keys, transactions, and row level locking for MySQL > InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM > tables > http://www.innodb.com/order.php
Ok, outputs are as follows (please excuse the wrapping) mysql> show processlist; +---------+---------------+--------------------------------+---------+-------------+--------+-----------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +---------+---------------+--------------------------------+---------+-------------+--------+-----------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+ | 1 | system user | | NULL | Connect | 343096 | Waiting for master to send event | NULL | | 2 | system user | | NULL | Connect | 1 | Has read all relay log; waiting for the I/O slave thread to update it | NULL | | 123 | qmail_cluster | 202.9.50.49:56749 | qmaildb | Sleep | 2 | | NULL | | 575 | qmail_cluster | coffee.telenet.net.au:54590 | qmaildb | Query | 8 | Copying to tmp table | select message_id from qmaildb.messages where deleted=1 and (server1=2 or server2=2) order by rand() | | 588 | qmail_cluster | 202.9.50.49:56752 | qmaildb | Query | 27 | Copying to tmp table | select message_id from qmaildb.messages where deleted=1 and (server1=4 or server2=4) order by rand() | | 660 | qmail_cluster | gourami.telenet.net.au:59450 | qmaildb | Query | 2 | Copying to tmp table | select message_id from qmaildb.messages where deleted=1 and (server1=5 or server2=5) order by rand() | | 2211 | harlequin | harlequin.telenet.net.au:32836 | NULL | Binlog Dump | 342934 | Has sent all binlog to slave; waiting for binlog to be updated | NULL | | 16120 | horde | evo.telenet.net.au:47653 | horde | Sleep | 38 | | NULL | | 612578 | horde | evo.telenet.net.au:43909 | horde | Sleep | 2363 | | NULL | | 2283169 | horde | evo.telenet.net.au:48247 | horde | Sleep | 8 | | NULL | | 2315669 | horde | evo.telenet.net.au:45055 | horde | Sleep | 642 | | NULL | | 2319544 | postaci | evo.telenet.net.au:48162 | postaci | Sleep | 1339 | | NULL | | 2331554 | postaci | evo.telenet.net.au:56971 | postaci | Sleep | 1180 | | NULL | | 2338172 | horde | evo.telenet.net.au:34624 | horde | Sleep | 3192 | | NULL | | 2339043 | horde | evo.telenet.net.au:35374 | horde | Sleep | 57 | | NULL | | 2487489 | qmail_cluster | 202.9.50.49:53340 | qmaildb | Query | 27 | Sending data | SELECT message_id, server1, server2, uid FROM qmaildb.messages msg WHERE deleted=1 AND (server1=4 OR | | 2671866 | ccclogin | coffee.telenet.net.au:54950 | qmaildb | Sleep | 5 | | NULL | | 2850407 | qmail_cluster | coffee.telenet.net.au:36555 | qmaildb | Query | 24 | Sending data | SELECT message_id, server1, server2, uid FROM qmaildb.messages msg WHERE deleted=1 AND (server1=2 OR | | 2865207 | postaci | evo.telenet.net.au:56580 | postaci | Sleep | 940 | | NULL | | 2928221 | horde | evo.telenet.net.au:47851 | horde | Sleep | 1886 | | NULL | | 2944091 | qmail_cluster | gourami.telenet.net.au:40864 | qmaildb | Query | 28 | Sending data | SELECT message_id, server1, server2, uid FROM qmaildb.messages msg WHERE deleted=1 AND (server1=5 OR | | 2947708 | postaci | evo.telenet.net.au:41855 | postaci | Sleep | 1115 | | NULL | | 2947803 | postaci | evo.telenet.net.au:41931 | postaci | Sleep | 5888 | | NULL | | 2948204 | postaci | evo.telenet.net.au:42349 | postaci | Sleep | 863 | | NULL | | 2948400 | postaci | evo.telenet.net.au:42513 | postaci | Sleep | 411 | | NULL | | 2948792 | postaci | evo.telenet.net.au:42853 | postaci | Sleep | 779 | | NULL | | 2971503 | horde | evo.telenet.net.au:33067 | horde | Sleep | 1009 | | NULL | | 2972782 | horde | evo.telenet.net.au:34206 | horde | Sleep | 148 | | NULL | | 2987009 | horde | evo.telenet.net.au:48692 | horde | Sleep | 82 | | NULL | | 2987011 | horde | evo.telenet.net.au:48695 | horde | Sleep | 14311 | | NULL | | 3005330 | horde | evo.telenet.net.au:38312 | horde | Sleep | 679 | | NULL | | 3006343 | horde | evo.telenet.net.au:39585 | horde | Sleep | 12134 | | NULL | | 3006602 | postaci | evo.telenet.net.au:39957 | postaci | Sleep | 874 | | NULL | | 3006921 | horde | evo.telenet.net.au:40353 | horde | Sleep | 12050 | | NULL | | 3044123 | horde | evo.telenet.net.au:50302 | horde | Sleep | 8492 | | NULL | | 3072067 | qmail_cluster | gourami.telenet.net.au:57564 | qmaildb | Sleep | 3 | | NULL | | 3075438 | postaci | evo.telenet.net.au:52293 | postaci | Sleep | 9 | | NULL | | 3117287 | qmail_cluster | mummichog.telenet.net.au:35852 | qmaildb | Sleep | 2005 | | NULL | | 3131765 | qmailuser | mummichog.telenet.net.au:49240 | qmaildb | Sleep | 768 | | NULL | | 3137611 | qmail_cluster | evo.telenet.net.au:51925 | qmaildb | Sleep | 448 | | NULL | | 3140897 | qmail_cluster | mummichog.telenet.net.au:56867 | qmaildb | Sleep | 290 | | NULL | | 3143115 | qmail_cluster | mummichog.telenet.net.au:58991 | qmaildb | Sleep | 122 | | NULL | | 3143970 | qmailuser | evo.telenet.net.au:56997 | qmaildb | Sleep | 48 | | NULL | | 3144093 | qmail_cluster | evo.telenet.net.au:57105 | qmaildb | Sleep | 33 | | NULL | | 3144186 | qmailuser | evo.telenet.net.au:57198 | qmaildb | Sleep | 23 | | NULL | | 3144408 | qmailuser | mummichog.telenet.net.au:60218 | qmaildb | Sleep | 9 | | NULL | | 3144476 | root | localhost | NULL | Query | 0 | NULL | show processlist | | 3144479 | qmail_cluster | evo.telenet.net.au:57380 | qmaildb | Sleep | 3 | | NULL | | 3144480 | qmail_cluster | evo.telenet.net.au:57381 | qmaildb | Query | 0 | update | insert into qmaildb.messages (uid, server1, server2, deleted, size, delivered) values (65076, 0, 0, | | 3144490 | qmailuser | evo.telenet.net.au:57429 | qmaildb | Sleep | 1 | | NULL | | 3144493 | qmail_cluster | evo.telenet.net.au:57440 | qmaildb | Sleep | 1 | | NULL | | 3144496 | qmailuser | evo.telenet.net.au:57448 | qmaildb | Sleep | 1 | | NULL | | 3144499 | qmailuser | mummichog.telenet.net.au:60306 | qmaildb | Sleep | 1 | | NULL | | 3144500 | qmail_cluster | mummichog.telenet.net.au:60308 | qmaildb | Sleep | 0 | | NULL | +---------+---------------+--------------------------------+---------+-------------+--------+-----------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+ 54 rows in set (0.02 sec) show innodb status: ---TRANSACTION 0 44073587, not started, process no 10733, OS thread id 4291100724 MySQL thread id 3144776, query id 19034295 evo.telenet.net.au 202.9.50.45 qmail_cluster ---TRANSACTION 0 44073208, not started, process no 10618, OS thread id 4290629693 MySQL thread id 3144661, query id 19033661 evo.telenet.net.au 202.9.50.45 qmail_cluster ---TRANSACTION 0 44069589, not started, process no 9048, OS thread id 4284297289 MySQL thread id 3143115, query id 19017978 mummichog.telenet.net.au 202.9.50.51 qmail_cluster ---TRANSACTION 0 44067708, not started, process no 6822, OS thread id 4275212319 MySQL thread id 3140897, query id 19004276 mummichog.telenet.net.au 202.9.50.51 qmail_cluster ---TRANSACTION 0 44065988, not started, process no 3503, OS thread id 4261752904 MySQL thread id 3137611, query id 18986756 evo.telenet.net.au 202.9.50.45 qmail_cluster ---TRANSACTION 0 44043593, not started, process no 15586, OS thread id 4178505774 MySQL thread id 3117287, query id 18867274 mummichog.telenet.net.au 202.9.50.51 qmail_cluster ---TRANSACTION 0 44054022, not started, process no 6073, OS thread id 4007092236 MySQL thread id 3075438, query id 19031849 evo.telenet.net.au 202.9.50.45 postaci ---TRANSACTION 0 44059764, not started, process no 1929, OS thread id 3725139984 MySQL thread id 3006602, query id 18948305 evo.telenet.net.au 202.9.50.45 postaci ---TRANSACTION 0 44061015, not started, process no 8839, OS thread id 3488350247 MySQL thread id 2948792, query id 18955568 evo.telenet.net.au 202.9.50.45 postaci ---TRANSACTION 0 44056018, not started, process no 8447, OS thread id 3486744600 MySQL thread id 2948400, query id 19034819 evo.telenet.net.au 202.9.50.45 postaci Sending data select user_id from tblLoggedUsers where hash='[EMAIL PROTECTED]' ---TRANSACTION 0 44051806, not started, process no 22209, OS thread id 3145986080 MySQL thread id 2865207, query id 18944101 evo.telenet.net.au 202.9.50.45 postaci ---TRANSACTION 0 44059827, not started, process no 8251, OS thread id 3485941789 MySQL thread id 2948204, query id 18949126 evo.telenet.net.au 202.9.50.45 postaci ---TRANSACTION 0 44052258, not started, process no 7755, OS thread id 3483910169 MySQL thread id 2947708, query id 18929646 evo.telenet.net.au 202.9.50.45 postaci ---TRANSACTION 0 43809778, not started, process no 7850, OS thread id 3484299286 MySQL thread id 2947803, query id 18574076 evo.telenet.net.au 202.9.50.45 postaci ---TRANSACTION 0 44054716, not started, process no 5956, OS thread id 960143396 MySQL thread id 2331554, query id 18924231 evo.telenet.net.au 202.9.50.45 postaci ---TRANSACTION 0 43986852, not started, process no 26379, OS thread id 910950418 MySQL thread id 2319544, query id 18912393 evo.telenet.net.au 202.9.50.45 postaci ---TRANSACTION 0 44072105, not started, process no 5421, OS thread id 2736185 MySQL thread id 660, query id 19034833 gourami.telenet.net.au 202.9.50.50 qmail_cluster ---TRANSACTION 0 44073743, ACTIVE 3 sec, process no 4116, OS thread id 3469094929 fetching rows, thread declared inside InnoDB 436 mysql tables in use 1, locked 0 MySQL thread id 2944091, query id 19034643 gourami.telenet.net.au 202.9.50.50 qmail_cluster Sending data SELECT message_id, server1, server2, uid FROM qmaildb.messages msg WHERE deleted=1 AND (server1=5 OR server2=5) Trx read view will not see trx with id >= 0 44073744, sees < 0 44072105 ---TRANSACTION 0 44073713, ACTIVE 3 sec, process no 5318, OS thread id 2387991 fetching rows, thread declared inside InnoDB 465 mysql tables in use 1, locked 0 MySQL thread id 575, query id 19034577 coffee.telenet.net.au 202.9.50.47 qmail_cluster Copying to tmp table select message_id, server1, server2 from qmaildb.messages where (((server2!=0 and server1=0) or (server1!=0 and server2=0)) and (server1!=2 and server2!=2)) and deleted=0 order by rand() limit 300 Trx read view will not see trx with id >= 0 44073714, sees < 0 44072105 ---TRANSACTION 0 44073611, ACTIVE 4 sec, process no 32609, OS thread id 1598853146 fetching rows, thread declared inside InnoDB 110 mysql tables in use 1, locked 0 MySQL thread id 2487489, query id 19034347 202.9.50.49 qmail_cluster Sending data SELECT message_id, server1, server2, uid FROM qmaildb.messages msg WHERE deleted=1 AND (server1=4 OR server2=4) Trx read view will not see trx with id >= 0 44073612, sees < 0 44071441 ---TRANSACTION 0 44072754, ACTIVE 25 sec, process no 5335, OS thread id 2441230 fetching rows, thread declared inside InnoDB 458 mysql tables in use 1, locked 0 MySQL thread id 588, query id 19032089 202.9.50.49 qmail_cluster Copying to tmp table select message_id, server1, server2 from qmaildb.messages where (((server2!=0 and server1=0) or (server1!=0 and server2=0)) and (server1!=4 and server2!=4)) and deleted=0 order by rand() limit 300 Trx read view will not see trx with id >= 0 44072755, sees < 0 44071441 ---TRANSACTION 0 44072732, ACTIVE 27 sec, process no 7381, OS thread id 3085365276 fetching rows, thread declared inside InnoDB 117 mysql tables in use 1, locked 0 MySQL thread id 2850407, query id 19031954 coffee.telenet.net.au 202.9.50.47 qmail_cluster Sending data SELECT message_id, server1, server2, uid FROM qmaildb.messages msg WHERE deleted=1 AND (server1=2 OR server2=2) Trx read view will not see trx with id >= 0 44072733, sees < 0 44070982 -------- FILE I/O -------- I/O thread 0 state: waiting for i/o request (insert buffer thread) I/O thread 1 state: waiting for i/o request (log thread) I/O thread 2 state: waiting for i/o request (read thread) I/O thread 3 state: waiting for i/o request (write thread) Pending normal aio reads: 9, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 1910234986 OS file reads, 3746792 OS file writes, 1672341 OS fsyncs 4959.76 reads/s, 19921 avg bytes/read, 14.75 writes/s, 6.25 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf for space 0: size 1, free list len 387, seg size 389, 739868 inserts, 739904 merged recs, 355995 merges Hash table size 34679, used cells 0, node heap has 1 buffer(s) 3766.31 hash searches/s, 1029.24 non-hash searches/s --- LOG --- Log sequence number 0 4058824418 Log flushed up to 0 4058824418 Last checkpoint at 0 4058823011 0 pending log writes, 0 pending chkp writes 1226356 log i/o's done, 4.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 17258796; in additional pool allocated 1021952 Buffer pool size 512 Free buffers 0 Database pages 511 Modified db pages 0 Pending reads 9 Pending writes: LRU 0, flush list 0, single page 0 Pages read 2330404897, created 9146, written 2656110 6030.74 reads/s, 0.00 creates/s, 11.50 writes/s Buffer pool hit rate 935 / 1000 -------------- ROW OPERATIONS -------------- 5 queries inside InnoDB, 0 queries in queue Main thread process no. 4662, id 28680, state: sleeping Number of rows inserted 118044, updated 760157, deleted 167945, read 1657072353 0.50 inserts/s, 1.00 updates/s, 1.50 deletes/s, 525082.23 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ Regards, Daniel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
