We recently started having big issues with our website slowing down quite often due to maxed out CPU usage (MySQL process using 200% CPU). The CPU usage is constantly at 60-85% and maxes out multiple times throughout the day where the entire website hangs. We rarely had this issue in the past but we did have occasional CPU spikes caused by MySQL. We had approximately 30,000 products when we got this server but we now have nearly 200,000 products. Our traffic also increased by about 30%.
We are also at 75% constant RAM usage (out of 32 GB) where MySQL is using most of it. Even though the RAM usage is high, it isn't really an issue since it is mostly stable.
As our catalog was growing, we had issues with the database where large operations would often fail (such as catalog search indexing). I am no server optimization expert but I tried using tools such as mysqltuner to tweak the settings and it helped but now I don't know what to do anymore to optimize it further. Unfortunately, I cannot detect a pattern in the CPU spikes. Sometimes it happens with 25 concurrent visitors and other times it is fine with 50 concurrent visitors.
Please help me figure out if my hardware simply isn't powerful enough anymore or there's something wrong with the configuration! I feel like Magento is using an unreasonable amount of resources. Here's a quick summary of our setup and MySQL config file:
Dedicated Server
- Xeon E3-1270 v6
- 500 GB SSD drive
- 32 GB RAM
Store
- 2 storeviews
- 200,000 products
- 4,000 daily visitors (~50 concurrent visitors during peak hours)
MySQL my.cnf file
[mysqld]
skip-external-locking
max_allowed_packet = 268435456
sort_buffer_size = 1M
net_buffer_length = 16K
innodb_file_per_table
thread_cache_size = 32
query_cache_limit = 2M
key_buffer_size = 32M
innodb_autoextend_increment = 512
read_buffer_size = 2M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 128M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
query_cache_type = 0
query_cache_size = 128M
join_buffer_size = 32M
table_open_cache = 2000
performance_schema = ON
innodb_buffer_pool_size = 12G
innodb_log_file_size = 1G
innodb_buffer_pool_instances = 12
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 2
max_connections = 400
max_user_connections = 300
innodb_thread_concurrency = 0
max_heap_table_size = 512M
tmp_table_size = 512M
innodb_use_native_aio = 1
default_storage_engine = MyISAM
default-storage-engine = MyISAM
open_files_limit = 10000
UPDATE
As many of you requested, here's more info on my setup:
- I use Redis as cache storage and session storage
- I am NOT using Varnish yet as my theme requires modification to support full page cache. The header block isn't cacheable. This is something I want to implement soon.
- I am NOT using a CDN because all my users are within Canada
- OP Cache is enabled
- JS CSS HTML min, merge are all enabled
- Flat catalog is enabled
- No suspicious cron jobs
- Production mode is enabled
Below are results of top commands at high load. I/O usage seems to be pretty low. At the same moment I took the screenshots, the access logs showed high activity from Google bots. Even though the number of active users was only 30, the browsing speed of the bots was very fast, quickly clicking through category pages and applying filters etc..
iotop
htop
top
Best Answer