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 server.

$ perl mysqltuner.pl --host 127.0.0.1 --forcemem 8000 --user root --pass ███████████
 >>  MySQLTuner 1.7.2 - Major Hayden <[email protected]>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script
[--] Performing tests on 127.0.0.1:3306
[OK] Logged in using credentials passed on the command line
[--] Assuming 8000 MB of physical memory
[!!] Assuming 0 MB of swap space (use --forceswap to specify)
[OK] Currently running supported MySQL version 5.7.18
[OK] Operating on 64-bit architecture

-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: stderr(0B)
[!!] Log file stderr doesn't exist
[!!] Log file stderr isn't readable.

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in InnoDB tables: 16K (Tables: 1)
[OK] Total fragmented tables: 0

-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] User 'wordpress@%' has user name as password.
[!!] User 'root@%' hasn't specific host restriction.
[!!] User 'wordpress@%' hasn't specific host restriction.
[--] There are 612 basic passwords in the list.

-------- CVE Security Recommendations --------------------------------------------------------------
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 32s (19 q [0.594 qps], 12 conn, TX: 43K, RX: 1K)
[--] Reads / Writes: 100% / 0%
[--] Binary logging is disabled
[--] Physical Memory     : 7.8G
[--] Max MySQL memory    : 338.9M
[--] Other process memory: 202.5M
[--] Total buffers: 169.0M global + 1.1M per thread (151 max threads)
[--] P_S Max memory usage: 72B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 170.1M (2.13% of installed RAM)
[OK] Maximum possible memory usage: 338.9M (4.24% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/19)
[OK] Highest usage of available connections: 0% (1/151)
[!!] Aborted connections: 8.33%  (1/12)
[!!] Query cache may be disabled by default due to mutex contention.
[!!] Query cache efficiency: 0.0% (0 cached / 8 selects)
[OK] Query cache prunes per day: 0
[OK] No Sort requiring temporary tables
[OK] No joins without indexes
[OK] Temporary tables created on disk: 15% (10 on disk / 66 total)
[OK] Thread cache hit rate: 91% (1 created / 12 connections)
[OK] Table cache hit rate: 93% (102 open / 109 opened)
[OK] Open file limit used: 0% (14/65K)
[OK] Table locks acquired immediately: 100% (102 immediate / 102 locks)

-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 72B
[--] Sys schema is installed.

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.3% (1M used / 8M cache)
[OK] Key buffer size / total MyISAM indexes: 8.0M/43.0K
[!!] Read Key buffer hit rate: 50.0% (6 cached / 3 reads)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 128.0M/16.0K
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (75 %): 48.0M * 2/128.0M should be equal 25%
[OK] InnoDB buffer pool instances: 1
[--] Number of InnoDB Buffer Pool Chunk : 1 for 1 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[!!] InnoDB Read buffer efficiency: 82.53% (1176 hits/ 1425 total)
[!!] InnoDB Write Log efficiency: 0% (2 hits/ 0 total)
[OK] InnoDB log waits: 0.00% (0 waits / 2 writes)

-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.

-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.

-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.

-------- RocksDB Metrics ---------------------------------------------------------------------------
[--] RocksDB is disabled.

-------- Spider Metrics ----------------------------------------------------------------------------
[--] Spider is disabled.

-------- Connect Metrics ---------------------------------------------------------------------------
[--] Connect is disabled.

-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.

-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] This is a standalone server.

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Set up a Secure Password for user@host ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
    Restrict Host for user@% to user@SpecificDNSorIp
    MySQL started within last 24 hours - recommendations may be inaccurate
    Reduce or eliminate unclosed connections and network issues
Variables to adjust:
    query_cache_size (=0)
    query_cache_type (=0)
    query_cache_limit (> 1M, or use smaller result sets)
    innodb_log_file_size * innodb_log_files_in_group should be equal to 1/4 of buffer pool size (=64M) if possible.

As you can see, there are a number of security improvements for me to follow up on, and some variables I can change to improve performance.

Option 3. Cloudflare

The previous two recommendations have been all about improving the performance of MySQL queries, but what about reducing queries in the first place? Many websites, such as a personal blog or portfolio, don’t actually change all that often. This means it should be possible to serve a static HTML version of the site instead of having every visitor query the database for every loaded page.

Cloudflare is the most popular solution to placing a caching layer between your user and the database. It’s global content delivery network (CDN) is one of the fastest out there, which means your website will not only load faster, but it will rely on your database less than ever. Plus, if your site goes down, Cloudflare will try to serve its cached version rather than show an error.

For WordPress users, there’s even a plugin to make that process easier than ever. It doesn’t make sense to simply duplicate the process that Cloudflare has put together on their own support pages, so we recommend you go checkout their Using Cloudflare with WordPress page for more information.

For other applications, it’s also possible to use the free tier of Cloudflare as a caching layer as well. In that case, you need to point your domain to Cloudflare’s nameservers, and then point Cloudflare’s DNS to your server’s IP.

Additional options and resources

There are other caching layers that exist on the server itself (such as memcached), rather than a CDN like Cloudflare, but setting those up are beyond the scope of this tutorial.

• memcached
• Redis
• MySQL 5.7 Performance Tuning Immediately After Installation