MySQL is widely used in business environments but can become very complex when managing databases of significant size. We need to check cache, memory usage, and look for optimization opportunities in various areas.
This documentation will not only cover MysqlTuner, but also how to optimize your MySQL server in general.
>> 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:
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.39-log
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 2G (Tables: 35)[--] Data in InnoDB tables: 7M (Tables: 41)[!!] Total fragmented tables: 43-------- Performance Metrics -------------------------------------------------
[--] Up for: 16h 38m 24s (5K q [0.089 qps], 22 conn, TX: 20M, RX: 3M)[--] Reads / Writes: 65% / 35%
[--] Total buffers: 812.0M global + 1.2M per thread (151 max threads)[OK] Maximum possible memory usage: 991.3M (1% of installed RAM)[!!] Slow queries: 29% (1K/5K)[OK] Highest usage of available connections: 1% (3/151)[OK] Key buffer size / total MyISAM indexes: 128.0M/167.5M
[OK] Key buffer hit rate: 99.1% (59K cached / 557 reads)[!!] Query cache efficiency: 0.1% (4 cached / 3K selects)[OK] Query cache prunes per day: 0[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 6 sorts)[OK] Temporary tables created on disk: 9% (8 on disk / 87 total)[OK] Thread cache hit rate: 86% (3 created / 22 connections)[!!] Table cache hit rate: 17% (64 open / 376 opened)[OK] Open file limit used: 16% (121/755)[OK] Table locks acquired immediately: 100% (4K immediate / 4K locks)[!!] Connections aborted: 13%
[OK] InnoDB data size / buffer pool: 7.9M/512.0M
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Increase table_cache gradually to avoid file descriptor limits
Your applications are not closing MySQL connections properly
Variables to adjust:
query_cache_limit (> 1M, or use smaller result sets) table_cache (> 64)
Created_tmp_disk_tables: should be 0 as often as possible (except when having blob or text fields, not much can be done)
Created_tmp_files: when Created_tmp_disk_tables is not sufficient and more files need to be created on disk
Created_tmp_tables: number of times temporary tables have been created
Here, I’d like to improve this a bit, although it’s not really necessary given the small number of disk tables created. Let’s check the current maximum allowed size:
To monitor slow queries, we need to enable slow_query and set a maximum value before queries are logged (here 1 second). First, let’s see if it’s enabled:
Qcache_free_memory: Free memory to add queries in the query cache (storing the query + result (130 MB here))
Qcache_hits: number of cache hits
Qcache_inserts: Qcache_hits should be higher than Qcache_inserts for optimal cache performance (here since there isn’t much traffic, this doesn’t increase)
For better performance, determine if the query cache should be kept. If:
Qcache_hits < Qcache_inserts: disable the cache
Qcache_not_cached < (Qcache_hits + Qcache_inserts): Try increasing cache size and query size limit. If Qcache_not_cached continues to rise, disable the cache
Qcache_lowmem_prunes: if no more space in cache, old queries will be replaced by new ones. Increase cache if you have too many Qcache_lowmem_prunes
Here Table_locks_waited is 0, which is perfect. This number generally increases when you have high load and are not using the right engine (typically using MyISAM instead of InnoDB).
That’s: (89718784 + 131317760) =~ 100MB
So we’ll increase the buffer pool to 128MB (more than necessary):
1
2
3
...
innodb_buffer_pool_size= 128M
..
Let’s also change the default parameters with best practices. Note that changing the log size will require some modifications before restarting. Modify your MySQL configuration like this:
1
2
3
4
5
6
7
8
9
10
11
...default-storage-engine=innodbinnodb_buffer_pool_size=128Minnodb_data_file_path=ibdata1:10M:autoextendinnodb_additional_mem_pool_size=20Minnodb_file_per_table# Warning : changing this needs stop of mysql, removal (backup of ib_log* files), and mysql startupinnodb_log_file_size=256Minnodb_log_buffer_size=8Minnodb_flush_log_at_trx_commit=1...
Then follow these steps:
Backup the ‘ib_log*’ files
Stop the MySQL server
Delete the ‘ib_logs*’ files
Restart the server
Note: on Debian, you might hit the timeout of the service, but it will still start (a small pgrep -f mysql will show where it is, as well as the logs).
Let’s increase it to 128 MB. However, we won’t do it on-the-fly (side effects almost guaranteed) and it’s not recommended to exceed 4GB (here we have plenty of room).
To reset global status, you need to run this command:
1
flush status;
Now you can see it:
1
show global status;
If you restart your MySQL server, it does the same thing.
Increasing Performance for Temporary File Access link
The idea is to point temporary files to a RAM filesystem to store temporary files. This will significantly increase performance. First, build your temporary filesystem:
It’s important to know what can be optimized in your table structure for all tables in the database. There is a command to analyze a database and provide optimization hints per column:
1
SELECT*FROMmy_tablePROCEDUREANALYSE();
You can then compare with the current table structure:
I created a small Perl script to automate database optimization. To use it, simply use a user who has select and insert rights on all databases. You can also exclude certain databases. To ensure you have the latest version of this script, go to my git. Here’s the beginning of the code:
#!/usr/bin/perl#===============================================================================## FILE: optimizer.pl## USAGE: ./optimizer.pl## DESCRIPTION: MySQL Automatic Tables Optimizer## OPTIONS: ---# REQUIREMENTS: ---# BUGS: ---# NOTES: ---# AUTHOR: Pierre Mavro (), xxx@mycompany.com# COMPANY:# VERSION: 0.1# CREATED: 08/04/2010 17:50:23# REVISION: ---#===============================================================================usestrict;
usewarnings;
useDBI;
# MySQL configuration# This user should have insert and select rightsmy$host='localhost';
my$database='mysql';
my$user='optimizer';
my$pw='optimizer';
my@exclude_databases=qw/information_schema database1 database2/;
########## DO NOT TOUCH NOW ########### Varsmy (@all_databases,@all_tables);
my ($aref, $cur_database, $cur_table);
# Connect to the BDDmy$dbdetails="DBI:mysql:$database;host=$host";
my$dbh=DBI->connect($dbdetails, $user, $pw) ordie"Could not connect to database: $DBI::errstr\n";
# Get all databasesmy$sth=$dbh->prepare(q{SHOW DATABASES}) ordie"Unable to prepare show databases: ".$dbh->errstr."\n";
$sth->executeordie"Unable to exec show databases: ".$dbh->errstr."\n";
while ($aref=$sth->fetchrow_arrayref)
{
push(@all_databases,$aref->[0]);
}
$sth->finish;
# Disconnect the BDD$dbh->disconnect();
# Optimize all tables of all databasesmy$unwanted_found=0;
foreach$cur_database (@all_databases)
{
# Exclude optimization on unwanted databasesforeach (@exclude_databases)
{
if ($cur_databaseeq$_)
{
$unwanted_found=1;
last;
}
}
if ($unwanted_found==1)
{
$unwanted_found=0;
next;
}
# Connect on databasemy$dbdetails="DBI:mysql:$cur_database;host=$host";
my$dbh=DBI->connect($dbdetails, $user, $pw) ordie"Could not connect to database: $DBI::errstr\n";
# Get the List of tables@all_tables=$dbh->tables;
# Optimize all tablesforeach$cur_table (@all_tables)
{
$dbh->do("optimize table $cur_table");
}
$dbh->disconnect();
}
Here’s a small patch I wrote to fix a bug when trying to connect to a port other than the default:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
*** mysqltuner.pl.old mar. avr 10 09:26:01 2012
--- mysqltuner.pl mar. avr 10 09:28:14 2012
***************
*** 274,280 ****
}
# Did we already get a username and password passed on the command line?
if ($opt{user} ne 0 and $opt{pass} ne 0) {
! $mysqllogin = "-u $opt{user} -p'$opt{pass}'".$remotestring;
my $loginstatus = `mysqladmin ping $mysqllogin 2>&1`;
if ($loginstatus =~ /mysqld is alive/) {
goodprint "Logged in using credentials passed on the command line\n";
--- 274,280 ----
}
# Did we already get a username and password passed on the command line?
if ($opt{user} ne 0 and $opt{pass} ne 0) {
! $mysqllogin = "-u $opt{user} -p'$opt{pass}' -P $opt{port}".$remotestring;
my $loginstatus = `mysqladmin ping $mysqllogin 2>&1`;
if ($loginstatus =~ /mysqld is alive/) {
goodprint "Logged in using credentials passed on the command line\n";
I submitted it, but since there’s no maintainer anymore…