Uploaded image for project: 'Percona Server'
  1. Percona Server
  2. PS-3702

LP #1690588: ALTER TABLE is slow in 5.7 when the number of dirty pages is high

    XMLWordPrintable

    Details

      Description

      **Reported in Launchpad by jocelyn fournier last update 25-05-2017 08:07:39

      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

        Attachments

          Activity

            People

            • Assignee:
              satya.bodapati Satya Bodapati
              Reporter:
              lpjirasync lpjirasync (Inactive)
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Time Tracking

                Estimated:
                Original Estimate - Not Specified
                Not Specified
                Remaining:
                Remaining Estimate - 0 minutes
                0m
                Logged:
                Time Spent - 1 hour, 2 minutes
                1h 2m