Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

migrated from stackoverflow.com May 6 '13 at 15:08

This question came from our site for professional and enthusiast programmers.

    
Please run these queries: 1) SELECT VERSION(); 2) SHOW VARIABLES LIKE 'key_buffer_size'; 3) SELECT SUM(index_length) ndxsize FROM information_schema.tables WHERE engine='MyISAM'; 4) How much RAM does the DB Server have? –  RolandoMySQLDBA May 6 '13 at 15:56
    
1) 5.5.29-0ubuntu0.12.04.2-log 2) 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 3) +----------+ | ndxsize | +----------+ | 59862016 | +----------+ 4) The server has 16GB of RAM, but it is not a DB server...It is running nginx + php-fpm –  Tarik Čelik May 6 '13 at 20:30
    
Please give me this: SELECT SUM(data_length+index_length)/power(1024,2) datndxsize FROM information_schema.tables WHERE engine='MyISAM'; –  RolandoMySQLDBA May 6 '13 at 20:35
    
+--------------------+ | datndxsize | +--------------------+ | 488.69915199279785 | +--------------------+ –  Tarik Čelik May 6 '13 at 20:37

1 Answer 1

up vote 3 down vote accepted

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:

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:

EPILOGUE

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

share|improve this answer
    
Thanks.You saved me. :) –  Tarik Čelik May 6 '13 at 20:58
    
I want to convert the MyISAM tables to InnoDB. Here is my current my.cnf pastebin.com/FW8YXtLQ What would be the best setup (need to add InnoDB settings). –  Tarik Čelik May 9 '13 at 20:33
    
I put a link the script in the question already. Look at the link When to switch from MyISAM to InnoDB? under the heading NEED #2 –  RolandoMySQLDBA May 9 '13 at 20:37
    
The conversion is not a problem.When I add InnoDB settings in my my.cnf, mysql doesn't want to start. –  Tarik Čelik May 9 '13 at 20:38
    
Please post this as a separate question –  RolandoMySQLDBA May 9 '13 at 20:43

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.