Tune MySQL database with mysqltuner in easy steps

mysqltuner is an excellent tool to analyze MySQL database server.It gives you insight information about your MySQL database server.In brief it gives below information

  1. General Statistics
  2. Storage engine statistics
  3. Performance metrics
  4. Recommendations  (two parts one is general recommendations and variables to adjust)

So if you are using MySQL as database it is good idea to install this on your system.To install mysqltuner on debian vps follow below steps

  • Log into your SSH terminal as root or any other user
  • Type below command in terminal to install mysqltuner on VPS
#apt-get install mysqltuner
  • After installing mysqltuner you can run it using below command on terminal command prompt
#mysqltuner
  • You will be prompted for MySQL user name and password.Enter the details asked.
  • You will see below information after entering the detail(the information below is taken from one of my test servers and might vary a bit in your case.It is included here to give you detail of information printed).
 >>  MySQLTuner 1.0.1 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.29-1~dotdeb.0
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
[--] Data in MyISAM tables: 4M (Tables: 18)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] InnoDB is enabled but isn't being used
[!!] Total fragmented tables: 1

-------- Performance Metrics -------------------------------------------------
[--] Up for: 22h 1m 52s (59K q [0.753 qps], 6K conn, TX: 106M, RX: 7M)
[--] Reads / Writes: 95% / 5%
[--] Total buffers: 240.0M global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 645.8M (84% of installed RAM)
[OK] Slow queries: 0% (2/59K)
[OK] Highest usage of available connections: 6% (10/151)
[OK] Key buffer size / total MyISAM indexes: 16.0M/2.8M
[OK] Key buffer hit rate: 99.5% (1M cached / 4K reads)
[OK] Query cache efficiency: 31.2% (10K cached / 34K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 8K sorts)
[!!] Temporary tables created on disk: 34% (3K on disk / 8K total)
[OK] Thread cache hit rate: 99% (14 created / 6K connections)
[OK] Table cache hit rate: 23% (64 open / 272 opened)
[OK] Open file limit used: 8% (89/1K)
[OK] Table locks acquired immediately: 99% (32K immediate / 32K locks)

-------- Recommendations -----------------------------------------------------
General recommendations:
    Add skip-innodb to MySQL configuration to disable InnoDB
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
  • It is best to let database server run for more than 24 hours before conducting this test.
  • You should adjust the values in mysql configuration file (/etc/mysql/mysql.cnf) and test again to see the impact.
  • To capture the output in file you can use below command.
#mysqltuner > data
  • The output of command will go to file data which you can use later while optimizing your configuration file.

Happy optimizing MySQL database.Consider sharing the post if you find it useful.Sharing may help others find the post and is a way to thank me.

Leave a Comment