{"id":163,"date":"2022-02-14T10:48:09","date_gmt":"2022-02-14T10:48:09","guid":{"rendered":"https:\/\/openquery.com\/?p=163"},"modified":"2022-02-28T13:57:43","modified_gmt":"2022-02-28T13:57:43","slug":"experiment-mysql-tmpdir-on-tmpfs","status":"publish","type":"post","link":"https:\/\/openquery.com\/experiment-mysql-tmpdir-on-tmpfs\/","title":{"rendered":"Experiment: MYSQL TMPDIR On TMPFS"},"content":{"rendered":"\n

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.<\/p>\n\n\n\n

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. <\/p>\n\n\n\n

In this article, we\u2019ll show you how to use tmpfs to hold your temporary storage.<\/p>\n\n\n\n

\"Experiment<\/figure><\/div>\n\n\n\n

Why Move MySQL Temporary Directory To Tmpfs?<\/strong><\/h2>\n\n\n\n

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. <\/p>\n\n\n\n

This means that temporary tables will be stored in memory, rather than on disk. This will make your site run faster.<\/p>\n\n\n\n

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.<\/p>\n\n\n\n

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.<\/p>\n\n\n\n

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. <\/p>\n\n\n\n

This allows you to store files on this disk without having to write them to a physical disk.<\/p>\n\n\n\n

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.<\/p>\n\n\n\n

How To Enable It?<\/strong><\/h2>\n\n\n\n

To enable this feature you need to add the following line in the \/etc\/fstab file.<\/p>\n\n\n\n

tmpfs \/var\/lib\/mysql\/data tmpfs defaults,noatime,mode 1777 0 1<\/p>\n\n\n\n

Here we are using default options and mode 1777 means no permissions will be set for directories created by mysql.<\/p>\n\n\n\n

Note<\/strong>: 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.<\/p>\n\n\n\n

Set the maximum number of open files (ulimit -n)<\/p>\n\n\n\n

Note<\/strong>: if you have more than 256MB of memory then you don\u2019t need to worry about setting this value.
ulimit -n 8192<\/p>\n\n\n\n

Method Using An Existing Tmpfs Directory<\/strong><\/h2>\n\n\n\n

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. <\/p>\n\n\n\n

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.<\/p>\n\n\n\n

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. <\/p>\n\n\n\n

This will allow us to use our custom configuration without having Ubuntu update overwrite our changes. Next, we must restart MySQL service. <\/p>\n\n\n\n

Finally, make sure that the new setting is now in effect:<\/p>\n\n\n\n

Methodology<\/strong><\/h2>\n\n\n\n

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<\/p>\n\n\n\n

Mounting And Fstab<\/strong><\/h2>\n\n\n\n

Append this to fstab. Change 108 and 109 in the “gid” and “uid” sections to your mysql group id and user id.<\/p>\n\n\n\n

tmpfs \/var\/mysqltmp tmpfs rw,gid=108,uid=109,size=256M,nr_inodes=10k,mode=0700 0 0<\/p>\n\n\n\n

Now, you can mount the partition like so:
mount -a<\/p>\n\n\n\n

Modify or add the following line to \/etc\/mysql\/conf.d\/local.cnf<\/p>\n\n\n\n

tmpdir = \/var\/mysqltmp<\/p>\n\n\n\n

Performance Improvements<\/strong><\/h2>\n\n\n\n
\"Performance<\/figure><\/div>\n\n\n\n

Moving MySQL’s temporary files from the hard drive to RAM can make a significant improvement in performance. <\/p>\n\n\n\n

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%.<\/p>\n\n\n\n

Slow query rate also drops significantly. Server load goes down as well.<\/p>\n\n\n\n

Problems With Mounting tmpdir On Tmpfs<\/strong><\/h2>\n\n\n\n

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”. <\/p>\n\n\n\n

If you have a large number of tmpfs directories mounted on top of each other, then the last one mounted may be corrupted.<\/p>\n\n\n\n

To avoid these problems, don’t mount any additional tmpfs directories on top of each other. Instead, mount them separately.<\/p>\n\n\n\n

Mounting multiple tmpfs directories at once<\/p>\n\n\n\n

You can mount several tmpfs directories at once. <\/p>\n\n\n\n

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.<\/p>\n\n\n\n

For example, suppose that you have three tmpfs directories mounted at the same time:<\/p>\n\n\n\n