MySQL process goes over 100% of CPU usage

Question

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:

The top command:

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

Answer 1

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.

Answer 2

Run top -H to see all the running threads and not just the overall process. Also, if you hit the 1 key while in top, it will show you the cpu usage for the individual CPUs/cores.

Answer 3

You have a processor which has more than one core, or you have multiple processors. If you have two cores and a process is using 100% of both cores, it will show as 200% in top.

Likewise, this is likely working as intended -- nothing is wrong with your configuration. If you are experiencing frequent hangs, from what you posted, you may want to look into adding proper indexes to your tables (or optimizing your queries).

Answer 4

Mysql has multiple processes (threads) working independently, one, for example, is responsible for writing data from memory onto the disk. With multiple cores in the CPU (and/or multiple CPUs), more than one thread is working, and so it can run more than 100% of a single core - on a simplistic level, maybe 75% of each of two cores are running, giving 150%.

Answer 5

I've noticed a problem, not related to CPU. If you're using apache and MySQL on the same server you can reach bad conditions (RAM) when your apache activity increase.

MySQLTunner tells you that using the 200 available connections (your max connection setting) you will fill the RAM. Let's say you have limited apache to 150 process you'll certainly won't have enough RAM when MySQL and apache will try to use 150 connexions (as Apache as well is a good RAM eater).

So this is about RAM and you're maybe not hit yet :-) The top commands show only 15 apache process (but you're in load average 3/6/16 so that means the storm was 15 minute ago and is now in leaving).

About the CPU problem, to complement the good response of shakalandy, this may be because of one single query. It can be on a huge table, or doing a lot of re-index tasks, or using a lot of temporary file, an index missing (removed?), etc. The only way to detect it is activating the slow query log (maybe with a high thresold, like 8s). Then use the mysqlsla tool to analyse this slow query log, and run some explain on the identified queries.