How to figure out why mysql is at 170% cpu load?

"SHOW PROCESSLIST" will show you what threads are currently running. This might give an immediate indication of something wrong. I also like to use mysqltuner.pl to see if something's out of tune.

170% is a lot, however. Is this a sudden increase? What does "ps auxwww" look like?

Yeah it's a sudden increase.

Ever since I installed this one e-mailing addon + postfix.

> USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND

root 1 0.0 0.0 10308 740 ? Ss 2010 0:00 init [2]

root 2 0.0 0.0 0 0 ? S 2010 0:00 [migration/0]

root 3 0.0 0.0 0 0 ? SN 2010 0:00 [ksoftirqd/0]

root 4 0.0 0.0 0 0 ? S 2010 0:00 [migration/1]

root 5 0.0 0.0 0 0 ? SN 2010 0:00 [ksoftirqd/1]

root 6 0.0 0.0 0 0 ? S 2010 0:00 [migration/2]

root 7 0.0 0.0 0 0 ? SN 2010 0:00 [ksoftirqd/2]

root 8 0.0 0.0 0 0 ? S 2010 0:00 [migration/3]

root 9 0.0 0.0 0 0 ? SN 2010 0:00 [ksoftirqd/3]

root 10 0.0 0.0 0 0 ? S< 2010 0:00 [events/0]

root 11 0.0 0.0 0 0 ? S< 2010 0:00 [events/1]

root 12 0.0 0.0 0 0 ? S< 2010 0:00 [events/2]

root 13 0.0 0.0 0 0 ? S< 2010 0:00 [events/3]

root 14 0.0 0.0 0 0 ? S< 2010 0:00 [khelper]

root 15 0.0 0.0 0 0 ? S< 2010 0:00 [kthread]

root 17 0.0 0.0 0 0 ? S< 2010 0:00 [xenwatch]

root 18 0.0 0.0 0 0 ? S< 2010 0:00 [xenbus]

root 27 0.0 0.0 0 0 ? S< 2010 0:00 [kblockd/0]

root 28 0.0 0.0 0 0 ? S< 2010 0:00 [kblockd/1]

root 29 0.0 0.0 0 0 ? S< 2010 0:00 [kblockd/2]

root 30 0.0 0.0 0 0 ? S< 2010 0:00 [kblockd/3]

root 32 0.0 0.0 0 0 ? S< 2010 0:00 [kseriod]

root 112 0.0 0.0 0 0 ? S 2010 0:00 [pdflush]

root 113 0.0 0.0 0 0 ? S 2010 0:00 [pdflush]

root 114 0.0 0.0 0 0 ? S< 2010 0:07 [kswapd0]

root 115 0.0 0.0 0 0 ? S< 2010 0:00 [aio/0]

root 116 0.0 0.0 0 0 ? S< 2010 0:00 [aio/1]

root 117 0.0 0.0 0 0 ? S< 2010 0:00 [aio/2]

root 118 0.0 0.0 0 0 ? S< 2010 0:00 [aio/3]

root 120 0.0 0.0 0 0 ? S< 2010 0:00 [jfsIO]

root 121 0.0 0.0 0 0 ? S< 2010 0:00 [jfsCommit]

root 122 0.0 0.0 0 0 ? S< 2010 0:00 [jfsCommit]

root 123 0.0 0.0 0 0 ? S< 2010 0:00 [jfsCommit]

root 124 0.0 0.0 0 0 ? S< 2010 0:00 [jfsCommit]

root 125 0.0 0.0 0 0 ? S< 2010 0:00 [jfsSync]

root 126 0.0 0.0 0 0 ? S< 2010 0:00 [xfslogd/0]

root 127 0.0 0.0 0 0 ? S< 2010 0:00 [xfslogd/1]

root 128 0.0 0.0 0 0 ? S< 2010 0:00 [xfslogd/2]

root 129 0.0 0.0 0 0 ? S< 2010 0:00 [xfslogd/3]

root 130 0.0 0.0 0 0 ? S< 2010 0:00 [xfsdatad/0]

root 131 0.0 0.0 0 0 ? S< 2010 0:00 [xfsdatad/1]

root 132 0.0 0.0 0 0 ? S< 2010 0:00 [xfsdatad/2]

root 133 0.0 0.0 0 0 ? S< 2010 0:00 [xfsdatad/3]

root 742 0.0 0.0 0 0 ? S< 2010 0:00 [net_accel/0]

root 743 0.0 0.0 0 0 ? S< 2010 0:00 [net_accel/1]

root 744 0.0 0.0 0 0 ? S< 2010 0:00 [net_accel/2]

root 745 0.0 0.0 0 0 ? S< 2010 0:00 [net_accel/3]

root 754 0.0 0.0 0 0 ? S< 2010 0:00 [kpsmoused]

root 758 0.0 0.0 0 0 ? S< 2010 0:00 [kcryptd/0]

root 759 0.0 0.0 0 0 ? S< 2010 0:00 [kcryptd/1]

root 760 0.0 0.0 0 0 ? S< 2010 0:00 [kcryptd/2]

root 761 0.0 0.0 0 0 ? S< 2010 0:00 [kcryptd/3]

root 762 0.0 0.0 0 0 ? S< 2010 0:00 [kmirrord]

root 770 0.0 0.0 0 0 ? S< 2010 0:06 [kjournald]

root 999 0.0 0.0 6288 636 ? Ss 2010 0:00 dhclient3 -pf /var/run/dhclient.eth0.pid -lf /var/lib/dhcp3/dhclient.eth0.leases eth0

root 1088 0.0 0.1 187108 1544 ? Sl 2010 0:04 /usr/sbin/rsyslogd -c3

root 1102 0.0 0.1 48856 1172 ? Ss 2010 0:00 /usr/sbin/sshd

root 1145 0.0 0.1 17312 1448 ? S 2010 0:00 /bin/sh /usr/bin/mysqld_safe

mysql 1184 66.2 9.6 248708 76120 ? Sl 2010 7054:14 /usr/sbin/mysqld –basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking --port=3306 --socket=/var/run/mysqld/mysqld.sock

root 1185 0.0 0.0 3772 588 ? S 2010 0:00 logger -p daemon.err -t mysqld_safe -i -t mysqld

root 1528 0.0 0.1 19828 1040 ? Ss 2010 0:00 /usr/sbin/cron

root 1567 0.0 0.7 68824 5628 ? Sl 2010 0:07 /usr/bin/python /usr/bin/fail2ban-server -b -s /var/run/fail2ban/fail2ban.sock

root 1588 0.0 0.0 3792 568 tty1 Ss+ 2010 0:00 /sbin/getty 38400 tty1

root 15821 0.0 0.2 36836 2292 ? Ss 2010 0:10 /usr/lib/postfix/master

postfix 15823 0.0 0.3 39028 2464 ? S 2010 0:04 qmgr -l -t fifo -u

root 24230 0.0 0.3 51420 2784 ? Ss 14:34 0:00 sshd: dfischer [priv]

dfischer 24233 0.0 0.1 51420 1528 ? S 14:34 0:00 sshd: dfischer@pts/0

dfischer 24234 0.0 0.2 18080 2152 pts/0 Ss 14:34 0:00 -bash

root 24317 0.1 1.6 294112 13228 ? Ss 14:58 0:00 /usr/sbin/apache2 -k start

www-data 24320 0.1 3.1 299608 24988 ? S 14:58 0:00 /usr/sbin/apache2 -k start

www-data 24321 0.2 2.9 297372 23088 ? S 14:58 0:00 /usr/sbin/apache2 -k start

www-data 24323 0.0 0.9 294468 7652 ? S 14:58 0:00 /usr/sbin/apache2 -k start

www-data 24324 0.0 0.8 294112 6896 ? S 14:58 0:00 /usr/sbin/apache2 -k start

www-data 24325 0.0 0.8 294112 7064 ? S 14:58 0:00 /usr/sbin/apache2 -k start

www-data 24326 0.1 2.1 295624 16640 ? S 14:58 0:00 /usr/sbin/apache2 -k start

www-data 24327 0.0 0.8 294112 6904 ? S 14:58 0:00 /usr/sbin/apache2 -k start

www-data 24328 0.0 0.8 294112 6904 ? S 14:58 0:00 /usr/sbin/apache2 -k start

www-data 24329 0.0 2.3 295572 18336 ? S 14:58 0:00 /usr/sbin/apache2 -k start

www-data 24330 0.0 0.8 294112 6904 ? S 14:58 0:00 /usr/sbin/apache2 -k start

www-data 24331 0.0 2.3 296100 18824 ? S 14:58 0:00 /usr/sbin/apache2 -k start

www-data 24332 0.0 0.9 294112 7100 ? S 14:58 0:00 /usr/sbin/apache2 -k start

www-data 24333 0.0 0.9 294248 7472 ? S 14:58 0:00 /usr/sbin/apache2 -k start

www-data 24334 0.3 2.8 297784 22688 ? S 14:58 0:00 /usr/sbin/apache2 -k start

www-data 24335 0.0 0.9 294112 7084 ? S 14:58 0:00 /usr/sbin/apache2 -k start

www-data 24336 0.1 3.1 303276 24648 ? S 14:58 0:00 /usr/sbin/apache2 -k start

www-data 24337 0.0 0.8 294112 7064 ? S 14:58 0:00 /usr/sbin/apache2 -k start

www-data 24338 0.0 0.9 294112 7092 ? S 14:58 0:00 /usr/sbin/apache2 -k start

www-data 24339 0.0 0.8 294112 7064 ? S 14:58 0:00 /usr/sbin/apache2 -k start

www-data 24340 0.0 0.8 294112 6904 ? S 14:58 0:00 /usr/sbin/apache2 -k start

www-data 24341 0.0 0.9 294112 7456 ? S 14:58 0:00 /usr/sbin/apache2 -k start

www-data 24342 2.7 5.1 319716 40304 ? S 14:58 0:03 /usr/sbin/apache2 -k start

www-data 24343 0.0 0.8 294112 6896 ? S 14:58 0:00 /usr/sbin/apache2 -k start

www-data 24344 0.0 0.8 294112 7072 ? S 14:58 0:00 /usr/sbin/apache2 -k start

www-data 24345 0.0 0.8 294112 7060 ? S 14:58 0:00 /usr/sbin/apache2 -k start

www-data 24346 0.0 1.6 294932 12844 ? S 14:58 0:00 /usr/sbin/apache2 -k start

www-data 24347 0.0 0.8 294112 7064 ? S 14:58 0:00 /usr/sbin/apache2 -k start

www-data 24348 0.0 0.8 294112 6896 ? S 14:58 0:00 /usr/sbin/apache2 -k start

www-data 24349 0.1 2.9 300260 23188 ? S 14:58 0:00 /usr/sbin/apache2 -k start

www-data 24350 0.1 2.3 295340 18528 ? S 14:58 0:00 /usr/sbin/apache2 -k start

www-data 24351 0.1 2.3 295948 18560 ? S 14:58 0:00 /usr/sbin/apache2 -k start

www-data 24352 0.0 0.9 294112 7444 ? S 14:58 0:00 /usr/sbin/apache2 -k start

www-data 24353 0.0 0.9 294112 7444 ? S 14:58 0:00 /usr/sbin/apache2 -k start

www-data 24354 0.1 2.2 295616 17336 ? S 14:58 0:00 /usr/sbin/apache2 -k start

www-data 24355 0.3 2.3 295344 18452 ? S 14:58 0:00 /usr/sbin/apache2 -k start

www-data 24356 0.0 0.8 294112 7064 ? S 14:58 0:00 /usr/sbin/apache2 -k start

www-data 24357 0.0 0.8 294112 6904 ? S 14:58 0:00 /usr/sbin/apache2 -k start

www-data 24358 0.0 1.6 294940 12848 ? S 14:58 0:00 /usr/sbin/apache2 -k start

www-data 24359 0.0 0.9 294112 7092 ? S 14:58 0:00 /usr/sbin/apache2 -k start

www-data 24360 0.0 1.8 295564 14276 ? S 14:58 0:00 /usr/sbin/apache2 -k start

postfix 24366 0.0 0.3 43168 2568 ? S 14:59 0:00 smtp -t unix -u -c

postfix 24367 0.0 0.2 38924 2244 ? S 14:59 0:00 bounce -z -n defer -t unix -u -c

dfischer 24368 0.0 0.1 14724 984 pts/0 R+ 15:00 0:00 ps auxwww

> vmstat 5

procs –---------memory---------- ---swap-- -----io---- -system-- ----cpu----

r b swpd free buff cache si so bi bo in cs us sy id wa

2 1 4 216476 6264 259664 0 0 14 7 16 6 13 7 78 1

2 0 4 197456 6268 259712 0 0 15 1 3546 3363 17 10 69 3

1 0 4 190684 6276 260616 0 0 54 8 3659 3403 20 10 65 4

1 0 4 207800 6288 260840 0 0 30 14 3522 3262 18 10 65 7

2 0 4 197572 6296 261920 0 0 11 39 3566 3401 16 10 69 4

2 0 4 191776 6304 261924 0 0 0 14 3515 3384 16 10 71 3

1 0 4 186140 6312 262100 0 0 35 14 3641 3413 18 10 69 3

I don't know how to read that. Is that bad?

Your vmstat says there's no swap activity, not a whole lot of I/O going on, and low I/O wait. So you're not thrashing.

What e-mail addon? Is this for something like WordPress?

See if you can get a MySQL slow query log. Your addon might be firing off some stupid queries.

@hybinet:

Your vmstat says there's no swap activity, not a whole lot of I/O going on, and low I/O wait. So you're not thrashing.

What e-mail addon? Is this for something like WordPress?

See if you can get a MySQL slow query log. Your addon might be firing off some stupid queries.

vBulletin.

How do I get the MySQL slow query log?

For mysql 5.0

http://dev.mysql.com/doc/refman/5.0/en/ … y-log.html">http://dev.mysql.com/doc/refman/5.0/en/slow-query-log.html

5.1 http://dev.mysql.com/doc/refman/5.1/en/ … y-log.html">http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html

5.5 http://dev.mysql.com/doc/refman/5.5/en/ … y-log.html">http://dev.mysql.com/doc/refman/5.5/en/slow-query-log.html

You could also install mytop which is like top but for mysql.

@obs:

For mysql 5.0

http://dev.mysql.com/doc/refman/5.0/en/ … y-log.html">http://dev.mysql.com/doc/refman/5.0/en/slow-query-log.html

5.1 http://dev.mysql.com/doc/refman/5.1/en/ … y-log.html">http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html

5.5 http://dev.mysql.com/doc/refman/5.5/en/ … y-log.html">http://dev.mysql.com/doc/refman/5.5/en/slow-query-log.html

You could also install mytop which is like top but for mysql.

How's this look for mytop?

https://img.skitch.com/20110108-pqwackr … 1hg542.jpg">https://img.skitch.com/20110108-pqwackr4if7xbsahq4kq1hg542.jpg

(I didn't hide the text from you, more for google searching. Sorry!)

Looking into slow query log now.

26 queries per second with a 98% key efficiency seems fine.

Weird. I have no idea why the CPU usage is so high right now then.

I guess I'll wait for the slow queries log to write…