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, 2025, Oracle Corporation and/or its affiliates. All rights reserved.