MariaDB – tmp_disk_table_size

測試環境為 CentOS 8 x86_64

最近遇到一個硬碟爆滿的問題,路徑為 /var/tmp/systemd-private-[########]-mariadb.service-[CCCCCC] ,裡面一堆 .MAD 的檔案把系統硬碟都佔滿了.

上網查才知道當 SQL 語法所產生的資料大於記憶體可以處理的時候會暫存在這邊,關於這一塊官網有一個參數可以設定.

tmp_disk_table_size 預設值 : 18446744073709551615 (無限制) , 範圍從 1024 到 18446744073709551615
Description: Max size for data for an internal temporary on-disk MyISAM or Aria table. These tables are created as part of complex queries when the result doesn’t fit into the memory engine. You can set this variable if you want to limit the size of temporary tables created in your temporary directory tmpdir.

[root@localhost ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 10.3.28-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> SHOW VARIABLES LIKE 'tmp_disk_table_size';
+---------------------+----------------------+
| Variable_name       | Value                |
+---------------------+----------------------+
| tmp_disk_table_size | 18446744073709551615 |
+---------------------+----------------------+
1 row in set (0.002 sec)

可以依據需求改小一點 (我真的不知道要改多少才合理)

MariaDB [(none)]> SET GLOBAL tmp_disk_table_size=1024;
Query OK, 0 rows affected (0.000 sec)

需登出再登入才能看出改變.

MariaDB [(none)]> SHOW VARIABLES LIKE 'tmp_disk_table_size';
+---------------------+----------------------+
| Variable_name       | Value                |
+---------------------+----------------------+
| tmp_disk_table_size | 18446744073709551615 |
+---------------------+----------------------+
1 row in set (0.001 sec)

MariaDB [(none)]> exit
Bye
[root@localhost ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 11
Server version: 10.3.28-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> SHOW VARIABLES LIKE 'tmp_disk_table_size';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| tmp_disk_table_size | 1024  |
+---------------------+-------+
1 row in set (0.001 sec)

SET GLOBAL 在 MariaDB 重新啟動之後就會恢復,可以透過修改設定檔 /etc/my.cnf.d/mariadb-server.cnf 的方式.

[root@localhost ~]# vi /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
...
tmp_disk_table_size=2048

須重啟服務.

[root@localhost ~]# systemctl restart mariadb
[root@localhost ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.3.28-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> SHOW VARIABLES LIKE 'tmp_disk_table_size';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| tmp_disk_table_size | 2048  |
+---------------------+-------+
1 row in set (0.001 sec)