Bug #98546 Transient indexes statistics are updated in foreground causing performance issue
Submitted: 11 Feb 2020 6:38 Modified: 12 Feb 2020 15:13
Reporter: Charly Batista Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: InnoDB storage engine Severity:S5 (Performance)
Version:8, 5.7 OS:Any
Assigned to: CPU Architecture:Any

[11 Feb 2020 6:38] Charly Batista
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)) {
      table->stat_modified_counter = 0;

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% */) {
    /* 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_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; 

Suggested fix:
Make the transient statistics use the same path as persistent statistics when it needs to be recalculated, use a background thread.
[12 Feb 2020 15:13] MySQL Verification Team
Hi Mr. Batista,

Thank you for your bug report.

I have checked analysis and I agree with you. I have tested the benchmark with 5.7 and I can see what you mean.

However, this is performance bug.

Verified .........