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