Before we start let us remind you that the MySQL tuning or any other Linux service tuning for that matter is an extensive topic and we cannot summarize everything into a single blog post. In this blog post, we are exploring the possibilities by which we can avoid a database performance bottleneck to some extent.

We can check multiple MySQL variables to get an almost accurate picture of the current performance levels of the database service in a server but it is indeed a time consuming and daunting task.

What if we could automate those checks and get a brief report of the current MySQL performance status?

Introducing… MySQL tuner! It is a nifty little script written in Perl language that lets you validate the MySQL installation and provide a brief report along with some recommendations to improve performance. So, let’s dive into the steps to run the MySQL tuner:

STEP #1: Download the tuner script from here:

wget https://github.com/major/MySQLTuner-perl/blob/461c8fb60e032ce29172393d38183549331fa840/mysqltuner.pl

STEP #2: Provide the mysqltuner.pl file execute permissions to run the script.

chmod +x mysqltuner.pl

STEP #3: Run the script

./mysqltuner.pl

You may have to provide the MySQL administrator login details.

Once the script finishes, go through the recommendations and decide on what suggestions to implement based on your use case. However, there are certain MySQL parameters you can change without much worry to improve the overall performance right away, they are:

key_buffer

By changing the key_buffer parameter you can control the memory allocation to MySQL. This can significantly speed up your databases, assuming you have that much memory free. The general rule of thumb is that the key_buffer size should not take up more than 25 percent of the system memory when using the MyISAM table engine, and up to 70 percent for InnoDB. If this value is set too high, resources are wasted.

As per MySQL official documentation, servers with 256MB (or more) of RAM with many tables, a 64M key_buffer value is recommended while 16MB is the default value. You can tinker with bigger values and find the sweet spot for your particular server but always monitor the server load statistics before deciding.

innodb_buffer_pool_size

The InnoDB buffer pool is a key component for optimizing MySQL/MariaDB. It stores data and indexes. We can make it usually as large as possible so as to keep as much of the data and indexes in memory, reducing disk IO, as main bottleneck. So, the typical value ranges between 70% – 80% of the system RAM, given the server is used specifically to handle/process sql queries.

This value is only applicable for sql services that use InnoDB as its sql storage engine.

To find out about the current storage engine being used, follow this tutorial:

https://dev.mysql.com/doc/refman/5.7/en/innodb-check-availability.html

max_allowed_packet

Using this parameter, you can set the maximum size of a packet. A packet for the uninitiated, is a single SQL state, a single row that is sent to a client, or it can be a log being sent from a source database to a replica. If your use case involves processing large packets, it is best to increase this value to the size of your largest packet. If this is set too small, you would receive an error in your error log.

thread_cache_size

If thread_cache_size is set to 0, it is essentially off and any new connection being made needs a new thread created for it. When the connections close the corresponding thread is destroyed. The thread_cache_size parameter controls the number of unused threads to store in the cache until they can be reused for a connection. If you are receiving hundreds of connections per minute, this value should be increased so the majority of connections can make use of the cached threads.

max_connections

This value sets the maximum number of concurrent connections. It is best to consider the maximum number of connections you have had in the past before setting this number, so you’ll have a buffer between that upper number and the max_connections value. Keep in mind that this does not indicate the maximum number of users on your website at any given time but the maximum number of requests received concurrently.

The options doesn’t end here but be cautious before applying values other than default one to other sql configuration parameters as they can result in unforeseen issues if the type of workload you have and the configuration you apply doesn’t match.

Tips:

• Always take a backup of /etc/my.cnf before beginning to tinker with the MySQL parameters
• Restart the MySQL service every time you make a change as it will help you to decide on what specific change may or may not work.
• Implementing the MySQL tuner recommendations is not a one-time fix and you have to optimize the service parameters periodically based on the amount of data being processed or as the system scales vertically.

Did you know that RackNerd can provide managed hosting solutions whereby we can assist you in optimizing your MySQL database? Feel free to check out our server hosting solutions below, and don’t hesitate to contact us for further assistance!