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:
- Xeon E3-1270 v6
- 500 GB SSD drive
- 32 GB RAM
- 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
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..