Movatterモバイル変換

Increase buffer pool size

When table data is cached in theInnoDB buffer pool, it can be accessed repeatedly by queries without requiring any disk I/O. Specify the size of the buffer pool with theinnodb_buffer_pool_size option. This memory area is important enough that it is typically recommended thatinnodb_buffer_pool_size is configured to 50 to 75 percent of system memory. For more information see,Section 10.12.3.1, “How MySQL Uses Memory”.

Adjust the flush method

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

Configure a threshold for operating system flushes

By default, whenInnoDB 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 flushes of data from the operating system cache, you can use theinnodb_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 supportfdatasync() system calls, theinnodb_use_fdatasync variable, introduced in MySQL 8.0.26, permits usingfdatasync() instead offsync() for operating system flushes. Anfdatasync() system call does not flush changes to file metadata unless required for subsequent data retrieval, providing a potential performance benefit.

A subset ofinnodb_flush_method settings such asfsync,O_DSYNC, andO_DIRECT usefsync() system calls. Theinnodb_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 theinnodb_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. For more information, seeSection 17.8.6, “Using Asynchronous I/O on Linux”.

Use direct I/O on Solaris 10 for x86_64 architecture

When using theInnoDB storage engine on Solaris 10 for x86_64 architecture (AMD Opteron), use direct I/O forInnoDB-related files to avoid degradation ofInnoDB performance. To use direct I/O for an entire UFS file system used for storingInnoDB-related files, mount it with theforcedirectio option; seemount_ufs(1M). (The default on Solaris 10/x86_64 isnot to use this option.) To apply direct I/O only toInnoDB file operations rather than the whole file system, setinnodb_flush_method = O_DIRECT. With this setting,InnoDB callsdirectio() instead offcntl() 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 theInnoDB storage engine with a largeinnodb_buffer_pool_size value on any release of Solaris 2.6 and up and any platform (sparc/x86/x64/amd64), conduct benchmarks withInnoDB data files and log files on raw devices or on a separate direct I/O UFS file system, using theforcedirectio mount option as described previously. (It is necessary to use the mount option rather than settinginnodb_flush_method if you want direct I/O for the log files.) Users of the Veritas file system VxFS should use theconvosync=direct mount option.

Do not place other MySQL data files, such as those forMyISAM tables, on a direct I/O file system. Executables or librariesmust 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. For related information, seeSection 10.12.1, “Optimizing Disk I/O”.

Alternatively,InnoDB tablespace data files and log files can be placed on different physical disks. For more information, refer to the following sections:

Consider non-rotational storage

Non-rotational 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 includefile-per-table andgeneral tablespace data files,undo tablespace files, andtemporary tablespace files. Sequential I/O-oriented files includeInnoDBsystem tablespace files (due todoublewrite buffering prior to MySQL 8.0.20 andchange buffering), doublewrite files introduced in MySQL 8.0.20, and log files such asbinary log files andredo log files.

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

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 ofInnoDBcheckpoint operations, consider increasing the value of theinnodb_io_capacity configuration option. Higher values cause more frequentflushing, 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 withInnoDBflushing operations, consider lowering the value of theinnodb_io_capacity configuration option. Typically, you keep this option value as low as practical, but not so low that it causes periodic drops in throughput as mentioned in the preceding bullet. In a typical scenario where you could lower the option value, you might see a combination like this in the output fromSHOW ENGINE INNODB STATUS:

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. SeeSection 17.6.4, “Doublewrite Buffer”.) 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, aninnodb_flush_method setting ofO_DIRECT is recommended.

Disable logging of compressed pages

When using theInnoDB tablecompression feature, images of re-compressedpages are written to theredo log when changes are made to compressed data. This behavior is controlled byinnodb_log_compressed_pages, which is enabled by default to prevent corruption that can occur if a different version of thezlib compression algorithm is used during recovery. If you are certain that thezlib version is not subject to change, disableinnodb_log_compressed_pages to reduce redo log generation for workloads that modify compressed data.