MySQL Tools: MySQLTuner

3-minute read
mysql • optimization • performance • tuning

I just stumbled across this interesting mysql tuning script called MySQLTuner. Don’t see this (or any other similar script) as the magical tool that will optimize your MySQL server… There is no such thing, because there are so many different applications and each will require its specific database tunings. Still, mysqltuner can be very useful tool in many cases, like for ex:

  • it gives a quick overview on your running mysql configuration and some common sense recommendations.
  • i really liked how it computes the total amount of memory needed for the current configuration (assuming all global buffers will be used and the maximum number of threads being connected).
  • very simple usage: you just have to download and run it.
wget http://mysqltuner.com/mysqltuner.pl
perl mysqltuner.pl

The output for a server that has most of the parameters on default might look like:

# ./mysqltuner.pl
MySQL High-Performance Tuner - Major Hayden
Bug reports, feature requests, and downloads at mysqltuner.com
Run with ‘–help’ for additional options and output filtering
[OK] Currently running supported MySQL version 5.0.44-log
General Statistics
[""] Up for: 15s (15 q [1.000 qps], 8 conn, TX: 20K, RX: 894)
[OK] Maximum possible memory usage: 318.7M (63% of installed RAM)
[OK] Slow queries: 0%
[OK] Highest usage of available connections: 2%
[!!] Key buffer size / total MyISAM indexes: 8.0M/64.8M
[!!] Query cache is disabled
[OK] Temporary tables created on disk: 19%
[!!] Thread cache hit rate: 12%
[OK] Table cache hit rate: 80%
[OK] Open file limit used: 4%
[OK] Table locks acquired immediately: 100%
Recommendations
General recommendations:
MySQL started within last 24 hours - recommendations may be inaccurate
Variables to increase:
key_buffer_size (> 64.8M)
query_cache_size (>= 8M)
Variables to decrease:
long_query_time (<= 5)
max_seeks_for_key (<= 100)

From its documentation:

  • Memory Usage: Calculates MySQL memory usage at max load and makes recommendations for increasing or decreasing the MySQL memory footprint. Per-thread and server-wide buffer data is calculated separately for an accurate snapshot of the server’s configuration.

  • Slow Queries: Reviews the amount of slow queries relative to the total queries. Slow query time limits are also analyzed and recommendations are made.

  • Connections: Current and historical connection counts are reviewed.

  • Key Buffer: Takes configuration data and compares it to the actual indexes found in MyISAM tables. Key cache hit rates are calculated and variable adjustments are suggested.

  • Query Cache: Query cache hit rates and usage percentages are used to make recommendations for the query cache configuration variables.

  • Sorting & Joins: Per-thread buffers that affect sorts and joins are reviewed along with the statistics from the queries run against the server.

  • Temporary Tables: Variable recommendations are made to reduce temporary tables that are written to the disk.

  • Table Cache: Compares total tables opened to the currently open tables. Calculates the table cache hit rate in order to make suggestions.

  • Open Files: Determines if the server will approach or run into the open file limit set by the operating system or the MySQL server itself.

  • Table Locks: Finds table locking that forces queries to wait and makes suggestions for reducing locks that require a wait.

  • Thread Cache: Calculates how many times MySQL must create a new thread to respond to a query.

  • Aborted Connections: Finds applications that are not closing connections to MySQL properly.

  • Read/Write Ratios: Calculates the percentage of read and write operations on your MySQL installation.

Check it out as it is definitely an interesting script that will give you a quick look at the configuration of your mysql server and start your journey to optimize your mysql server.

comments powered by Disqus