MySQLd High CPU Usage 100% even without apache2 process

MySQLd may take a High CPU Usage at +100% or so even without apache2 process. Apart from apache daemon, I've also killed all MySQL calling processes such as bind9/named, postfix, *ftpd, etc., etc.
I found no solution, even after much much Googling since more than 6 months...

Under Linux Debian 6 Squeeze MySQL version 5.1.63-0+squeeze1,  I did the MySQL OPTIMIZE TABLE to defragment tables, no effects. mysqltuner.pl gave me always Fragmented tables of about 80. MySQLd CPU climbs to 100%, 120%, 130%,... after 3 minutes of database service, it never lowers to less than 100%. It worked at a very high CPU load, but did not cause system crush, as shown below, captured on 1st January 2013, server up since 31 days:

The CPU usage was at 151%. It's worth noticing that we have a double core CPU, so our CPU maximum ratio is 200%. Moreover, as a virtual dedicated server, we benefit from some hosting company's kind tolerating margin...

The only thing that I didn't accomplish is to export all tables as .sql files and import them all individually.


Today, miracle, without doing that myself, after a server system crush and restarted, mysqltuner.pl gives Fragmented tables: 7, the CPU is lowered to 20%.

I d'ont know how MySQLd repaired framented tables itself. Here is mysqltuner.pl results today:

 >>  MySQLTuner 1.2.0 - Major Hayden 
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
[OK] Logged in using credentials from debian maintenance account.

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.63-0+squeeze1-log
[OK] Operating on 32-bit architecture with less than 2GB RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 170M (Tables: 91)
[--] Data in InnoDB tables: 171M (Tables: 180)
[!!] Total fragmented tables: 7

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 5h 23m 28s (6M q [58.550 qps], 871K conn, TX: 4B, RX: 770M)
[--] Reads / Writes: 95% / 5%
[--] Total buffers: 642.0M global + 10.6M per thread (100 max threads)
[OK] Maximum possible memory usage: 1.7G (84% of installed RAM)
[OK] Slow queries: 0% (1K/6M)
[OK] Highest usage of available connections: 62% (62/100)
[OK] Key buffer size / total MyISAM indexes: 24.0M/167.9M
[OK] Key buffer hit rate: 99.9% (42M cached / 30K reads)
[OK] Query cache efficiency: 43.5% (1M cached / 3M selects)
[!!] Query cache prunes per day: 239329
[OK] Sorts requiring temporary tables: 0% (2 temp sorts / 38K sorts)
[OK] Temporary tables created on disk: 13% (7K on disk / 54K total)
[OK] Thread cache hit rate: 99% (6K created / 871K connections)
[OK] Table cache hit rate: 32% (429 open / 1K opened)
[OK] Open file limit used: 22% (299/1K)
[OK] Table locks acquired immediately: 99% (926K immediate / 927K locks)
[OK] InnoDB data size / buffer pool: 171.8M/180.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Increasing the query_cache size over 128M may reduce performance
Variables to adjust:
    query_cache_size (> 180M) [see warning above]

This is my good top output:

As a deduction, the high MySQL CPU usage might have a relation with fragmented MySQL tables.

Since then, I deleted all useless MySQL queries, as those on pages that would get no records.

I also replaced mysql_query() by mysqli_query().

Also you should check free forum db tables as PhpBB, SQL injection may result in very big tables. This would also result in a high CPU usage of MySQLd.

I cannot say that I found the exact cause. The multi-direction treatment of our problem eased our MySQLd usage since months.