2

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 iotop

htop htop

top enter image description here

  • this is very hard to say without error logs, linux top info at high load, also your database real size. when load goes high what you see in access log? you have too high mysql buffers. do you have varnish? – MagenX Sep 23 at 18:34
  • Try logging and reviewing MySQL queries. You may be able to discern whether certain queries are taking far too long, or if your server is simply not keeping up with the volume. – jiheison Sep 24 at 23:11
  • @Oliver Additional information request. Post on pastebin.com and share the links. Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; E) complete MySQLTuner report F) SHOW ENGINE INNODB STATUS; AND Optional very helpful information, if available includes - htop OR top OR mytop for most active apps, ulimit -a for a linux/unix list of limits, iostat -xm 5 3 for IOPS by device and core/cpu count, for server workload tuning analysis to provide suggestions. – Wilson Hauck Sep 26 at 13:13
  • 1
    Please see my update for more info – Oliver Sep 27 at 21:19
  • @Oliver Please post on pastebin.com and share the links. Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; E) complete MySQLTuner report F) SHOW ENGINE INNODB STATUS; AND Optional very helpful information, if available includes - ulimit -a for a linux/unix list of limits, iostat -xm 5 3 for IOPS by device and core/cpu count, for server workload tuning analysis to provide suggestions. – Wilson Hauck Sep 26 at 13:13 Delete – Wilson Hauck Sep 30 at 21:42
1
  • First, you have to update the Magento version to the latest version.
  • Use Magento 2.3 default elasticsearch instead of Magento MySQL Catalog Search.
  • Use Redis as Cache storage.
  • Optimized the code.
  • using redis for cache storage in 2019 with magento 2 is a waste, Varnish is a must – MagenX Sep 24 at 8:41
  • Magenx why you thought redis for cache storage ? Can please explain it? – Amit Bera Sep 24 at 10:41
  • you better set accent on Varnish as redis on ssd drive not much of a difference. – MagenX Sep 24 at 12:45
1

As you have Mentioned, your Server Configuration looks enough but i guess you should have to increase your SSD, as Mysql operations will required more IOPS. You can check IOPS usage by running the following Command:

iotop

Another Thing which you should have to check is your access log, make sure that the traffic is actual, not the bots, scanners etc. As many bots will increase the traffic on the server.

  • increase your SSD ... there is already 500Gb SSD drive, how exactly increase will speedup everything??? – MagenX Sep 24 at 8:38
  • can you please provide iotop output or screenshot. – Oscprofessionals Sep 24 at 8:43
  • Some more details, you can get with this link from iotop: support.hypernode.com/knowledgebase/… – Oscprofessionals Sep 24 at 9:05
  • Please see my update for more info – Oliver Sep 27 at 21:19
0
  • You have to switch to using Redis as cache stored and the session stored
  • Have to run the performance review and optimize your code
  • Have to using ElasticSearch instead of Mysql Full-Text Search (Because of Catalog Category View is using it too)
  • Using CDN and Varnish Cache for full page cache engine. (Fastly cache or setup Varnish cache by your self)
  • Enable Production Mode, min, merge html, css, js file..
  • Review Cronjob schedule running time to make sure didn't conflict.
0

In Addition, Please use split database. So it will separate your product , customers and order tables and split the load. and also use load balancer as well.

  • why he needs to split the database with 50 users online??? – MagenX Sep 24 at 8:36
0

If you are prepared to spend the money services such as cloudflare can help. Depends how far you want to take it. They can offer help with worldwide distribution to spread load, protect against malicious traffic (although analysis and configuration will be required) and also offer a very decent cache mechanism which you can configure page rules to punch holes for dynamic content. Plus offer edge cache which again spreads load. With CF cache you dont need varnish. Although varnish is still a very good solution.

At this stage I would avoid splitting database. And flattening catalog. Redis is good but only if you have 100s of concurrent users. A decent search solution is a must. Have you looked into algolia? You can manage search yourself but in my experience with that number of SKUs it becomes a time sink.

0

magento version you have is 2.1, web root not pub/, TTFB time is only ~4 seconds, so production mode cache not working, (with proper built theme and production mode TTFB must be ~500ms) what makes me think that you have issues, not optimal code, probably bad customizations or outdated extensions, outdated template design.

please run magento profiler, to see what exactly slows down code execution.

do not change any configuration until you find out profiler details. random copy-paste webstack config changes will bring more confusion later.

-1

Rate Per Second=RPS - Suggestions to consider for your my.cnf [mysqld] section

read_rnd_buffer_size=256K  # from 16M to reduce handler_read_rnd_next RPS
innodb_io_capacity=1900  # from 200 to enable higher IOPS to your devices
innodb_lru_scan_depth=100  # from 1024 to conserve 90% of CPU cycles used for function
query_cache_limit=0  # from 2M to conserve RAM when query_cache_type=0
query_cache_min_res_unit=512  # from default for minimum to conserve QC RAM, when used
query_cache_size=0  # from 128M to conserve RAM when QC is NOT in USE

Disclaimer: I am the content author of website mentioned in my profile, Network profile where we have FREE downloadable Utility Scripts, additional analysis services, contact information. Please post comment after suggestion implementation +7 days on results of positive/negative.

  • @oliver Any reason for TOP's 218 Sleeping PID's that have been sleeping for multiple hours? Please post comment after suggestion implementation +7 days on results of positive/negative performance change for your instance. – Wilson Hauck 7 hours ago

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.