https://www.percona.com/blog/wp-content/uploads/2023/09/Dont-Start-Using-Your-MySQL-Server-Until-Youve-Configured-Your-OS-150×150.png
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).
echo " -- THP check";cat /sys/kernel/mm/transparent_hugepage/enabled; cat /sys/kernel/mm/transparent_hugepage/defrag;echo " --Swappiness";sysctl vm.swappiness ; cat /etc/sysctl.conf | grep -i swap;echo " -- OOM for MySQL";cat /proc/$(pidof mysqld)/oom_score ; cat /proc/$(pidof mysqld)/oom_score_adj;echo " -- jemalloc"; sudo pt-mysql-summary | grep -A5 -i "memory management" ; sudo grep -i jem /proc/$(pidof mysqld)/maps
We want to see something like below, but I am sure we are not.
> echo " -- THP check";cat /sys/kernel/mm/transparent_hugepage/enabled; cat /sys/kernel/mm/transparent_hugepage/defrag;echo " --Swappiness";sysctl vm.swappiness ; cat /etc/sysctl.conf | grep -i swap;echo " -- OOM for MySQL";cat /proc/$(pidof mysqld)/oom_score ; cat /proc/$(pidof mysqld)/oom_score_adj;echo " -- jemalloc"; sudo pt-mysql-summary | grep -A5 -i "memory management" ; sudo grep -i jem /proc/$(pidof mysqld)/maps -- THP check always madvise [never] always defer defer+madvise madvise [never] --Swappiness vm.swappiness = 1 vm.swappiness = 1 -- OOM for MySQL 0 -800 -- jemalloc # Memory management library ################################## jemalloc enabled in mysql config for process with id 29584 Using jemalloc from /usr/lib/x86_64-linux-gnu/libjemalloc.so.1 # The End #################################################### 7f3456ac1000-7f3456af4000 r-xp 00000000 08:01 63812 /usr/lib/x86_64-linux-gnu/libjemalloc.so.1 7f3456af4000-7f3456cf3000 ---p 00033000 08:01 63812 /usr/lib/x86_64-linux-gnu/libjemalloc.so.1 7f3456cf3000-7f3456cf5000 r--p 00032000 08:01 63812 /usr/lib/x86_64-linux-gnu/libjemalloc.so.1 7f3456cf5000-7f3456cf6000 rw-p 00034000 08:01 63812 /usr/lib/x86_64-linux-gnu/libjemalloc.so.1
Let’s quickly fix it.
Disable THP
Let’s create a service which will disable THP for us:
sudo su - cat <<EOF > /usr/lib/systemd/system/disable-thp.service [Unit] Description=Disable Transparent Huge Pages (THP) [Service] Type=simple ExecStart=/bin/sh -c "echo 'never' > /sys/kernel/mm/transparent_hugepage/enabled && echo 'never' > /sys/kernel/mm/transparent_hugepage/defrag" [Install] WantedBy=multi-user.target EOF
And the below command to enable this service:
sudo systemctl daemon-reload sudo systemctl start disable-thp sudo systemctl enable disable-thp
vm.swappiness = 1
Change swappiness at runtime.
echo 1 > /proc/sys/vm/swappiness
And let’s persist it in the config file:
echo "# Swappiness" >> /etc/sysctl.conf echo "vm.swappiness = 1" >> /etc/sysctl.conf
And enable this change.
sudo sysctl -p
OOM and Jemalloc
We are halfway through improving things, but let’s keep pushing for better memory usage. Let’s install jemalloc.
sudo apt-get install libjemalloc1
Please confirm that we have it on the correct path:
ls -l /usr/lib/x86_64-linux-gnu/libjemalloc.so.1
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.
sudo systemctl edit mysql
Add the specified content to the file below immediately.
[Service] Environment= "LD_PRELOAD=/usr/lib64/libjemalloc.so.1" OOMScoreAdjust=-800
To apply this change, we need to reload daemon and mysql service.
sudo systemctl daemon-reload sudo systemctl restart mysql
The optimization of our memory settings has been completed successfully. You can verify it by executing the same check above.
echo " -- THP check";cat /sys/kernel/mm/transparent_hugepage/enabled; cat /sys/kernel/mm/transparent_hugepage/defrag;echo " --Swappiness";sysctl vm.swappiness ; cat /etc/sysctl.conf | grep -i swap;echo " -- OOM for MySQL";cat /proc/$(pidof mysqld)/oom_score ; cat /proc/$(pidof mysqld)/oom_score_adj;echo " -- jemalloc"; sudo pt-mysql-summary | grep -A5 -i "memory management" ; sudo grep -i jem /proc/$(pidof mysqld)/maps
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.
sudo mount | grep "/var/lib/mysql"
The result you will more likely get is:
/dev/sdb on /var/lib/mysql type ext4 (rw,relatime)
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.
> cat /etc/fstab | grep "/var/lib/mysql" /dev/sdb /var/lib/mysql ext4 defaults 0 0
So it’s easy to update the fstab file and add the required options for mount point = noatime, nodiratime.
sudo vim /etc/fstab /dev/sdb /var/lib/mysql ext4 defaults,noatime,nodiratime 0 0
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.
sudo systemctl stop mysql
Once MySQL service is stopped, we can unmount our /mysql directory,
sudo umount /var/lib/mysql
and mount it again using updated /etc/fstab settings:
sudo mount -av
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:
> sudo mount | grep grep "/var/lib/mysql" /dev/sdb on /var/lib/mysql type ext4 (rw,noatime,nodiratime)
We see the options are correct, so we can start the mysql service.
sudo systemctl start mysql
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!
Planet MySQL