MySQL process goes over 100% of CPU usage

cpu-usageMySQLperformance

I'm experiencing some problems with my LAMP server. Recently everything became very slow, even though visitor count on my websites didn't change to much. When I run top command, it says that MySQL process has taken over 150-200% of CPU. How's that possible, I always thought that 100% is a maximum?

I'm running Ubuntu 9.04 server edition with 1,5 GB RAM.

my.cnf settings:

key_buffer      = 64M
max_allowed_packet  = 16M
thread_stack        = 192K
thread_cache_size       = 8

myisam-recover         = BACKUP
max_connections        = 200
table_cache            = 512
table_definition_cache = 512
thread_concurrency     = 2

read_buffer_size = 1M
sort_buffer_size = 4M
join_buffer_size = 1M

query_cache_limit   = 1M  # the maximum size of individual query results
query_cache_size    = 128M

Here is the output of MySQLTuner:

MySQLTuner output

The top command:

top output

What could be the cause of this problem? Can I make changes to my my.cnf to prevent server from hanging?

Best Answer

  1. Increase Key Buffer ( yours is currently 64MB, but total indexes are 116M, so put at least 128MB). Should help immediately.
  2. Run mysqloptimize and mysqlrepair on your tables
  3. Increase table cache/ decrease total number of tables to increase the table cache hit rate. Maybe you've got some unused or old tables which could be deleted.

Other recommended confugration options:

  • log_slow_queries = /var/log/mysql/mysql-slow.log
  • long_query_time = 4
  • log-queries-not-using-indexes

Check log file after some time.

Related Topic