MySQL 8.0 Reference Manual(读书笔记74节--Spin Lock Polling & Purge Configuration)

这篇具有很好参考价值的文章主要介绍了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:


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


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:


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.




到了这里,关于MySQL 8.0 Reference Manual(读书笔记74节--Spin Lock Polling & Purge Configuration)的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!


本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用


  • MySQL 8.0 Reference Manual(读书笔记36节-- 字符编码(3))

    Regardless of how you configure the MySQL character set for application use, you must also consider the environment within which those applications execute. For example, if you intend to send statements using UTF-8 text taken from a file that you create in an editor, you should edit the file with the locale of your environment set to UTF-8 so that the file e

  • MySQL 8.0 Reference Manual(读书笔记19节-- 日期与计算)

    为什么设计的时候,存放的是出生日期而不是年龄呢?这个问题简单,细想很有意思,也包含着智慧,来自生产生活的思考。下面的解释很到位。 How about age? That might be of interest, but it is not a good thing to store in a database. Age changes as time passes, which means you\\\'d have to update your records

  • MySQL 8.0 Reference Manual(读书笔记34节-- 字符编码(1))

    MySQL includes character【ˈkærəktər 字母,符号;】 set support that enables you to store data using a variety【vəˈraɪəti (同一事物的)不同种类,多种式样; 变化; (植物、语言等的)变种,变体; 多样化; 综艺节目; 品种; 多变性; 异体; 】 of character sets and perform comparisons【kəmˈpɛrəsənz 比较; 对比

  • MySQL 8.0 Reference Manual(读书笔记67节--Phantom Rows)

    The so-called phantom problem occurs within a transaction when the same query produces different sets of rows at different times. For example, if a SELECT is executed twice, but returns a row the second time that was not returned the first time, the row is a “phantom” row. Suppose that there is an index on the id column of the child table and that you wa

  • MySQL 8.0 Reference Manual(读书笔记40节-- Data Types(2))

    The string data types are CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM, and SET. In some cases, MySQL may change a string column to a type different from that given in a CREATE TABLE or ALTER TABLE statement.  For definitions of character string columns (CHAR, VARCHAR, and the TEXT types), MySQL interprets【ɪnˈtɜːrprəts 诠释; 说明; 把…理解

  • MySQL 8.0 Reference Manual(读书笔记80节-- InnoDB Row Formats)

    The row format of a table determines how its rows are physically stored, which in turn can affect the performance of queries and DML operations. As more rows fit into a single disk page, queries and index lookups can work faster, less cache memory is required in the buffer pool, and less I/O is required to write out updated values. The data in each table is

  • MySQL 8.0 Reference Manual(读书笔记69节--InnoDB Startup Configuration)

    有些InnoDB的配置,是在实例初始化时,就决定了,所以,建议写在configuration file 文件中。 Because MySQL uses data file, log file, and page size settings to initialize InnoDB, it is recommended that you define these settings in an option file that MySQL reads at startup, prior to initializing InnoDB. Normally, InnoDB is initialize

  • MySQL 8.0 Reference Manual(读书笔记70节--InnoDB Buffer Pool Configuration)

    When increasing or decreasing innodb_buffer_pool_size , the operation is performed in chunks. Chunk size is defined by the innodb_buffer_pool_chunk_size configuration option, which has a default of 128M.-----innodb_buffer_pool_size的扩容和缩容,都是以innodb_buffer_pool_chunk_size为单位进行的,其默认为128M 。 Buffer pool size must always

  • MySQL 8.0 Reference Manual(读书笔记20节-- NULL+模式匹配+外键+自增属性)

    The NULL value can be surprising until you get used to it. Conceptually【kənˈsɛptʃuəli 概念; 观念上; 概念上; 在概念上; 概念地; 】, NULL means “a missing unknown value” and it is treated somewhat differently from other values. To test for NULL, use the IS NULL and IS NOT NULL operators, as shown here:  You cannot use arithmetic comp

  • MySQL 8.0 Reference Manual(读书笔记83节-- InnoDB and Online DDL (3))

    The following table provides an overview of online DDL support for foreign key operations. An asterisk【ˈæstərɪsk 星号(置于词语旁以引起注意或另有注释);】 indicates additional information, an exception, or a dependency. Operation Instant In Place Rebuilds Table Permits Concurrent DML Only Modifies Metadata Adding a foreign key constrain