| Bug #79462 | ONLINE ALTER table holds metadata lock preventing local DML | ||
|---|---|---|---|
| Submitted: | 30 Nov 2015 19:17 | Modified: | 4 Dec 2015 8:11 |
| Reporter: | Matthew Montgomery | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Cluster: Cluster (NDB) storage engine | Severity: | S3 (Non-critical) |
| Version: | ndb-7.3.7, 7.3.12 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[4 Dec 2015 8:11]
MySQL Verification Team
Hello Matthew, Thank you for the report. Observed with 7.3.12 build. Thanks, Umesh
[4 Dec 2015 8:13]
MySQL Verification Team
// Build used
[umshastr@hod03]/export/umesh/cluster/mysql-cluster-7.3.12: cat docs/INFO_SRC
commit: 32a414f1e9cb382fe4fa417693a140f1570610d7
date: 2015-12-02 16:36:11 +0100
build-date: 2015-12-02 19:46:14 +0100
short: 32a414f
branch: mysql-5.6-cluster-7.3
MySQL source 5.6.27
create database if not exists test;
use test;
drop table if exists test1;
CREATE TABLE `test1` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`b` varchar(250),
PRIMARY KEY (`id`)
) ENGINE=ndb;
set @id:=0;
insert into `test1` values
(@id:=@id+1,md5(rand()*1000000))
, (@id:=@id+1,md5(rand()*1000000))
, (@id:=@id+1,md5(rand()*1000000))
, (@id:=@id+1,md5(rand()*1000000));
insert into `test1`(`id`,`b`) select @id:=@id+1,md5(rand()*1000000) from `test1` k1, `test1` k2, `test1` k3, `test1` k4,`test1` k5,`test1` k6, `test1` k7, `test1` k8, `test1` k9,`test1` k0,`test1` ka, `test1` kb, `test1` kc, `test1` kd limit 500000;
Populated some more data.
Session1>select count(*) from test1;
+----------+
| count(*) |
+----------+
| 1450020 |
+----------+
1 row in set (0.01 sec)
Session1>alter table test1 add index `b` (b), algorithm=inplace; alter table test1 drop index `b`, algorithm=inplace;
Query OK, 0 rows affected (11.68 sec)
Records: 0 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.12 sec)
Records: 0 Duplicates: 0 Warnings: 0
## session 2
Session2>set profiling = 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Session2>insert into test1 (b) values('mysql;mysql;');
Query OK, 1 row affected (9.47 sec)
Session2>set profiling = 0;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Session2>show profiles;
+----------+------------+----------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+----------------------------------------------+
| 1 | 0.00067900 | insert into test1 (b) values('mysql;mysql;') |
| 2 | 9.46695675 | insert into test1 (b) values('mysql;mysql;') |
+----------+------------+----------------------------------------------+
2 rows in set, 1 warning (0.00 sec)
Session2>show profile for query 2;
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.000068 |
| checking permissions | 0.000009 |
| Opening tables | 0.000026 |
| Waiting for table metadata loc | 9.466248 |
| Opening tables | 0.000030 |
| init | 0.000022 |
| System lock | 0.000126 |
| update | 0.000279 |
| end | 0.000006 |
| query end | 0.000104 |
| closing tables | 0.000013 |
| freeing items | 0.000015 |
| cleaning up | 0.000014 |
+--------------------------------+----------+
13 rows in set, 1 warning (0.00 sec)
Session2>
[27 Dec 2017 8:14]
Ankit Kapoor
Hi Team , I too face these issues when using online DDL (5.7.17) . I see you have mentioned to remove meta data lock as a suggested fix. Is my understanding correct ? If yes , can you please tell how to remove it ? If no , what is the fix for this ? Moreover , when im rebuilding my INNODB table , i have seen that a new temp ibd table has been created in data directory which is usually default behavior.So what is the use of INPLACE then ? alter table table_name engine=INNODB,algorithm=inplace,lock=none; Regards Ankit Kapoor
Description: When performing ALTER with ONLINE or ALGORITHM=INPLACE a metadata lock is held on the local SQL node preventing other threads from updating the table while the ALTER is in progress, other SQL nodes within the cluster are not blocked from performing INSERT as expected. INSERT is not blocked for InnoDB online alter. How to repeat: #### SESSION 1 #### mysql> alter table test1 add index `b` (b), algorithm=inplace; alter table test1 drop index `b`, algorithm=inplace; Query OK, 0 rows affected (5.40 sec) Records: 0 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (2.46 sec) Records: 0 Duplicates: 0 Warnings: 0 #### SESSION 2 concurrent #### mysql> set profiling = 1; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> insert into test1 (b) values('mysql;mysql;'); Query OK, 1 row affected (4.55 sec) mysql> set profiling = 0; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> show profiles; +----------+------------+------------------------------------------------+ | Query_ID | Duration | Query | +----------+------------+------------------------------------------------+ | 1 | 4.55565800 | insert into test1 (b) values('mysql;mysql;') | +----------+------------+------------------------------------------------+ 1 row in set, 1 warning (0.05 sec) mysql> show profile for query 1; +--------------------------------+----------+ | Status | Duration | +--------------------------------+----------+ | starting | 0.000074 | | checking permissions | 0.000014 | | Opening tables | 0.000025 | | Waiting for table metadata loc | 4.550346 | | Opening tables | 0.000033 | | init | 0.000009 | | System lock | 0.000065 | | update | 0.003029 | | end | 0.000060 | | query end | 0.001918 | | closing tables | 0.000025 | | freeing items | 0.000036 | | cleaning up | 0.000025 | +--------------------------------+----------+ 13 rows in set, 1 warning (0.09 sec) Suggested fix: Remove local metadata lock to make online ADD/DROP index behavior consistent with InnoDB behavior where locally originating DML is not blocked while ALTER is in progress.