High CPU Usage in mysql

Question

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.

Answer 1

high load with one CPU is normal, because all processes use it.

here is your correct config:

skip-external-locking
bind-address            = 127.0.0.1
innodb_buffer_pool_size = 1G
max_allowed_packet      = 64M
thread_cache_size       = 8
myisam-recover         = BACKUP
max_connections        = 50
innodb_thread_concurrency = 0
innodb_flush_log_at_trx_commit = 2
table_definition_cache = 512
table_open_cache = 512
query_cache_limit = 2M
query_cache_type = 1
query_cache_size = 64M
join_buffer_size = 512K
tmp_table_size = 64M
key_buffer = 16M
innodb_autoextend_increment=512
max_heap_table_size = 64M
read_buffer_size = 512K
read_rnd_buffer_size = 512K
bulk_insert_buffer_size = 512K
interactive_timeout = 7200
wait_timeout = 7200
net_read_timeout = 120
net_write_timeout = 300
log_error = /var/log/mysql/error.log

you need to add one more cpu core OR if you stay with one cpu then -

please use full page cache plugins for magento. also go for hhvm, it will reduce cpu load.

Answer 2

If you have 3-10 visitors concurrently clicking your site and you also have your webserver and mysql server on the same machine, 1 CPU is not enough.

You should have at least 2 -4 cpu cores assigennd to the machine

Answer 3

1 Core with Magento, that simply won't work. Magento needs it's power, especially while you build out your, categories, products, attributes, modules. There is many factors to what you need. A similar answer has been answered before and should help you along you Magento journey.

how to determine hosting requirements