errors – tmpdir is automatically cleaned

When testing a 3-node percona cluster, we often get:

Error Code: 28 - There is no space left on the device.

After connecting to the servers and checking the settings tmpdir I notice that it is empty and that the clients can connect again without problem. So I wonder if there is an automatic purge / delete variable or a parameter that, without having to restart the servers, cleans the tmpdir or if there is an SQL command that could help the request clean / empty the tmp directory.

MySQL: TMPDIR is ignored by tempting

I use MySQL 8 Comm. Edition on an Ubuntu server.
I have a HD-RAID on a mount point in addition to the normal hard drive.
I want to point the variable "TMPDIR" in "/etc/mysql/mysql.conf.d/mysqld.cnf" to "/ raiddrive / mysqltmp".

When I run a query with a lot of data, my disk runs out and ends with: "no disk space"
during the query, the word "tempting" rises up to occupy my free space on the local hard drive. on the RAID, I have enough free space.

Why MySQL does not know TMPDIR variabel in terms of tentables?

& # 39; memory / tentable, & # 39; 1.13 GiB & # 39;
& # 39; memory / performance_schema & # 39; 271.13 MiB & # 39;
& # 39; memory / innodb & # 39;; 65, 55 MiB & # 39;
& # 39; memory / mysys & # 39; 8.63 Mio & # 39;
& # 39; memory / sql, & quot; 6.18 MiB & quot;
& # 39; memory / vio & # 39; 3.58 KiB & # 39;
& # 39; memory / mysqlx & # 39; 3.51KB & quot;
& # 39; memory / myisam & # 39; 696 bytes & # 39;
& # 39; memory / black hole & # 39; 88 bytes & # 39;
& # 39; memory / csv & # 39; 88 bytes & # 39;

is the use of SWAP caused by insufficient space in mysql tmpdir?

Hello,
SEMrush

In recent days, I am facing a problem of high load on the server. Due to overload spikes, the use of SWAP is high and MySQL (5.5.58-cll) is the process that takes the most SWAP:

$ ps -e -o vsz, rss, comm | {lu x; echo "$ x"; sort -k2n | tail; }
ORDER VSZ RSS
348704 265096 httpd
348828 265120 httpd
349132 265140 httpd
349088 265288 httpd
349280 265468 httpd
349156 265520 httpd
349208 265548 httpd
917556 534052 clamd
3485196 2773796 grep
13168468 2827400 mysqld

Code (markup):

The problem is that once SWAP is filled, it does not empty until I restart MySQL.

$ free -mht
total used cached free shared buffers
Mem: 13G 12G 504M 592K 0B 8.5G
- / + buffers / cache: 4.0G 9.0G
Swap: 2.0G 2.0G 17M
Total: 15G 14G 521M

Code (markup):

My MySQL tmpdir is / dev / shm (it is 9GB in size), but I recently dropped my VPS RAM to around 13GB because I needed more RAM for another VPS. The server has a slow hard drive that usually becomes a bottleneck.

# df -h | grep -v virtfs
Size of file system used Usage used% Mounted
/ dev / simfs 342G 92G 251G 27% /
none 9.0G 4.0K 9.0G 1% / dev
none 9.0G 8.0K 9.0G 1% / dev / shm
tmpfs 9.0G 376K 9.0G 1% / tmp
tmpfs 9.0G 0 9.0G 0% / var / tmp

Code (markup):

(I still have that / dev / shm was in memory (tmpfs), now I see the file system "none" above, should i move mysql tmpdir to tmpfs like / tmp or to another location with no more disk quota?)

By the way, regarding the MySQL parameters, the mysqltuner.pl script says:

[--]    Physical Memory: 13.0G
[--] Max MySQL memory: 2.6G
[--] Other process memory: 4.3G
[--] Total buffers: 2.3G global + 1.1M per thread (230 threads max)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max Memory Usage: 0B
[OK] Maximum utilization of the memory reached: 2.5 G (19.07% of the installed RAM)
[OK] Maximum possible use of memory: 2.6 G (19.67% of installed RAM)
[OK] The possible overall use of memory with another process is compatible with the available memory

Code (markup):

I do not want to waste your time, but if you're interested, full release of the script: https://pastebin.com/60PBLfzs
I have 1-2 low visit sites whose mysql table is close to 1 GB, I am not a programmer to change its structure to use indexes, it is known CMS (PHPBB, vBulletin).

My settings mysql: /etc/my.cnf (https://pastebin.com/V2t2u48L)
(I do not think it is necessary to increase the buffers according to the tuner's advice, but if you see one or two values ​​too low or too high, thank you for letting me know)

my question is, as mentioned above, what to do with mysql tmpdir and secondly, if you see something wrong in my configuration and thirdly, what are the possible causes of the saturation of MySQL data by SWAP and that these data are not deleted and that SWAP stays full and fourth, how to prevent this (except dramatically increase the RAM)? Thank you