Re: Weird load issues
Daniel, - Original Message - From: Daniel Andersen [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Tuesday, December 21, 2004 1:04 AM Subject: Re: Weird load issues On Mon, 20 Dec 2004 09:29 pm, Heikki Tuuri wrote: 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 Hey, The only thing thats changed recently is that the machine had a power outage, thus the low uptime. Otherwise nothing has changed in ages. In any case, if it was a query wouldn't the load derive from one of the child processes, not the parent? queries are processed by mysqld. It used to handle just as many queries perfecty well before the reboot, and as far as i can tell nothing has occurred to increase the number of queries coming through. Please see http://dev.mysql.com/doc/mysql/en/EXPLAIN.html I'm not much of an expert on InnoDB, what would be a good buffer size in your opinion? Please see http://dev.mysql.com/doc/mysql/en/InnoDB_configuration.html Daniel Regards, Heikki -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Weird load issues
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
Re: Weird load issues
On Mon, 20 Dec 2004 09:29 pm, Heikki Tuuri wrote: 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 Hey, The only thing thats changed recently is that the machine had a power outage, thus the low uptime. Otherwise nothing has changed in ages. In any case, if it was a query wouldn't the load derive from one of the child processes, not the parent? It used to handle just as many queries perfecty well before the reboot, and as far as i can tell nothing has occurred to increase the number of queries coming through. I'm not much of an expert on InnoDB, what would be a good buffer size in your opinion? Daniel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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 |
Re: Weird load issues
Daniel, - Original Message - From: Daniel Andersen [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Friday, December 17, 2004 2:04 AM Subject: Weird load issues Hey, A couple days ago my SQL server started hogging the entire CPU for no reason that I can find. I'm running MySQL 4.0.20 on Slackware 9.1 (i think), kernel version 2.4.22. The process using up all the CPU is the parent process, ie the top level mysql process that starts all the others. The server is both a master and slave for replication but all is up to date in that regard, so I don't think this is a replication related issue. Details from top are as follows: 10:01:24 up 2 days, 18:46, 1 user, load average: 7.20, 7.39, 7.27 101 processes: 92 sleeping, 9 running, 0 zombie, 0 stopped CPU0 states: 92.0% user 7.4% system0.0% nice 0.0% iowait 0.0% idle CPU1 states: 93.3% user 6.0% system0.0% nice 0.0% iowait 0.1% idle Mem: 1025220k av, 1014276k used, 10944k free, 0k shrd, 36268k buff 698604k active, 257652k inactive Swap: 2097136k av,8088k used, 2089048k free 869940k cached PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND 4654 mysql 9 0 56488 48M 3136 S99.9 4.8 1842m 0 mysqld 13007 deanders 18 0 1076 1076 816 R 1.9 0.1 0:00 0 top 9 root 9 0 00 0 SW0.1 0.0 5:57 0 kjournald etc etc etc. As you can see the process isn't even using a significant amount of memory, just a lot of CPU. Restarting the MySQL process fixes it for a few minutes but then it jumps back to 100% cpu again. Has anyone out there ever seen a problem like this in the past, or could anyone shine some light on what might be causing it? 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Weird load issues
Hey, A couple days ago my SQL server started hogging the entire CPU for no reason that I can find. I'm running MySQL 4.0.20 on Slackware 9.1 (i think), kernel version 2.4.22. The process using up all the CPU is the parent process, ie the top level mysql process that starts all the others. The server is both a master and slave for replication but all is up to date in that regard, so I don't think this is a replication related issue. Details from top are as follows: 10:01:24 up 2 days, 18:46, 1 user, load average: 7.20, 7.39, 7.27 101 processes: 92 sleeping, 9 running, 0 zombie, 0 stopped CPU0 states: 92.0% user 7.4% system0.0% nice 0.0% iowait 0.0% idle CPU1 states: 93.3% user 6.0% system0.0% nice 0.0% iowait 0.1% idle Mem: 1025220k av, 1014276k used, 10944k free, 0k shrd, 36268k buff 698604k active, 257652k inactive Swap: 2097136k av,8088k used, 2089048k free 869940k cached PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND 4654 mysql 9 0 56488 48M 3136 S99.9 4.8 1842m 0 mysqld 13007 deanders 18 0 1076 1076 816 R 1.9 0.1 0:00 0 top 9 root 9 0 00 0 SW0.1 0.0 5:57 0 kjournald etc etc etc. As you can see the process isn't even using a significant amount of memory, just a lot of CPU. Restarting the MySQL process fixes it for a few minutes but then it jumps back to 100% cpu again. Has anyone out there ever seen a problem like this in the past, or could anyone shine some light on what might be causing it? Daniel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
