ALTER TABLE is slow in 5.7 when the number of dirty pages is high

[jocelyn fournier]

Hi,

5.7 is much slower than 5.6 to execute ALTER TABLE ... ADD INDEX, even on an empty table when the number of dirty pages in the buffer pool is high.

In 5.6, with 1.2m dirty pages (6.6% of the buffer pool):

CREATE TABLE `data_3468769` (
    -> `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
    -> `extid` varchar(256) COLLATE latin1_general_ci NOT NULL DEFAULT '',
    -> `pra` varchar(60) CHARACTER SET latin1 NOT NULL,
    -> `from` varchar(60) CHARACTER SET latin1 NOT NULL,
    -> `rcpt` varchar(60) CHARACTER SET latin1 NOT NULL,
    -> `domaine` varchar(50) CHARACTER SET latin1 NOT NULL,
    -> `etat` enum('','done','filtre','soft','hard') CHARACTER SET latin1 NOT NULL DEFAULT '',
    -> `smtpmsg` tinytext CHARACTER SET latin1 NOT NULL,
    -> `ouvert` tinyint(3) unsigned NOT NULL,
    -> `clicks` tinyint(3) unsigned NOT NULL,
    -> `sales_count` tinyint(3) unsigned NOT NULL DEFAULT '0',
    -> `sales_amount` decimal(7,2) NOT NULL DEFAULT '0.00',
    -> `desabo` tinyint(3) unsigned NOT NULL,
    -> `spam` tinyint(3) unsigned NOT NULL,
    -> PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
Query OK, 0 rows affected (0.15 sec)

mysql> ALTER TABLE `data_3468769` ADD INDEX (etat), ADD INDEX (domaine);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

In 5.7, with 1.4m dirty pages (8% of the buffer pool):
ALTER TABLE `data_3468769` ADD INDEX (etat), ADD INDEX (domaine);
Query OK, 0 rows affected (1.38 sec)

With about 4.2m dirty pages:
ALTER TABLE `data_3468769` ADD INDEX (etat), ADD INDEX (domaine);
Query OK, 0 rows affected (5.45 sec)

It seems the slowdown is cause by the call to FlushObserver::flush in row_merge_build_indexes, which count the number of dirty pages (buf_pool_get_dirty_pages_count). It scans the whole flush_list for each buffer pool searching the right space id, which is quite inefficient here!

The number of dirty pages seems to be only used by begin_phase_flush to estimate the amount of work, perhaps it would be faster to use the number of records of the table, instead of trying to compute the real number of pending dirty pages?

Thanks,
  Jocelyn

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-3702

[Satya Bodapati (satya-bodapati)]

Can you please specify the buffer pool size used?