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've had about 48 hours of extremely high CPU usage on my main MySQL database for no apparent reason.

I realize this question is quite similar to the one posed here: High CPU usage from MySQL with no queries at all running

However, this MySQL server does not have ntp installed at all, and as the leap-second bug is from a few months ago, I don't think that's the explanation.

SHOW PROCESSLIST does not show any long-running processes/queries, and there have been no recent changes to DB schema, etc.

The server is also extremely locked down so that only other application servers within the VPN may access it, and has plenty of RAM (17 GB) which is not maxed out either.

Is there anything else that I should look into, or any other common causes of high CPU?

The server is an EC2 m2.xlarge instance: 17.1 GB RAM, 6.5 EC2 "Compute Units" with this configuration:

user        = mysql
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
skip-external-locking

key_buffer      = 256M
sort_buffer_size    = 16M
read_buffer_size    = 16M
max_allowed_packet  = 32M
thread_stack        = 192K
thread_cache_size       = 8

max_connections        = 200
thread_concurrency     = 12

query_cache_limit   = 4M
query_cache_size        = 128M

log_error                = /var/log/mysql/error.log

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

server-id       = 1
log_bin         = /var/lib/mysql/mysql-bin.index
expire_logs_days    = 10
max_binlog_size         = 1000M
sync_binlog     = 1

innodb_data_file_path   = ibdata1:2000M;ibdata2:10M:autoextend
innodb_buffer_pool_size = 11G
innodb_additional_mem_pool_size = 128M
innodb_log_file_size    = 1000M
innodb_log_buffer_size  = 32M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
share|improve this question
add comment

1 Answer

You can have a high load from lots of short queries as well. Try setting your long_query_time to 0, and force all clients to reconnect. Let that run for a few minutes and then set it back to your default. You can peruse the results by hand or use a tool like pt-query-digest.

In either case, you should be able to figure out if there is in fact a bunch going on, just very quickly.

share|improve this answer
    
So setting long_query_time to 0 would effectively log every query, correct? Is there a way to set this on the fly, or does it require a mysql restart? –  disbelief Sep 20 '12 at 14:02
    
Sure, just: set global long_query_time=0; However, I believe that if your clients are already connected, they won't pick up the setting until you disconnect/reconnect. –  ers81239 Sep 20 '12 at 16:06
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.