[Maria-discuss] Disk increase with MAD and MAI extension files in tmp dir
Hello all,
MariaDB version -> 10.3.14
Recently, we had a new problem with our MariaDB instance. Indeed, suddenly the size of the tmp directory has increased almost instantaneously, to finish saturating the disk.
We have encountered this problem twice and don't understand currently the problem origin.
The result of 'ls -l /tmp' during the problem:
mysql@16c212e7dbb5:/tmp# ls -l total 126786988 -rw-rw---- 1 mysql mysql 14190428160 Jul 8 15:18 '#sql_1_1.MAD' -rw-rw---- 1 mysql mysql 8192 Jul 8 15:02 '#sql_1_1.MAI' -rw-rw---- 1 mysql mysql 13344178176 Jul 8 15:18 '#sql_1_11.MAD' -rw-rw---- 1 mysql mysql 8192 Jul 8 15:02 '#sql_1_11.MAI' -rw-rw---- 1 mysql mysql 13344546816 Jul 8 15:18 '#sql_1_14.MAD' -rw-rw---- 1 mysql mysql 8192 Jul 8 15:02 '#sql_1_14.MAI' -rw-rw---- 1 mysql mysql 8192 Jul 8 15:18 '#sql_1_2.MAD' -rw-rw---- 1 mysql mysql 8192 Jul 8 15:18 '#sql_1_2.MAI' -rw-rw---- 1 mysql mysql 12613459968 Jul 8 15:20 '#sql_1_25.MAD' -rw-rw---- 1 mysql mysql 8192 Jul 8 15:03 '#sql_1_25.MAI' -rw-rw---- 1 mysql mysql 12221440000 Jul 8 15:20 '#sql_1_26.MAD' -rw-rw---- 1 mysql mysql 8192 Jul 8 15:03 '#sql_1_26.MAI' -rw-rw---- 1 mysql mysql 12220719104 Jul 8 15:20 '#sql_1_29.MAD' -rw-rw---- 1 mysql mysql 8192 Jul 8 15:03 '#sql_1_29.MAI' -rw-rw---- 1 mysql mysql 12220907520 Jul 8 15:20 '#sql_1_36.MAD' -rw-rw---- 1 mysql mysql 8192 Jul 8 15:03 '#sql_1_36.MAI' -rw-rw---- 1 mysql mysql 14190247936 Jul 8 15:18 '#sql_1_4.MAD' -rw-rw---- 1 mysql mysql 8192 Jul 8 15:02 '#sql_1_4.MAI' -rw-rw---- 1 mysql mysql 12220588032 Jul 8 15:18 '#sql_1_47.MAD' -rw-rw---- 1 mysql mysql 8192 Jul 8 15:03 '#sql_1_47.MAI' -rw-rw---- 1 mysql mysql 13350912000 Jul 8 15:18 '#sql_1_6.MAD' -rw-rw---- 1 mysql mysql 8192 Jul 8 15:02 '#sql_1_6.MAI'
We failed to identify the origin of this behaviour.
Does anyone know what can trigger this behavior ? We also failed to find a configuration paramater to avoid and limit the size of these files, any ideas about that ?
Thanks for your help,
Am 07.08.19 um 10:30 schrieb Kevin DG:
MariaDB version -> 10.3.14
Recently, we had a new problem with our MariaDB instance. Indeed, suddenly the size of the tmp directory has increased almost instantaneously, to finish saturating the disk.
We have encountered this problem twice and don't understand currently the problem origin.
The result of 'ls -l /tmp' during the problem:
mysql@16c212e7dbb5:/tmp# ls -l total 126786988 -rw-rw---- 1 mysql mysql 14190428160 Jul 8 15:18 '#sql_1_1.MAD' -rw-rw---- 1 mysql mysql 8192 Jul 8 15:02 '#sql_1_1.MAI' -rw-rw---- 1 mysql mysql 13344178176 Jul 8 15:18 '#sql_1_11.MAD' -rw-rw---- 1 mysql mysql 8192 Jul 8 15:02 '#sql_1_11.MAI' -rw-rw---- 1 mysql mysql 13344546816 Jul 8 15:18 '#sql_1_14.MAD'
looks like temporary tables from bad queries enable slow query log
Hi, Kevin!
When did it start?
Did you, for example, upgrade from 10.3.13 to 10.3.14 and then noticed it? (I'm not saying this is a known bug in 10.3.14, it was just an example)
What filesystem is it?
And, don't forget, if it looks like a bug to you, report it at jira.mariadb.org to get it fixed
On Aug 07, Kevin DG wrote:
Hello all,
MariaDB version -> 10.3.14
Recently, we had a new problem with our MariaDB instance. Indeed, suddenly the size of the tmp directory has increased almost instantaneously, to finish saturating the disk.
We have encountered this problem twice and don't understand currently the problem origin.
The result of 'ls -l /tmp' during the problem:
mysql@16c212e7dbb5:/tmp# ls -l total 126786988 -rw-rw---- 1 mysql mysql 14190428160 Jul 8 15:18 '#sql_1_1.MAD' -rw-rw---- 1 mysql mysql 8192 Jul 8 15:02 '#sql_1_1.MAI' -rw-rw---- 1 mysql mysql 13344178176 Jul 8 15:18 '#sql_1_11.MAD' -rw-rw---- 1 mysql mysql 8192 Jul 8 15:02 '#sql_1_11.MAI' -rw-rw---- 1 mysql mysql 13344546816 Jul 8 15:18 '#sql_1_14.MAD' -rw-rw---- 1 mysql mysql 8192 Jul 8 15:02 '#sql_1_14.MAI' -rw-rw---- 1 mysql mysql 8192 Jul 8 15:18 '#sql_1_2.MAD' -rw-rw---- 1 mysql mysql 8192 Jul 8 15:18 '#sql_1_2.MAI' -rw-rw---- 1 mysql mysql 12613459968 Jul 8 15:20 '#sql_1_25.MAD' -rw-rw---- 1 mysql mysql 8192 Jul 8 15:03 '#sql_1_25.MAI' -rw-rw---- 1 mysql mysql 12221440000 Jul 8 15:20 '#sql_1_26.MAD' -rw-rw---- 1 mysql mysql 8192 Jul 8 15:03 '#sql_1_26.MAI' -rw-rw---- 1 mysql mysql 12220719104 Jul 8 15:20 '#sql_1_29.MAD' -rw-rw---- 1 mysql mysql 8192 Jul 8 15:03 '#sql_1_29.MAI' -rw-rw---- 1 mysql mysql 12220907520 Jul 8 15:20 '#sql_1_36.MAD' -rw-rw---- 1 mysql mysql 8192 Jul 8 15:03 '#sql_1_36.MAI' -rw-rw---- 1 mysql mysql 14190247936 Jul 8 15:18 '#sql_1_4.MAD' -rw-rw---- 1 mysql mysql 8192 Jul 8 15:02 '#sql_1_4.MAI' -rw-rw---- 1 mysql mysql 12220588032 Jul 8 15:18 '#sql_1_47.MAD' -rw-rw---- 1 mysql mysql 8192 Jul 8 15:03 '#sql_1_47.MAI' -rw-rw---- 1 mysql mysql 13350912000 Jul 8 15:18 '#sql_1_6.MAD' -rw-rw---- 1 mysql mysql 8192 Jul 8 15:02 '#sql_1_6.MAI'
We failed to identify the origin of this behaviour.
Does anyone know what can trigger this behavior ? We also failed to find a configuration paramater to avoid and limit the size of these files, any ideas about that ?
Thanks for your help,
Regards, Sergei VP of MariaDB Server Engineering and security@mariadb.org
Perhaps you can use one of the file_summary_*/other tables in the performance_schema to identify the query causing this?
R
-----Original Message----- From: Maria-discuss maria-discuss-bounces+rhys.campbell=swisscom.com@lists.launchpad.net On Behalf Of Sergei Golubchik Sent: 07 August 2019 13:42 To: Kevin DG kevin@dubourg.info Cc: maria-discuss@lists.launchpad.net Subject: Re: [Maria-discuss] Disk increase with MAD and MAI extension files in tmp dir
Hi, Kevin!
When did it start?
Did you, for example, upgrade from 10.3.13 to 10.3.14 and then noticed it? (I'm not saying this is a known bug in 10.3.14, it was just an example)
What filesystem is it?
And, don't forget, if it looks like a bug to you, report it at jira.mariadb.org to get it fixed
On Aug 07, Kevin DG wrote:
Hello all,
MariaDB version -> 10.3.14
Recently, we had a new problem with our MariaDB instance. Indeed, suddenly the size of the tmp directory has increased almost instantaneously, to finish saturating the disk.
We have encountered this problem twice and don't understand currently the problem origin.
The result of 'ls -l /tmp' during the problem:
mysql@16c212e7dbb5:/tmp# ls -l total 126786988 -rw-rw---- 1 mysql mysql 14190428160 Jul 8 15:18 '#sql_1_1.MAD' -rw-rw---- 1 mysql mysql 8192 Jul 8 15:02 '#sql_1_1.MAI' -rw-rw---- 1 mysql mysql 13344178176 Jul 8 15:18 '#sql_1_11.MAD' -rw-rw---- 1 mysql mysql 8192 Jul 8 15:02 '#sql_1_11.MAI' -rw-rw---- 1 mysql mysql 13344546816 Jul 8 15:18 '#sql_1_14.MAD' -rw-rw---- 1 mysql mysql 8192 Jul 8 15:02 '#sql_1_14.MAI' -rw-rw---- 1 mysql mysql 8192 Jul 8 15:18 '#sql_1_2.MAD' -rw-rw---- 1 mysql mysql 8192 Jul 8 15:18 '#sql_1_2.MAI' -rw-rw---- 1 mysql mysql 12613459968 Jul 8 15:20 '#sql_1_25.MAD' -rw-rw---- 1 mysql mysql 8192 Jul 8 15:03 '#sql_1_25.MAI' -rw-rw---- 1 mysql mysql 12221440000 Jul 8 15:20 '#sql_1_26.MAD' -rw-rw---- 1 mysql mysql 8192 Jul 8 15:03 '#sql_1_26.MAI' -rw-rw---- 1 mysql mysql 12220719104 Jul 8 15:20 '#sql_1_29.MAD' -rw-rw---- 1 mysql mysql 8192 Jul 8 15:03 '#sql_1_29.MAI' -rw-rw---- 1 mysql mysql 12220907520 Jul 8 15:20 '#sql_1_36.MAD' -rw-rw---- 1 mysql mysql 8192 Jul 8 15:03 '#sql_1_36.MAI' -rw-rw---- 1 mysql mysql 14190247936 Jul 8 15:18 '#sql_1_4.MAD' -rw-rw---- 1 mysql mysql 8192 Jul 8 15:02 '#sql_1_4.MAI' -rw-rw---- 1 mysql mysql 12220588032 Jul 8 15:18 '#sql_1_47.MAD' -rw-rw---- 1 mysql mysql 8192 Jul 8 15:03 '#sql_1_47.MAI' -rw-rw---- 1 mysql mysql 13350912000 Jul 8 15:18 '#sql_1_6.MAD' -rw-rw---- 1 mysql mysql 8192 Jul 8 15:02 '#sql_1_6.MAI'
We failed to identify the origin of this behaviour.
Does anyone know what can trigger this behavior ? We also failed to find a configuration paramater to avoid and limit the size of these files, any ideas about that ?
Thanks for your help,
Regards, Sergei VP of MariaDB Server Engineering and security@mariadb.org
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Hello !
Thanks for your replies.
looks like temporary tables from bad queries
at this time, we don't find the related queries, when we restart the docker container we lost the /tmp files
enable slow query log
Indeed, we activated the slow queries and log them, but we believe that it will not help us because the slow log is write at the end of a queries and at this time the disk is full most of the time.
When did it start?
We noticed this new problem after the upgrade 10.2.23 to 10.3.14 (last 24 April). We had two problems like that since.
What filesystem is it?
MariaDB is inside a docker container, the volume is mount on EXT4 filesystem.
In the last problem, the tmp files were taking 120Go in fews seconds. We didn't find a paramater in the configuration to limit the tmp file size. We tried the parameter tmp_disk_table_size but it's seems that works only on MyISAM.
Le mer. 7 août 2019 à 14:06, Rhys.Campbell@swisscom.com a écrit :
Perhaps you can use one of the file_summary_*/other tables in the performance_schema to identify the query causing this?
R
-----Original Message----- From: Maria-discuss <maria-discuss-bounces+rhys.campbell= swisscom.com@lists.launchpad.net> On Behalf Of Sergei Golubchik Sent: 07 August 2019 13:42 To: Kevin DG kevin@dubourg.info Cc: maria-discuss@lists.launchpad.net Subject: Re: [Maria-discuss] Disk increase with MAD and MAI extension files in tmp dir
Hi, Kevin!
When did it start?
Did you, for example, upgrade from 10.3.13 to 10.3.14 and then noticed it? (I'm not saying this is a known bug in 10.3.14, it was just an example)
What filesystem is it?
And, don't forget, if it looks like a bug to you, report it at jira.mariadb.org to get it fixed
On Aug 07, Kevin DG wrote:
Hello all,
MariaDB version -> 10.3.14
Recently, we had a new problem with our MariaDB instance. Indeed, suddenly the size of the tmp directory has increased almost instantaneously, to finish saturating the disk.
We have encountered this problem twice and don't understand currently the problem origin.
The result of 'ls -l /tmp' during the problem:
mysql@16c212e7dbb5:/tmp# ls -l total 126786988 -rw-rw---- 1 mysql mysql 14190428160 Jul 8 15:18 '#sql_1_1.MAD' -rw-rw---- 1 mysql mysql 8192 Jul 8 15:02 '#sql_1_1.MAI' -rw-rw---- 1 mysql mysql 13344178176 Jul 8 15:18 '#sql_1_11.MAD' -rw-rw---- 1 mysql mysql 8192 Jul 8 15:02 '#sql_1_11.MAI' -rw-rw---- 1 mysql mysql 13344546816 Jul 8 15:18 '#sql_1_14.MAD' -rw-rw---- 1 mysql mysql 8192 Jul 8 15:02 '#sql_1_14.MAI' -rw-rw---- 1 mysql mysql 8192 Jul 8 15:18 '#sql_1_2.MAD' -rw-rw---- 1 mysql mysql 8192 Jul 8 15:18 '#sql_1_2.MAI' -rw-rw---- 1 mysql mysql 12613459968 Jul 8 15:20 '#sql_1_25.MAD' -rw-rw---- 1 mysql mysql 8192 Jul 8 15:03 '#sql_1_25.MAI' -rw-rw---- 1 mysql mysql 12221440000 Jul 8 15:20 '#sql_1_26.MAD' -rw-rw---- 1 mysql mysql 8192 Jul 8 15:03 '#sql_1_26.MAI' -rw-rw---- 1 mysql mysql 12220719104 Jul 8 15:20 '#sql_1_29.MAD' -rw-rw---- 1 mysql mysql 8192 Jul 8 15:03 '#sql_1_29.MAI' -rw-rw---- 1 mysql mysql 12220907520 Jul 8 15:20 '#sql_1_36.MAD' -rw-rw---- 1 mysql mysql 8192 Jul 8 15:03 '#sql_1_36.MAI' -rw-rw---- 1 mysql mysql 14190247936 Jul 8 15:18 '#sql_1_4.MAD' -rw-rw---- 1 mysql mysql 8192 Jul 8 15:02 '#sql_1_4.MAI' -rw-rw---- 1 mysql mysql 12220588032 Jul 8 15:18 '#sql_1_47.MAD' -rw-rw---- 1 mysql mysql 8192 Jul 8 15:03 '#sql_1_47.MAI' -rw-rw---- 1 mysql mysql 13350912000 Jul 8 15:18 '#sql_1_6.MAD' -rw-rw---- 1 mysql mysql 8192 Jul 8 15:02 '#sql_1_6.MAI'
We failed to identify the origin of this behaviour.
Does anyone know what can trigger this behavior ? We also failed to find a configuration paramater to avoid and limit the size of these files, any ideas about that ?
Thanks for your help,
Regards, Sergei VP of MariaDB Server Engineering and security@mariadb.org
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
participants (4)
-
Kevin DG -
Reindl Harald -
Rhys.Campbell＠swisscom.com -
Sergei Golubchik