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