| Bug #114420 | ALTER TABLE operation with Inplace algorithm is slower in 8.0 | ||
|---|---|---|---|
| Submitted: | 20 Mar 2024 1:22 | Modified: | 2 Dec 2024 21:56 |
| Reporter: | Chelluru Vidyadhar | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S5 (Performance) |
| Version: | 8.0.36 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[21 Mar 2024 0:06]
Marc Reilly
Seems this behavior may be a regression introduced in 8.0.20
```
[ec2-user@reillym-sysbench-wrfwd-1a ~]$ for i in {16..33}; do time ./sandboxes/msb_8_0_${i}/use -e "select @@version;alter table sysbench.sbtest1 engine innodb, algorithm inplace;";done
+-----------+
| @@version |
+-----------+
| 8.0.16 |
+-----------+
real 0m12.666s
user 0m0.007s
sys 0m0.000s
+-----------+
| @@version |
+-----------+
| 8.0.18 |
+-----------+
real 0m11.276s
user 0m0.007s
sys 0m0.000s
+-----------+
| @@version |
+-----------+
| 8.0.19 |
+-----------+
real 0m12.945s
user 0m0.007s
sys 0m0.000s
+-----------+
| @@version |
+-----------+
| 8.0.20 |
+-----------+
real 0m38.102s <--- Jumps to 38 seconds using defaults
user 0m0.002s
sys 0m0.005s
+-----------+
| @@version |
+-----------+
| 8.0.21 |
+-----------+
real 0m44.812s
user 0m0.000s
sys 0m0.007s
+-----------+
| @@version |
+-----------+
| 8.0.22 |
+-----------+
real 0m46.139s
user 0m0.002s
sys 0m0.005s
.
.
.
.
+-----------+
| @@version |
+-----------+
| 8.0.33 |
+-----------+
real 0m40.224s
user 0m0.002s
sys 0m0.005s
```
After a bit of digging I think it may have been introduced as part of this change ce14ef91196c9c46c487b7b31a213c7d6a660af1
To try test this I checked with doublewrite enabled/disabled and perf on 8019 matches that of 8020.
```
mysql [localhost:8020] {msandbox} (sysbench) > select @@version,@@innodb_doublewrite;
+-----------+----------------------+
| @@version | @@innodb_doublewrite |
+-----------+----------------------+
| 8.0.20 | 0 |
+-----------+----------------------+
1 row in set (0.00 sec)
mysql [localhost:8020] {msandbox} (sysbench) > alter table sysbench.sbtest1 engine innodb, algorithm inplace;
Query OK, 0 rows affected (2.32 sec)
Records: 0 Duplicates: 0 Warnings: 0
```
```
mysql [localhost:8020] {msandbox} (sysbench) > alter table sysbench.sbtest1 engine innodb, algorithm inplace;
Query OK, 0 rows affected (2.20 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql [localhost:8019] {msandbox} (sysbench) > select @@version,@@innodb_doublewrite;
+-----------+----------------------+
| @@version | @@innodb_doublewrite |
+-----------+----------------------+
| 8.0.19 | 0 |
+-----------+----------------------+
1 row in set (0.00 sec)
mysql [localhost:8019] {msandbox} (sysbench) > alter table sysbench.sbtest1 engine innodb, algorithm inplace;
Query OK, 0 rows affected (2.33 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql [localhost:8019] {msandbox} (sysbench) > alter table sysbench.sbtest1 engine innodb, algorithm inplace;
Query OK, 0 rows affected (2.44 sec)
Records: 0 Duplicates: 0 Warnings: 0
```
Latest 8036
```
mysql> select @@version,@@innodb_doublewrite;
+-----------+----------------------+
| @@version | @@innodb_doublewrite |
+-----------+----------------------+
| 8.0.36 | ON |
+-----------+----------------------+
1 row in set (0.00 sec)
mysql> alter table sysbench.sbtest1 engine innodb, algorithm inplace;
Query OK, 0 rows affected (19.95 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table sysbench.sbtest1 engine innodb, algorithm inplace;
Query OK, 0 rows affected (19.52 sec)
Records: 0 Duplicates: 0 Warnings: 0
===================
mysql> select @@version,@@innodb_doublewrite;
+-----------+----------------------+
| @@version | @@innodb_doublewrite |
+-----------+----------------------+
| 8.0.36 | OFF |
+-----------+----------------------+
1 row in set (0.00 sec)
mysql> alter table sysbench.sbtest1 engine innodb, algorithm inplace;
Query OK, 0 rows affected (1.95 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table sysbench.sbtest1 engine innodb, algorithm inplace;
Query OK, 0 rows affected (1.91 sec)
Records: 0 Duplicates: 0 Warnings: 0
```
[22 Mar 2024 7:15]
MySQL Verification Team
Hello Chelluru, Thank you for the report and feedback. Verified as described. regards, Umesh
[24 Mar 2024 6:49]
Marc Reilly
FYI in case anyone stumbles across this based on the title, I also opened https://bugs.mysql.com/bug.php?id=114465 separately to avoid confusion where 8027+ seems to be affected by another issue related to parallel index builds.
[24 Apr 2024 14:57]
huahua xu
Hi Chelluru, On 8.0, the inplace algorithm rebuilds the indexes by bulk load, and would not write redo logs when filling data pages. The mechanism has caused two issues that lead to performance degradation. 1). The fewer redo logs may limit the ratio of flushing dirty pages; 2). The ddl threads would participate in flushing dirty pages by `BUF_FLUSH_SINGLE_PAGE`, then wait for all dirty pages were flushed. Overall, the main performance bottleneck is flushing dirty pages. To improve the performance of flushing dirty pages, I set innodb_buffer_pool_size=1024M to enable the parallelizability of pages cleaner. set innodb_buffer_pool_size=512M, ``` mysql> alter table test.sbtest1 engine innodb, algorithm copy; Query OK, 10000 rows affected (1 min 9.01 sec) mysql> alter table test.sbtest1 engine innodb, algorithm inplace; Query OK, 0 rows affected (2 min 15.84 sec) ``` set innodb_buffer_pool_size=1024M, ``` mysql> alter table test.sbtest1 engine innodb, algorithm copy; Query OK, 10000 rows affected (1 min 22.01 sec) mysql> alter table test.sbtest1 engine innodb, algorithm inplace; Query OK, 0 rows affected (1 min 16.67 sec) ``` Based on the above results, inplace algorithm is not slower than copy algorithm.
[14 Jun 2024 5:36]
Nisha Padmini Gopalakrishnan
Posted by developer: The current default value of 4 for 'innodb_doublewrite_pages' derived from 'innodb_write_io_threads' is low compared to versions prior to 8.0.20 and results in more fsync operations for doublewrite. It should be set to 120, equivalent to the legacy `srv_doublewrite_batch_size`. Executing ALTER TABLE INPLACE operation: 8.0.36(with innodb_doublewrite_pages set to 120) mysql> alter table sbtest1 engine innodb, algorithm inplace; Query OK, 0 rows affected (16.65 sec) Records: 0 Duplicates: 0 Warnings: 0 8.0.19 mysql> alter table sbtest1 engine innodb, algorithm inplace; Query OK, 0 rows affected (18.22 sec) Records: 0 Duplicates: 0 Warnings: 0 As seen the performance of ALTER TABLE,INPLACE in 8.0.36 is similar compared to 8.0.19(version prior to changes in 8.0.20 related doublewrite buffer: https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-20.html#mysqld-8-0-20-feature).
[16 Jul 2024 17:29]
Srinivasarao Ravuri
Posted by developer: 8.0.20 introduced a new configuration variable _innodb_doublewrite_pages <https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_doublewrite_p...> with default value set equal to _innodb_write_io_threads <https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_write_io_thre...>_ (defaults to 4). We later found that 4 was the wrong default value because it caused too many fsync operations for doublewrite. Executing above query with innodb_doublewrite_pages set to 120 results in same performance compared to 5.7. We changed the default value to innodb_doublewrite_pages = 128 in 8.4. See innodb_doublewrite_pages <https://dev.mysql.com/doc/refman/8.4/en/innodb-parameters.html#sysvar_innodb_doublewrite_p...> in 8.4 manual. This can be seen as a bug in 8.0 (wrong default value causes regression from 5.7). The only reason we have not fixed this in 8.0 is because of the LTS stability of 8.0. Workaround for 8.0 is to explicitly set innodb_doublewrite_pages = 128 in 8.0 or upgrade to 8.4.
[2 Dec 2024 21:56]
Philip Olson
Posted by developer: This bug was closed a documentation bug. An example piece of new documentation: The default value changed from 4 (copied from innodb_write_io_threads in 8.0) to 128 in MySQL 8.4.0. This small value could cause too many fsync operations for doublewrite operations. For related information, see optimizing-innodb-diskio (link). https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_doublewrite_p... Thanks for reporting this issue.
[28 Apr 19:43]
Jean-François Gagné
Related: Bug #111353 (with tests for the fix).
[29 Apr 6:38]
MySQL Verification Team
Bug #111353 marked as duplicate of this one
Description: When performing an alter operation to rebuild the table is slower with algorithm inplace compared to the algorithm copy. This is noticed on tables with dynamic format and involving LOB datatypes. The inplace algorithm on 8.0 on a table with 10k records (including text column) as described in repro section completed in 67.68 seconds and the same operation on 5.7 (on same dataset) completed in 46.69 seconds which is approx 30% faster. Further, we see that copy algorithm is faster than inplace algorithm in this case on idle mysqld instance. How to repeat: Run below steps on 5.7.44 and 8.0.36 1. create a table with below schema and load 10k rows into the same using sysbench command. mysql> show create table sbtest.sbtest2\G *************************** 1. row *************************** Table: sbtest2 Create Table: CREATE TABLE `sbtest2` ( `id` int NOT NULL AUTO_INCREMENT, `k` int NOT NULL DEFAULT '0', `c` text, `pad` char(60) NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `k_1` (`k`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci 1 row in set (0.00 sec) 2. update max_allowed_packet_size and update the column 'c' with large string values. mysql> alter table sbtest.sbtest2 modify column c text; mysql> update sbtest.sbtest2 set c=repeat(c,100); mysql> update sbtest.sbtest2 set c=repeat(c,5); 3. Consider running alter operation with different algorithms (copy and inplace) 8.0:- mysql> show status like 'innodb%pages%'; +----------------------------------+-------+ | Variable_name | Value | +----------------------------------+-------+ | Innodb_buffer_pool_pages_data | 5287 | | Innodb_buffer_pool_pages_dirty | 0 | | Innodb_buffer_pool_pages_flushed | 186 | | Innodb_buffer_pool_pages_free | 27476 | | Innodb_buffer_pool_pages_misc | 5 | | Innodb_buffer_pool_pages_total | 32768 | | Innodb_dblwr_pages_written | 45 | | Innodb_pages_created | 143 | | Innodb_pages_read | 5144 | | Innodb_pages_written | 186 | | Innodb_sampled_pages_read | 0 | | Innodb_sampled_pages_skipped | 0 | +----------------------------------+-------+ 12 rows in set (0.00 sec) mysql> alter table sbtest.sbtest2 engine innodb, algorithm copy; Query OK, 10000 rows affected (44.00 sec) Records: 10000 Duplicates: 0 Warnings: 0 mysql> show status like 'innodb%pages%'; +----------------------------------+-------+ | Variable_name | Value | +----------------------------------+-------+ | Innodb_buffer_pool_pages_data | 32763 | | Innodb_buffer_pool_pages_dirty | 3860 | | Innodb_buffer_pool_pages_flushed | 36311 | | Innodb_buffer_pool_pages_free | 0 | | Innodb_buffer_pool_pages_misc | 5 | | Innodb_buffer_pool_pages_total | 32768 | | Innodb_dblwr_pages_written | 36482 | | Innodb_pages_created | 40250 | | Innodb_pages_read | 45311 | | Innodb_pages_written | 36623 | | Innodb_sampled_pages_read | 0 | | Innodb_sampled_pages_skipped | 0 | +----------------------------------+-------+ 12 rows in set (0.00 sec) restart mysqld and rerun the alter operation with algorithm inplace mysql> show status like 'innodb%pages%'; +----------------------------------+-------+ | Variable_name | Value | +----------------------------------+-------+ | Innodb_buffer_pool_pages_data | 9039 | | Innodb_buffer_pool_pages_dirty | 0 | | Innodb_buffer_pool_pages_flushed | 186 | | Innodb_buffer_pool_pages_free | 23724 | | Innodb_buffer_pool_pages_misc | 5 | | Innodb_buffer_pool_pages_total | 32768 | | Innodb_dblwr_pages_written | 45 | | Innodb_pages_created | 143 | | Innodb_pages_read | 8896 | | Innodb_pages_written | 186 | | Innodb_sampled_pages_read | 0 | | Innodb_sampled_pages_skipped | 0 | +----------------------------------+-------+ 12 rows in set (0.00 sec) mysql> alter table sbtest.sbtest2 engine innodb, algorithm inplace; show status like 'innodb%pages%'; Query OK, 0 rows affected (1 min 7.68 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show status like 'innodb%pages%'; +----------------------------------+-------+ | Variable_name | Value | +----------------------------------+-------+ | Innodb_buffer_pool_pages_data | 15700 | | Innodb_buffer_pool_pages_dirty | 65 | | Innodb_buffer_pool_pages_flushed | 22827 | | Innodb_buffer_pool_pages_free | 17064 | | Innodb_buffer_pool_pages_misc | 4 | | Innodb_buffer_pool_pages_total | 32768 | | Innodb_dblwr_pages_written | 22686 | | Innodb_pages_created | 40237 | | Innodb_pages_read | 49083 | | Innodb_pages_written | 40300 | | Innodb_sampled_pages_read | 0 | | Innodb_sampled_pages_skipped | 0 | +----------------------------------+-------+ 12 rows in set (0.00 sec) 5.7:- When we perform the same test on any 5.7 version we see that both ALTER operations using different algorithm on same data set completed around same time. mysql> show status like 'innodb%pages%'; +----------------------------------+-------+ | Variable_name | Value | +----------------------------------+-------+ | Innodb_buffer_pool_pages_data | 325 | | Innodb_buffer_pool_pages_dirty | 0 | | Innodb_buffer_pool_pages_flushed | 36 | | Innodb_buffer_pool_pages_free | 32439 | | Innodb_buffer_pool_pages_misc | 0 | | Innodb_buffer_pool_pages_total | 32764 | | Innodb_dblwr_pages_written | 2 | | Innodb_pages_created | 34 | | Innodb_pages_read | 291 | | Innodb_pages_written | 36 | +----------------------------------+-------+ 10 rows in set (0.00 sec) mysql> alter table sbtest.sbtest2 engine innodb, algorithm copy; Query OK, 10000 rows affected (43.10 sec) Records: 10000 Duplicates: 0 Warnings: 0 mysql> show status like 'innodb%pages%'; +----------------------------------+-------+ | Variable_name | Value | +----------------------------------+-------+ | Innodb_buffer_pool_pages_data | 32273 | | Innodb_buffer_pool_pages_dirty | 4053 | | Innodb_buffer_pool_pages_flushed | 35435 | | Innodb_buffer_pool_pages_free | 490 | | Innodb_buffer_pool_pages_misc | 1 | | Innodb_buffer_pool_pages_total | 32764 | | Innodb_dblwr_pages_written | 36241 | | Innodb_pages_created | 40141 | | Innodb_pages_read | 40422 | | Innodb_pages_written | 36274 | +----------------------------------+-------+ 10 rows in set (0.01 sec) restart mysqld instance mysql> show status like 'innodb%pages%'; +----------------------------------+-------+ | Variable_name | Value | +----------------------------------+-------+ | Innodb_buffer_pool_pages_data | 325 | | Innodb_buffer_pool_pages_dirty | 0 | | Innodb_buffer_pool_pages_flushed | 36 | | Innodb_buffer_pool_pages_free | 32439 | | Innodb_buffer_pool_pages_misc | 0 | | Innodb_buffer_pool_pages_total | 32764 | | Innodb_dblwr_pages_written | 2 | | Innodb_pages_created | 34 | | Innodb_pages_read | 291 | | Innodb_pages_written | 36 | +----------------------------------+-------+ 10 rows in set (0.00 sec) mysql> alter table sbtest.sbtest2 engine innodb, algorithm inplace; Query OK, 0 rows affected (46.69 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show status like 'innodb%pages%'; +----------------------------------+-------+ | Variable_name | Value | +----------------------------------+-------+ | Innodb_buffer_pool_pages_data | 31778 | | Innodb_buffer_pool_pages_dirty | 20 | | Innodb_buffer_pool_pages_flushed | 19761 | | Innodb_buffer_pool_pages_free | 986 | | Innodb_buffer_pool_pages_misc | 0 | | Innodb_buffer_pool_pages_total | 32764 | | Innodb_dblwr_pages_written | 40109 | | Innodb_pages_created | 40126 | | Innodb_pages_read | 40414 | | Innodb_pages_written | 40143 | +----------------------------------+-------+ 10 rows in set (0.00 sec) Summary: The ALTER command completed much faster in 5.7 compared to 8.0 with inplace algorithm. Also, the operation with copy algorithm is much faster than inplace algorithm on idle (only one connection and no other foreground threads) mysql instance running 8.0. Suggested fix: Improve performance of inplace algorithm in 8.0