Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Won't Fix
-
10.2.17, 10.2.18
-
Centos 7.4
Description
We have 4 slaves using MariaDB 10.2 doing replication from a 10.2 master. Binary log format is MIXED and temporary tables are using MyISAM format. The number of temporary tables on the slave is growing continuously up to the point of exhausting the file descriptor limit of the operating system assigned to the process. After that the only solution is stopping the slave server and starting again.
This was happening with 10.2.17 on the slaves. We have upgraded to 10.2.18 and the behavior is the same.
The Slave_open_temp_tables variable counter goes up and is consistent with the number of #sql*.MYD files on the tmpdir.
mainro [(none)]> SHOW STATUS LIKE 'Slave_open_temp_tables';
|
+------------------------+-------+
|
| Variable_name | Value |
|
+------------------------+-------+
|
| Slave_open_temp_tables | 3665 |
|
+------------------------+-------+
|
1 row in set (0.00 sec)
|
# cd /var/db/tmp ; ls -l *.MYD | wc -l
|
3665
|
The server does not delete the tmp files from different slave sql threads execution. For example:
-rw-rw---- 1 mysql mysql 0 Oct 9 16:46 #sql4bb4_4809_ff2c.MYD
|
-rw-rw---- 1 mysql mysql 1604 Oct 9 16:46 #sql4bb4_4809_ff42.MYD
|
-rw-rw---- 1 mysql mysql 84 Oct 9 16:46 #sql4bb4_4809_ff43.MYD
|
-rw-rw---- 1 mysql mysql 0 Oct 9 16:46 #sql4bb4_4809_ff45.MYD
|
-rw-rw---- 1 mysql mysql 2912 Oct 9 16:46 #sql4bb4_4809_ff5b.MYD
|
-rw-rw---- 1 mysql mysql 84 Oct 9 16:46 #sql4bb4_4809_ff5c.MYD
|
-rw-rw---- 1 mysql mysql 0 Oct 9 16:46 #sql4bb4_4809_ff5e.MYD
|
-rw-rw---- 1 mysql mysql 5928 Oct 9 16:46 #sql4bb4_4809_ffb1.MYD
|
-rw-rw---- 1 mysql mysql 84 Oct 9 16:46 #sql4bb4_4809_ffb2.MYD
|
-rw-rw---- 1 mysql mysql 0 Oct 9 16:46 #sql4bb4_4809_ffb4.MYD
|
-rw-rw---- 1 mysql mysql 0 Oct 8 17:34 #sql4bb4_77_10488.MYD
|
-rw-rw---- 1 mysql mysql 0 Oct 8 17:34 #sql4bb4_77_10489.MYD
|
-rw-rw---- 1 mysql mysql 24 Oct 8 17:36 #sql4bb4_77_10866.MYD
|
-rw-rw---- 1 mysql mysql 0 Oct 8 17:37 #sql4bb4_77_10a73.MYD
|
-rw-rw---- 1 mysql mysql 468 Oct 8 14:07 #sql4bb4_77_10.MYD
|
-rw-rw---- 1 mysql mysql 0 Oct 8 17:46 #sql4bb4_77_11ade.MYD
|
-rw-rw---- 1 mysql mysql 1468 Oct 8 17:46 #sql4bb4_77_11b1e.MYD
|
-rw-rw---- 1 mysql mysql 84 Oct 8 17:46 #sql4bb4_77_11b1f.MYD
|
-rw-rw---- 1 mysql mysql 0 Oct 8 17:46 #sql4bb4_77_11b21.MYD
|
-rw-rw---- 1 mysql mysql 1352 Oct 8 17:46 #sql4bb4_77_11b35.MYD
|
-rw-rw---- 1 mysql mysql 84 Oct 8 17:46 #sql4bb4_77_11b36.MYD
|
After stopping the server, also does not remove the files. We have to remove the files manually.
We have tried changing the variable default_tmp_storage_engine from MyISAM to InnoDB and the counter Slave_open_temp_tables also keeps growing but, of course, we don't see any temp files on tmpdir, but we believe that eventually something will broke inside InnoDB.
We have been analyzing the binary log and it appears that all explicit create temporary tables are being dropped, either manually by the application, or automatically by the master after session close. Could these stale temporary tables be internal temporary tables that the server is using to resolve some difficult queries?
Attachments
Issue Links
- is blocked by
-
-
- Closed
-
- relates to
-
MDEV-35860 use O_TMPFILE for (implicit) temporary Aria/MyISAM tables
-
- Open
-
Activity
I couldn't build/compile 10.2.17 (using tag mariadb-10.2.17) on my Ubuntu 24.04.1 LTS. I got the following error,
-- Found GnuTLS: /usr/lib/x86_64-linux-gnu/libgnutls.so (found suitable version "3.8.3", minimum required is "3.3.24")
|
-- TLS library/version: GnuTLS 3.8.3
|
-- SYSTEM_LIBS m;m;/usr/lib/x86_64-linux-gnu/libgnutls.so
|
-- Dynamic column API support: ON
|
-- GSSAPI: DYNAMIC
|
SYSTEM processor: x86_64
|
CMake Error at libmariadb/cmake/ConnectorName.cmake:30 (ENDMACRO):
|
Flow control statements are not properly nested.
|
Call Stack (most recent call first):
|
libmariadb/CMakeLists.txt:406 (INCLUDE)
|
So I tested on 10.2.44. I performed the following steps on a 1->2 replication setup,
--slave
|
SET GLOBAL default_tmp_storage_engine = 'MyISAM';
|
--master
|
SET GLOBAL binlog_format = MIXED;
|
|
|
--master
|
CREATE TABLE t1 (c1 TEXT, c2 TEXT);
|
INSERT INTO t1 VALUES (REPEAT('x', 1000), REPEAT('y', 1000));
|
INSERT INTO t1 SELECT * FROM t1;
|
INSERT INTO t1 SELECT * FROM t1;
|
INSERT INTO t1 SELECT * FROM t1;
|
INSERT INTO t1 SELECT * FROM t1;
|
INSERT INTO t1 SELECT * FROM t1;
|
INSERT INTO t1 SELECT * FROM t1;
|
INSERT INTO t1 SELECT * FROM t1;
|
INSERT INTO t1 SELECT * FROM t1;
|
INSERT INTO t1 SELECT * FROM t1;
|
CREATE TABLE t2 AS (SELECT * FROM t1) UNION (SELECT * FROM t1);
|
|
|
--slave
|
10.2.44-opt>SHOW STATUS LIKE 'Slave_open_temp_tables';
|
+------------------------+-------+
|
| Variable_name | Value |
|
+------------------------+-------+
|
| Slave_open_temp_tables | 0 |
|
+------------------------+-------+
|
1 row in set (0.00 sec)
|
I couldn't hit the problem with the above steps on 1->2 replication setup. I'm trying to perform the same on a single master and 4 slave setup.
If you want to check 10.2.17, you can try a release bintar. I don't know if it works on Ubuntu 24.04, but at least mariadb-10.2.15-linux-glibc_214-x86_64.tar.gz works on my Debian 12.
10.2 is EOL and the bug isn't present in any still supported version.
Added MDEV-35860 as an option to resolve the cleanup efforts with O_TMPFILE.
On the general leak that caused this problem I guess validating the code paths around the flags HA_CREATE_TMP_TABLE (SQL inplicit) HA_CREATE_GLOBAL_TMP_TABLE (replication thread SQL implicit).
As a replication statement to test create table t as (SELECT * FROM test) UNION (SELECT * FROM test);