In the following tutorial i will describe the steps to optimize the performance of a MySQL database with the mysqltuner script:
Step1. Download the mysqltuner script:
cd /usr/bin wget http://mysqltuner.pl/mysqltuner.pl chmod +x mysqltuner.pl
Step2. Run mysqltuner:
root@deb:~# /usr/bin/mysqltuner.pl
Enter the root as username and the password and you will get a output like this:
root@deb:~# /usr/bin/mysqltuner.pl >> MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net> >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Run with '--help' for additional options and output filtering Please enter your MySQL administrative login: root Please enter your MySQL administrative password: -------- General Statistics -------------------------------------------------- [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 5.1.58-1~dotdeb.1 [!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM
-------- Storage Engine Statistics ------------------------------------------- [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster [--] Data in MyISAM tables: 414M (Tables: 532) [--] Data in InnoDB tables: 5M (Tables: 1) [!!] Total fragmented tables: 17 -------- Security Recommendations ------------------------------------------- [OK] All database users have passwords assigned -------- Performance Metrics ------------------------------------------------- [--] Up for: 19d 13h 22m 29s (35M q [20.806 qps], 1M conn, TX: 1427B, RX: 8B) [--] Reads / Writes: 77% / 23% [--] Total buffers: 58.0M global + 2.7M per thread (151 max threads) [OK] Maximum possible memory usage: 463.8M (11% of installed RAM) [OK] Slow queries: 0% (4/35M) [!!] Highest connection usage: 100% (152/151) [OK] Key buffer size / total MyISAM indexes: 16.0M/63.7M [OK] Key buffer hit rate: 99.9% (2B cached / 1M reads) [OK] Query cache efficiency: 65.4% (17M cached / 26M selects) [!!] Query cache prunes per day: 30445 [OK] Sorts requiring temporary tables: 0% (100 temp sorts / 910K sorts) [!!] Temporary tables created on disk: 39% (375K on disk / 950K total) [OK] Thread cache hit rate: 99% (6K created / 1M connections) [!!] Table cache hit rate: 0% (64 open / 121K opened) [OK] Open file limit used: 11% (122/1K) [OK] Table locks acquired immediately: 99% (14M immediate / 14M locks) [OK] InnoDB data size / buffer pool: 5.5M/8.0M -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance Enable the slow query log to troubleshoot bad queries Reduce or eliminate persistent connections to reduce connection usage When making adjustments, make tmp_table_size/max_heap_table_size equal Reduce your SELECT DISTINCT queries without LIMIT clauses Increase table_cache gradually to avoid file descriptor limits Variables to adjust: max_connections (> 151) wait_timeout (< 28800) interactive_timeout (< 28800) query_cache_size (> 16M) tmp_table_size (> 16M) max_heap_table_size (> 16M) table_cache (> 64) root@deb:~#
October 27th, 2011 on 08:13
Hello,
If you have a file named /root/.my.cnf with config for credentials (e.g. http://www.togaware.com/linux/survivor/MySQL_Root.html), then the script won’t ask for the login/password combination.
Also, some output from this script is to be taken with a grain of salt. From what I could find, lately, on the subject of MySQL fine-tuning, there are a lot of things to take into account.. So searching for info (a lot of it) on the internet is your best bet.
Also, you could combine the hints from the mysqltuner script to those of mysql tuning primer (https://launchpad.net/mysql-tuning-primer) and mysqlreport (http://hackmysql.com/mysqlreport)
Finally, another advice is to monitor what actually changes in the database’s performance when you change settings. For this, if you’re using cacti, you can use the scripts from http://code.google.com/p/mysql-cacti-templates/ ; and if you’re using munin or collectd, you can find plugins easily.
Make sure you inform yourself about what performance values are supposed to be affected before changing something so that you can expect the kind of change that should happen in your performance graphs.