Whenever you install your favorite MySQL server on a freshly created Ubuntu instance, you start by updating the configuration for MySQL, such as configuring buffer pool, changing the default datadir director, and disabling one of the most outstanding features – query cache. It’s a nice thing to do, but first things first. Let’s review the best practices we usually follow in Managed Services before using your MySQL server in production and stage env, even for home play purposes.

Memory

Our usual recommendation is to use specific memory parameters, which we suggest to ensure optimal performance.

  • To prevent out-of-memory (OOM) episodes, the OOM Score has to be set to -800.
  • vm.swappiness = 1
  • Disable Transparent Huge Pages
  • Install and enable jemalloc. Let’s briefly go through each setting to understand why adjustments are needed. Afterward, we will see how to configure these settings on your OS.

OOM

The OOM killer checks oom_score_adj to adjust its final calculated score. This file is present in /proc/$pid/oom_score_adj. You can add a sizable negative score to this file to ensure that OOM killer is less likely to pick up and terminate your process. The oom_score_adj can vary from -1000 to 1000. If you assign -1000 to it, it can use 100% memory and avoid getting terminated by OOM killer. On the other hand, if you assign 1000 to it, the Linux kernel will keep killing the process even when it uses minimal memory.

Swappiness

Swappiness is a Linux kernel parameter determining how aggressively the Linux virtual machine swaps pages between memory and the swap space on the system’s disk. The default value of vm.swappiness is 60, representing the percentage of free memory before activating the swap. Lower values reduce swapping and keep more memory pages in physical memory. Changing the value directly influences the performance of the Linux system. These values are defined as:

  • 0: swap is disabled
  • 1: Minimum amount of swapping without disabling it entirely
  • 10: recommended value to improve performance when sufficient memory exists in a system
  • 100: aggressive swapping

Transparent Huge Pages and Jemalloc

When it comes to Transparent Huge Pages (THP), they can take up more memory. The kernel’s memory allocation function allocates the requested page size, and sometimes more, rounded up to fit within the available memory. In other words, even if your application requires a small amount of memory, it will still be allocated at least a full page.

Additionally, pages must be contiguous in memory, which applies to ‘huge pages.’ This means that if the server cannot find a full page available in a row, it will defragment the memory before allocating it. This can negatively impact performance and cause delays.

InnoDB is built on a B*-Tree of indices, meaning that its workload will usually have sparse rather than contiguous-memory access, and, as such, it will likely noticeably perform worse with THP.

If you use jemalloc in conjunction with THP, the server may run out of memory over time because unused memory cannot be freed. Therefore, disabling Transparent Huge Pages for database servers is advisable to avoid this situation.

Using jemalloc instead of glibc memory allocator for MySQL results in less memory fragmentation and more efficient resource management. This is especially true when Transparent Huge Pages are disabled.

Action steps for memory settings

Before we change what needs to be adjusted, we need to know the current situation on our DB instance. By the way, I assume you installed the pt-toolkit and your favorite MySQL server to make your life easier. If you haven’t, please install it (Percona Toolkit documentation).

We want to see something like below, but I am sure we are not.

Let’s quickly fix it.

Disable THP

Let’s create a service which will disable THP for us:

And the below command to enable this service:

vm.swappiness = 1

Change swappiness at runtime.

And let’s persist it in the config file:

And enable this change.

OOM and Jemalloc

We are halfway through improving things, but let’s keep pushing for better memory usage. Let’s install jemalloc.

Please confirm that we have it on the correct path:

And the last thing we need to push our MySQL service to use our magic jemalloc library, let’s create an override for systemd:

Note: Depending on the system, it can be shown as mysql or mysqld. You can use systemctl | grep mysql to get the proper mysql service name.

Add the specified content to the file below immediately.

To apply this change, we need to reload daemon and mysql service.

The optimization of our memory settings has been completed successfully. You can verify it by executing the same check above.

Mount point option for disk

Another thing I want to address in this article is how to reduce IO stress on our disks. It’s one of the most straightforward tasks we have, but it will give us a lot of performance for our powerful disks, which keeps our databases healthy and durable.

By default, when most disks are mounted using the relatime option, the system updates the metadata statistics for files each time they are accessed or changed on the mount point. This process can result in a significant amount of IO usage, which can be particularly problematic when running a database on that mount point. Given that MySQL typically accesses and writes numerous files concurrently, we must prioritize IO for more critical processes within the database rather than for updating metadata. Therefore, it is advisable to refrain from using the relatime option by default in such scenarios. To make this happen, we need to update it to noatime,nodiratime.

How to check the current options we have: I assume that you are using a separate mount point for the MySQL database attached to /var/lib/mysql path.

The result you will more likely get is:

Action steps to apply best practices for disk settings

Let’s find out where we have these disk settings for that fstab coming to help.

So it’s easy to update the fstab file and add the required options for mount point = noatime, nodiratime.

From that moment, we are almost done, but we can’t apply these changes until our MySQL server is running, so we need to stop our mysql service, umount datadir directory, and mount it with new options.

Once MySQL service is stopped, we can unmount our /mysql directory,

and mount it again using updated /etc/fstab settings:

At that point, disk settings should be good, but it’s worth verifying that we have the desired mount point options. Afterward, we can start the MySQL service:

We see the options are correct, so we can start the mysql service.

Conclusion

Optimizing memory and disk settings for MySQL can greatly improve the performance and stability of your database. Following the steps outlined in this article, you can reduce IO stress on your disks, prioritize IO for critical processes within the database, and improve memory usage. Remember always to verify your changes and consult with a professional if you have any questions or concerns. With these optimizations in place, your MySQL database will be better equipped to handle the demands of your applications and users.

Percona Distribution for MySQL is the most complete, stable, scalable, and secure open source MySQL solution available, delivering enterprise-grade database environments for your most critical business applications… and it’s free to use!

 

Try Percona Distribution for MySQL today!

Subscribe
Notify of
guest

1 Comment
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Matthew Lenz

All great stuff. For the /var/lib/mysql mount changes; what about systems were /var/lib/mysql is on the root drive? From what I can see except for a few very specific programs using noatime,nodirtime seems to be considered safe across all filesystem mounts. What about other sysctl values? I’ve seen fs.aio-max-nr mentioned a bunch. There are only few references to it in the the mysql documentation and they usually imply that it’s only useful to increase beyond it’s OS default if you are running multiple instances of mysql server on the same host. Here is one I discovered after much mental anguish. net.ipv4.tcp_keepalive_time = 300 on GCP instances that communicate with other instances in a VPC network. The issue is that the GCP Firewall has idle timeouts and if you have log running queries (loading data that had large index creation times) initiated from another instance. These queries would timeout due to the keepalive time exceeding the firewall timeout resulting in the dreaded “server has gone away” message.

Last edited 6 months ago by Matthew Lenz
1
Reply