Hello all,

I have been struggling the last few days with this. I have only one site running on one dedicated server. The mysqld process is stuck at 99.9% cpu usage:

230 processes: 229 sleeping, 1 running, 0 zombie, 0 stopped
CPU0 states: 81.0% user, 0.0% system, 0.0% nice, 18.0% idle
CPU1 states: 3.0% user, 0.0% system, 0.0% nice, 96.0% idle
CPU2 states: 85.0% user, 3.0% system, 0.0% nice, 11.0% idle
CPU3 states: 7.0% user, 0.0% system, 0.0% nice, 92.0% idle
Mem: 1547568K av, 1517748K used, 29820K free, 0K shrd, 318592K buff
Swap: 1044216K av, 50296K used, 993920K free 825096K cached

PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
10461 mysql 9 0 105M 105M 1784 S 99.9 7.0 2:22 mysql


Hardware:
Dual P4 XEON 2.4Ghz w/ HT enabled
1.5GB of RAM
Dual RAID System

Software Installed:
RedHat 8 w/ latestest updates
Apache 2.0.40
MySQL 3.23.56
PHP 4.2.2



my.cnf file:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-locking
set-variable = key_buffer_size=384M
#set-variable = max_connections=300
set-variable = max_allowed_packet=1M
set-variable = table_cache=512
set-variable = sort_buffer=2M
set-variable = record_buffer=2M
set-variable = thread_cache_size=8
# Try number of CPU's*2 for thread_concurrency
set-variable = thread_concurrency=8
set-variable = myisam_sort_buffer_size=64M
set-variable = wait_timeout=30
log-bin=thoth-bin.001
log-bin-index=thoth-bin.index
server-id=1

[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=256M
set-variable = sort_buffer=256M
set-variable = read_buffer=2M
set-variable = write_buffer=2M

[myisamchk]
set-variable = key_buffer=256M
set-variable = sort_buffer=256M
set-variable = read_buffer=2M
set-variable = write_buffer=2M

[mysqlhotcopy]
interactive-timeout

[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid



httpd.conf file:
TimeOut 50
KeepAlive on
MaxKeepAliveRequests 500
KeepAliveTimeout 15

<IfModule prefork.c>
StartServers 5
MinSpareServers 5
MaxSpareServers 20
MaxClients 200
MaxRequestsPerChild 0
</IfModule>



mytop output:
MySQL on localhost (3.23.56-log) up 0+10:46:13 [18:19:11]
Queries: 3.8M qps: 102 Slow: 5.0 Se/In/Up/De(%): 75/00/06/00
qps now: 53 Slow qps: 0.0 Threads: 35 ( 1/ 7) 62/00/09/00
Key Efficiency: 99.9% Bps in/out: 20.9k/93.0k Now in/out: 5.4k/74.0k


I have an average of 102 queries per second. After searching this forum and trying different settings I have not been able to get cpu usage lower.

Please Help.

Thank you.

Try with keep alives off.

I really suggest moving to 4.x version of mysql. and adding this to your config:

query_cache_limit=1M
query_cache_size=32M
query_cache_type=1

I wouldn't think your query cache would cause the spike in CPU usage...

Have you changed anything recently.

I had this EXACT problem on a Dual PIII system awhile back. Pissed me off soooo much. :angry:

Never really found out exactly what happened. A site that we hosted, a MMORPG, was the main cause. Our conclusion was that the site's coding was sloppy and that queries opened were never closed. They didn't want to review the whole code so we eventually let them go. Hope you get your problem fixed.

Looks like you only have a 300 mysql connection limit while you have a 500 apache (keep-alive) connection limit... I crashed a server once doing that (mysql_pconnect is evil [on servers with huge apache connection limits], it's okay servers with smaller limits).

Thank you all for your suggestions and I have implemented with a little bit of improvement. I will be upgrading to MySQL 4.0.20 as well to try to resolve this. But I did try one more thing, I changed my customers website to mysql_connect instead of mysql_pconnect. And the amount of mysql processes went way way down. The mysqld process actually fluctuated and did not hold 99.9% cpu usage. How does mysql_pocnnect work? I thought that is the better way. But this morning when the amount connections went up it began to have database connection errors. So I had to change it back to mysql_pconnect.

Any ideas on this?

connect tries to establish a connection each time your page loads and closes it after the page (php) is done processing. pconnect does not close the connection, instead it puts it in a pool of available connections - next sessions takes free connection with needed credentials and uses it. of course, it's faster. it may have bugs though, especially with earlier versions.

try query caching (and MySQL 4) above all, post results after you've done it...

pconnect doesn't pool connections per se.... It keeps one independent connection open per apache thread.

A side note, from your my.conf..you got log-bin turned on.....this can take up alot of space over time...keep you eye on it.