MySQL high CPU usage (MyISAM table indexes)

Question

I have a problem with an inherited MySQL database. From time to time mysqld uses up to 2300% CPU.. The only solution is to service mysql stop and run an myisamchk -r on a table. After the indexes have been fixed, I start MySQL and everything is ok.

Any ideas for an permanent solution?

Edit (from the comments):

Using 5.5.29-0ubuntu0.12.04.2-log

key_buffer = 16M 
max_allowed_packet = 16M 
thread_stack = 128K 
thread_cache_size = 8 
myisam-recover = BACKUP 
max_connections = 500 
#table_cache = 512 
#thread_concurrency = 10 
query_cache_limit = 1M 
query_cache_size = 16M 

SELECT SUM(index_length) ndxsize 
FROM information_schema.tables 
WHERE engine='MyISAM'

returns

+----------+ 
| ndxsize  | 
+----------+
| 59862016 | 
+----------+ 

SELECT SUM(data_length+index_length)/power(1024,2) datndxsize 
FROM information_schema.tables 
WHERE engine='MyISAM'

returns:

+--------------------+ 
| datndxsize         |
+--------------------+
| 488.69915199279785 | 
+--------------------+

The server has 16GB of RAM, but it is not a DB server...It is running nginx + php-fpm

Answer 1

SUGGESTION

Your key_buffer_size is only 16MB, but your MyISAM indexes total 59MB. In a highly-trafficked website, I can see index pages getting kicked out of and pushed into the MyISAM keycache frequently. This could have an affect on indexes being updated and overall write performance on the MyISAM tables.

You could just bump up key_buffer_size to 64 MB.

You must add this to /etc/my.cnf

[mysqld]
key_buffer_size=64M

You don't even need to restart mysql. Just run this:

SET @MB = 1024 * 1024;
SET GLOBAL key_buffer_size = @MB * 64;

You can also convert the row format of all your MyISAM tables. This will just about double the disk space used by will increase read performance 20-25%. I wrote about this before as well:

• Which is faster, InnoDB or MyISAM?
• Best of MyISAM and InnoDB

Here is a script to convert all your MyISAM tables to Fixed Row Format

SQLSTMT="SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ROW_FORMAT=Fixed;')"
SQLSTMT="${SQLSTMT} FROM information_schema.tables WHERE engine='MyISAM' AND"
SQLSTMT="${SQLSTMT} table_schema NOT IN ('information_schema','mysql','performance_schema')"
mysql -uroot -p -A --skip-column-names -e"${SQLSTMT}" > /root/MakeFixedRows.sql
mysql -uroot -p < /root/MakeFixedRows.sql

ALTERNATE SUGGESTION

Given you have a total of 488 MB of of MyISAM data and indexes, I would like to suggest the following: Switch Everything to InnoDB. I have two reasons:

REASON # 1

MyISAM only caches indexes. InnoDB caches data and indexes. See my post : What are the main differences between InnoDB and MyISAM?

This will reduce disk I/O for reading data and indexes. In your particular case, you can easily handle an InnoDB Buffer Pool of 512M and basically fit everything into RAM.

REASON # 2

The MyISAM storage engine cannot access multiple CPUs. InnoDB can. In fact, I have many posts on tweeking InnoDB for this:

• Jul 23, 2012 : How to get the most out of MySQL on a QuadCore machine with 16 GB of RAM?
• Sep 20, 2011 : Multi cores and MySQL Performance
• Sep 12, 2011 : Possible to make MySQL use more than one core?
• May 26, 2011 : About single threaded versus multithreaded databases performance

EPILOGUE

I would like you really consider the conversion from MyISAM to InnoDB

• When to switch from MyISAM to InnoDB?
• Any gotchas at all with converting from MyISAM to InnoDB?
• InnoDB vs MyISAM with many indexes