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 HaydenThis is my good top output:>> 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]
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.
