WL#9513: Introduce new sys variable to include estimates for delete marked records
Affects: Server-8.0 — Status: Complete
PROBLEM By design stats estimation always reads uncommitted data. In this scenario an uncommitted transaction has deleted all rows in the table. In Innodb uncommitted delete records are marked as deleted but not actually removed from Btree until the transaction has committed or a read view for the rows is present. While calculating persistent stats we were ignoring the delete marked records, since all the records are delete marked we were estimating the number of rows present in the table as zero which leads to bad query plans for other transactions operating on the table. Fix Introduce a system variable called innodb_stats_include_delete_marked which when enabled includes delete marked records in calculation of table and index statistics.
Functional requirements: FN -1 A new configuration variable named "innodb_stats_include_delete_marked" is added. FN -2: When this system variable is enabled, persistent statistics should include delete marked records when calculating row estimates and index statistics. FN -3: This variable is global wide and effects all tables stored in Innodb engine. FN -4: This variable can be dynamically changed using "SET GLOBAL innodb_stats_include_delete_marked=ON/OFF" FN -5: Setting this variable will also cause ANALYZE TABLE to calculate the stats considering the delete marked records. FN -6: Valid values (0, 1, ON, OFF) default is OFF. FN -7 : This configuration variable will only impact how persistent statistics is calculated. Calculation of non-persistent statistics is not influenced by this configuration variable Non Functionaal Requirements Nil
Persistent stats works on the principle of estimating the rows in table of Btree by scanning just a few leaf sample pages. When a record is deleted it is not immediately removed from the Btree but it is just marked as delete marked. The purge later takes care of deleting them. If a read view exists or the transaction deleting the records has not yet committed, these deleted records are still present in the Btree and the optimizer should know the approx number of tuples for calculating the plan for other transactions which still see the table rows. So we are including these delete marked records in our calculation of persistent table and index statistics when the innodb_stats_include_delete_marked option is enabled.
Copyright (c) 2000, 2023, Oracle Corporation and/or its affiliates. All rights reserved.