Question :
I need help because I do not understand much MySQL settings on a dedicated server.
I have one WordPress website installed with medium traffic with W3 Total Cache.
The storage engine is InnoDB.
Below is my configuration and report from MySQLTuner which I do not understand much and need some help, please.
Server
CPU: 8 cores / 8 threads, 1 CPU
64bit architecture
L3 cache: 8MB
Speed: 2400 MHz
RAM: 32GB RAM DDR3
Storage: 2x250GB SSD (RAID1)
Link: 1Gbps
Setup
Debian 7 (Linux)
Nginx
PHP 5.4 using php-fpm (cache: PHP-APC, PHP-memcache, Memcached)
MySQL 5.5
MySQL config
key_buffer 512M
max_allowed_packet 16M
thread_stack 256K
thread_cache_size 286
table_cache 1024
table_open_cache 16384
table_definition_cache 8192
sort_buffer_size 32M
read_buffer_size 32M
read_rnd_buffer_size 32M
join_buffer_suze 32M
query_cache_limit 20M
query_cache_size 2048M
tmp_table_size 1024M
max_heap_table_size 1024M
innodb_buffer_pool_size 20G
innodb_buffer_pool_instances 8
[mysqldump]
max_allowed_packet 16M
[isamchk]
key_buffer 16M
MySQLTuner report
-------- CVE Security Recommendations --------------------------------------------------------------
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 1d 21h 26m 35s (8M q [49.822 qps], 471K conn, TX: 399G, RX: 1G)
[--] Reads / Writes: 87% / 13%
[--] Binary logging is disabled
[--] Physical Memory : 31.4G
[--] Max MySQL memory : 42.4G
[--] Other process memory: 5.7G
[--] Total buffers: 23.5G global + 128.2M per thread (151 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[!!] Maximum reached memory usage: 26.8G (85.18% of installed RAM)
[!!] Maximum possible memory usage: 42.4G (135.00% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (0/8M)
[OK] Highest usage of available connections: 17% (26/151)
[OK] Aborted connections: 0.12% (576/471646)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[!!] Query cache may be disabled by default due to mutex contention.
[OK] Query cache efficiency: 59.6% (3M cached / 5M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (42 temp sorts / 629K sorts)
[OK] No joins without indexes
[!!] Temporary tables created on disk: 56% (236K on disk / 414K total)
[OK] Thread cache hit rate: 99% (26 created / 471K connections)
[OK] Table cache hit rate: 84% (1K open / 2K opened)
[OK] Open file limit used: 5% (1K/32K)
[OK] Table locks acquired immediately: 99% (2M immediate / 2M locks)
-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.
[--] Memory used by P_S: 0B
[--] Sys schema isn't installed.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 21.5% (115M used / 536M cache)
[OK] Key buffer size / total MyISAM indexes: 512.0M/416.0K
[OK] Read Key buffer hit rate: 100.0% (148M cached / 16K reads)
[!!] Write Key buffer hit rate: 53.2% (546K cached / 290K writes)
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[!!] InnoDB File per table is not activated
[OK] InnoDB buffer pool / data size: 20.0G/1.9G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (0.048828125 %): 5.0M * 2/20.0G should be equal 25%
[!!] InnoDB buffer pool instances: 8
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 100.00% (12017309000 hits/ 12017381306 total)
[!!] InnoDB Write Log efficiency: 59.93% (169492 hits/ 282840 total)
[OK] InnoDB log waits: 0.00% (0 waits / 113348 writes)
-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.
-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.
-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.
-------- RocksDB Metrics ---------------------------------------------------------------------------
[--] RocksDB is disabled.
-------- Spider Metrics ----------------------------------------------------------------------------
[--] Spider is disabled.
-------- Connect Metrics ---------------------------------------------------------------------------
[--] Connect is disabled.
-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.
-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] Binlog format: STATEMENT
[--] XA support enabled: ON
[--] Semi synchronous replication Master: Not Activated
[--] Semi synchronous replication Slave: Not Activated
[--] This is a standalone server
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Reduce your overall MySQL memory footprint for system stability
Dedicate this server to your database for highest performance.
Enable the slow query log to troubleshoot bad queries
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
Temporary table size is already large - reduce result set size
Reduce your SELECT DISTINCT queries without LIMIT clauses
Consider installing Sys schema from github
Before changing innodb_log_file_size and/or innodb_log_files_in_group read this
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)
innodb_file_per_table=ON
innodb_log_file_size should be (=2G) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
innodb_buffer_pool_instances(=20)
Please help. Thanks.
Answer :
Suggestions to consider for your my.cnf [mysqld] section
Lead with # spacebar to allow defaults to work for you for the following
sort_buffer_size
read_buffer_size
read_rnd_buffer_size
join_buffer_size
the values you have above are impacting the RAM footprint for no useful purpose
thread_cache_size=100 # from 286 for CAP suggested in v 8.0 to avoid OOM
key_buffer_size=256M # from 512M because only 115M used
key_cache_age_threshold=64800 # from 300 seconds to reduce key_reads
key_cache_division_limit=50 # from 100 percent for Hot/Warm caches
key_cache_block_size=16384 # from 1024 to minimize overhead
tmp_table_size=320M # from 1024M for 1% of RAM
max_heap_table_size=320M # should always be same size as tmp_table_size
innodb_buffer_pool_size=4G # from 20G with ~2G of data and ndx's 4G allows growth
for additional assistance, see my profile for contact info.
Would like to have feedback on your success when time permits.