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

Bug #1690588 reported by jocelyn fournier on 2017-05-13
8
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Percona Server moved to https://jira.percona.com/projects/PS
New
Undecided
Unassigned

Bug Description

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

tags: added: performance regression upstream

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) wrote :

Can you please specify the buffer pool size used?

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers