Bug #98546 Transient indexes statistics are updated in foreground causing performance issue
Description:
The persistent statistic is recalculated in a background thread while the transient is recalculated in the foreground thread. The diverging point is the function row_update_statistics_if_needed(dict_table_t *)[1].
As we can see there it checks if the table has the persistent statistics enabled. If the table has the persistent statistics enabled and has 10% of the rows changed it will add the table to the recalc pool, which is processed by the background stats gathering thread[2]:
```
if (dict_stats_is_persistent_enabled(table)) {
if (counter > n_rows / 10 /* 10% */
&& dict_stats_auto_recalc_is_enabled(table)) {
dict_stats_recalc_pool_add(table);
table->stat_modified_counter = 0;
}
return;
}
```
However, if the table doesn't have persistent statistics enabled (transient stats) it will check if 1/16 rows of the table have been changed and if has will call the dict_stats_update function in the foreground:
```
if (counter > 16 + n_rows / 16 /* 6.25% */) {
ut_ad(!mutex_own(&dict_sys->mutex));
/* this will reset table->stat_modified_counter to 0 */
dict_stats_update(table, DICT_STATS_RECALC_TRANSIENT);
}
```
It not only degrades performance while recalculating stats in the foreground thread but also holds a mutex making the whole process more expensive[3]:
```
dict_table_stats_lock(table, RW_X_LATCH);
dict_stats_update_transient(table);
dict_table_stats_unlock(table, RW_X_LATCH);
```
[1] https://github.com/mysql/mysql-server/blob/8.0/storage/innobase/row/row0mysql.cc#L1100
[2] https://github.com/mysql/mysql-server/blob/8.0/storage/innobase/dict/dict0stats_bg.cc#L118
[3] https://github.com/mysql/mysql-server/blob/8.0/storage/innobase/dict/dict0stats.cc#L2831-L...
How to repeat:
The below sysbench run will eventually cause the statistics to be recalculated and if test with persistent statistics enabled and disabled can see the performance degradation:
sysbench --mysql-host=`hostname` --mysql-user=sbtest --mysql-password=sbtest --tables=10 --table-size=4000000 --max-requests=0 --time=900 --threads=128 --db-driver=mysql --db-ps-mode=disable oltp_update_index prepare
while true; do
sysbench --mysql-host=`hostname` --mysql-user=sbtest --mysql-password=sbtest --tables=10 --table-size=4000000 --max-requests=0 --time=900 --threads=128 --db-driver=mysql --db-ps-mode=disable oltp_update_index run;
sleep 2;
done
Suggested fix:
Make the transient statistics use the same path as persistent statistics when it needs to be recalculated, use a background thread.
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.048 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
Description: The persistent statistic is recalculated in a background thread while the transient is recalculated in the foreground thread. The diverging point is the function row_update_statistics_if_needed(dict_table_t *)[1]. As we can see there it checks if the table has the persistent statistics enabled. If the table has the persistent statistics enabled and has 10% of the rows changed it will add the table to the recalc pool, which is processed by the background stats gathering thread[2]: ``` if (dict_stats_is_persistent_enabled(table)) { if (counter > n_rows / 10 /* 10% */ && dict_stats_auto_recalc_is_enabled(table)) { dict_stats_recalc_pool_add(table); table->stat_modified_counter = 0; } return; } ``` However, if the table doesn't have persistent statistics enabled (transient stats) it will check if 1/16 rows of the table have been changed and if has will call the dict_stats_update function in the foreground: ``` if (counter > 16 + n_rows / 16 /* 6.25% */) { ut_ad(!mutex_own(&dict_sys->mutex)); /* this will reset table->stat_modified_counter to 0 */ dict_stats_update(table, DICT_STATS_RECALC_TRANSIENT); } ``` It not only degrades performance while recalculating stats in the foreground thread but also holds a mutex making the whole process more expensive[3]: ``` dict_table_stats_lock(table, RW_X_LATCH); dict_stats_update_transient(table); dict_table_stats_unlock(table, RW_X_LATCH); ``` [1] https://github.com/mysql/mysql-server/blob/8.0/storage/innobase/row/row0mysql.cc#L1100 [2] https://github.com/mysql/mysql-server/blob/8.0/storage/innobase/dict/dict0stats_bg.cc#L118 [3] https://github.com/mysql/mysql-server/blob/8.0/storage/innobase/dict/dict0stats.cc#L2831-L... How to repeat: The below sysbench run will eventually cause the statistics to be recalculated and if test with persistent statistics enabled and disabled can see the performance degradation: sysbench --mysql-host=`hostname` --mysql-user=sbtest --mysql-password=sbtest --tables=10 --table-size=4000000 --max-requests=0 --time=900 --threads=128 --db-driver=mysql --db-ps-mode=disable oltp_update_index prepare while true; do sysbench --mysql-host=`hostname` --mysql-user=sbtest --mysql-password=sbtest --tables=10 --table-size=4000000 --max-requests=0 --time=900 --threads=128 --db-driver=mysql --db-ps-mode=disable oltp_update_index run; sleep 2; done Suggested fix: Make the transient statistics use the same path as persistent statistics when it needs to be recalculated, use a background thread.