1

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.

  • Expect Magento to choke on a single processor server if you have any appreciable traffic. You have three servers in one (http, database, php plus the server OS) trying to battle it out in a one room house. 25,000 skus and simultaneous visit from Bing and Google is appreciable traffic. – Fiasco Labs Feb 14 '16 at 18:31
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.

  • Thanks, can you please explain some of the key changes and whats their role? – Sari Alalem Feb 13 '16 at 13:48
  • your values too big. if you run mysqltuner.pl it will be more corrected. – MagenX Feb 13 '16 at 14:22
1

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

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.