Optimizing MySQL for performance

[Shogun 4463]

So yesterday I got a PM asking for help optimizing a MySQL server and I though Icould just as well share what would be my answer with everyone.

 

First of all make sure you are using a recent version of MySQL (5.6 at least) or MariaDB (10.3 or newer) as they generally should perform better than older versions.

 

Upgrading your MySQL is easy - just uninstall the current version (ie: pkg delete mysql55-server) and install the new one (pkg install mysql57-server). You can find what is the currently installed version with pkg info. You may need to also uninstall and install the client separately.

 

Note that when you uninstall MySQL it doesn't delete you data so you should be able to start your upgraded server straight away, unless you're moving to a very different version - in that case you may need to run mysql_upgrade, through newer versions of MySQL and MariaDB will do this themselves. Regardless, it's a good idea to make a backup first: either a physical copy (meaning, just copy the files in /var/db/mysql elsewhere, but stop the mysql service first to avoid table corruption!) - if we are using a still supported version we can install again in case of a problem - or backing up with mysqldump if we aren't.

 

If you have been using your own my.cnf file for a long time, installing a new version will not replace it with fresh defaults, which means you may be using obsolete settings here that will prevent mysql from starting - just check your MySQL log at /var/db/mysql/<yourhostname>.err after starting the service to make sure everything is fine.

 

So now that we are running a recent version, let's get to optimization proper. I am not going to explain how to find yourself what to optimize - that's what Database Administration degrees are for. I will just point you to some useful tools. It is recommended, in the first place, that you migrate as many tables as you can to InnoDB. If you use Navicat, and even if you don't, this is a very trivial task.

 

ZFS tuning for MySQL

 

If you have a good amount of InnoDB tables and you are using the ZFS filesystem (OVH installs ZFS by default for example) you may want to set up specific filesystems for them to match the block size of the InnoDB tables. Caution, this procedure is not advised for begginers:

 

First thing, add these settings to my.cnf under the [mysqld] part (if these settings already exist, change them accordingly)

 

innodb_data_home_dir = /var/db/mysql-innodb

innodb_log_group_home_dir = /var/db/mysql-innodb-logs

 

Stop the mysql-server service if it's running and then move your mysql directory:

 

mv /var/db/mysql /var/db/mysql-bak

 

Then in the shell execute these commands to create our custom filesystems for InnoDB:

 

zfs create -o recordsize=16k -o primarycache=metadata zroot/var/db/mysql-innodb
zfs create -o recordsize=128k -o primarycache=metadata zroot/var/db/mysql-innodb-logs
zfs create -o recordsize=8k zroot/var/db/mysql

 

Now let's copy the following files to their appropiate locations (you may not have all of these - it's ok, just skip it then)

 

cp /var/db/mysql-bak/ib_logfile* /var/db/mysql-innodb-logs/

cp /var/db/mysql-bak/ib_buffer_pool /var/db/mysql-innodb/

cp /var/db/mysql-bak/ibdata* /var/db/mysql-innodb/

cp /var/db/mysql-bak/ibtmp* /var/db/mysql-innodb/

cp -r /var/db/mysql-bak/* /var/db/mysql/

 

Now start the mysql server and check the logs to make sure there's no errors; then once you're sure you can delete the /var/db/mysql-bak folder.

 

Tuning tools

 

First things first. The easiest way to set the host, user and password for all the MySQL related tools is to create a file named /root/.my.cnf and grant chmod 600 to it so other users can't read our password.

 

[client]

user=root

password=whatever

port=3306

socket=/tmp/mysql.sock

 

This way we will log in automatically without the need to specify the same options over and over.

 

MySQLTuner

 

MySQLTuner is a popular perl script that will give you actionable suggestions to improve performance of your mysql server. Personally I prefer Percona Toolkit but this is a good starting point for beginners nevertheless as it's very easy to read the output.

 

cd /root

fetch http://mysqltuner.pl

perl mysqltuner.pl

 

After entering user and password of our MySQL server, it will spit a number of statistics and recommendations.

 

For more information and command line options: 

This is the hidden content, please

• Sign In
• or
• Sign Up

 

Percona Toolkit

 

Percona Server is, in fact, a drop in replacement for MySQL, just like MariaDB. Personally, I haven't tested it because their FreeBSD support seems sketchy. Nevertheless, we can find a port of their auxiliary tool package "percona-toolkit", which provides more insightful recommendations that MySQLTuner.

 

Not all the tools in the kit do work, as some rely in linux commands and nobody cared to adapt them to FreeBSD (I said that the support was sketchy!), but some of the most useful ones do:

 

pt-duplicate-key-checker        Find duplicate indexes and foreign keys on MySQL tables.
pt-index-usage                         Read queries from a log and analyze how they use indexes
pt-query-digest                       Analyze MySQL queries from logs, processlist or tcpdump

pt-table-usage                         Analyze how queries use tables.
pt-variable-advisor                  Analyze MySQL variables and advise on possible problems (Similar to MySQLTuner suggestions)
 

Usage examples of Percona Toolkit, with the two tools I found the most useful for beginners. Index usage reads a slow query log and suggests indexes to remove, while variable advisor shows suggestions similar to MySQLTuner:

 

pt-index-usage -F /root/.my.cnf  /var/db/mysql/slow.log

pt-variable-advisor "S=/tmp/mysql.sock"

 

That's all for now. Don't expect miracles for running automated tools - those tools don't know your hardware specs, your goals and needs, or whether you are running other software in the machine. These are all important considerations - giving MySQL all the memory for itself is not a good idea if your Metin server is in the same machine for example. Use them to help you understanding what can be changed and the effect it has, and don't just implement suggestions blindly. And remember some of the settings in MySQL 

 

If you have performance problems and can't sort them out, try using a default my.cnf or deleting all the optimizations you made - sometimes reverting to defaults is the best option.