The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

MySQL server tuning advice

Discussion in 'Workarounds and Optimization' started by Masimo, Feb 7, 2016.

  1. Masimo

    Masimo Well-Known Member

    Nov 19, 2015
    Likes Received:
    Trophy Points:
    cPanel Access Level:
    Root Administrator
    Here is my server mysqltuner report, any advice?

    mysqltuner output
    ------------------------------------------------- [found]
    >> MySQLTuner 1.6.4 - Major Hayden <>
    >> Bug reports, feature requests, and downloads at MySQLTuner-perl by major
    >> Modified by George Liu (eva2000) at
    >> 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 -------------------------------------------
    [--] 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 at 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)
    #1 Masimo, Feb 7, 2016
    Last edited by a moderator: Feb 8, 2016
  2. vanessa

    vanessa Well-Known Member

    Sep 26, 2006
    Likes Received:
    Trophy Points:
    Virginia Beach, VA
    cPanel Access Level:
    DataCenter Provider
    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.
  3. Masimo

    Masimo Well-Known Member

    Nov 19, 2015
    Likes Received:
    Trophy Points:
    cPanel Access Level:
    Root Administrator
    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)

  4. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Apr 11, 2011
    Likes Received:
    Trophy Points:
    cPanel Access Level:
    Root Administrator

Share This Page