Tmpfs is a filesystem that stores temporary files in RAM instead of on disk so that the operating system can quickly access them when needed. It uses either swap space or memory as its backing store.
The advantage is that it does not use up any physical storage space. This is particularly useful for embedded systems where there may be limited amounts of physical storage available.
In this article, we’ll show you how to use tmpfs to hold your temporary storage.
Why Move MySQL Temporary Directory To Tmpfs?
In order to speed up your site, you should try to avoid using disk as much as possible. You can do this by telling MySQL to store data in memory instead of on disk.
This means that temporary tables will be stored in memory, rather than on disk. This will make your site run faster.
This means that the size of the table must fit within the amount of RAM allocated for the buffer pool. Also, when you use the MEMORY storage engine, the maximum number of rows per table is limited.
For more information about temporary tables, please refer to the documentation. /dev/shm is an excellent place to store temporary data. It’s fast and doesn’t require much memory.
A RAM Disk is a virtual hard drive that resides inside your computer’s main memory (RAM). You can create a RAM Disk using the mkramdisk command.
This allows you to store files on this disk without having to write them to a physical disk.
Memory-mapped files allow you to map an entire file into memory as if it were part of your application’s address space. This allows you to use the file without having to read or write it back to disk.
How To Enable It?
To enable this feature you need to add the following line in the /etc/fstab file.
tmpfs /var/lib/mysql/data tmpfs defaults,noatime,mode 1777 0 1
Here we are using default options and mode 1777 means no permissions will be set for directories created by mysql.
Note: You should ensure that your server has enough memory to support all the processes running at once. If you have more than 4GB of memory then you don’t need to worry about setting the size of the tmpfs mount point.
Set the maximum number of open files (ulimit -n)
Note: if you have more than 256MB of memory then you don’t need to worry about setting this value.
ulimit -n 8192
Method Using An Existing Tmpfs Directory
A temporary file system is used by programs that need to store files temporarily. Programs use temporary file systems because they do not want to take up too much space on your hard drive.
Temporary file systems are usually located on RAM disks (RAM stands for random access memory). You can create a RAM disk using the command mktemp.
So, all we need is to tell MySQL to use this directory by creating a file called /etc/. The next step is to add this to the file: local.cnf.
This will allow us to use our custom configuration without having Ubuntu update overwrite our changes. Next, we must restart MySQL service.
Finally, make sure that the new setting is now in effect:
Methodology
Enter these console commands in order. Make sure to follow any steps or instructions presented on the screen.
mkdir -p /var/mysqltmp
chown mysql:mysql /var/mysqltmp
id mysql
Mounting And Fstab
Append this to fstab. Change 108 and 109 in the “gid” and “uid” sections to your mysql group id and user id.
tmpfs /var/mysqltmp tmpfs rw,gid=108,uid=109,size=256M,nr_inodes=10k,mode=0700 0 0
Now, you can mount the partition like so:
mount -a
Modify or add the following line to /etc/mysql/conf.d/local.cnf
tmpdir = /var/mysqltmp
Performance Improvements
Moving MySQL’s temporary files from the hard drive to RAM can make a significant improvement in performance.
In this case, moving the temporary files from disk to RAM increased the average response time by about 50%.
Making use of RAM disk makes a significant improvement in performance. I/O wait time decreases by about 50%.
Slow query rate also drops significantly. Server load goes down as well.
Problems With Mounting tmpdir On Tmpfs
If you try to mount a tmpfs directory on top of another tmpfs directory, then you’ll get errors like “Error opening journal” or “Failed to open journal”.
If you have a large number of tmpfs directories mounted on top of each other, then the last one mounted may be corrupted.
To avoid these problems, don’t mount any additional tmpfs directories on top of each other. Instead, mount them separately.
Mounting multiple tmpfs directories at once
You can mount several tmpfs directories at once.
However, you should only mount tmpfs directories that are completely unrelated. Mounting two different tmpfs directories at the same time could cause serious damage to your server.
For example, suppose that you have three tmpfs directories mounted at the same time:
- /dev/shm/1
- /dev/shmem/2
- /dev/shmmem/3
The first directory contains a shared memory area for processes running on the machine.
The second directory contains a shared memory region for processes running on other machines. The third directory contains a shared memory zone for processes running on other computers.
Now, if you run out of memory, then the kernel will automatically move some of the data from /dev/shmmem into /dev/shm.
Since both of those directories are mounted at the same time, this means that your entire /dev/shm filesystem will be moved into the shared memory region.
This would corrupt your system.
If you want to mount more than one tmpfs directory at the same time, then you should mount them separately. For example, you might mount /dev/shm/ and /dev/shmem/ separately.
Note that there is no way to prevent users from mounting their own tmpfs directories on top of each other. So, even if you try to prevent them from doing so, they can still do it anyway.
Conclusion
Mounting your temporary directory in ram might well improve your performance! Hopefully, this guide has helped show you how to do it!
Here at Open Query I want to make it as easy as possible for you to learn about topics that might not be easily accessible elsewhere online. I don’t know about you, but I haven’t seen many other websites talking about the MariaDB Foundation and its updates.
That’s what makes Open Query truly unique. While others might assume everyone knows this information already automatically, I am filling my online resource to help.
- What Is Percentile Rank? - February 15, 2022
- How To Use Distinct In SQL - February 14, 2022
- How To Drop A Column In SQL - February 14, 2022