Question How to setup Ram Disk for MariaDB/MySQL Temp directory on Ubuntu 20.04.2 LTS with Plesk Obsidian v18.0.37 ?

[Automata]

Hi,

> SOFTWARE SPECIFICATION <

Operating system:
Ubuntu 20.04.2 LTS

Plesk version:
Plesk Obsidian v18.0.37_build1800210716.08

PHP version:
8.0.8

Web server type:
NGINX only

> HARDWARE SPECIFICATION <

CPU:
1 x AMD Ryzen 7 3700X 8-Core Processor (16 thread) at 3600 MHz plus enabled auto frequency boost up to 4400 MHz

RAM:
4 x G-Skill Flare X - F4-3200C16-16GFX (CL16-18-18-38 1.35V) - 64 GB DDR4 at 3200 MHz

NVMe:
2 x Samsung SSD 970 EVO Plus 500 TB (Gen. 3) with Raid 0 software

Network:
1x Realtek RTL8111H at 1 Gigabit

GPU:
1 x NVIDIA GeForce GT 710 with 2 GB RAM

Audio:
1 x Realtek ALC S1200A 8-Channel High Definition Audio CODEC

I state that I have already read the excellent guide posted by IgorG at this topic and post Question - Ram Disk installation

Unfortunately this guide is over almost 4 and a half years ago so I ask IgorG or anyone who wants or can help me to write here all the complete and necessary commands to be able to setup a persistent Ram Disk for MariaDB/MySQL Temp directory on Ubuntu 20.04.2 LTS with Plesk Obsidian v18.0.37

Obviously the temporary directory created on Ram Disk for MariaDB/MySQL must be persistent so as to be automatically created and made available for the system at each reboot.

Furthermore, I also ask you the courtesy of being able to suggest and advise the best both for the size of the temporary directory and for the number of inodes to be created.

If you can also specify how to get the correct uid and gid I would be very grateful as well as how to check the amount of space currently used by the MariaDB / MySQL temporary directory.

I therefore ask for the help and support of IgorG or anyone who can or wants to help me as this could help both me and many other users to improve the performance of their servers regarding the speed of MariaDB / MySQL.

I say this because currently the benchmarks related to my NVMe 2 x Samsung SSD 970 EVO Plus 500 TB (Gen. 3) with Raid 0 software compared to a Ram Disk based on my RAM memories 4 x G-Skill Flare X - F4-3200C16 -16GFX (CL16-18-18-38 1.35V) - 64 GB DDR4 at 3200 MHz with the CrystalDiskMark benchmark suite produce roughly these results:

Samsung SSD 970 EVO Plus 500 TB (Gen. 3):



Read [MB/s] (Seq Q32T1) = 3465.1
Read [MB/s] (4KiBQ8T8) = 1006.6
Read [MB/s] (4KiBQ32T1) = 595.8
Read [MB/s] (4KiBQ1T1) = 48.70

Write [MB/s] (Seq Q32T1) = 2506.9
Write [MB/s] (4KiBQ8T8) = 2091.7
Write [MB/s] (4KiBQ32T1) = 502.6
Write [MB/s] (4KiBQ1T1) = 174.5

G-Skill Flare X - F4-3200C16 -16GFX (CL16-18-18-38 1.35V) - 16 GB DDR4 at 3200 MHz



We are therefore talking about an average increase in speed from about a minimum of 75% for some values to a maximum of about 520% for some other values !!!

This is therefore why, for the benefit of all, I ask for your precious help.

I thank in advance anyone who wants or can help me.

Greetings !

 

[mow]

I state that I have already read the excellent guide posted by IgorG at this topic and post Question - Ram Disk installation

Unfortunately this guide is over almost 4 and a half years ago so I ask IgorG or anyone who wants or can help me to write here all the complete and necessary commands to be able to setup a persistent Ram Disk for MariaDB/MySQL Temp directory on Ubuntu 20.04.2 LTS with Plesk Obsidian v18.0.37

The guide is still valid. Unix doesn't change APIs that often.

Obviously the temporary directory created on Ram Disk for MariaDB/MySQL must be persistent so as to be automatically created and made available for the system at each reboot.

This is achieved by entering the data into fstab as described.

Furthermore, I also ask you the courtesy of being able to suggest and advise the best both for the size of the temporary directory and for the number of inodes to be created.

Start with the values suggested by IgorG.

If you can also specify how to get the correct uid and gid I would be very grateful

Igor already wrote that, id mysql outputs both.

We are therefore talking about an average increase in speed from about a minimum of 75% for some values to a maximum of about 520% for some other values !!!

Real-world speed increase will be much lower, though. Linux uses all available RAM for buffers and cache, so volatile data won't even reach the disk in most cases (metadata will, though). So you'll have some decrease in SSD writes but not nearly as much speed gain as you think.

 

[Automata]

The guide is still valid. Unix doesn't change APIs that often.

This is achieved by entering the data into fstab as described.

Start with the values suggested by IgorG.

Igor already wrote that, id mysql outputs both.

Real-world speed increase will be much lower, though. Linux uses all available RAM for buffers and cache, so volatile data won't even reach the disk in most cases (metadata will, though). So you'll have some decrease in SSD writes but not nearly as much speed gain as you think.

Hi mow,

thank you very much for your help, you were very very clear.

Thanks again and have a nice day.

 

[Automata]

Hi dear mow,

how to delete or resize the tmpfs directory or change the inode value ?

Thanks in advance for the support.

 

[weltonw]

See this line (bolded):

tmpfs /var/tmpfs tmpfs rw,uid=xxx,gid=xxx,size=500M,nr_inodes=10k,mode=0700 0 0

 

[Automata]

Hi john0001 and thanks for the support.

 

[Nutshay]

I state that I have already read the excellent guide posted by IgorG at this topic and post Question - Ram Disk installation

Unfortunately this guide is over almost 4 and a half years ago so I ask IgorG or anyone who wants or can help me to write here all the complete and necessary commands to be able to setup a persistent Ram Disk for MariaDB/MySQL Temp directory on Ubuntu 20.04.2 LTS with Plesk Obsidian v18.0.37

Automata if your problem is solved, will you please help me? I have visited IgorG thread but unfortunately, I couldn't understand that much. I am installing Kingston FURY Beast RGB 16GB DDR4 RAM for Ryzen 5 5600x