3 added information from the comments to the question
source | link

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

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?

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

2 deleted 1 characters in body
source | link

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 ana table. After the indexes have been fixed, I start MySQL and everything is ok.

Any ideas for an permanent solution?

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 an table. After the indexes have been fixed, I start MySQL and everything is ok.

Any ideas for an permanent solution?

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?

    Post Migrated Here from stackoverflow.com
1
source | link

MySQL high CPU usage (MyISAM table indexes)

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 an table. After the indexes have been fixed, I start MySQL and everything is ok.

Any ideas for an permanent solution?