MySQL 8.0 Reference Manual（讀書筆記57節--Optimizing for InnoDB Tables）

InnoDB is the storage engine that MySQL customers typically use in production databases where reliability【rɪˌlaɪə'bɪləti 可靠性;】 and concurrency【併發;】 are important. InnoDB is the default storage engine in MySQL. This section explains how to optimize database operations for InnoDB tables.

1 Optimizing Storage Layout for InnoDB Tables

• Once your data reaches a stable【ˈsteɪbl 穩固的;(化學狀態或原子狀態)穩定的;牢固的;穩重的;沉穩的;持重的;】 size, or a growing table has increased by tens or some hundreds of megabytes, consider using the OPTIMIZE TABLE statement to reorganize the table and compact any wasted space. The reorganized tables require less disk I/O to perform full table scans. This is a straightforward【ˌstreɪtˈfɔːrwərd 直截了當的;簡單的;坦率的;易懂的;率直的;坦誠的;不複雜的;】 technique that can improve performance when other techniques such as improving index usage or tuning application code are not practical.

OPTIMIZE TABLE copies the data part of the table and rebuilds the indexes. The benefits come from improved packing of data within indexes, and reduced fragmentation【ˌfræɡmenˈteɪʃn 碎片;碎裂（化），片段，摘錄，（原子核）爆炸，分區輸入程式，殺傷（炸彈），破片散飛;】 within the tablespaces and on disk. The benefits vary depending on the data in each table. You may find that there are significant gains for some and not for others, or that the gains decrease over time until you next optimize the table. This operation can be slow if the table is large or if the indexes being rebuilt do not fit into the buffer pool. The first run after adding a lot of data to a table is often much slower than later runs.

• In InnoDB, having a long PRIMARY KEY (either a single column with a lengthy value, or several columns that form a long composite value) wastes a lot of disk space. The primary key value for a row is duplicated in all the secondary index records that point to the same row. Create an AUTO_INCREMENT column as the primary key if your primary key is long, or index a prefix of a long VARCHAR column instead of the entire column.

• Use the VARCHAR data type instead of CHAR to store variable-length strings or for columns with many NULL values. A CHAR(N) column always takes N characters to store data, even if the string is shorter or its value is NULL. Smaller tables fit better in the buffer pool and reduce disk I/O.

When using COMPACT row format (the default InnoDB format) and variable-length character sets, such as utf8mb4 or sjis, CHAR(N) columns occupy a variable amount of space, but still at least N bytes.

• For tables that are big, or contain lots of repetitive【rɪˈpetətɪv 重覆的;重覆乏味的;】 text or numeric data, consider using COMPRESSED row format. Less disk I/O is required to bring data into the buffer pool, or to perform full table scans. Before making a permanent decision, measure the amount of compression you can achieve by using COMPRESSED versus COMPACT row format.

2 Optimizing InnoDB Transaction Management

To optimize InnoDB transaction processing, find the ideal balance between the performance overhead【ˌoʊvərˈhed , ˈoʊvərhed 開銷;經常費用;經常開支;（尤指飛機的）頂艙;用於高射投影器的幻燈片;】 of transactional features and the workload of your server. For example, an application might encounter performance issues if it commits thousands of times per second, and different performance issues if it commits only every 2-3 hours.

• The default MySQL setting AUTOCOMMIT=1 can impose【ɪmˈpoʊz 把…強加於;使接受，使意識到;推行，採用(規章制度);迫使;強制實行;勉強（某人做某事）;使(別人)接受自己的意見;】 performance limitations on a busy database server. Where practical, wrap【ræp 包;裹(禮物等);（使文字）換行;用…包裹(或包扎、覆蓋等);用…纏繞(或圍緊);】 several related data change operations into a single transaction, by issuing SET AUTOCOMMIT=0 or a START TRANSACTION statement, followed by a COMMIT statement after making all the changes.

InnoDB must flush the log to disk at each transaction commit if that transaction made modifications to the database. When each change is followed by a commit (as with the default autocommit setting), the I/ O throughput of the storage device puts a cap on the number of potential operations per second.

• Alternatively, for transactions that consist only of a single SELECT statement, turning on AUTOCOMMIT helps InnoDB to recognize read-only transactions and optimize them.

• Avoid performing rollbacks after inserting, updating, or deleting huge numbers of rows. If a big transaction is slowing down server performance, rolling it back can make the problem worse, potentially【pə'tenʃəli 潛在地;】 taking several times as long to perform as the original data change operations. Killing the database process does not help, because the rollback starts again on server startup.

To minimize the chance of this issue occurring:

• Increase the size of the buffer pool so that all the data change changes can be cached rather than immediately written to disk.
• Set innodb_change_buffering=all so that update and delete operations are buffered in addition to inserts.
• Consider issuing COMMIT statements periodically during the big data change operation, possibly breaking a single delete or update into multiple statements that operate on smaller numbers of rows.

  To get rid of a runaway rollback once it occurs, increase the buffer pool so that the rollback becomes CPU-bound and runs fast, or kill the server and restart with innodb_force_recovery=3.

  This issue is expected to be infrequent【ɪnˈfriːkwənt 罕見的;不常發生的;】 with the default setting innodb_change_buffering=all, which allows update and delete operations to be cached in memory, making them faster to perform in the first place, and also faster to roll back if needed. Make sure to use this parameter setting on servers that process long-running transactions with many inserts, updates, or deletes.

• If you can afford the loss of some of the latest committed transactions if an unexpected exit occurs, you can set the innodb_flush_log_at_trx_commit parameter to 0. InnoDB tries to flush the log once per second anyway, although the flush is not guaranteed.

• When rows are modified or deleted, the rows and associated undo logs are not physically removed immediately, or even immediately after the transaction commits. The old data is preserved until transactions that started earlier or concurrently are finished, so that those transactions can access the previous state of modified or deleted rows. Thus, a long-running transaction can prevent InnoDB from purging data that was changed by a different transaction.

• When rows are modified or deleted within a long-running transaction, other transactions using the READ COMMITTED and REPEATABLE READ isolation levels have to do more work to reconstruct the older data if they read those same rows.

• When a long-running transaction modifies a table, queries against that table from other transactions do not make use of the covering index technique. Queries that normally could retrieve all the result columns from a secondary index, instead look up the appropriate values from the table data.

If secondary index pages are found to have a PAGE_MAX_TRX_ID that is too new, or if records in the secondary index are delete-marked, InnoDB may need to look up records using a clustered index.

3 Optimizing InnoDB Read-Only Transactions

InnoDB can avoid the overhead associated with setting up the transaction ID (TRX_ID field) for transactions that are known to be read-only. A transaction ID is only needed for a transaction that might perform write operations or locking reads such as SELECT ... FOR UPDATE. Eliminating【ɪˈlɪmɪneɪtɪŋ 消除;排除;消滅，幹掉(尤指敵人或對手);清除;(比賽中)淘汰;】 unnecessary transaction IDs reduces the size of internal data structures that are consulted each time a query or data change statement constructs a read view.

InnoDB detects read-only transactions when:

• The transaction is started with the START TRANSACTION READ ONLY statement. In this case, attempting to make changes to the database (for InnoDB, MyISAM, or other types of tables) causes an error, and the transaction continues in read-only state:

ERROR 1792 (25006): Cannot execute statement in a READ ONLY transaction.

You can still make changes to session-specific temporary tables in a read-only transaction, or issue locking queries for them, because those changes and locks are not visible to any other transaction.

• The autocommit setting is turned on, so that the transaction is guaranteed【ˌɡærənˈtiːd 必然的;確定會發生的;】 to be a single statement, and the single statement making up the transaction is a “non-locking” SELECT statement. That is, a SELECT that does not use a FOR UPDATE or LOCK IN SHARED MODE clause.

• The transaction is started without the READ ONLY option, but no updates or statements that explicitly lock rows have been executed yet. Until updates or explicit locks are required, a transaction stays in read-only mode.

Thus, for a read-intensive application such as a report generator, you can tune a sequence of InnoDB queries by grouping them inside START TRANSACTION READ ONLY and COMMIT, or by turning on the autocommit setting before running the SELECT statements, or simply by avoiding any data change statements interspersed with the queries.

【Transactions that qualify as auto-commit, non-locking, and read-only (AC-NL-RO) are kept out of certain internal InnoDB data structures and are therefore not listed in SHOW ENGINE INNODB STATUS output.】

4 Optimizing InnoDB Redo Logging

Consider the following guidelines for optimizing redo logging:

• Increase the size of your redo log files. When InnoDB has written redo log files full, it must write the modified contents of the buffer pool to disk in a checkpoint. Small redo log files cause many unnecessary disk writes.

From MySQL 8.0.30, the redo log file size is determined by the innodb_redo_log_capacity setting. InnoDB tries to maintain 32 redo log files of the same size, with each file equal to 1/32 * innodb_redo_log_capacity. Therefore, changing the innodb_redo_log_capacity setting changes the size of the redo log files.

Before MySQL 8.0.30, the size and number of redo log files are configured using the innodb_log_file_size and innodb_log_files_in_group variables.

• Consider increasing the size of the log buffer. A large log buffer enables large transactions to run without a need to write the log to disk before the transactions commit. Thus, if you have transactions that update, insert, or delete many rows, making the log buffer larger saves disk I/O. Log buffer size is configured using the innodb_log_buffer_size configuration option, which can be configured dynamically in MySQL 8.0.

• Configure the innodb_log_write_ahead_size configuration option to avoid “read-on-write”. This option defines the write-ahead block size for the redo log. Set innodb_log_write_ahead_size to match the operating system or file system cache block size. Read-on-write occurs when redo log blocks are not entirely cached to the operating system or file system due to a mismatch between write-ahead block size for the redo log and operating system or file system cache block size.

Valid values for innodb_log_write_ahead_size are multiples of the InnoDB log file block size (2n ). The minimum value is the InnoDB log file block size (512). Write-ahead does not occur when the minimum value is specified. The maximum value is equal to the innodb_page_size value. If you specify a value for innodb_log_write_ahead_size that is larger than the innodb_page_size value, the innodb_log_write_ahead_size setting is truncated to the innodb_page_size value.Setting the innodb_log_write_ahead_size value too low in relation to the operating system or file system cache block size results in read-on-write. Setting the value too high may have a slight impact on fsync performance for log file writes due to several blocks being written at once.

• MySQL 8.0.11 introduced dedicated log writer threads for writing redo log records from the log buffer to the system buffers and flushing the system buffers to the redo log files. Previously, individual user threads were responsible those tasks. As of MySQL 8.0.22, you can enable or disable log writer threads using the innodb_log_writer_threads variable. Dedicated log writer threads can improve performance on high-concurrency systems, but for low-concurrency systems, disabling dedicated log writer threads provides better performance.

• Optimize the use of spin delay by user threads waiting for flushed redo. Spin delay helps reduce latency. During periods of low concurrency, reducing latency may be less of a priority, and avoiding the use of spin delay during these periods may reduce energy consumption. During periods of high concurrency, you may want to avoid expending processing power on spin delay so that it can be used for other work. The following system variables permit setting high and low watermark values that define boundaries for the use of spin delay.

• innodb_log_wait_for_flush_spin_hwm: Defines the maximum average log flush time beyond which user threads no longer spin while waiting for flushed redo. The default value is 400 microseconds.
• innodb_log_spin_cpu_abs_lwm: Defines the minimum amount of CPU usage below which user threads no longer spin while waiting for flushed redo. The value is expressed as a sum of CPU core usage. For example, The default value of 80 is 80% of a single CPU core. On a system with a multicore processor, a value of 150 represents 100% usage of one CPU core plus 50% usage of a second CPU core.
• innodb_log_spin_cpu_pct_hwm: Defines the maximum amount of CPU usage above which user threads no longer spin while waiting for flushed redo. The value is expressed as a percentage of the combined total processing power of all CPU cores. The default value is 50%. For example, 100% usage of two CPU cores is 50% of the combined CPU processing power on a server with four CPU cores.

    The innodb_log_spin_cpu_pct_hwm configuration option respects processor affinity. For example, if a server has 48 cores but the mysqld process is pinned to only four CPU cores, the other 44 CPU cores are ignored.

5 Bulk Data Loading for InnoDB Tables

• When importing data into InnoDB, turn off autocommit mode, because it performs a log flush to disk for every insert. To disable autocommit during your import operation, surround it with SET autocommit and COMMIT statements:

SET autocommit=0;
... SQL import statements ...
COMMIT;

The mysqldump option --opt creates dump files that are fast to import into an InnoDB table, even without wrapping them with the SET autocommit and COMMIT statements.

• If you have UNIQUE constraints on secondary keys, you can speed up table imports by temporarily turning off the uniqueness checks during the import session:

SET unique_checks=0;
... SQL import statements ...
SET unique_checks=1;

For big tables, this saves a lot of disk I/O because InnoDB can use its change buffer to write secondary index records in a batch. Be certain that the data contains no duplicate keys

• If you have FOREIGN KEY constraints in your tables, you can speed up table imports by turning off the foreign key checks for the duration of the import session:

SET foreign_key_checks=0;
... SQL import statements ...
SET foreign_key_checks=1;

For big tables, this can save a lot of disk I/O.

 • Use the multiple-row INSERT syntax to reduce communication overhead between the client and the server if you need to insert many rows:

INSERT INTO yourtable VALUES (1,2), (5,5), ...;

This tip is valid for inserts into any table, not just InnoDB tables.

• When doing bulk inserts into tables with auto-increment columns, set innodb_autoinc_lock_mode to 2 (interleaved) instead of 1 (consecutive).

• When performing bulk inserts, it is faster to insert rows in PRIMARY KEY order. InnoDB tables use a clustered index, which makes it relatively【ˈrelətɪvli 相對地;相當地;相當程度上;】 fast to use data in the order of the PRIMARY KEY. Performing bulk inserts in PRIMARY KEY order is particularly important for tables that do not fit entirely within the buffer pool.

• For optimal【ˈɑːptɪməl 最佳的;最優的;】 performance when loading data into an InnoDB FULLTEXT index, follow this set of steps:

1. Define a column FTS_DOC_ID at table creation time, of type BIGINT UNSIGNED NOT NULL, with a unique index named FTS_DOC_ID_INDEX. For example:

CREATE TABLE t1 (
FTS_DOC_ID BIGINT unsigned NOT NULL AUTO_INCREMENT,
title varchar(255) NOT NULL DEFAULT '',
text mediumtext NOT NULL,
PRIMARY KEY (`FTS_DOC_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE UNIQUE INDEX FTS_DOC_ID_INDEX on t1(FTS_DOC_ID);

2. Load the data into the table.

3. Create the FULLTEXT index after the data is loaded.

【When adding FTS_DOC_ID column at table creation time, ensure that the FTS_DOC_ID column is updated when the FULLTEXT indexed column is updated, as the FTS_DOC_ID must increase monotonically with each INSERT or UPDATE. If you choose not to add the FTS_DOC_ID at table creation time and have InnoDB manage DOC IDs for you, InnoDB adds the FTS_DOC_ID as a hidden column with the next CREATE FULLTEXT INDEX call. This approach, however, requires a table rebuild which can impact performance.】

• If loading data into a new MySQL instance, consider disabling redo logging using ALTER INSTANCE {ENABLE|DISABLE} INNODB REDO_LOG syntax. Disabling redo logging helps speed up data loading by avoiding redo log writes.

【This feature is intended only for loading data into a new MySQL instance. Do not disable redo logging on a production system. It is permitted to shutdown and restart the server while redo logging is disabled, but an unexpected server stoppage while redo logging is disabled can cause data loss and instance corruption.】

• Use MySQL Shell to import data. MySQL Shell's parallel table import utility util.importTable() provides rapid【ˈræpɪd 快速的;迅速的;快捷的;瞬間的;短時間內發生的;】 data import to a MySQL relational table for large data files. MySQL Shell's dump loading utility util.loadDump() also offers parallel load capabilities.

6 Optimizing InnoDB Queries

To tune queries for InnoDB tables, create an appropriate set of indexes on each table.Follow these guidelines for InnoDB indexes:

• Because each InnoDB table has a primary key (whether you request one or not), specify a set of primary key columns for each table, columns that are used in the most important and time-critical queries.

• Do not specify too many or too long columns in the primary key, because these column values are duplicated in each secondary index. When an index contains unnecessary data, the I/O to read this data and memory to cache it reduce the performance and scalability of the server.

• Do not create a separate secondary index for each column, because each query can only make use of one index. Indexes on rarely tested columns or columns with only a few different values might not be helpful for any queries. If you have many queries for the same table, testing different combinations of columns, try to create a small number of concatenated【kənˈkætəˌneɪtəd 使連接（連續，銜接）起來；連鎖；串級;】 indexes rather than a large number of single-column indexes. If an index contains all the columns needed for the result set (known as a covering index), the query might be able to avoid reading the table data at all.

• If an indexed column cannot contain any NULL values, declare it as NOT NULL when you create the table. The optimizer can better determine which index is most effective to use for a query, when it knows whether each column contains NULL values.

• You can optimize single-query transactions for InnoDB tables.

7 Optimizing InnoDB DDL Operations

• Many DDL operations on tables and indexes (CREATE, ALTER, and DROP statements) can be performed online.

• Online DDL support for adding secondary indexes means that you can generally speed up the process of creating and loading a table and associated indexes by creating the table without secondary indexes, then adding secondary indexes after the data is loaded.

• Use TRUNCATE TABLE to empty a table, not DELETE FROM tbl_name. Foreign key constraints can make a TRUNCATE statement work like a regular DELETE statement, in which case a sequence of commands like DROP TABLE and CREATE TABLE might be fastest.

• Because the primary key is integral【ˈɪntɪɡrəl 完整的;不可或缺的;必需的;完備的;作為組成部分的;】 to the storage layout of each InnoDB table, and changing the definition of the primary key involves reorganizing the whole table, always set up the primary key as part of the CREATE TABLE statement, and plan ahead so that you do not need to ALTER or DROP the primary key afterward.

8 Optimizing InnoDB Disk I/O

If you follow best practices for database design and tuning techniques for SQL operations, but your database is still slow due to heavy disk I/O activity, consider these disk I/O optimizations. If the Unix top tool or the Windows Task Manager shows that the CPU usage percentage with your workload is less than 70%, your workload is probably disk-bound.

• Increase buffer pool size

When table data is cached in the InnoDB buffer pool, it can be accessed repeatedly by queries without requiring any disk I/O. Specify the size of the buffer pool with the innodb_buffer_pool_size option. This memory area is important enough that it is typically recommended that innodb_buffer_pool_size is configured to 50 to 75 percent of system memory.

• Adjust the flush method

In some versions of GNU/Linux and Unix, flushing files to disk with the Unix fsync() call (which InnoDB uses by default) and similar methods is surprisingly slow. If database write performance is an issue, conduct benchmarks with the innodb_flush_method parameter set to O_DSYNC.

• Configure a threshold for operating system flushes

By default, when InnoDB creates a new data file, such as a new log file or tablespace file, the file is fully written to the operating system cache before it is flushed to disk, which can cause a large amount of disk write activity to occur at once. To force smaller, periodic【ˌpɪriˈɑːdɪk 周期性;定期的;周期的;間發性的;】 flushes of data from the operating system cache, you can use the innodb_fsync_threshold variable to define a threshold value, in bytes. When the byte threshold is reached, the contents of the operating system cache are flushed to disk. The default value of 0 forces the default behavior, which is to flush data to disk only after a file is fully written to the cache.

Specifying a threshold to force smaller, periodic flushes may be beneficial in cases where multiple MySQL instances use the same storage devices. For example, creating a new MySQL instance and its associated data files could cause large surges of disk write activity, impeding the performance of other MySQL instances that use the same storage devices. Configuring a threshold helps avoid such surges in write activity.

• Use fdatasync() instead of fsync()

On platforms that support fdatasync() system calls, the innodb_use_fdatasync variable, introduced in MySQL 8.0.26, permits using fdatasync() instead of fsync() for operating system flushes. An fdatasync() system call does not flush changes to file metadata unless required for subsequent【ˈsʌbsɪkwənt 隨後的;之後的;後來的;接後的;】 data retrieval, providing a potential performance benefit.

A subset of innodb_flush_method settings such as fsync, O_DSYNC, and O_DIRECT use fsync() system calls. The innodb_use_fdatasync variable is applicable when using those settings.

• Use a noop or deadline I/O scheduler with native AIO on Linux

InnoDB uses the asynchronous I/O subsystem (native AIO) on Linux to perform read-ahead and write requests for data file pages. This behavior is controlled by the innodb_use_native_aio configuration option, which is enabled by default. With native AIO, the type of I/O scheduler has greater influence on I/O performance. Generally, noop and deadline I/O schedulers are recommended. Conduct benchmarks to determine which I/O scheduler provides the best results for your workload and environment.

• Use direct I/O on Solaris 10 for x86_64 architecture

When using the InnoDB storage engine on Solaris 10 for x86_64 architecture (AMD Opteron), use direct I/O for InnoDB-related files to avoid degradation of InnoDB performance. To use direct I/O for an entire UFS file system used for storing InnoDB-related files, mount it with the forcedirectio option; see mount_ufs(1M). (The default on Solaris 10/x86_64 is not to use this option.) To apply direct I/O only to InnoDB file operations rather than the whole file system, set innodb_flush_method = O_DIRECT. With this setting, InnoDB calls directio() instead of fcntl() for I/O to data files (not for I/O to log files).

• Use raw storage for data and log files with Solaris 2.6 or later

When using the InnoDB storage engine with a large innodb_buffer_pool_size value on any release of Solaris 2.6 and up and any platform (sparc/x86/x64/amd64), conduct benchmarks with InnoDB data files and log files on raw devices or on a separate direct I/O UFS file system, using the forcedirectio mount option as described previously. (It is necessary to use the mount option rather than setting innodb_flush_method if you want direct I/O for the log files.) Users of the Veritas file system VxFS should use the convosync=direct mount option.

Do not place other MySQL data files, such as those for MyISAM tables, on a direct I/O file system. Executables or libraries must not be placed on a direct I/O file system.

• Use additional storage devices

Additional storage devices could be used to set up a RAID configuration.Alternatively, InnoDB tablespace data files and log files can be placed on different physical disks.

• Consider non-rotational storage

Non-rotational【roʊˈteɪʃənl 旋轉的;轉動的，輪流的;】 storage generally provides better performance for random I/O operations; and rotational storage for sequential I/O operations. When distributing data and log files across rotational and non-rotational storage devices, consider the type of I/O operations that are predominantly performed on each file.

Random I/O-oriented files typically include file-per-table and general tablespace data files, undo tablespace files, and temporary tablespace files. Sequential I/O-oriented files include InnoDB system tablespace files (due to doublewrite buffering prior to MySQL 8.0.20 and change buffering), doublewrite files introduced in MySQL 8.0.20, and log files such as binary log files and redo log files.

Review settings for the following configuration options when using non-rotational storage:

  • innodb_checksum_algorithm

  The crc32 option uses a faster checksum algorithm and is recommended for fast storage systems.

  • innodb_flush_neighbors

  Optimizes I/O for rotational storage devices. Disable it for non-rotational storage or a mix of rotational and non-rotational storage. It is disabled by default.

  • innodb_idle_flush_pct

  Permits placing a limit on page flushing during idle periods, which can help extend the life of nonrotational storage devices. Introduced in MySQL 8.0.18.

  • innodb_io_capacity

  The default setting of 200 is generally sufficient for a lower-end non-rotational storage device. For higher-end, bus-attached devices, consider a higher setting such as 1000.

  • innodb_io_capacity_max

  The default value of 2000 is intended for workloads that use non-rotational storage. For a high-end, bus-attached non-rotational storage device, consider a higher setting such as 2500.

  • innodb_log_compressed_pages

  If redo logs are on non-rotational storage, consider disabling this option to reduce logging.

  • innodb_log_file_size (deprecated in MySQL 8.0.30)

  If redo logs are on non-rotational storage, configure this option to maximize caching and write combining.

  • innodb_redo_log_capacity

  If redo logs are on non-rotational storage, configure this option to maximize caching and write combining.

  • innodb_page_size

  Consider using a page size that matches the internal sector size of the disk. Early-generation SSD devices often have a 4KB sector size. Some newer devices have a 16KB sector size. The default InnoDB page size is 16KB. Keeping the page size close to the storage device block size minimizes the amount of unchanged data that is rewritten to disk.

  • binlog_row_image

  If binary logs are on non-rotational storage and all tables have primary keys, consider setting this option to minimal to reduce logging.

  Ensure that TRIM support is enabled for your operating system. It is typically enabled by default.

• Increase I/O capacity to avoid backlogs

If throughput drops periodically because of InnoDB checkpoint operations, consider increasing the value of the innodb_io_capacity configuration option. Higher values cause more frequent flushing, avoiding the backlog of work that can cause dips in throughput.

• Lower I/O capacity if flushing does not fall behind

If the system is not falling behind with InnoDB flushing operations, consider lowering the value of the innodb_io_capacity configuration option. Typically, you keep this option value as low as practical【ˈpræktɪkl 實際的;適用的;切實可行的;有用的;真實的;明智的;幾乎完全的;客觀存在的;心靈手巧的;】, but not so low that it causes periodic drops in throughput as mentioned in the preceding bullet【ˈbʊlɪt 子彈;彈丸;】. In a typical scenario where you could lower the option value, you might see a combination like this in the output from SHOW ENGINE INNODB STATUS:

• History list length low, below a few thousand.
• Insert buffer merges close to rows inserted.
• Modified pages in buffer pool consistently well below innodb_max_dirty_pages_pct of the buffer pool. (Measure at a time when the server is not doing bulk inserts; it is normal during bulk inserts for the modified pages percentage to rise significantly.)
• Log sequence number - Last checkpoint is at less than 7/8 or ideally less than 6/8 of the total size of the InnoDB log files.

• Store system tablespace files on Fusion-io devices

You can take advantage of a doublewrite buffer-related I/O optimization by storing the files that contain the doublewrite storage area on Fusion-io devices that support atomic writes. (Prior to MySQL 8.0.20, the doublewrite buffer storage are resides in the system tablespace data files. As of MySQL 8.0.20, the storage area resides in doublewrite files.When doublewrite storage area files are placed on Fusion-io devices that support atomic writes, the doublewrite buffer is automatically disabled and Fusion-io atomic writes are used for all data files. This feature is only supported on Fusion-io hardware and is only enabled for Fusion-io NVMFS on Linux. To take full advantage of this feature, an innodb_flush_method setting of O_DIRECT is recommended.

【Because the doublewrite buffer setting is global, the doublewrite buffer is also disabled for data files that do not reside on Fusion-io hardware.】

• Disable logging of compressed pages

When using the InnoDB table compression feature, images of re-compressed pages are written to the redo log when changes are made to compressed data. This behavior is controlled by innodb_log_compressed_pages, which is enabled by default to prevent corruption that can occur if a different version of the zlib compression algorithm is used during recovery. If you are certain that the zlib version is not subject to change, disable innodb_log_compressed_pages to reduce redo log generation for workloads that modify compressed data.

9 Optimizing InnoDB Configuration Variables

Different settings work best for servers with light, predictable【prɪˈdɪktəbl 可預測的;可預見的;可預料的;意料之中的;老套乏味的;】 loads, versus servers that are running near full capacity all the time, or that experience spikes of high activity.

Because the InnoDB storage engine performs many of its optimizations automatically, many performance-tuning tasks involve monitoring to ensure that the database is performing well, and changing configuration options when performance drops.

The main configuration steps you can perform include:

• Controlling the types of data change operations for which InnoDB buffers the changed data, to avoid frequent small disk writes.Because the default is to buffer all types of data change operations, only change this setting if you need to reduce the amount of buffering.

• Turning the adaptive hash indexing feature on and off using the innodb_adaptive_hash_index option.You might change this setting during periods of unusual activity, then restore it to its original setting.

• Setting a limit on the number of concurrent threads that InnoDB processes, if context switching is a bottleneck.

• Controlling the amount of prefetching that InnoDB does with its read-ahead operations. When the system has unused I/O capacity, more read-ahead can improve the performance of queries. Too much read-ahead can cause periodic drops in performance on a heavily loaded system.

• Increasing the number of background threads for read or write operations, if you have a high-end I/O subsystem that is not fully utilized by the default values.

• Controlling how much I/O InnoDB performs in the background.You might scale back this setting if you observe periodic drops in performance.

• Controlling the algorithm that determines when InnoDB performs certain types of background writes.The algorithm works for some types of workloads but not others, so you might disable this feature if you observe periodic drops in performance.

• Taking advantage of multicore processors and their cache memory configuration, to minimize delays in context switching.

• Preventing one-time operations such as table scans from interfering with the frequently accessed data stored in the InnoDB buffer cache.

• Adjusting log files to a size that makes sense for reliability and crash recovery. InnoDB log files have often been kept small to avoid long startup times after a crash. Optimizations introduced in MySQL 5.5 speed up certain steps of the crash recovery process. In particular, scanning the redo log and applying the redo log are faster due to improved algorithms for memory management. If you have kept your log files artificially small to avoid long startup times, you can now consider increasing log file size to reduce the I/O that occurs due recycling of redo log records.

• Configuring the size and number of instances for the InnoDB buffer pool, especially important for systems with multi-gigabyte buffer pools.

• Increasing the maximum number of concurrent transactions, which dramatically improves scalability for the busiest databases.

• Moving purge operations (a type of garbage collection) into a background thread.To effectively measure the results of this setting, tune the other I/O-related and thread-related configuration settings first.

• Reducing the amount of switching that InnoDB does between concurrent threads, so that SQL operations on a busy server do not queue up and form a “traffic jam”. Set a value for the innodb_thread_concurrency option, up to approximately 32 for a high-powered modern system. Increase the value for the innodb_concurrency_tickets option, typically to 5000 or so. This combination of options sets a cap on the number of threads that InnoDB processes at any one time, and allows each thread to do substantial work before being swapped out, so that the number of waiting threads stays low and operations can complete without excessive context switching.

10 Optimizing InnoDB for Systems with Many Tables

If you have configured non-persistent optimizer statistics (a non-default configuration), InnoDB computes index cardinality【基數；集的勢;】 values for a table the first time that table is accessed after startup, instead of storing such values in the table. This step can take significant time on systems that partition the data into many tables. Since this overhead only applies to the initial table open operation, to “warm up” a table for later use, access it immediately after startup by issuing a statement such as SELECT 1 FROM tbl_name LIMIT 1.

Optimizer statistics are persisted【pərˈsɪstɪd 持續存在;保持;維持;頑強地堅持;執著地做;】 to disk by default, enabled by the innodb_stats_persistent configuration option.

 11 Optimizing for MEMORY Tables

Consider using MEMORY tables for noncritical data that is accessed often, and is read-only or rarely updated. Benchmark your application against equivalent InnoDB or MyISAM tables under a realistic workload, to confirm that any additional performance is worth the risk of losing data, or the overhead of copying data from a disk-based table at application start.

For best performance with MEMORY tables, examine the kinds of queries against each table, and specify the type to use for each associated index, either a B-tree index or a hash index. On the CREATE INDEX statement, use the clause USING BTREE or USING HASH. B-tree indexes are fast for queries that do greater-than or less-than comparisons through operators such as > or BETWEEN. Hash indexes are only fast for queries that look up single values through the = operator, or a restricted set of values through the IN operator.