Speed up site: moving MySQL to tmpfs

To speed up the site, it is recommended to transfer temporary MySQL files to RAM. This is the easiest and most effective way to improve the performance of your sites and projects. In this case, MySQL will work with temporary tables through RAM. The difference will be noticeable even if you have a VPS server with SSD drives.

1. Create a folder for MySQL temporary files. For example, to create the /var/lib/mysql/temp folder, enter:

mkdir /var/lib/mysql/temporary

2. Change the group and owner of the created folder to mysql:

crown mysql: /var/lib/mysql/temporary

3. If you are not sure exactly, then specify the MySQL user and group ID using the command:

id mysql

4. To open the /etc/fstab file run:

nano /etc/fstab

5. Add the following line to this file:

tmpfs /var/lib/mysql/temporary tmpfs rw,gid=27,uid=27,size=1G,nr_inodes=10k,mode=0700 0 0

size is the amount of RAM allocated for MySQL temporary files, in our example it is 1G. Be sure to consider the amount of available RAM on the virtual server.

6. Connect the new partition:

mount /var/lib/mysql/temporary

7. Open the config file MySQL:

nano /etc/mysql/my.cnf/

8. Add the line to the config file:

tmpdir=/var/lib/mysql/temporary

9. Restart MySQL:

systemctl restart mysqld

It is also recommended to optimize the site's database to improve the efficiency of the site (using the correct sizes and types of fields, taking into account the content, adjusting indexes and creating queries in accordance with these indexes).

If you still have questions, please create a ticket to technical support.