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.