Improving Performance

Question

We run a Magento 1.9 online store. It contains 110,000 products. We are running into issue around long page load times for category pages and product pages.

Dedicated WebApp server specs: - Apache 2.4 - PHP 5.6 - PHP.ini heavily tweaked, along with an 8GB Redis cache - 6 CPU Cores - 16GB Memory - SSD Storage

Dedicated Database Server: - MySQL 5.6 (tuned with MySQL Tuner). - 6 CPU Cores - 16GB Memory - SSD Storage

The site hasn't launched yet as we are trying to fix these performance issues.

Seems to me the bottleneck is the database. I've read nearly every article I could find on improving performance but we are still at 6-11s load times.

[mysqld]
innodb_buffer_pool_instances = 11
innodb_log_file_size = 1024M
innodb_buffer_pool_size = 12000M
query_cache_size = 64M
query_cache_type = 0
query_cache_limit = 2M
key_buffer_size = 36M
max_heap_table_size = 16M
tmp_table_size = 256M
join_buffer_size = 8M
max_allowed_packet = 16M
innodb_lock_wait_timeout=300
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_thread_concurrency = 12
max_connections = 500
thread_cache_size = 32
thread_concurrency = 12
innodb_flush_log_at_trx_commit = 2
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Actual server resource usage is low. I'd love any recommendations on how we can improve the load speed and increase server resource usage of CPU and Memory.

• MagenX: correct way to debug it - do not change any settings - install Aoe_Profiler
• James: Thanks, this AOE Profiler gives an amazing graphical break down. This is indicating that header.phtml is taking 5.1s to load. Time to do some more investigation
• Wilson Hauck: 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 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, df -h for a linux/unix free space list by device, for server workload tuning analysis.
• Wilson Hauck: @james If you could post additional information requested May 31, suggestions will be made for you to improve performance.
• Wilson Hauck: @james If you could post additional information requested May 31, suggestions will be made for you to improve performance - 3rd time might be the charm.

Answer 1

Suggestions to consider for your my.cnf [mysqld] section

innodb_lru_scan_depth=100 # from 1024 to reduce amount of CPU work every second by 90% for this function
innodb_read_io_threads=64 # from 8 to raise restriction
innodb_write_io-threads=64 # from 8 to raise restriction
thread_cache_size=100 # from 32 to reduce threads_created
query_cache_size=0 # from 64M conserve RAM since query_cache_type=0 which is OFF
query_cache_limit=0 # from 2M to conserve RAM
tmp_table_size=160M # from 256M for 1% RAM
max_heap_table_size=160M # from 16M to be matched to tmp_table_size

Disclaimer: I am the content author of website mentioned in my profile, Network profile with contact info. Please post comment on positive / negative results after implementation + 24 hours.

• Wilson Hauck: @james One more suggestion, innodb_io_capacity=1900 # from 200 to allow higher IOPS to your SSD devices Have you used any of the suggestions of July 30, 2019? Results positive/negative? Please leave comment.
• Wilson Hauck: @james Did you get your site to production? Were the suggestions above helpful?
• Wilson Hauck: @james Still wondering if you got your Magento site to production? How is performance? Posting additional information requested on May 31, 2019 will still get you some suggestions to improve performance. The three year old offer.

Answer 2

Flat catalog tables enabled? If not, enable them. The unions for EAV attributes are time consuming. If large tables are being sorted, it has to write to disk to sort. If you have too many attributes, you should be sure they are properly configured.

I won’t dive into the fact that you are launching on an end of life version of PHP where 7.x has proven to have a major performance improvement.

—— update

Check to make sure that your top navigation is being cached.

• MagenX: php version is not a problem here
• df2k2: I understand your point of view that php 5.6 is not a problem here, but to me, any end-of-life php version is a problem. Php 7.x serves twice the requests in half the time, so if it’s not a problem now, it will be eventually.
• Dominic Pixie: I support the comment suggesting upgrading to php 7. Difference between this and 5.6 is noticeable. The only way to get 5.6 to work as fast is to implent opcache.