MySQL Performance Analyzation and Tuning

Please note that this article / this category refers either on older software / hardware components or is no longer maintained for other reasons. This page is no longer updated and is purely for reference purposes still here in the archive available.

This article describes different ways to analyse and optimize a MySQL server's performance. Different tools and literature are shown.

Theory

MySQL Training/Certification

MySQL offers various trainings, some of them can also completed with a certification exam.

These is short list of possibilities:

MySQL for Database Administrators (CMDBA)
MySQL for Developers (CMDEV)
MySQL Cluster Database Administrator (CMCDBA)
MySQL Performance Tuning

All information on trainings can be found here: http://www.mysql.com/training

Literature

MySQL Performance Blog von Percona [1]
mySQL DBA Blog [2]
MySQL Community Librarian [3]
Book "High Performance MySQL" from O'Reilly
Pro-Linux Article (only in German) [4]

Monitoring Software

MySQL Enterprise Monitor from MySQL (kommerziell), http://www.mysql.com/products/enterprise/monitor.html
Kontrollbase from Kontrollsoft, http://kontrollsoft.com/software-kontrollbase
Nagios Plugins, see Monitoringexchange or Nagios Exchange
Cacti Mysql Templates, http://code.google.com/p/mysql-cacti-templates/

Here is a MySQL Cacti sample graph:

Tuning Tools

mysqltuner.pl

This perl script allows a fast and easy overview of the MySQL server. The script gives recommendations for configuration enhancements to improve the performance.

The following two steps are necessary to execute:

Download the latest version with wget:

server:~# wget http://mysqltuner.pl

Alternatively when using Ubuntu or Debian, mysqltuner can be installed directly from the repositories.

server:~# apt-get install mysqltuner

Next step: start MySQL-Tuner. Possibly login credentials need to be supplied.

Here a sample output of a MySQL server that has not been further optimized:

server:~# perl mysqltuner.pl 

 >>  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
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password: XXXXXXXX

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.51a-24+lenny2+spu1
[OK] Operating on 32-bit architecture with less than 2GB RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
[--] Data in MyISAM tables: 1M (Tables: 26)
[--] Data in InnoDB tables: 22M (Tables: 39)
[--] Data in MEMORY tables: 0B (Tables: 1)
[!!] Total fragmented tables: 10

-------- Performance Metrics -------------------------------------------------
[--] Up for: 11d 16h 28m 56s (8M q [8.714 qps], 597K conn, TX: 2B, RX: 1B)
[--] Reads / Writes: 93% / 7%
[--] Total buffers: 58.0M global + 2.6M per thread (100 max threads)
[OK] Maximum possible memory usage: 320.5M (15% of installed RAM)
[OK] Slow queries: 0% (0/8M)
[OK] Highest usage of available connections: 19% (19/100)
[OK] Key buffer size / total MyISAM indexes: 16.0M/1.9M
[OK] Key buffer hit rate: 99.4% (266K cached / 1K reads)
[OK] Query cache efficiency: 70.2% (4M cached / 6M selects)
[!!] Query cache prunes per day: 2132
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 586K sorts)
[!!] Temporary tables created on disk: 30% (24K on disk / 81K total)
[OK] Thread cache hit rate: 99% (1K created / 597K connections)
[!!] Table cache hit rate: 14% (64 open / 433 opened)
[OK] Open file limit used: 0% (6/1K)
[OK] Table locks acquired immediately: 100% (2M immediate / 2M locks)
[!!] InnoDB data size / buffer pool: 22.1M/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    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
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    query_cache_size (> 16M)
    tmp_table_size (> 32M)
    max_heap_table_size (> 16M)
    table_cache (> 64)
    innodb_buffer_pool_size (>= 22M)