WL#9513: Introduce new sys variable to include estimates for delete marked records

Affects: Server-8.0   —   Status: Complete   —   Priority: Medium


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.


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 

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 

FN -4: This variable can be dynamically changed using "SET GLOBAL 

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 
        Calculation of non-persistent statistics is not   influenced by this 
configuration variable

Non Functionaal Requirements

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 
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.