MySQL Performance Tuning

From Thomas-Krenn-Wiki
Jump to: navigation, search

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

Here is a MySQL Cacti sample graph:

Mysql-cacti-graph.png

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)

More information about the script can be found here: http://mysqltuner.com/

Related articles

Linux I/O Performance Tests using dd
SSD Secure Erase
Test TCP Port 25 (smtp) access with telnet