The "top" program shows mysqld-max CPU usage maxing out at 99.9%. With a multi-processor system, I would expect to be able to go above 100% usage. Does MySQL need to be setup to use threading/processes in such a way as to spread the load over multiple CPUs? (Or maybe, it's just that "top" refuses to show more that 99.9% for a single process.)
Yes, I know that mysql is supposed to be multithreaded out of the box, but it's just not looking right.
System is a dual 3.06 ghz Xeon machine with hyperthreading. To the kernel this looks like 4 CPUs, and the top program also shows this.
System is Fedora Core 1 with updates, and has the following MySQL packages installed:
Code:
MySQL-shared-compat-4.0.20-0
MySQL-shared-4.0.20-0
php-mysql-4.3.10-3.2
MySQL-server-4.0.20-0
MySQL-client-4.0.20-0
MySQL-Max-4.0.20-0
perl-DBD-MySQL-2.9002-1
MySQL-devel-4.0.20-0
Kernel version 2.4.22-1.2174.nptlsmp
Sample top output:
Code:
16:02:06 up 1 day, 3:53, 1 user, load average: 2.20, 2.13, 2.10
81 processes: 80 sleeping, 1 running, 0 zombie, 0 stopped
CPU states: cpu user nice system irq softirq iowait idle
total 154.0% 0.0% 64.8% 0.0% 0.0% 0.0% 180.4%
cpu00 37.8% 0.0% 17.4% 0.0% 0.0% 0.0% 44.6%
cpu01 8.7% 0.0% 4.8% 0.0% 0.0% 0.0% 86.4%
cpu02 50.4% 0.0% 21.3% 0.0% 0.0% 0.0% 28.1%
cpu03 57.2% 0.0% 21.3% 0.0% 0.0% 0.0% 21.3%
Mem: 1032040k av, 912676k used, 119364k free, 0k shrd, 111740k buff
266800k active, 569828k inactive
Swap: 514040k av, 12084k used, 501956k free 313884k cached
PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND
14000 mysql 15 0 31288 28M 3820 S 99.9 2.8 959:30 0 mysqld-max
22996 apache 16 0 21136 20M 10852 S 1.9 2.0 0:25 2 httpd
23193 apache 16 0 22340 21M 10828 S 1.9 2.1 0:28 1 httpd
23260 apache 16 0 20520 19M 10884 S 1.9 1.9 0:24 1 httpd
24167 apache 16 0 20456 19M 10876 S 1.9 1.9 0:27 3 httpd
25389 apache 16 0 20196 19M 10852 S 1.9 1.9 0:22 3 httpd
26722 apache 16 0 20136 19M 10872 S 1.9 1.9 0:21 3 httpd
MySQL config (my.cnf):
Code:
[client]
port = 3306
socket = /var/lib/mysql/mysql.sock
# Here follows entries for some specific programs
# The MySQL server
[mysqld]
datadir=/data/mysql
bind-address = 127.0.0.1
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
set-variable = key_buffer=128M
#set-variable = max_allowed_packet=1M
set-variable = table_cache=1024
set-variable = sort_buffer=1024K
#set-variable = net_buffer_length=8K
set-variable = myisam_sort_buffer_size=16M
#log-bin
#server-id = 1
skip-innodb
query-cache-type = 1
query-cache-size = 24M
skip-bdb
[mysql.server]
user=mysql
basedir=/var/lib
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[mysqldump]
quick
set-variable = max_allowed_packet=16M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[isamchk]
set-variable = key_buffer=20M
set-variable = sort_buffer=20M
set-variable = read_buffer=2M
set-variable = write_buffer=2M
[myisamchk]
set-variable = key_buffer=20M
set-variable = sort_buffer=20M
set-variable = read_buffer=2M
set-variable = write_buffer=2M
[mysqlhotcopy]
interactive-timeout
And yes, the database is legitimately using that much CPU - it's not due to bad programming. This server deals out more than one million pageviews per day, with multiple (but simple) queries per pageview.
That being said, I have no doubt that my.cnf could be further optimized. Feel free to post suggestions - I have 1 GB RAM. Still, the big issue is making sure that my multiple processors are being fully utilized.
Okay, I think it's a limitation in top, and that I can get a better snapshot by starting top with "top -q", filtering to just the mysql user, and hitting H to show the threads. Not really easy to read, but it shows a little more of what's going on.
