0

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.

  • Please add to your Question, complete report from Mysqltuner. You have skipped posting first of report that includes by ENGINE, data space used, table counts and other helpful information. Any recommendation should be carefully evaluated. Please check my profile for contact information. – Wilson Hauck Jun 29 '18 at 14:04
  • Please also post complete, not edited, my.cnf or my.ini and post from the OS results of ulimit -a so we can see your OS limits. Thanks – Wilson Hauck Jun 29 '18 at 14:14
  • There are some tips available for you, please login. View my profile, Network profile for Skype contact info, please. – Wilson Hauck Jul 9 '18 at 19:27
0

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.

| improve this answer | |
  • @fritex Would like your Review at my web site, www.mysqlservertuning.com SHOP, Free My TOP 10 Global Variable Suggestions, scroll down to leave review, please. Create account is not required to leave a Review. Create account is required to pick up Utility Scripts+ that you will find helpful. Thanks – Wilson Hauck Aug 5 '18 at 9:46
  • @fritex Would like your Review at my web site, www.mysqlservertuning.com SHOP, Free My TOP 10 Global Variable Suggestions, scroll down to leave review, please. Create account is not required to leave a Review. Create account is required to pick up Utility Scripts+ that you will find helpful. Thanks – Wilson Hauck Sep 8 '18 at 12:10
  • @Fritex Use of our Utility Scripts+ will identify Fragmented tables and find-redundant-indexes.sql both will reduce CPU busy after cleanup of tables and indexes. The BEST to you and your customers/associates. – Wilson Hauck Nov 5 '18 at 22:39

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.