Bug #91541 | "Flush status" statement adds twice to global values | ||
---|---|---|---|
Submitted: | 3 Jul 2018 19:53 | Modified: | 30 Jan 2019 14:43 |
Reporter: | Carlos Tutte | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Information schema | Severity: | S3 (Non-critical) |
Version: | 5.7.22, 8.0.11 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[3 Jul 2018 19:53]
Carlos Tutte
[4 Jul 2018 9:53]
MySQL Verification Team
Hello Carlos, Thank you for the report. Thanks, Umesh
[26 Jul 2018 11:44]
Yura Sorokin
an MTR test case which simulates the problem (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.
Contribution: bug91541.test (application/octet-stream, text), 3.16 KiB.
[26 Jul 2018 11:47]
Yura Sorokin
Here is a simplified scenario: 1. A new connection 'con1' is opened. "SHOW GLOBAL STATUS LIKE 'Slow_queries'" is executed and returns 0. 2. Another connection 'con2' is opened and a very slow query like "SELECT SLEEP(10)" is executed. After that both "SHOW GLOBAL STATUS LIKE 'Slow_queries'" and "SHOW SESSION STATUS LIKE 'Slow_queries'" (for 'con2') return 1. 3. 'con2' is closed. 4. In 'con1' "SHOW GLOBAL STATUS LIKE 'Slow_queries'" is executed and returns 1 as expected. Moreover "SHOW SESSION STATUS LIKE 'Slow_queries'" also returns expected value 0. 5. "FLUSH STATUS" is executed in 'con1' and "SHOW GLOBAL STATUS LIKE 'Slow_queries'" now returns 2.
[27 Jul 2018 12:54]
Yura Sorokin
This happens because when a connection is closed 'THD::release_resources()' method is called which in turn calls "add_to_status(&global_status_var, &status_var, false);". The last parameter 'false' indicates that session values must not be reset after aggregation to global ones. The comment in the code says that the status values are not reset so that they can still be read by performance schema. On the other hand, when "FLUSH STATUS" is executed, 'refresh_status()' function is called which in turn calls 'reset_pfs_status_stats()'. This function propagates all PFS stats accumulated on different levels: PFS_thread -> PFS_account -> PFS_user -> PFS_host -> Global status vars. However, it does not take into account that some values have already been added to global status vars when other connections were closed. Fix: 'PFS_status_stats' class extended with additional set of counters 'm_stats_aggregated_in_global' that will be used similarly to 'm_stats' to accumulate values that have already been added to global status vars. 'PFS_status_stats::aggregate_from()' method is extended with additional boolean parameter 'already_aggregated' which determines to which array ('m_stats' or 'm_stats_aggregated_in_global') the passed values must be accumulated. This method is now called with 'thd->status_var_aggregated' passed as this boolean parameter. On the other hand 'PFS_status_stats::aggregate_to()' methods is also extended with additional boolean parameter 'include_aggregated' indicating whether stats already aggregated in global status vars should be added to the output object. This allows to eliminate double aggregation when this methos is called for the global status vars "xxx.aggregate_to(&global_status_var, false);". For other local calculations it is called with 'true' "xxx.aggregate_to(&yyy, true);".
[27 Jul 2018 12:55]
Yura Sorokin
Suggested fix for the 5.7 branch (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.
Contribution: bug91541_5_7.diff (application/octet-stream, text), 10.31 KiB.
[27 Jul 2018 12:55]
Yura Sorokin
Suggested fix for the 8.0 branch (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.
Contribution: bug91541_8_0.diff (application/octet-stream, text), 11.63 KiB.
[30 Jan 2019 14:43]
Paul DuBois
Posted by developer: Fixed in 5.7.26, 8.0.16. During FLUSH STATUS execution, the Performance Schema unnecessarily aggregated session status to global status, causing double counts for some status variables.