High performance MySQL (8) Optimizing Server configuration: I/O

There are some configuration items that affect how MySQL synchronizes data to disk and how to do recovery operations. This write operation has a great impact on performance because it is designed to expensive I/O operations, usually ensuring that data is immediately and consistently written to disk is expensive, sometimes having to take a little risk, delaying persistence to disk, To increase concurrency and reduce I/O waiting.

First, InnoDB I/O configuration

For common applications, InnoDB log file size, InnoDB How to refresh the log buffer, and how to perform I/O is more important.

A, InnoDB transaction log

InnoDB uses logs to reduce the cost of committing transactions, logging transactions without flushing the buffer pool to disk on each transaction commit. Once the log is securely written to disk, the transaction is persisted, which is necessary because the log has a fixed size and is written in a circular way, written to the tail, and skips to the beginning to continue writing. InnoDB uses a background thread to intelligently refresh these changes to the data file.

The overall log file size is controlled by the innodb_log_file_size and innodb_log_files_in_group two parameters, and by default there are only 2 5MB files, which require at least hundreds of MB or GB for high-performance work.

To modify the log file size, you must completely cleanly shut down MySQL, otherwise the data can not be restored, it is best to back up the old log files first.

When InnoDB changes any data, a change is written to the memory log buffer. When the buffer is full, when the transaction commits, or every second, InnoDB flushes the contents of the buffer to the disk log file. If there is a large transaction, increasing the log buffer (default 1MB) size can reduce I/O, variable innodb_log_buffer_size control, a few hundred MB can be.

As a rule of thumb, the full size of the log file should be sufficient to accommodate the server's one-hour active content.

When InnoDB flushes the log buffer to the log file, the buffer is locked, the refresh completes, and the remaining entries are moved to the front of the buffer. Log buffering must be flushed to persistent storage to ensure that committed transactions are completely persisted. If you care more about performance than persistence, you can modify Innodb_flush_log_at_trx_commit to control the refresh rate, like the value below

0: Write the log buffer to the log file and refresh every second, but do not do anything when the transaction is committed.

1: The log buffer is written to the log file, and each transaction commit is flushed to the persistent store.

2: Every time the log buffer to write to the log file, but do not refresh, InnoDB every second to do a refresh.

In most operating systems, the caching of buffers to the log is simply a transfer of data from the InnoDB memory buffer to the operating system cache, and there is no real persistence.

Refreshing the log to persistent storage means that the InnoDB requests the operating system to brush the data out of the cache and make sure that it is written to disk, a blocking I/O call, knowing that the data is all written out.

Use the Innodb_flush_method option to configure how InnoDB tracks file system interactions. There are several values you can set, but not one of them.

1, Fdatasync

2, 0_direct

3, All_0_direct

4, 0_dsync

5, there are several configurations under Windows.

InnoDB saves data to the tablespace. The configuration table space allows you to customize tablespace files, which are placed under the Innodb_data_home_dir specified directory: Innodb_data_file_path

Innodb_data_home_dir =/var/lib/mysql/

Innodb_data_file_path = ibdata1:1g;ibdata2:2g;

To allow the table space to grow beyond the allocated space, you can configure the last file to expand automatically

ibdata3:1g:autoextend:max:2g

Limits are extended up to 2G.

Managing a single table space is a bit of a hassle, innodb_file_per_table option lets InnoDB use a file for each table. Stored as "table name. ibd" file, this in the previous zoning technology has been tested, there are actual screenshots, you can see. Although easy to manage, there are drawbacks:

Each table uses its own table space, and removing the tablespace actually requires INNODB locking and scanning the buffer pool, looking for pages that belong to this table's space, in a huge buffer pool Lord Oh girl, this is very slow. However, Percona server has a fixed option innodb_lazy_drop_table.