Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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 sais 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?

share|improve this question
add comment

5 Answers 5

up vote 7 down vote accepted
  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.

share|improve this answer
    
Thank you for the recommendations, I'll try them and let you know if it helped. –  Temnovit Jan 9 '11 at 14:02
    
tell me please, how did you calculate that total indexes are 116M? –  Temnovit Jan 9 '11 at 14:08
    
got it myself :) –  Temnovit Jan 9 '11 at 14:17
add comment

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).

share|improve this answer
    
Thank you, I didn't know this. –  Temnovit Jan 9 '11 at 14:01
add comment

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%.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
    
Thank you, indeed, mysql is not the only process around :) –  Temnovit Jan 9 '11 at 14:03
add comment

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.