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!