MySQL Bugs: #15661: MySQL only uses 1 CPU on SMP

Bug #15661	MySQL only uses 1 CPU on SMP
Submitted:	11 Dec 2005 7:20	Modified:	16 Feb 2006 19:23
Reporter:	Michael Metz	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S5 (Performance)
Version:	4.1.15	OS:	Linux (Fedora Linux Core 4 x86_64)
Assigned to:		CPU Architecture:	Any

Description:
We've migrated our DB-Server from 32 to 64 Bit Platform. Since this migration MySQL is only running on 1 CPU on our 2 x AMD Dual Core 275 (4 CPUs).
Linux itself is running correctly with SMP (and using it) but MySQL seems to use only Threads which can't take advantage of all 4 CPUs.

Apart of the fact that the server performs much better with 1 CPU than the old one with 2 Xeon's and HT we would like to use more than one CPU ;-)

Changing from official Fedora-RPMs to MySQL AB's RPMs didn't solve the problem.

# ps auxm
USER       PID %CPU %MEM    VSZ   RSS TTY      STAT START   TIME COMMAND
root     12668  0.0  0.0  53936  1168 pts/0    -    07:40   0:00 /bin/sh /usr/bin/mysqld_safe --defaults-file=/etc/my.cnf --pid-file=/var/run/mysqld/mysqld
root         -  0.0    -      -     - -        S    07:40   0:00 -
mysql    12701  1.6  9.8 2786232 794196 pts/0  -    07:40   0:36 /usr/libexec/mysqld --defaults-file=/etc/my.cnf --basedir=/usr --datadir=/var/lib/mysql --
mysql        -  0.0    -      -     - -        Sl   07:40   0:01 -
mysql        -  0.0    -      -     - -        Sl   07:40   0:00 -
mysql        -  0.0    -      -     - -        Sl   07:40   0:00 -
[...and 400 more Lines of that...]

How to repeat:
Buy a 2 x AMD Dual Core 275 (4 CPUs) and try to run MySQL which uses more than one CPU ;-)
(sorry, need to fill in something here...)

Hi!

Please post SHOW PROCESSLIST and SHOW INNODB STATUS\G during high load.

Note that if you only have one SQL query running, it will only use one CPU.

Regards,

Heikki

Thank you for a problem report. Sorry, but how you came to the conlusion that MySQL does not use all CPUs?

Can you, please, send the results of the top command while your MySQL server is running. Something like:

 13:16:10  up 165 days, 18:03,  2 users,  load average: 0.00, 0.00, 0.00
72 processes: 71 sleeping, 1 running, 0 zombie, 0 stopped
CPU states:  cpu    user    nice  system    irq  softirq  iowait    idle
           total    0.0%    0.0%    0.0%   0.0%     0.0%    0.0%   99.9%
           cpu00    0.0%    0.0%    0.0%   0.0%     0.0%    0.0%  100.0%
           cpu01    0.0%    0.0%    0.0%   0.0%     0.0%    0.0%  100.0%
           cpu02    0.2%    0.0%    0.0%   0.0%     0.0%    0.0%   99.8%
           cpu03    0.0%    0.0%    0.0%   0.0%     0.0%    0.0%  100.0%
Mem:  3980060k av, 3185420k used,  794640k free,       0k shrd,  738196k buff
      1781832k active,             698444k inactive
Swap:       0k av,       0k used,       0k free                 1621472k cached

  PID USER     PRI  NI  SIZE  RSS SHARE STAT %CPU %MEM   TIME CPU COMMAND
    1 root      15   0   520  520   448 S     0.0  0.0  56:19   0 init
    2 root      RT   0     0    0     0 SW    0.0  0.0   0:00   0 migration/0
    3 root      RT   0     0    0     0 SW    0.0  0.0   0:00   1 migration/1
    4 root      RT   0     0    0     0 SW    0.0  0.0   0:00   2 migration/2
    5 root      RT   0     0    0     0 SW    0.0  0.0   0:00   3 migration/3
    6 root      15   0     0    0     0 SW    0.0  0.0   0:02   1 keventd
    7 root      34  19     0    0     0 SWN   0.0  0.0   0:00   0 ksoftirqd/0
    8 root      34  19     0    0     0 SWN   0.0  0.0   0:00   1 ksoftirqd/1
    9 root      34  19     0    0     0 SWN   0.0  0.0   0:00   2 ksoftirqd/2
   10 root      34  19     0    0     0 SWN   0.0  0.0   0:12   3 ksoftirqd/3
...

top

Attachment: top.txt (text/plain), 6.33 KiB.

show innodb status

Attachment: innodbStatus.txt (text/plain), 62.50 KiB.

show processlist

Attachment: processlist.txt (text/plain), 40.97 KiB.

show status

Attachment: status.txt (text/plain), 9.13 KiB.

currently there are about 2700 queries per second.

MySQL on localhost (4.1.15)                                                                                                        up 0+06:15:11 [13:55:49]
 Queries: 24.8M  qps: 1153 Slow:    21.0         Se/In/Up/De(%):    80/07/05/00
             qps now: 2723 Slow qps: 0.0  Threads:  399 (   1/   3) 80/07/05/00
 Cache Hits: 7.1M  Hits/s: 331.9 Hits now: 673.8  Ratio: 36.1% Ratio now: 31.1%
 Key Efficiency: 100.0%  Bps in/out: 99.3k/979.6k   Now in/out: 234.8k/ 2.2M

Thank you for the additional information. Please, send the results of ulimit -a command, just to be sure. Your glibc version may be of some use too.

# ulimit -a
core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
file size               (blocks, -f) unlimited
pending signals                 (-i) 65536
max locked memory       (kbytes, -l) 32
max memory size         (kbytes, -m) unlimited
open files                      (-n) 1024
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
stack size              (kbytes, -s) 10240
cpu time               (seconds, -t) unlimited
max user processes              (-u) 65536
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited

glibc-2.3.5-10.3 (official fedora-rpm)

Thanks for help on a sunday!

Sorry, I am not so good on Sunday... I wanted to ask about the results of uname -a (I want to know the kernel version and exact platfrom). So, please, send the results of uname -a too.

# uname -a
Linux db1.somewhere 2.6.14-1.1644_FC4smp #1 SMP Sun Nov 27 03:37:58 EST 2005 x86_64 x86_64 x86_64 GNU/Linux

Sorry, but your SHOW PROCESSLIST results and status (from the uploaded files) is for a period where only 1 SQL query was running, so it is surely used one CPU only. Is your top results for the same period (they clearly shows mysqld doing something, and 1 CPU loaded much more then the others)?

So, please, try to get a snapshot of "top" and SHOW PROCESSLIST while > 1 queries are perfromed.

cpuUsage 24h

Attachment: cpuUsage24h.png (image/png, text), 27.81 KiB.

This Server has constantly about 2700 qps (more than 3k in the evening). The Attachments are taken in nearly the same period of time.
The majority of queries are "easy" and very fast - so most of the time "show processlist" shows no queries (they seem to be too fast).

mysqld seems to work only with threads and so is unable to take andvantage of more than one CPU - on our old Server mysqld was running with processes.

Attached you'll find our 24h-Statistics about cpu-Usage of cpu0-3. cpu3 is used by mysqld all the time - the server is running mysqld only (apart of rsync-backup in the morning)

(Take care of the different scales on y-axis)

Just want to clarify that:

"mysqld seems to work only with threads and so is unable to take andvantage of
more than one CPU - on our old Server mysqld was running with processes."

MySQL never worked with processes. It was (and is) a single multithreaded process. You may see several mysqld in the ps or top results, but this is because of the feature (error?) of the threading library used (NPTL) and platform.

In any case, if you have only 1 active thread at a time, OS scheduler may decide to run it on the same CPU all the time (not to perfrorm CPU caches sincronisation, for example). It us OK, nothing to worry about.

Please, try to create some "long running" parallel tasks on this server (use a shell script to open >=8 connections, each of them selecting something large and NOT disconnecting immediately, for example), let it run for some time and then check top results once more.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Hmm - seems it was no issue. :-)

On the newer system the kernel seems to load-balance across CPUs differently. While only lightly loaded utilisation stays on one CPU, spreads as load rises.

Sorry for the noise.