Bug #117022 DDL operations using copy algorithm aquires unnecessary row locks thus causing performance regession

• Submitted: 19 Dec 8:02
• Modified: 19 Dec 11:43
• Reporter: Brian Yue (OCA)
• Status: Not a Bug
• Category: MySQL Server: DDL
• Severity: S5 (Performance)
• Version: 8.0.40
• OS: Any (Redhat 7.4)
• CPU Architecture: Any (Intel)

[19 Dec 8:02] Brian Yue

Description:
Hello,
  Recently we find that DDL operations that use COPY algorithm aquires row locks when doing data copy:
```
[session 1]
(1) Prepare a table sbtest1 with 10000000 records with sysbench

(2) Let's check info of table sbtest1
mysql> show create table sbtest1 \G
*************************** 1. row ***************************
       Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
  `id` int unsigned NOT NULL,
  `k` int unsigned NOT NULL DEFAULT '0',
  `c` char(120) COLLATE utf8mb3_bin NOT NULL DEFAULT '',
  `pad` char(60) COLLATE utf8mb3_bin NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin MAX_ROWS=1000000
1 row in set (0.00 sec)

mysql> select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (1.53 sec)

(3) Start DDL operation that changes column type of pad from char(60) to text
mysql> alter table sbtest1 modify column pad text;

(4) query performance_schema.data_locks and we can find a lot of row locks are aquired:
mysql> select * from performance_schema.data_locks;
.....
| INNODB | 140066266860760:92:4:5780:72:140064240430008            |              29339167 |        70 |       28 | yxx           | sbtest1          | NULL           | NULL              | PRIMARY    |       140064240430008 | RECORD    | S,REC_NOT_GAP | GRANTED     | 372918    |
| INNODB | 140066266860760:92:4:5780:73:140064240430008            |              29339167 |        70 |       28 | yxx           | sbtest1          | NULL           | NULL              | PRIMARY    |       140064240430008 | RECORD    | S,REC_NOT_GAP | GRANTED     | 372919    |
| INNODB | 140066266860760:92:4:5780:74:140064240430008            |              29339167 |        70 |       28 | yxx           | sbtest1          | NULL           | NULL              | PRIMARY    |       140064240430008 | RECORD    | S,REC_NOT_GAP | GRANTED     | 372920    |
+--------+---------------------------------------------------------+-----------------------+-----------+----------+---------------+------------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
305844 rows in set (1.15 sec)
```

Actually, COPY algrithm of DDL operation has already protected concurrent data changement with EXCLUSIVE metadata lock, thus there cannot be any session changes data when DDL is copying data from source table to dest table, and then row locks on source table is unnecessay and will cause performance regression.

In order to test how much performance regression is introduced by the unnecessary row locks, I tried to remove row lock aquirement by ALTER statement (change m_prebuilt->select_lock_type from LOCK_S to LOCK_NONE for command SQLCOM_ALTER_TABLE in function ha_innobase::store_lock of source file storage/innobase/handler/ha_innodb.cc), as a result my testment shows 15% performance improvement (before testment data of table is preheated for correctness):

[before modification, row locks are aquired by DDL copy operation, totally 250 seconds costed]
mysql> alter table sbtest1 modify column pad text; alter table sbtest1 modify column pad char(60);
Query OK, 10000000 rows affected (2 min 3.61 sec)
Records: 10000000  Duplicates: 0  Warnings: 0

Query OK, 10000000 rows affected (2 min 6.63 sec)
Records: 10000000  Duplicates: 0  Warnings: 0

[after modification, row locks are not aquired by DDL copy operation, totally 213 seconds costed]
mysql> alter table sbtest1 modify column pad text; alter table sbtest1 modify column pad char(60);
Query OK, 10000000 rows affected (1 min 42.67 sec)
Records: 10000000  Duplicates: 0  Warnings: 0

Query OK, 10000000 rows affected (1 min 50.49 sec)
Records: 10000000  Duplicates: 0  Warnings: 0

total cost time is decreased from 250 seconds to 213 seconds, 15% performance improvement.

How to repeat:
Has already described above

[19 Dec 11:43] MySQL Verification Team

Hi Mr. Yue,

Thank you for your bug report.

First and first of all, this is a forum for reports with fully repeatable test cases.

Hence, we need the manner in which to fill up that table.

However, there is much more important problem in your performance improvement request.

Row locks must be taken, in spite of the metadata locks, due to the various algorithms for performing DDL operations .....

Hence, seems that this request can not be implemented, but we shall consult the team in charge.