Recently I'm getting high cpu usage from mysql - around 60% - which is choking my Server when combined with my other php5-fpm threads using up to 6% each.
I have an Ubuntu: - 1 x vCPU 2.6GHz - 4GB RAM - SSD Storage I have a lot of tables (thats Magento) and mysqltuner.pl is saying that I have a lot of fragmented tables, but I've read somewhere that this is rarely an issue.
This is my.cnf:
skip-external-locking bind-address = 127.0.0.1 log_slow_queries = /var/log/mysql/mysql-slow.log long_query_time = 4 log-queries-not-using-indexes ft_min_word_len=1 ft_stopword_file ="" innodb_buffer_pool_size=1G thread_stack= 1024k max_allowed_packet = 16M thread_cache_size = 8 myisam-recover = BACKUP max_connections = 50 innodb_thread_concurrency = 4 innodb_flush_log_at_trx_commit = 2 thread_cache_size = 32 table_cache = 512 query_cache_limit = 2M join_buffer_size = 8M tmp_table_size = 256M key_buffer = 256M innodb_autoextend_increment=512 max_allowed_packet = 16M max_heap_table_size = 256M read_buffer_size = 2M read_rnd_buffer_size = 16M bulk_insert_buffer_size = 64M myisam_sort_buffer_size = 128M myisam_repair_threads = 1 # To reduce InnoDB cpu usage when checking query cache # This was set to 64M and changing it to 0 didn't help query_cache_size = 0 log_error = /var/log/mysql/error.log expire_logs_days = 10 max_binlog_size = 100M
What I've tried so far:
- Changed the table_cache to show as "OK" in mysql tuner
- Changed query_cahce to reduce InnoDB cpu cycle usage when checking it
Check Slow Queries, and i'm not sure if they are "slow", this is a capture from the slow-queries log (which goes on like this, rarely up to 0.8):
Query_time: 0.000152 Query_time: 0.000503 Query_time: 0.000395 Query_time: 0.000794 Query_time: 0.000970 Query_time: 0.000837 Query_time: 0.001199 Query_time: 0.001754 Query_time: 0.527001 Query_time: 0.000751 Query_time: 0.000069 Query_time: 0.000197
I'm not sure whats causing it. PS: I have 3-10 online vistors at the same time.
Edit: after trying a lot, I had to increase the CPU cores to 4. In addition to that, I noticed that googlebot & bingbot were crawling my site like crazy because I have a marketplace with more than 25000 different SKU's. So I reduced the crawl rate a bit for each in order to make resources for actual users to use my store.