Table of Contents
- Why should you tune MySQL, MariaDB or Percona?
- Tools and Resources
- The Basics of Optimizing MySQL
Content Error or Suggest an Edit
Notice a grammatical error or technical inaccuracy? Let us know, we will give you credit!
The stock configuration for MySQL, MariaDB or Percona is untuned. It will work for all instances, from extra small to very large. Since the workload for each server is different, the configuration is unique. You don’t want to max out the memory on a small instance right out of the gate. Therefore the stock configuration isn’t going to be efficient for your workload in 99% of instances.
You will want to have a couple of tools on hand to make this process more manageable.
MySQLtuner.pl – The one and Only!
The tool MySQLtuner.pl has been around for a long time. Created by Major Hayden and is pretty much the go-to tool for optimizing MySQL, MariaDB and Percona. This will be the only tool you need.
MySQLTuner is a script written in Perl that allows you to review a MySQL installation quickly and make adjustments to increase performance and stability. The current configuration variables and status data is retrieved and presented in a brief format along with some basic performance suggestions.https://github.com/major/MySQLTuner-perl
- Tuning-Primer – An alternative to MySQLTuner.pl but not as in-depth or actively maintained.
- Percona Toolkit – Has a ton of tools.
- mysqlreport – Rolled into MariaDB installs, but also available as a git repository at https://github.com/captin411/mysqlreport
- mytop – A top tool for MySQL, most Linux distributions have this via their package manager.
- Netdata – Great tool for monitoring MySQL performance; will require extra setup.
- MySQL Memory Calculator – This tool will help you calculate the memory your MySQL server will use based on MySQL configuration options.
- Hayden James – Lots of articles on MySQL and Tunning.
- Percona Database Performance Blog – Great resources for all things MySQL and Performance.
- Looking for more, let me know your online resources!
- Scalability Pro – Improves WordPress and WooCommerce Performance
Alright, let’s dig in. Some simple changes to the default MySQL configuration can improve its performance right out of the gate. I will cover a couple of them below.
Note: If you’re still using MyISAM, you should convert your tables. There shouldn’t be a need for the use of MyISAM today.
The InnoDB memory buffer is essential as it will buffer your database into memory versus keeping it on disk. This will improve all transactions as memory is faster than disk. Here’s the technical explanation from MySQL.
The buffer pool is an area in main memory where InnoDB caches table and index data as it is accessed. The buffer pool permits frequently used data to be accessed directly from memory, which speeds up processing. On dedicated servers, up to 80% of physical memory is often assigned to the buffer pool.
For efficiency of high-volume read operations, the buffer pool is divided into pages that can potentially hold multiple rows. For efficiency of cache management, the buffer pool is implemented as a linked list of pages; data that is rarely used is aged out of the cache using a variation of the least recently used (LRU) algorithm.
Knowing how to take advantage of the buffer pool to keep frequently accessed data in memory is an important aspect of MySQL tuning.https://dev.mysql.com/doc/refman/8.0/en/innodb-buffer-pool.html
A general rule of thumb is to have your entire database or databases within the innodb_buffer_pool. So if all your databases add up to 5GB, then you should set the innodb_buffer_pool to 5GB. Once set, your MySQL server will need 5GB of memory to store the frequently used MySQL database data. Additionally, the MySQL server will need memory for other functions and configurations, which could be variable.
Instead of trying to figure out your database or database sizes, you can run the MySQLTuner.pl tool to get an idea on what your innodb_buffer_pool size should be.
Typically you’ll want to run a MySQL Calculator (as mentioned above) to figure out how big your database(s) are. Here’s an example of the Innodb metrics from mysquner.pl
As you can see, the InnoDB Buffer Pool is 128M, and the data size is 32.8M which is what all your databases are taking up. So you wouldn’t want to increase the InnoDB buffer pool in this instance. As time passes and you add more data to the databases, you may need to increase the size. Just run mysqltuner.pl again.
There are a number of variables that you should be adjusting to ensure that you don’t end up using more memory than needed or end up using more than your instance (VPS or baremetal) allows.
You might be familiar with the below bit of text from mysqltuner.pl which provides an idea on the maximum memory MySQL would use based on your current MySQL configuration.
The following screenshot shows the differences in memory use based on the settings I will be discussing below.
Setting max_allowed_packet for MySQL does change the maximum memory usage by almost half. The max_allowed_packet is a per thread setting, meaning each thread or connection is affected.
The default can vary depending on your flavour of MySQL (Oracle, MariaDB or Percona) and operating system. The default for MariaDB is 16M, but in other instances you might see 64M as the default. Which is enormous and probably isn’t required unless you’re sucking in a considerable amount of data using a plugin. So think migration, importing or exporting data and large queries.
Dropping it down to 16M will result in a reduction in maximum possible memory usage. You can then tweak other settings that would increase memory usage but improve performance.
Another variable to consider is max_connections, which is generally set really high by default but rarely reached in most instances. As you can see in the screenshot above, this increases the maximum possible memory when allocated. When I say allocated, I mean when max_connections value is met.
If you have on average, 10 connections maximum over the span of a week, and max_connections is set to 50. You don’t really have anything to worry about.
However, if you hit 50 connections on MySQL due to an attack, and the max amount of memory MySQL can use is higher than your system memory. Uh-oh, you’ll run out of memory and Linux will run the Linux OOM (Out Of Memory) process and kill MySQL.
The Linux OOM (Out Of Memory) process will run when the system starts running out of memory and will kill one or more process to free up system memory. This is to avoid a complete system lock-up. The process won’t be restarted, and if the process killed is MySQL then all your sites on this server will stop functioning.
If you have monit on GridPane, there is configuration to stop MySQL from using too much memory. The limits are pretty low, so be aware when tuning MySQL as you may run into issues with Monit restarting MySQL for using too much memory, when in fact it’s not. You simply have to configure monit’s MySQL settings which can be found in this GridPane article
If a condition does occur when memory is being used rapidly, Monit within a minute or two will restart mysql automatically and you won’t hit the OOM routine. This is however rare, and usually OOM kicks in beforehand.
There is one problem with monit restarting MySQL. If the attack continues, MySQL will be continually restarted. Instead, if you set your max connections to 10 or 20, an error will be displayed about too many connections to the database which is less harmful.
- 08-24-2022 – Added Optimizing Max Memory Usage section.