Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.1.8
-
None
-
debian
-
10.1.14
Description
Enabling Defragmenting in 10.1.8 still causes OPTIMIZE TABLE to take metadatalocks.
enabled it:
MariaDB [(none)]> set global innodb_defragment=on;
|
Query OK, 0 rows affected (0.00 sec)
|
|
|
MariaDB [(none)]> set global innodb_defragment_frequency=1000;
|
Query OK, 0 rows affected (0.00 sec)
|
|
|
MariaDB [(none)]> set global innodb_defragment_n_pages=32;
|
Query OK, 0 rows affected (0.00 sec)
|
|
|
MariaDB [(none)]> show global variables like '%defrag%';
|
+--------------------------------------+----------+
|
| Variable_name | Value |
|
+--------------------------------------+----------+
|
| innodb_defragment | ON |
|
| innodb_defragment_fill_factor | 0.900000 |
|
| innodb_defragment_fill_factor_n_recs | 20 |
|
| innodb_defragment_frequency | 1000 |
|
| innodb_defragment_n_pages | 32 |
|
| innodb_defragment_stats_accuracy | 0 |
|
+--------------------------------------+----------+
|
6 rows in set (0.00 sec)
|
this is how processlist looks like:
MariaDB [(none)]> show processlist;
|
+-----+-------------+-------------------+------+---------+------+----------------------------------+-----------------------------------------+----------+
|
| Id | User | Host | db | Command | Time | State | Info | Progress |
|
+-----+-------------+-------------------+------+---------+------+----------------------------------+-----------------------------------------+----------+
|
| 16 | monitoring | localhost | NULL | Query | 440 | executing | optimize table graph.graph | 0.000 |
|
| 283 | system user | | NULL | Connect | 230 | Waiting for master to send event | NULL | 0.000 |
|
| 284 | system user | | NULL | Connect | 230 | Waiting for table metadata lock | NULL | 0.000 |
|
| 477 | monitoring | localhost | sys | Query | 12 | Waiting for table metadata lock | select * from information_schema.TABLES | 0.000 |
|
+-----+-------------+-------------------+------+---------+------+----------------------------------+-----------------------------------------+----------+
|
Attachments
Activity
This is a complex issue. OPTIMIZE always used to take rather strong locks because of MyISAM —MyISAM cannot allow any other connection accessing the table that is being optimized. InnoDB does not have this limitation. But we need to open the table to know whether it's MyISAM or InnoDB table. And we need to take metadata locks before the table is opened (because the table's engine is also part of metadata, we cannot read it without the metadata lock).
We'll fix it nevertheless. But may be this fix won't make it into 10.1.14
I see that this issue appears to be closed, but I just tried innodb_fragment = 1 and optimise table still blocks access to that table.
First, on connection #1:
MariaDB [geozone_app]> optimize table user_update;
|
Then, on connection #2:
MariaDB [geozone_app]> select * from user_update limit 1;
|
It's blocked.
*************************** 1. row ***************************
|
Id: 829
|
User: samuel
|
Host: localhost
|
db: geozone_app
|
Command: Query
|
Time: 361
|
State: executing
|
Info: optimize table user_update
|
Progress: 0.000
|
|
|
*************************** 4. row ***************************
|
Id: 11798
|
User: samuel
|
Host: localhost
|
db: geozone_app
|
Command: Query
|
Time: 323
|
State: Waiting for table level lock
|
Info: select * from user_update limit 1
|
Progress: 0.000
|
Is this to be expected?
business% mysql --version
|
mysql Ver 15.1 Distrib 10.1.18-MariaDB, for Linux (x86_64) using readline 5.1
|
ioquatix, it's a different issue. This MDEV-9115 is about metadata locks, see in the original bug description Waiting for table metadata lock, but what you see is a table level data lock, note Waiting for table level lock. This might've been fixed in 10.2.
Okay, do you know if there is an issue being tracked somewhere?
Additionally, I tried using this, but no disk space was freed up despite there being I think 5Gb of unused space by the table - I dropped all indexes, etc.
Facebook implementation introduced new SQL-syntax and it used different metadata locks. MariaDB implementation re-used OPTIMIZE TABLE SQL-syntax and uses the metadata locks based on that. Not sure why it would take exclusive lock (replication ?) when shared should be enough to avoid concurrent DDL-operations. Assigning to serg for comment, can metadata lock be changed to shared-lock or no lock.