WL#5889: Add InnoDB events to Performance Schema's Event Stage table
Status: Complete — Priority: Medium
Starting MySQL 5.6.3, the Performance Schema now instruments stages. See http://dev.mysql.com/doc/refman/5.7/en/performance-schema-stage-tables.html Some tasks inside InnoDB take significant amount of time (more than a few minutes) and users need a way to check the progress of such a task. For example if somebody has waited 3 hours for an ALTER TABLE command to complete, he wants to know if it will complete in 10 minutes or in 7 hours. To solve this problem, this worklog will introduce InnoDB stages in the performance_schema.events_stages_* tables that are named like 'stage/innodb/%'. This WL will not cover all possible areas to stage-instrument in InnoDB, it will instrument some of the important ones and will pave the way for such instrumentation to be added in the future when new features are implemented.
Functional requirements: F-1: New events must appear in performance_schema.events_stages_* tables that would allow the users to monitor the progress of some long running tasks. Non-Functional requirements: NF-1: The added instrumentation must not impact the performance of the instrumented tasks more than the usual 1-2% for PFS overhead. Ideally the overhead would be none or <1%.
This is a list of the stage events to be added by this WL: * 'stage/innodb/buffer pool load' This will indicate the progress of a buffer pool load operation (e.g. when "SET GLOBAL innodb_buffer_pool_load_now=ON;" is executed). The buffer pool load reads innodb-buffer-pool-size bytes from disk, trying to use sequential IO, but it may end up with a mixture of sequential and random IO. So with big buffer pools this operation could take more than a few minutes. The units of the columns work_completed and work_estimated in performance_schema.events_stages_* are 'pages'. The event is active in performance_schema.events_stages_current while the buffer pool load operation is ongoing. * 'stage/innodb/alter table'
Copyright (c) 2000, 2019, Oracle Corporation and/or its affiliates. All rights reserved.