0

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?

1
  • Does no one have any ideas?
    – Holger
    Aug 23, 2023 at 19:06

1 Answer 1

0

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:

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.

5
  • Thank you very much for the answer, I know that some queries are written temporarily to disk, I can't change that because they come from Nextcloud. Therefore I tried to move "the disk" where the temporary data is written to the memory. (dba.stackexchange.com/questions/53201/…). Unfortunately it does not work.So I am looking for an idea what / where is written from the database. My database has less then100 MB but 30 GB are written/day... that sounds strange especially if the internal queries (as I hope) end up on a ramdisk
    – Holger
    Aug 25, 2023 at 7:30
  • Maybe with the right information you can advise Nextcloud of the problem queries so they can fix them. Temporary files are created with O_TMPFILE (see man 2 open) so will not appear on the filesystem and I don't know if inotify sees them. Start container with --cap-add CAP_SYS_PTRACE and you can strace -p 1 after MariaDB is installed.
    – danblack
    Aug 25, 2023 at 20:02
  • Thanks for the idea with strace, even if it somehow makes no sense for :/ The output of strace -t -f -p 1 -e write,openat,close and iotop do not fit together. Strace says that it writes to my ramdisk openat(AT_FDCWD, "/tmp/#sql-temptable-1-6-3.MAI")...., but iotop says at the same time that MariaDB would write at 25MB/s. <br/> df -h Filesystem Size Used Avail Use% Mounted on tmpfs 1.0G 13M 1012M 2% /tmp
    – Holger
    Aug 28, 2023 at 21:01
  • Sorry for the bad formatting I have not managed line breaks
    – Holger
    Aug 28, 2023 at 21:07
  • The slowlog should show you the "worst" queries; let's focus on one or two of them. We may be able to go back to the instructions you gave to Nextcloud and change things that avoid the need for temp tables. Or we may be able to add an composite index to the table.
    – Rick James
    Sep 14, 2023 at 3:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.