Daniel,
it is simply processing big SELECT queries. Maybe their optimization changed lately? You should tune your queries.
You should also tune InnoDB, because you are running with the default 8 MB buffer pool size, and it is reading in 6000 pages per second!
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
| 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() |
---------------------- 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
----- Original Message ----- From: "Daniel Andersen" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.myodbc
Sent: Monday, December 20, 2004 3:16 AM
Subject: Re: Weird load issues
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]
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
