Tutorial: Speeding Up MySQL Without Using the CPU

We recently had a user ask us a relatively simple question: How do I speed up my MySQL query performance without just relying on the CPU?

For those who run websites or applications that rely on MySQL databases, this is an incredibly important question, as it can mean the difference between a smooth experience and one that causes frustration for both you and your users.

In this tutorial, we’ll cover three methods of either improving the MySQL database performance, or establishing methods of reducing the need to query the database in the first place.

Prerequisites

1. A VPS (any OS works) running a MySQL database

Option 1. Percona Wizard

Percona offers a free-to-use configuration wizard for MySQL, which makes it easy to establish a baseline configuration that’s better suited for exactly the kind of problems you’re going to throw at your database. With a solid foundation to work from, you can get your service going quickly and then aim to improve performance later with further tweaks.

Note: Percona recommends this configuration wizard only be used for new servers/databases, not ones that have been running and contain data. The primary reason is that if you replace your old configuration with one created by this wizard, your database might become inaccessible. If you already have an active database, it’s probably best to skip to the second option.

If you’re an SSD Nodes customer, here’s a few answers related to our platform that will help get you started:

Where is this server hosted? Other

Do you use virtualization? Other

What type of storage do you use? SSD/Flash

How many CPUs does your system have? 4 (unless you’re on our smallest plans)

What is your operating system? Linux

The rest are entirely dependent on your particular application, although selecting the defaults in most cases will establish you with a good starting configuration that you can build from in the future. After moving through the seven steps of the wizard, you’ll get a configuration that you can drop into your my.cnf or my.ini files on the server in question.

Option 2. mysqltuner.pl

This popular script scans your MySQL database and offers up warnings about vulnerabilities or weak passwords, and also gives logical suggestions as to which configuration tweaks will improve performance.

To download the script, you have two options: clone the entire repository, or take only what you need in a more “minimal” installation.

Clone:

$ git clone https://github.com/major/MySQLTuner-perl.git
$ cd MySQLTuner-perl

After this, you’re ready to start the script. More on that in a moment.

Minimal:

$ mkdir MySQLTuner-perl && cd MySQLTuner-perl
$ wget http://mysqltuner.pl/ -O mysqltuner.pl
$ wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/basic_passwords.txt -O basic_passwords.txt
$ wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/vulnerabilities.csv -O vulnerabilities.csv

In this case, you’re creating a new directory for these files, and then using wget to download the relevant files, including the mysqltuner.pl script itself, a text file of vulnerable passwords, and a list of vulnerabilities.

Whichever method you chose, you’re now ready to run the script.

$ perl mysqltuner.pl

You can also enable the CVE vulnerabilities with the following:

$ perl mysqltuner.pl --cvefile=vulnerabilities.csv

When mysqltuner.pl is finished running, it will give you any number of recommendations about how you can improve your MySQL configuration, whether it’s security-based on for superior performance. That said, it’s important to reiterate the warning that the tuner’s developers have included in the project’s README.

It is extremely important for you to fully understand each change you make to a MySQL database server. If you don’t understand portions of the script’s output, or if you don’t understand the recommendations, you should consult a knowledgeable DBA or system administrator that you trust. Always test your changes on staging environments, and always keep in mind that improvements in one area can negatively affect MySQL in other areas.

It’s also possible to run mysqltuner.pl on a database running inside of a Docker container—perhaps useful if you followed our previous Docker tutorials that focus on Wordpress. For example, here’s the results of a quick scan I did on one of those Wordpress databases on my testing