LinuxQuestions.org
Visit Jeremy's Blog.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Software
User Name
Password
Linux - Software This forum is for Software issues.
Having a problem installing a new program? Want to know which application is best for the job? Post your question in this forum.

Notices

Reply
 
Search this Thread
Old 05-25-2005, 03:10 PM   #1
quill18
LQ Newbie
 
Registered: Feb 2004
Posts: 7

Rep: Reputation: 0
MySQL on SMP. CPU stats max at 99.9%


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.

Last edited by quill18; 05-25-2005 at 03:40 PM.
 
Old 05-25-2005, 04:50 PM   #2
bulliver
Senior Member
 
Registered: Nov 2002
Location: Edmonton AB, Canada
Distribution: Gentoo x86_64; Gentoo PPC; FreeBSD; OS X 10.9.4
Posts: 3,743
Blog Entries: 4

Rep: Reputation: 76
If you are interested in optimizing you DB (and perhaps making it more efficient) there are some hints/docs here:
http://dev.mysql.com/doc/mysql/en/my...imization.html

Maybe it will help?
 
Old 05-26-2005, 10:22 AM   #3
zeos
Member
 
Registered: Aug 2003
Posts: 150

Rep: Reputation: 15
Add to your [mysqld] section of my.cnf:

thread_concurrency=4

Also consider running myisamchk as well as optimizing your tables
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
max % of CPU for one process uselpa Slackware 2 07-30-2005 08:21 AM
how can I run my centrino cpu on max speed ? balki Linux - Hardware 3 07-17-2004 03:37 PM
max cpu for Red hat 9 tpeacham Linux - Software 0 02-18-2004 02:42 AM
smp but i only have 1 cpu o_O Longinus Linux - Newbie 4 12-06-2003 11:18 AM
[mysql] difference between Mysql Max or Normal BlackCell Programming 2 11-06-2003 05:41 PM


All times are GMT -5. The time now is 01:03 AM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration