Optimizing/Tuning MySQL, MariaDB and Percona for WordPress, WooCommerce.

Why should you tune MySQL, MariaDB or Percona?

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.

Tools and Resources

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

Alternative Tools

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

Online Resources

• 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!

WordPress Plugins

• Scalability Pro – Improves WordPress and WooCommerce Performance

The Basics of Optimizing MySQL

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.

1. InnoDB Memory Buffer Pool

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.

2. Optimizing Maximum Memory Usage

You should be adjusting several variables to ensure that you don’t use 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 of 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 discuss below.

max_allowed_packet

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.

max_connections

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

Memory Issues, Linux OOM, GridPane + Monit

The Linux OOM (Out Of Memory) process will run when the system starts running out of memory and will kill one or more processes to free up system memory. This is to avoid a complete system lock-up, and the process won’t be restarted. If the process is MySQL, then all your sites on this server will stop functioning.

If you have Monit on GridPane, there is a 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 it’s not. You have to configure monit’s MySQL settings which can be found in this GridPane article

https://gridpane.com/kb/configure-monit-with-gp-cli/#mysql

gpmonit mysql -mem-high-mb 800 -mem-restart-mb 900 

If a condition occurs when memory is being used rapidly, Monit will restart MySQL automatically within a minute or two, and you won’t hit the OOM routine.

There is one problem with Monit restarting MySQL. If memory usage continues, MySQL will be continually restarted. Instead, if you set your max connections to 10 or 20, an error about too many connections will be displayed, which is less harmful. Ideally, you don’t want to run out of connections, so you would want to look at updating your resources.

3. Calculating MySQL Innodb Buffer Pool based on Available System Memory

In #1 you learned about how the MySQL InnoDB Buffer Pool works and why it’s beneficial. Now you need to know how to size it appropriately based on the available memory you have on your system.

MySQL InnoDB Buffer Pool Calculation

The following article is a great read and explains how to size your MySQL InnoDB Buffer Pool for a non-WordPress workload.

The gist of the article basically comes down to providing enough memory for everything to operate on the server, then

1. Start with the total RAM available.
2. Subtract a suitable amount for the OS needs.
3. Subtract suitable amounts for all MySQL needs (like various MySQL buffers, temporary tables, connection pools, and replication-related buffers).
4. Divide the result by 105%, approximating the overhead required to manage the buffer pool itself.

Examples of the MySQL InnoDB Buffer Pool Calculation for WordPress

• 2GB System Memory – 512MB (OS/PHP/Redis) – 1GB (MySQL) / 105% = 0.464GB
• 4GB System Memory – 1G (OS/PHP/Redis) – 2GB (MySQL) / 105% = 1GB

I don’t necessiarly agree with these numbers, but it gives you a good idea on how to size servers. I will write up more here when time permits.

@TODO – Provide a better formula for sizing memory.

Questions

#1 – General Questions about tuning MySQL 03-27-2022

This question was a general how do I tune MySQL? They didn’t provide the full mysqltuner.pl output. I always suggest copying and pasting the entire output of mysqltuner.pl as there is additional information provided that will help explain the recommendations as well as help with further tuning that mysqltuner.pl doesn’t suggest 🙂

I only worked on this:

innodb_buffer_pool_size (>= 314.2M) if possible.

I made it 512M by following the gridpane guide. However, I don’t know if it’s something significant or it’s also just a regular recommendation.

It’s significant

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.

So you will want to tune this setting, and ideally, you want it to be the size of all your databases on your server + some room. Enough that you don’t have to come back to re-size, and enough that you don’t user too much of your instance memory.

For the experienced folks here, is the recommendation >= 314.2M same for all sites or does the MySQLTuner actually come up to this value by looking at my site configuration and performance?

It comes up with this value by adding up all your databases 🙂

What does it mean when it says ‘if possible’ ? How to determine whether it’s possible or not to make innodb_buffer_pool_size (>= 314.2M) ? What if I make it 2048M? Will it be good or bad? Can somebody help me understand?

“if possible” simply means if you have enough memory available. You’ll need enough memory for your server’s overall base function, and it’s services. This is typically low, the only variable would be your PHP workers which use memory at idle and when in use.

You’ll want to do some educated guessing. If you have 2GB or 4GB of memory for your instance, it will be tight depending on the traffic your sites receive. If you have static brochure sites, they’ll be cached most of the time except for the first requests or when someone logs into the WordPress admin or the cron runs.

There’s no set formula per se, it’s variable because WordPress’s memory usage is variable. Tracking your server’s memory usage over time will help find the average memory usage, this can be done using something like Netdata.

Also, what other kinds of really useful recommendation one can expect from this? Why is it recommended to run it once a week? Is there a great expectation to get some valuable recommendations? Please some of your use-case or how it helped you?

As your databases grow and your queries change, there will be more recommendations. For instance, you will need to change innodb_buffer_pool_size if your databases grow significantly.

However, I did find 9 warnings with ‘error’ text in it. ( Screenshot: error.log 2023-03-27 at 00…. )

These were all this:

2023-03-26T13:08:51.844222Z 0 [Warning] [MY-010915] [Server] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.

Is this actually an error or something to be worried about?

Always check the error logs and investigate the errors. In this case, it looks like there is a query to the database that will break in the future.

Because NO_ZERO_IN_DATE is deprecated, you should expect it to be removed in a future MySQL release as a separate mode name and its effect included in the effects of strict SQL mode.

https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sqlmode_no_zero_in_date

Do we need to look at the last ‘recommendation’ section only or are there anything to monitor and check under other sections as well.

You will want to look at several items here and tune them as needed on lower memory systems to increase your innodb_buffer_pool. All of them are mentioned in this article

Optimizing/Tuning MySQL, MariaDB and Percona for WordPress, WooCommerce. – Managing WP – All about Managing WordPress

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

managingwp.io

• max_connections can be tweaked to reduce memory; your highest connection count is 37, so you could move this down to 50 or 100 and monitor connection counts.

• max_allowed_packet is usually set insanely high for normal operation and can be decreased to save memory. You can increase this if you ever run into issues, which might 1% of the time.

Both above will allow you to better optimize memory so that you can utilize the memory elsewhere for performance benefits.

The Max MySQL memory is important, too, because it tells you how much memory MySQL could use. So you want to work to keep this down. For example, Gridpane uses monit to ensure MySQL doesn’t take too much memory and will restart MySQL if it does.

So just make sure you also follow this guide to change when monit kicks in otherwise, your tuning will end up killing your MySQL server far too often https://gridpane.com/kb/configure-monit-with-gp-cli/#mysql

Changelog

• 03-27-2023 – Updated to add questions section.
• 10-06-2022 – Updated article to include GridPane monit commands to increase MySQL memory before usage. Proofreading and text additions.
• 08-24-2022 – Added Optimizing Max Memory Usage section.