[MDEV-11261] InnoDB updated rec_per_key[] statistics is not published to the optimizer enough often Created: 2016-11-09 Updated: 2023-04-27 |
|
| Status: | Open |
| Project: | MariaDB Server |
| Component/s: | Optimizer, Storage Engine - InnoDB |
| Affects Version/s: | 10.0, 10.1, 10.2, 10.3, 10.4, 10.5 |
| Fix Version/s: | 10.4, 10.5 |
| Type: | Bug | Priority: | Major |
| Reporter: | Valerii Kravchuk | Assignee: | Sergei Petrunia |
| Resolution: | Unresolved | Votes: | 1 |
| Labels: | upstream | ||
| Description |
|
Please, check upstream http://bugs.mysql.com/bug.php?id=70629. Based on the last comment from Vasil Dimov there: "The problem here is that InnoDB's statistics rec_per_key[] (or cardinality or n_diff[]) are published to the optimizer when ha_innobase::info(HA_STATUS_CONST) is called and it is called only when a table object is created (usually only when the table is opened). So when InnoDB recalculates the statistics due to subsequent DML, the old stats are being used to calculate the execution plan. We will do some experiments and will probably call ha_innobase::info(HA_STATUS_CONST) at the beginning of each query." I am not sure if calling ha_innobase::info(HA_STATUS_CONST) before every statement is a good idea, but it makes sense to check why it is not called (as it seems) when 10% of table data are changed and automatic recalculation by background thread happens. |
| Comments |
| Comment by Sergei Petrunia [ 2016-12-13 ] |
|
If anybody is going to change the way statistics are updated, please keep in mind MDEV-10790. Current code has race conditions. |
| Comment by Marko Mäkelä [ 2020-07-16 ] |
|
I think that fixing this requires changes outside InnoDB, to request for updated statistics. Possibly we would need some event-based API that lets the storage engine notify the optimizer of statistics changes. Or we could implement some check in ha_innobase::open(). I would like to hear some insight from psergey. |