MariaDB writes more the 30GB/day temp data

Question

I´ve a Synology NAS where I´m running a Nextcloud in a Docker Container and a MariaDB in an other Docker Container. On a day the the NAS and also the Nextcloud doesn´t do anything, on my SSD are over 30GBs written. So I´ve looking for a while and saw with iotp, that every time when the Nextcloud cron job is running, the MariaDB writes with over 20MB/s to my SSD.

I tried to prevent it by using an ramdisk like here written: Why does MySQL produce so many temporary MYD files?

For this I created the MariaDB Docker Container with

docker container run -d \
--mount type=tmpfs,destination=/tmp,tmpfs-size=1g,tmpfs-mode=1777 \
....

but when the Nextcloud cron job is running, I still see that the Mariadb writes with over 20MB/s to my SSD :(

In the Container it looks fine:

df -h
Filesystem         Size  Used Avail Use% Mounted on
tmpfs              1.0G   13M 1012M   2% /tmp

with a script I´ve found witch uses inotifywait I see hat during the Nextcloud cron job a “/tmp//#sql-temptable-1-575-0.MAI” is created.

I´ve also looked with

touch test
=> run the cron job
find / -newer test | grep -v "^/sys/" | grep -v "^/proc/"

if other things where wirtten, but it doen´t seems so:

/dev/pts/3
/dev/pts/ptmx
find: ‘/proc/1470/task/1470/fd/6’: No such file or directory
find: ‘/proc/1470/task/1470/fdinfo/6’: No such file or directory
find: ‘/proc/1470/fd/5’: No such file or directory
find: ‘/proc/1470/fdinfo/5’: No such file or directory
/tmp
/var/lib/mysql/ib_logfile0

Does anyone has an idea why the ramdisk not working or I can see what is written by the MariaDB and prevent it?

score 0 · Answer 1 · answered Aug 24 '23 at 04:54

Its for the reasons mention in the link, there are a significant number of queries requiring a temporary table to resolve the query.

To find the queries set:

long_query_time=0.2 (called log_slow_query_time in 10.11+)
slow_query_log=1
log_queries_not_using_indexes=1
min_examined_row_limit=1000 (log_slow_min_examined_row_limit in 10.11+)

log_slow_query_file contains the log file name to examine after uptime for all the slow queries and queries not sufficiently indexed (other criteria may apply too) to avoid temporary table usage.

MariaDB writes more the 30GB/day temp data

1 Answers1