Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2008
    Posts
    63

    MySQL server tuning advice

    Here is my server mysqltuner report, any advice?

    -------------------------------------------------
    mysqltuner output
    -------------------------------------------------
    mysqltuner.pl [found]
    >> MySQLTuner 1.6.4 - Major Hayden <major@mhtx.net>
    >> Bug reports, feature requests, and downloads at http://mysqltuner.com/
    >> Modified by George Liu (eva2000) at http://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: ****931
    [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: http://bit.ly/1mi7c4C
    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)

  2. #2
    Join Date
    May 2005
    Location
    London, England
    Posts
    116
    This would be my major concern:

    [!!] Maximum reached memory usage: 155.1G (1333.65% of installed RAM)
    [!!] Maximum possible memory usage: 209.9G (1804.55% of installed RAM)

    You need to add more memory to your system or reduce down the cache sizes.
    Jacob Colton - catalyst2.com - 0800 107 7979
    Specialists in UK Managed Web Hosting for over 16 years.
    jacob@catalyst2.com Test us today: support@catalyst2.com
    Shared/Resellers/Email/Managed Servers/VMWare Hosting

  3. #3
    Hi,

    Please make sure your MySQL is online at least for about 48 hours to get more appropriate recommendations. Apart from this MySQL tuner, you can also use mysql primer to check the mysql variable optimizations.
    www.24x7servermanagement.com
    Server Management, Server Security, Server Monitoring.
    India's Leading Managed Service Provider !! Skype: techs24x7

Similar Threads

  1. Could use some server tuning advice
    By links1979 in forum Hosting Security and Technology
    Replies: 8
    Last Post: 05-22-2013, 10:39 PM
  2. MySQL Server - Advice Needed Please
    By Send Media in forum Dedicated Server
    Replies: 18
    Last Post: 01-24-2013, 09:21 PM
  3. need advice on mysql server
    By AEROSQL in forum Dedicated Server
    Replies: 3
    Last Post: 07-19-2005, 07:56 PM
  4. Strange MySql / Server Program . .. .
    By inbuco in forum Hosting Security and Technology
    Replies: 7
    Last Post: 06-29-2001, 06:55 PM
  5. Fast sendmail or PHP/mySQL server ...
    By JHalbrook in forum Dedicated Server
    Replies: 1
    Last Post: 01-29-2001, 05:45 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •