Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.6, 10.11, 11.4.1, 11.7.2, 11.8.1
-
None
-
Can result in unexpected behaviour
Description
I was investigating an issue with constantly decreasing RAM on our MariaDB server and found that performance_schema.table_handles contains over 200k records, most of which belong to a single table. The table has a period and a unique key WITHOUT OVERLAPS. Below is a simplified case to reproduce the issue:
MariaDB [test_db]> CREATE TABLE test
|
-> (
|
-> id int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
|
-> rel_id int(11) NOT NULL,
|
-> start_date datetime NOT NULL,
|
-> end_date datetime NOT NULL,
|
-> PERIOD FOR test_period (start_date, end_date),
|
-> UNIQUE KEY test_key (rel_id, test_period WITHOUT overlaps)
|
-> );
|
Query OK, 0 rows affected (0.005 sec)
|
|
|
MariaDB [test_db]>
|
MariaDB [test_db]> UPDATE test SET end_date='2025-01-16' WHERE id = 1;
|
Query OK, 0 rows affected (0.000 sec)
|
Rows matched: 0 Changed: 0 Warnings: 0
|
|
|
MariaDB [test_db]> SELECT * from performance_schema.table_handles WHERE OBJECT_NAME='test';
|
+-------------+---------------------------+-------------+-----------------------+-----------------+----------------+---------------+---------------+
|
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | OBJECT_INSTANCE_BEGIN | OWNER_THREAD_ID | OWNER_EVENT_ID | INTERNAL_LOCK | EXTERNAL_LOCK |
|
+-------------+---------------------------+-------------+-----------------------+-----------------+----------------+---------------+---------------+
|
| TABLE | test_db | test | 136829944959896 | 0 | 0 | NULL | NULL |
|
| TABLE | test_db | test | 136829940615392 | 15 | 1 | NULL | NULL |
|
+-------------+---------------------------+-------------+-----------------------+-----------------+----------------+---------------+---------------+
|
2 rows in set (0.000 sec)
|
|
|
MariaDB [test_db]>
|
MariaDB [test_db]> UPDATE test SET end_date='2025-01-16' WHERE id = 1;
|
Query OK, 0 rows affected (0.000 sec)
|
Rows matched: 0 Changed: 0 Warnings: 0
|
|
|
MariaDB [test_db]> SELECT * from performance_schema.table_handles WHERE OBJECT_NAME='test';
|
+-------------+---------------------------+-------------+-----------------------+-----------------+----------------+---------------+---------------+
|
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | OBJECT_INSTANCE_BEGIN | OWNER_THREAD_ID | OWNER_EVENT_ID | INTERNAL_LOCK | EXTERNAL_LOCK |
|
+-------------+---------------------------+-------------+-----------------------+-----------------+----------------+---------------+---------------+
|
| TABLE | test_db | test | 136829944959896 | 0 | 0 | NULL | NULL |
|
| TABLE | test_db | test | 136829940615392 | 15 | 1 | NULL | NULL |
|
| TABLE | test_db | test | 136829940615264 | 15 | 1 | NULL | NULL |
|
+-------------+---------------------------+-------------+-----------------------+-----------------+----------------+---------------+---------------+
|
3 rows in set (0.000 sec)
|
|
|
MariaDB [test_db]>
|
MariaDB [test_db]>
|
MariaDB [test_db]> UPDATE test SET end_date='2025-01-16' WHERE id = 1;
|
Query OK, 0 rows affected (0.000 sec)
|
Rows matched: 0 Changed: 0 Warnings: 0
|
|
|
MariaDB [test_db]> SELECT * from performance_schema.table_handles WHERE OBJECT_NAME='test';
|
+-------------+---------------------------+-------------+-----------------------+-----------------+----------------+---------------+---------------+
|
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | OBJECT_INSTANCE_BEGIN | OWNER_THREAD_ID | OWNER_EVENT_ID | INTERNAL_LOCK | EXTERNAL_LOCK |
|
+-------------+---------------------------+-------------+-----------------------+-----------------+----------------+---------------+---------------+
|
| TABLE | test_db | test | 136829944959896 | 0 | 0 | NULL | NULL |
|
| TABLE | test_db | test | 136829940615392 | 15 | 1 | NULL | NULL |
|
| TABLE | test_db | test | 136829940615264 | 15 | 1 | NULL | NULL |
|
| TABLE | test_db | test | 136829940615264 | 15 | 1 | NULL | NULL |
|
+-------------+---------------------------+-------------+-----------------------+-----------------+----------------+---------------+---------------+
|
|
You can see that after each update a record is added to the table_handles table and it looks like they are never cleaned until you restart the server.