MySQL 8.0 Reference Manual（读书笔记74节–Spin Lock Polling & Purge Configuration）

Configuring Spin Lock Polling

InnoDB mutexes and rw-locks are typically reserved for short intervals. On a multi-core system, it can be more efficient for a thread to continuously【kənˈtɪnjuəsli 连续不断地】 check if it can acquire a mutex or rw-lock for a period of time before it sleeps. If the mutex or rw-lock becomes available during this period, the thread can continue immediately, in the same time slice【slaɪs 薄片;部分;份额;】. However, too-frequent polling【ˈpoʊlɪŋ 获得(票数);对…进行民意调查】 of a shared object such as a mutex or rw-lock by multiple threads can cause “cache ping pong”【pɑːŋ 臭味;恶臭;强烈难闻的气味】, which results in processors invalidating【ɪnˈvælɪdeɪtɪŋ 使无效;使作废; 证明…错误】 portions of each other’s cache. InnoDB minimizes this issue by forcing a random delay between polls to desynchronize【使不同步;使去同步，使失同步】 polling activity. The random delay is implemented as a spin-wait loop.

The duration of a spin-wait loop is determined by the number of PAUSE instructions that occur in the loop. That number is generated by randomly selecting an integer ranging from 0 up to but not including the innodb_spin_wait_delay value, and multiplying【mʌltɪplaɪɪŋ 乘以;乘;成倍增加;迅速增加】 that value by 50. (The multiplier value, 50, is hardcoded before MySQL 8.0.16, and configurable thereafter.) For example, an integer is randomly selected from the following range for an innodb_spin_wait_delay setting of 6:

{0,1,2,3,4,5}

The selected integer is multiplied by 50, resulting in one of six possible PAUSE instruction【ɪnˈstrʌkʃn 用法说明;指示;命令;操作指南;吩咐;】 values:

{0,50,100,150,200,250}

For that set of values, 250 is the maximum number of PAUSE instructions that can occur in a spin-wait loop. An innodb_spin_wait_delay setting of 5 results in a set of five possible values {0,50,100,150,200}, where 200 is the maximum number of PAUSE instructions, and so on. In this way, the innodb_spin_wait_delay setting controls the maximum delay between spin lock polls.

On a system where all processor cores share a fast cache memory, you might reduce the maximum delay or disable the busy loop altogether by setting innodb_spin_wait_delay=0. On a system with multiple processor chips, the effect of cache invalidation can be more significant and you might increase the maximum delay.

In the 100MHz Pentium era, an innodb_spin_wait_delay unit was calibrated to be equivalent to one microsecond. That time equivalence did not hold, but PAUSE instruction duration remained fairly constant in terms of processor cycles relative to other CPU instructions until the introduction of the Skylake generation of processors, which have a comparatively longer PAUSE instruction. The innodb_spin_wait_pause_multiplier variable was introduced in MySQL 8.0.16 to provide a way to account for differences in PAUSE instruction duration.

The innodb_spin_wait_pause_multiplier variable controls the size of PAUSE instruction values. For example, assuming an innodb_spin_wait_delay setting of 6, decreasing the innodb_spin_wait_pause_multiplier value from 50 (the default and previously hardcoded value) to 5 generates a set of smaller PAUSE instruction values:

{0,5,10,15,20,25}

The ability to increase or decrease PAUSE instruction values permits fine tuning InnoDB for different processor architectures. Smaller PAUSE instruction values would be appropriate for processor architectures with a comparatively longer PAUSE instruction, for example.

The innodb_spin_wait_delay and innodb_spin_wait_pause_multiplier variables are dynamic. They can be specified in a MySQL option file or modified at runtime using a SET GLOBAL statement. Modifying the variables at runtime requires privileges sufficient to set global system variables.

Purge Configuration

InnoDB does not physically remove a row from the database immediately when you delete it with an SQL statement. A row and its index records are only physically removed when InnoDB discards the undo log record written for the deletion. This removal operation, which only occurs after the row is no longer required for multi-version concurrency control (MVCC) or rollback, is called a purge. Purge runs on a periodic schedule. It parses and processes undo log pages from the history list, which is a list of undo log pages for committed transactions that is maintained by the InnoDB transaction system. Purge frees the undo log pages from the history list after processing them.

Configuring Purge Threads

Purge operations are performed in the background by one or more purge threads. The number of purge threads is controlled by the innodb_purge_threads variable. The default value is 4.—-留意，默认值是4

If DML action is concentrated on a single table, purge operations for the table are performed by a single purge thread, which can result in slowed purge operations, increased purge lag, and increased tablespace file size if the DML operations involve large object values. From MySQL 8.0.26, if the innodb_max_purge_lag setting is exceeded, purge work is automatically redistributed among available purge threads. Too many active purge threads in this scenario can cause contention with user threads, so manage the innodb_purge_threads setting accordingly. The innodb_max_purge_lag variable is set to 0 by default, which means that there is no maximum purge lag by default.

If DML action is concentrated on few tables, keep the innodb_purge_threads setting low so that the threads do not contend with each other for access to the busy tables. If DML operations are spread across many tables, consider a higher innodb_purge_threads setting. The maximum number of purge threads is 32.

The innodb_purge_threads setting is the maximum number of purge threads permitted. The purge system automatically adjusts the number of purge threads that are used.

Configuring Purge Batch Size

The innodb_purge_batch_size variable defines the number of undo log pages that purge parses and processes in one batch from the history list. The default value is 300. In a multithreaded purge configuration, the coordinator purge thread divides innodb_purge_batch_size by innodb_purge_threads and assigns that number of pages to each purge thread.

The purge system also frees the undo log pages that are no longer required. It does so every 128 iterations【迭代;次数;迭代次数;叠代;重复进行】 through the undo logs. In addition to defining the number of undo log pages parsed and processed in a batch, the innodb_purge_batch_size variable defines the number of undo log pages that purge frees every 128 iterations through the undo logs.

The innodb_purge_batch_size variable is intended for advanced performance tuning and experimentation. Most users need not change innodb_purge_batch_size from its default value.

Configuring the Maximum Purge Lag【læɡ 滞后;落后于;发展缓慢;缓慢移动;】

 The innodb_max_purge_lag variable defines the desired【dɪˈzaɪərd 期望得到的;希望实现的】 maximum purge lag. When the purge lag exceeds【ɪkˈsiːdz 超过；超越】 the innodb_max_purge_lag threshold, a delay is imposed【ɪmˈpoʊzd 推行，采用；迫使;强制实行;】 on INSERT, UPDATE, and DELETE operations to allow time for purge operations to catch up. The default value is 0, which means there is no maximum purge lag and no delay.

The InnoDB transaction system maintains a list of transactions that have index records delete-marked by UPDATE or DELETE operations. The length of the list is the purge lag. Prior to MySQL 8.0.14, the purge lag delay is calculated by the following formula, which results in a minimum delay of 5000 microseconds:

(purge lag/innodb_max_purge_lag - 0.5) * 10000

As of MySQL 8.0.14, the purge lag delay is calculated by the following revised formula, which reduces the minimum delay to 5 microseconds. A delay of 5 microseconds is more appropriate for modern systems.

(purge_lag/innodb_max_purge_lag - 0.9995) * 10000

The delay is calculated at the beginning of a purge batch.

A typical innodb_max_purge_lag setting for a problematic workload might be 1000000 (1 million), assuming that transactions are small, only 100 bytes in size, and it is permissible to have 100MB of unpurged table rows.

The purge lag is presented as the History list length value in the TRANSACTIONS section of SHOW ENGINE INNODB STATUS output.

The History list length is typically a low value, usually less than a few thousand, but a write-heavy workload or long running transactions can cause it to increase, even for transactions that are read only. The reason that a long running transaction can cause the History list length to increase is that under a consistent read transaction isolation level such as REPEATABLE READ, a transaction must return the same result as when the read view for that transaction was created. Consequently【kɑːnsɪkwentli 因此;所以】, the InnoDB multi-version concurrency control (MVCC) system must keep a copy of the data in the undo log until all transactions that depend on that data have completed. The following are examples of long running transactions that could cause the History list length to increase:

• A mysqldump operation that uses the –single-transaction option while there is a significant【sɪɡˈnɪfɪkənt 重要的, 有重大意义的;显著的, 值得注意的;<统>显著的, 有效的; 相当数量的;】 amount of concurrent DML.

• Running a SELECT query after disabling autocommit, and forgetting to issue an explicit COMMIT or ROLLBACK.

To prevent excessive【ɪkˈsesɪv 过度的;过分的】 delays in extreme【ɪkˈstriːm 极端的;极度的;严重的;极大的;偏激的;过分的;严厉的;异乎寻常的;远离中心的】 situations where the purge lag becomes huge【hjuːdʒ 巨大的;非常成功的;极多的;走红的;程度高的】, you can limit the delay by setting the innodb_max_purge_lag_delay variable. The innodb_max_purge_lag_delay variable specifies the maximum delay in microseconds for the delay imposed when the innodb_max_purge_lag threshold is exceeded. The specified innodb_max_purge_lag_delay value is an upper limit on the delay period calculated by the innodb_max_purge_lag formula.

Purge and Undo Tablespace Truncation

The purge system is also responsible【rɪˈspɑːnsəbl 负责的;有责任;承担责任;责任重大的;承担义务;可信任的;作为原因;应受责备;成为起因】 for truncating undo tablespaces. You can configure the innodb_purge_rseg_truncate_frequency variable to control the frequency【ˈfriːkwənsi 频率;频繁;发生率;出现率;重复率】 with which the purge system looks for undo tablespaces to truncate.

—https://dev.mysql.com/doc/refman/8.0/en/innodb-performance-spin_lock_polling.html

—https://dev.mysql.com/doc/refman/8.0/en/innodb-purge-configuration.html