MySQL server tuning advice

Here is my server mysqltuner report, any advice?


-------------------------------------------------
mysqltuner output
-------------------------------------------------
mysqltuner.pl [found]
>> MySQLTuner 1.6.4 - Major Hayden 
>> Bug reports, feature requests, and downloads at MySQLTuner-perl by major
>> Modified by George Liu (eva2000) at vbtechsupport.com
>> Run with '--help' for additional options and output filtering
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.6.25
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 8G (Tables: 26269)
[--] Data in InnoDB tables: 2G (Tables: 9480)
[--] Data in MEMORY tables: 11M (Tables: 328)
[!!] Total fragmented tables: 220

-------- CVE Security Recommendations ---------------------------------------
[--] Skipped due to --cvefile option undefined

-------- Performance Metrics -------------------------------------------------
[--] Up for: 23h 32m 26s (23M q [275.291 qps], 498K conn, TX: 209B, RX: 3B)
[--] Reads / Writes: 81% / 19%
[--] Binary logging is disabled
[--] Total buffers: 5.1G global + 524.2M per thread (400 max threads)
[!!] Maximum reached memory usage: 155.1G (1333.65% of installed RAM)
[!!] Maximum possible memory usage: 209.9G (1804.55% of installed RAM)
[OK] Slow queries: 0% (1K/23M)
[OK] Highest usage of available connections: 73% (293/400)
[OK] Aborted connections: 1.25% (6224/498065)
[OK] Query cache efficiency: 77.9% (15M cached / 19M selects)
[!!] Query cache prunes per day: 1340931
[OK] Sorts requiring temporary tables: 3% (68K temp sorts / 1M sorts)
[!!] Joins performed without indexes: 78277
[!!] Temporary tables created on disk: 48% (449K on disk / 926K total)
[OK] Thread cache hit rate: 99% (2K created / 498K connections)
[!!] Table cache hit rate: 1% (4K open / 257K opened)
[OK] Open file limit used: 4% (4K/100K)
[OK] Table locks acquired immediately: 99% (6M immediate / 6M locks)

-------- MyISAM Metrics ------------------------------------------------------
[!!] Key buffer used: 45.2% (181M used / 402M cache)
[OK] Key buffer size / total MyISAM indexes: 384.0M/1.2G
[OK] Read Key buffer hit rate: 100.0% (2B cached / 373K reads)
[!!] Write Key buffer hit rate: 67.9% (3M cached / 1M writes)

-------- InnoDB Metrics ------------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 4.0G/3.0G
[OK] InnoDB buffer pool instances: 4
[!!] InnoDB Used buffer: 22.04% (57771 used/ 262140 total)
[OK] InnoDB Read buffer efficiency: 100.00% (6593396942 hits/ 6593432316 total)
[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
[OK] InnoDB log waits: 0.00% (0 waits / 443542 writes)

-------- ThreadPool Metrics --------------------------------------------------
[--] ThreadPool stat is disabled.

-------- AriaDB Metrics ------------------------------------------------------
[--] AriaDB is disabled.

-------- TokuDB Metrics ------------------------------------------------------
[--] TokuDB is disabled.

-------- Galera Metrics ------------------------------------------------------
[--] Galera is disabled.

-------- Replication Metrics -------------------------------------------------
[--] No replication slave(s) for this server.
[--] This is a standalone server..

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Restrict Host for user@% to user@SpecificDNSorIp
MySQL started within last 24 hours - recommendations may be inaccurate
Reduce your overall MySQL memory footprint for system stability
Increasing the query_cache size over 128M may reduce performance
Adjust your join queries to always utilize indexes
Temporary table size is already large - reduce result set size
Reduce your SELECT DISTINCT queries without LIMIT clauses
Increase table_open_cache gradually to avoid file descriptor limits
Read this before increasing table_open_cache over 64: table_cache negative scalability - MySQL Performance Blog
Beware that open_files_limit (100000) variable
should be greater than table_open_cache ( 4000)
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
*** Please note this recommendation is not entirely accurate.
The formula used to calculate max memory usage assumes all queries utilise
all memory buffers simultaneously. When in reality it is very rare for a
query to engage & utilise all memory buffers simultaneously.

Formula also assumes all predefined max_connections are reached.
You could have set max_connections = 1000 and in a whole year
of usage never hit beyond 50 max_used_connections. So your real MySQL
memory usage is only 1/20th of theorectical max memory usage reported.

So real max memory usage will never reach this peak.
So do not be too concerned with this warning.

It is better to monitor your real MySQL max_used_connection and MySQL
memory usage over time and adjust accordingly.

You can use tools such as Cacti, Munin or mysqlmymonlite.sh at mysqlmymon.com to
monitor your MySQL memory usage over time.

MySQL performs optimally when its required amount of memory is met.
Reducing and starving MySQL memory allocation to adhere to this
artificial max memory warning - of which in reality will never be reached,
will only reduce MySQL performance in many cases ***


query_cache_size (> 512M) [see warning above]
join_buffer_size (> 512.0M, or always use indexes with joins)
table_open_cache (> 4000)

vanessa

February 08, 2016 16:00

The best advice we can give is whatever the script says. Many of the tunables are dependent on specific things such as server activity, amount of RAM, etc. None of which is information any of us have. That's why said script exists.

Masimo

February 09, 2016 06:20

Im on trust with mysqltuner script, server working fine too. What im little worry is: [!!] Maximum reached memory usage: 155.1G (1333.65% of installed RAM) [!!] Maximum possible memory usage: 209.9G (1804.55% of installed RAM) :D

cPanelMichael

February 09, 2016 19:22

Hello :) You may want to consider using a third-party application such as Cloud Linux for the "MySQL Governor" feature: CloudLinux Documentation Thank you.

MySQL server tuning advice

Comments

Didn't find what you were looking for?