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.

my first time here!

First of all I want to say that I'm not a DBA and I have a very little experience with MySQL but I like to study and understand what I'm doing.

I have a virtual machine (2gb RAM, 1.4 Dual Core CPU) with a mysql server for an online game. I did a very extreme query optimization and now I use index on all of them. I also updated several time my.cnf but the mysqld process is still using a lot of CPU. In normal days (10-15 concurrent users) it's around 70% and in big load days (40-50 users) it's over 150%. Is it a normal value?

I really can't understand why.

More informations: mysql 5.5.31 and MyISAM tables.

That's the my.cnf file

user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
bind-address        = 0.0.0.0
key_buffer      = 16M
max_allowed_packet  = 16M
thread_stack        = 192K
thread_cache_size       = 8
myisam-recover         = BACKUP
max_connections        = 250
max_connect_errors     = 250
event_scheduler = ON
back_log=100
join_buffer_size=1M
table_cache=4000
query_prealloc_size=32K
query_cache_limit=64M
query_cache_size=64M
query_cache_type=1
key_buffer_size=512M
sort_buffer_size=16M
read_buffer_size=2M
read_rnd_buffer_size=4M
key_cache_age_threshold=3000
key_cache_division_limit=50
memlock=1
max_heap_table_size = 256M
tmp_table_size = 256M
log_error = /var/log/mysql/error.log
slow_query_log = ON
long_query_time = 3
slow_query_log_file = /var/log/mysql/mysql-slow.log
expire_logs_days    = 10
max_binlog_size         = 100M

I read dozens of answers here and on the web (that's how I get this my.cnf) but I still can't figure out how to reduce the CPU load. Can you help me or suggest where to investigate?

Thanks for your great work here!

share|improve this question
    
I forgot to say that in "normal days" we run around 2000 queries per minute –  lucas47 May 29 at 15:33
    
High load is a sign that the server is blocked trying to do something. You need to find out what the block is. It might be MySQL (check your processlist for long-running queries), it could be IO, swapping, etc. –  Alain Collins Jun 2 at 16:16

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.