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.
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).
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:
...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).
Checking Open Files
It can be important to see which tables cannot be cached (and therefore files on disk):
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).
If you restart your MySQL server, it does the same thing.
Increasing Performance for Temporary File Access
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:
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();}
Patch mysqltuner
Here's a small patch I wrote to fix a bug when trying to connect to a port other than the default:
*** 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...