MySql configuration for huge Magento

Question

Below is the report from mysqltuner and than at last I have written server stats and actual question.

Performance Metrics

[--] Up for: 2d 14h 28m 52s (59M q [266.240 qps], 96K conn, TX: 114G, RX: 16G)

[--] Reads / Writes: 18% / 82%

[--] Binary logging is disabled

[--] Physical Memory : 125.9G

[--] Max MySQL memory : 381.5G

[--] Other process memory: 308.9M

[--] Total buffers: 59.2G global + 66.0M per thread (5000 max threads)

[--] P_S Max memory usage: 72B

[--] Galera GCache Max memory usage: 0B

[OK] Maximum reached memory usage: 61.3G (48.69% of installed RAM)

[!!] Maximum possible memory usage: 381.5G (302.98% of installed RAM)

[!!] Overall possible memory usage with other process exceeded memory

[OK] Slow queries: 0% (171/59M)

[OK] Highest usage of available connections: 0% (32/5000)

[OK] Aborted connections: 0.00% (1/96766)

[!!] Query cache may be disabled by default due to mutex contention.

[OK] Query cache efficiency: 88.5% (34M cached / 39M selects)

[OK] Query cache prunes per day: 0

[OK] Sorts requiring temporary tables: 0% (264 temp sorts / 276K sorts)

[!!] Joins performed without indexes: 13649

[OK] Temporary tables created on disk: 3% (23K on disk / 641K total)

[OK] Thread cache hit rate: 99% (32 created / 96K connections)

[OK] Table cache hit rate: 70% (7K open / 10K opened)

[OK] Open file limit used: 0% (124/1M)

[OK] Table locks acquired immediately: 100% (1K immediate / 1K locks) MyISAM Metrics

[!!] Key buffer used: 18.1% (27M used / 150M cache)

[!!] Key buffer size / total MyISAM indexes: 144.0M/147.8M

[!!] Read Key buffer hit rate: 86.8% (38 cached / 5 reads)

InnoDB Metrics

[--] InnoDB is enabled.

[--] InnoDB Thread Concurrency: 16

[OK] InnoDB File per table is activated

[OK] InnoDB buffer pool / data size: 56.0G/8.5G

[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (2.67857142857143 %): 768.0M * 2/56.0G should be equal 25%

[!!] InnoDB buffer pool instances: 32

[--] Number of InnoDB Buffer Pool Chunk : 448 for 32 Buffer Pool Instance(s)

[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances

[OK] InnoDB Read buffer efficiency: 100.00% (13238376177 hits/ 13238716229 total)

[OK] InnoDB Write log efficiency: 99.46% (175848562 hits/ 176798170 total)

[OK] InnoDB log waits: 0.00% (0 waits / 949608 writes)

General recommendations:
Control warning line(s) into /my/path/to/log/mysql-error.log file
Control error line(s) into /my/path/to/log/mysql-error.log file
Reduce your overall MySQL memory footprint for system stability
Dedicate this server to your database for highest performance.
Adjust your join queries to always utilize indexes

Variables to adjust:

MySQL's maximum memory usage is dangerously high

Add RAM before increasing MySQL buffer variables

query_cache_size (=0)

query_cache_type (=0)

join_buffer_size (> 16.0M, or always use indexes with joins)

key_buffer_size (> 147.8M)

innodb_log_file_size * innodb_log_files_in_group should be equal to 1/4 of buffer pool size (=28G) if possible.

innodb_buffer_pool_instances(=56)

System Configuration

• Ubuntu 16.04.5 LTS

• Mysql Version : 5.7.22

I am using magento 1.9.3.4 with one website, 3 stores and 100k products, with around 1700 Categories.

Apart from the above Mysql recommendations, the other thing I notice is, when I load category in browser, mysql consumes 40-50% CPU usage.

Based on my RAM if some one can help to configure best suitable mysql configuration, than it would so so grateful.

Also Please keep in mind, since we have 100k+ products, speed is major issue, so any change in mysql configuration, should not drain the speed, instead if it could help in increasing speed than that would be great way to move ahead.

Thanks you.

Answer 1

Suggestions for your my.cnf [mysqld] section

thread_cache_size=64  # from 32 to expand capacity
innodb_lru_scan_depth=100  # from 1024 to conserve 90% of CPU cycles used for function every second

Disclaimer: I am the content author of website mentioned in my profile, Network profile where we have downloadable FREE Utility Scripts to assist with performance tuning and contact info.