| Bug #78262 | Adding indexes on empty table is slow with large innodb_sort_buffer_size. | ||
|---|---|---|---|
| Submitted: | 28 Aug 2015 15:19 | Modified: | 7 Dec 2015 18:50 |
| Reporter: | Jean-François Gagné | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S5 (Performance) |
| Version: | 5.6.26 | OS: | Any (CentOS release 6.6) |
| Assigned to: | CPU Architecture: | Any | |
[31 Aug 2015 11:06]
MySQL Verification Team
Hello Jean-François Gagné, Thank you for the report and test case. Observed this latency during index creation as described. Thanks, Umesh
[31 Aug 2015 11:06]
MySQL Verification Team
// 5.6.26
// with --innodb_sort_buffer_size=1M
scripts/mysql_install_db --basedir=/export/umesh/server/binaries/mysql-advanced-5.6.26 --datadir=/export/umesh/server/binaries/mysql-advanced-5.6.26/78262
bin/mysqld --innodb_sort_buffer_size=1M --basedir=/export/umesh/server/binaries/mysql-advanced-5.6.26 --datadir=/export/umesh/server/binaries/mysql-advanced-5.6.26/78262 --core-file --socket=/tmp/mysql_ushastry.sock --port=15000 --log-error=/export/umesh/server/binaries/mysql-advanced-5.6.26/78262/log.err 2>&1 &
mysql> use test
Database changed
mysql> CREATE TABLE `test_wo_keys` (
-> `f01` int AUTO_INCREMENT,
-> `f02` bigint, `f03` bigint, `f04` enum('a','b'),
-> `f05` date, `f06` int, `f07` int, `f08` double, `f09` int,
-> `f10` bigint, `f11` double, `f12` enum('a','b','c','d','e'),
-> `f13` int, `f14` int, `f15` varchar(255), `f16` int, `f17` int, `f18` int,
-> `f19` double, `f20` double, `f21` double, `f22` double, `f23` double, `f24` tinyint,
-> `f25` double, `f26` double, `f27` double, `f28` double, `f29` int unsigned,
-> `f30` int unsigned, `f31` bigint, `f32` int unsigned, `f33` bigint,
-> `f34` int unsigned, `f35` int unsigned,
-> PRIMARY KEY `f01` (`f01`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.00 sec)
mysql> ALTER TABLE test_wo_keys
-> ADD KEY `f06` (`f06`), ADD KEY `f05` (`f05`), ADD KEY `f04` (`f04`), ADD KEY `f23` (`f23`),
-> ADD KEY `f10` (`f10`), ADD KEY `f11` (`f11`), ADD KEY `f09` (`f09`), ADD KEY `f22` (`f22`),
-> ADD KEY `f21` (`f21`), ADD KEY `f07` (`f07`), ADD KEY `f08` (`f08`), ADD KEY `f18` (`f18`),
-> ADD KEY `f19` (`f19`), ADD KEY `f20` (`f20`), ADD KEY `f29` (`f29`,`f31`,`f33`),
-> ADD KEY `f35` (`f35`), ADD KEY `f25` (`f25`), ADD KEY `f26` (`f26`),
-> ADD KEY `f27` (`f27`), ADD KEY `f28` (`f28`);
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
// with --innodb_sort_buffer_size=64M
scripts/mysql_install_db --basedir=/export/umesh/server/binaries/mysql-advanced-5.6.26 --datadir=/export/umesh/server/binaries/mysql-advanced-5.6.26/78262
bin/mysqld --innodb_sort_buffer_size=64M --basedir=/export/umesh/server/binaries/mysql-advanced-5.6.26 --datadir=/export/umesh/server/binaries/mysql-advanced-5.6.26/78262 --core-file --socket=/tmp/mysql_ushastry.sock --port=15000 --log-error=/export/umesh/server/binaries/mysql-advanced-5.6.26/78262/log.err 2>&1 &
mysql> CREATE TABLE `test_wo_keys` (
-> `f01` int AUTO_INCREMENT,
-> `f02` bigint, `f03` bigint, `f04` enum('a','b'),
-> `f05` date, `f06` int, `f07` int, `f08` double, `f09` int,
-> `f10` bigint, `f11` double, `f12` enum('a','b','c','d','e'),
-> `f13` int, `f14` int, `f15` varchar(255), `f16` int, `f17` int, `f18` int,
-> `f19` double, `f20` double, `f21` double, `f22` double, `f23` double, `f24` tinyint,
-> `f25` double, `f26` double, `f27` double, `f28` double, `f29` int unsigned,
-> `f30` int unsigned, `f31` bigint, `f32` int unsigned, `f33` bigint,
-> `f34` int unsigned, `f35` int unsigned,
-> PRIMARY KEY `f01` (`f01`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.00 sec)
mysql> ALTER TABLE test_wo_keys
-> ADD KEY `f06` (`f06`), ADD KEY `f05` (`f05`), ADD KEY `f04` (`f04`), ADD KEY `f23` (`f23`),
-> ADD KEY `f10` (`f10`), ADD KEY `f11` (`f11`), ADD KEY `f09` (`f09`), ADD KEY `f22` (`f22`),
-> ADD KEY `f21` (`f21`), ADD KEY `f07` (`f07`), ADD KEY `f08` (`f08`), ADD KEY `f18` (`f18`),
-> ADD KEY `f19` (`f19`), ADD KEY `f20` (`f20`), ADD KEY `f29` (`f29`,`f31`,`f33`),
-> ADD KEY `f35` (`f35`), ADD KEY `f25` (`f25`), ADD KEY `f26` (`f26`),
-> ADD KEY `f27` (`f27`), ADD KEY `f28` (`f28`);
Query OK, 0 rows affected (10.52 sec)
Records: 0 Duplicates: 0 Warnings: 0
[31 Aug 2015 11:11]
MySQL Verification Team
// 5.7.9 seems to be not affected
// with --innodb_sort_buffer_size=1M
bin/mysql_install_db --insecure --basedir=/export/umesh/server/binaries/mysql-advanced-5.7.9 --datadir=/export/umesh/server/binaries/mysql-advanced-5.7.9/78085 -v
bin/mysqld --innodb_sort_buffer_size=1M --basedir=/export/umesh/server/binaries/mysql-advanced-5.7.9 --datadir=/export/umesh/server/binaries/mysql-advanced-5.7.9/78085 --core-file --socket=/tmp/mysql_ushastry.sock --port=15000 --log-error=/export/umesh/server/binaries/mysql-advanced-5.7.9/78085/log.err 2>&1 &
mysql> use test
Database changed
mysql> CREATE TABLE `test_wo_keys` (
-> `f01` int AUTO_INCREMENT,
-> `f02` bigint, `f03` bigint, `f04` enum('a','b'),
-> `f05` date, `f06` int, `f07` int, `f08` double, `f09` int,
`f13` int, `f14` int, `f15` varchar(255), `f16` int, `f17` int, `f18` int,
-> `f10` bigint, `f11` double, `f12` enum('a','b','c','d','e'),
-> `f13` int, `f14` int, `f15` varchar(255), `f16` int, `f17` int, `f18` int,
-> `f19` double, `f20` double, `f21` double, `f22` double, `f23` double, `f24` tinyint,
`f30` int unsigned, `f31` bigint, `f32` int unsigned, `f33` bigint,
-> `f25` double, `f26` double, `f27` double, `f28` double, `f29` int unsigned,
-> `f30` int unsigned, `f31` bigint, `f32` int unsigned, `f33` bigint,
-> `f34` int unsigned, `f35` int unsigned,
-> PRIMARY KEY `f01` (`f01`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.01 sec)
mysql> ALTER TABLE test_wo_keys
-> ADD KEY `f06` (`f06`), ADD KEY `f05` (`f05`), ADD KEY `f04` (`f04`), ADD KEY `f23` (`f23`),
-> ADD KEY `f10` (`f10`), ADD KEY `f11` (`f11`), ADD KEY `f09` (`f09`), ADD KEY `f22` (`f22`),
-> ADD KEY `f21` (`f21`), ADD KEY `f07` (`f07`), ADD KEY `f08` (`f08`), ADD KEY `f18` (`f18`),
-> ADD KEY `f19` (`f19`), ADD KEY `f20` (`f20`), ADD KEY `f29` (`f29`,`f31`,`f33`),
-> ADD KEY `f35` (`f35`), ADD KEY `f25` (`f25`), ADD KEY `f26` (`f26`),
-> ADD KEY `f27` (`f27`), ADD KEY `f28` (`f28`);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_sort_buffer_size';
+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| innodb_sort_buffer_size | 1048576 |
+-------------------------+---------+
1 row in set (0.00 sec)
// with --innodb_sort_buffer_size=64M
bin/mysql_install_db --insecure --basedir=/export/umesh/server/binaries/mysql-advanced-5.7.9 --datadir=/export/umesh/server/binaries/mysql-advanced-5.7.9/78085 -v
bin/mysqld --innodb_sort_buffer_size=64M --basedir=/export/umesh/server/binaries/mysql-advanced-5.7.9 --datadir=/export/umesh/server/binaries/mysql-advanced-5.7.9/78085 --core-file --socket=/tmp/mysql_ushastry.sock --port=15000 --log-error=/export/umesh/server/binaries/mysql-advanced-5.7.9/78085/log.err 2>&1 &
mysql> use test
Database changed
mysql> CREATE TABLE `test_wo_keys` (
-> `f01` int AUTO_INCREMENT,
-> `f02` bigint, `f03` bigint, `f04` enum('a','b'),
-> `f05` date, `f06` int, `f07` int, `f08` double, `f09` int,
-> `f10` bigint, `f11` double, `f12` enum('a','b','c','d','e'),
-> `f13` int, `f14` int, `f15` varchar(255), `f16` int, `f17` int, `f18` int,
-> `f19` double, `f20` double, `f21` double, `f22` double, `f23` double, `f24` tinyint,
-> `f25` double, `f26` double, `f27` double, `f28` double, `f29` int unsigned,
-> `f30` int unsigned, `f31` bigint, `f32` int unsigned, `f33` bigint,
-> `f34` int unsigned, `f35` int unsigned,
-> PRIMARY KEY `f01` (`f01`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.00 sec)
mysql> ALTER TABLE test_wo_keys
-> ADD KEY `f06` (`f06`), ADD KEY `f05` (`f05`), ADD KEY `f04` (`f04`), ADD KEY `f23` (`f23`),
-> ADD KEY `f10` (`f10`), ADD KEY `f11` (`f11`), ADD KEY `f09` (`f09`), ADD KEY `f22` (`f22`),
-> ADD KEY `f21` (`f21`), ADD KEY `f07` (`f07`), ADD KEY `f08` (`f08`), ADD KEY `f18` (`f18`),
-> ADD KEY `f19` (`f19`), ADD KEY `f20` (`f20`), ADD KEY `f29` (`f29`,`f31`,`f33`),
-> ADD KEY `f35` (`f35`), ADD KEY `f25` (`f25`), ADD KEY `f26` (`f26`),
-> ADD KEY `f27` (`f27`), ADD KEY `f28` (`f28`);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_sort_buffer_size';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| innodb_sort_buffer_size | 67108864 |
+-------------------------+----------+
1 row in set (0.00 sec)
[7 Dec 2015 18:50]
Daniel Price
Posted by developer: ixed as of the upcoming 5.6.29, 5.7.11, 5.8.0 release, and here's the changelog entry: With a large innodb_sort_buffer_size setting, adding an index on an empty table performed more slowly than expected. Thank you for the bug report.
[7 Dec 2015 18:51]
Daniel Price
Posted by developer:
Bug #21762319 ADDING INDEXES ON EMPTY TABLE IS SLOW
WITH LARGE INNODB_SORT_BUFFER_SIZE.
Problem:
=======
Adding index on empty table is slow when innodb_sort_buffer_size
is large.
Fix:
====
Delay the temporary file creation for alter table operation
will lead to avoid the file creation for empty table.
Description: Hi, I am creating a table without indexes and then adding indexes to the table. The table definition is the following: CREATE TABLE `test_wo_keys` ( `f01` int AUTO_INCREMENT, `f02` bigint, `f03` bigint, `f04` enum('a','b'), `f05` date, `f06` int, `f07` int, `f08` double, `f09` int, `f10` bigint, `f11` double, `f12` enum('a','b','c','d','e'), `f13` int, `f14` int, `f15` varchar(255), `f16` int, `f17` int, `f18` int, `f19` double, `f20` double, `f21` double, `f22` double, `f23` double, `f24` tinyint, `f25` double, `f26` double, `f27` double, `f28` double, `f29` int unsigned, `f30` int unsigned, `f31` bigint, `f32` int unsigned, `f33` bigint, `f34` int unsigned, `f35` int unsigned, PRIMARY KEY `f01` (`f01`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; and the indexes are the following: ALTER TABLE test_wo_keys ADD KEY `f06` (`f06`), ADD KEY `f05` (`f05`), ADD KEY `f04` (`f04`), ADD KEY `f23` (`f23`), ADD KEY `f10` (`f10`), ADD KEY `f11` (`f11`), ADD KEY `f09` (`f09`), ADD KEY `f22` (`f22`), ADD KEY `f21` (`f21`), ADD KEY `f07` (`f07`), ADD KEY `f08` (`f08`), ADD KEY `f18` (`f18`), ADD KEY `f19` (`f19`), ADD KEY `f20` (`f20`), ADD KEY `f29` (`f29`,`f31`,`f33`), ADD KEY `f35` (`f35`), ADD KEY `f25` (`f25`), ADD KEY `f26` (`f26`), ADD KEY `f27` (`f27`), ADD KEY `f28` (`f28`); When I create the table and add the indexes without previously inserting data in the table (empty table), it works as expected with "innodb_sort_buffer_size = 1M" but it is slow with "innodb_sort_buffer_size = 64M": > SHOW GLOBAL VARIABLES LIKE 'innodb_sort_buffer_size'; +-------------------------+---------+ | Variable_name | Value | +-------------------------+---------+ | innodb_sort_buffer_size | 1048576 | +-------------------------+---------+ 1 row in set (0.00 sec) > CREATE TABLE `test_wo_keys` ( ... Query OK, 0 rows affected (0.01 sec) > ALTER TABLE test_wo_keys ... Query OK, 0 rows affected (0.07 sec) Records: 0 Duplicates: 0 Warnings: 0 > SHOW GLOBAL VARIABLES LIKE 'innodb_sort_buffer_size'; +-------------------------+----------+ | Variable_name | Value | +-------------------------+----------+ | innodb_sort_buffer_size | 67108864 | +-------------------------+----------+ 1 row in set (0.00 sec) > CREATE TABLE `test_wo_keys` ( ... Query OK, 0 rows affected (0.00 sec) > ALTER TABLE test_wo_keys ... Query OK, 0 rows affected (6.21 sec) Records: 0 Duplicates: 0 Warnings: 0 This is unexpected and should probably be optimized. Thanks, JFG How to repeat: See description. Suggested fix: Monitoring during the index creation showed many write IOs to the filesystem hosting the temporary files (tmpdir = ../mysql_tmp which I am mounting on a different block device). Code diving point into the direction of storage/innobase/row/row0merge.cc where it looks like row_merge_write puts the full buffer to disk (64 MB), even it it is mostly empty. With creating 20 indexes, this means that 1280 MB are written to disk. I am even surprised the buffers are hitting the disk (most of them should fit in the OS cache, and I have tens of GB of RAM on that server). It looks like "posix_fadvise(..., POSIX_FADV_DONTNEED)" generates an early flush from the operating system, even if there is no memory pressure. Monitoring does not show any reads to the block device, so it looks like the writes are really not needed.