MySQL 5.6 Reference Manual-14.4 InnoDB Configuration

 

14.4.1 InnoDB Initialization and Startup Configuration

 

The first decisions to make about InnoDB configuration involve how to lay out InnoDB data files, and how much memory to allocate for the InnoDB storage engine. You record these choices either by recording them in a configuration file that MySQL reads at startup, or by specifying them as command-line options in a startup script. The full list of options, descriptions, and allowed parameter values is at Section 14.12, "InnoDB Startup Options and System Variables".

第一部分介绍了InnoDB的配置，涉及到了InnoDB的数据文件是如何分布的，以及InnoDB存储引擎是如何分配内存的。你可以把这些选项写到配置文件里，让MySQL的启动的时候去读它，或者在启动脚本里面加入这些命令行参数。参数的详细信息可见Section 14.12, "InnoDB Startup Options and System Variables"。

 

Overview of InnoDB Tablespace and Log Files

 

Two important disk-based resources managed by the InnoDB storage engine are its tablespace data files and its log files. If you specify no InnoDB configuration options, MySQL creates an auto-extending data file, slightly larger than 12MB, named ibdata1 and two log files named ib_logfile0 and ib_logfile1 in the MySQL data directory. Their size is given by the size of the innodb_log_file_size system variable. To get good performance, explicitly provide InnoDB parameters as discussed in the following examples. Naturally, edit the settings to suit your hardware and requirements.

InnoDB存储引擎管理的两个重要的基于磁盘的资源是它自身的表空间数据文件和日志文件。如果你没有指定InnoDB的配置参数，MySQL会在MySQL数据目录里创建一个自动扩展的数据文件，大约比12MB多一点，名字叫ibdata1，以及两个名字分别为ib_logfile0和ib_logfile1的日志文件。它们的大小是由innodb_log_file_size系统参数决定的。要得到更好的性能，可以根据下面例子讨论的显示指定InnoDB参数。自然地，配置的参数要适合你的硬件和要求。

 

The examples shown here are representative. See Section 14.12, "InnoDB Startup Options and System Variables" for additional information about InnoDB-related configuration parameters.

这里的例子是具有代表性的。关于详细InnoDB相关的配置参数可以查看Section 14.12, "InnoDB Startup Options and System Variables"。

 

Considerations for Storage Devices

 

In some cases, database performance improves if the data is not all placed on the same physical disk. Putting log files on a different disk from data is very often beneficial for performance. The example illustrates how to do this. It places the two data files on different disks and places the log files on the third disk. InnoDB fills the tablespace beginning with the first data file. You can also use raw disk partitions (raw devices) as InnoDB data files, which may speed up I/O. See Section 14.5.3, "Using Raw Disk Partitions for the System Tablespace".

在某些情况下，如果把数据文件分散到不同的物理磁盘上可以提高数据库的性能。这其中把日志文件当数据文件分离开来是一种常见的优化手段。这里的例如就说明了应该如何做：把两个数据文件放到不同的磁盘上，并把日志文件放到第三个磁盘上。InnoDB首先会填充表空间的第一个数据文件。你还可以使用raw disk partitions (raw devices)作为InnoDB的数据文件，这能够加快I/O的速度。详见Section 14.5.3, "Using Raw Disk Partitions for the System Tablespace"。

 

Caution

 

InnoDB is a transaction-safe (ACID compliant) storage engine for MySQL that has commit, rollback, and crash-recovery capabilities to protect user data. However, it cannot do so if the underlying operating system or hardware does not work as advertised. Many operating systems or disk subsystems may delay or reorder write operations to improve performance. On some operating systems, the very fsync() system call that should wait until all unwritten data for a file has been flushed might actually return before the data has been flushed to stable storage. Because of this, an operating system crash or a power outage may destroy recently committed data, or in the worst case, even corrupt the database because of write operations having been reordered. If data integrity is important to you, perform some "pull-the-plug" tests before using anything in production. On OS X 10.3 and higher, InnoDB uses a special fcntl() file flush method. Under Linux, it is advisable to disable the write-back cache.

InnoDB是事务安全(符合ACID)的存储引擎，能够commit，rollback，以及崩溃恢复的能力的保护用户数据。然而，如果其所在的操作系统或者硬件不工作了那也是不行的。许多操作系统或者磁盘子系统会用延迟或者重新排序写操作来该散性能。在一些操作系统里，会使用fsync()把数据刷新到稳定的存储里，但在返回结果之前它会一直等待。就因为这个，操作系统崩溃或者电源故障InnoDB会丢失最近一次的commit数据，最坏的情况是，因为重新写操作而损害了数据。如果一些环境数据完整是极为重要的，最好在生产环境使用之前做一些"pull-the-plug"的测试。在OS X 10.3或更高的版本上，InnoDB会使用特定的fcntl()文件刷新方法。在Linux环境下，最好是关闭write-back cache。

 

On ATA/SATA disk drives, a command such hdparm -W0 /dev/hda may work to disable the write-back cache. Beware that some drives or disk controllers may be unable to disable the write-back cache.

在ATA/SATA磁盘上，hdparm -W0 /dev/hda命令能够关闭write-back cache。还有要注意的是一些磁盘控制器是不能够关闭write-back cache的。

 

With regard to InnoDB recovery capabilities that protect user data, InnoDB uses a file flush technique involving a structure called the doublewrite buffer, which is enabled by default (innodb_doublewrite=ON). The doublewrite buffer adds safety to recovery following a crash or power outage, and improves performance on most varieties of Unix by reducing the need for fsync() operations. It is recommended that the innodb_doublewrite option remains enabled if you are concerned with data integrity or possible failures. For additional information about the doublewrite buffer, see Section 14.10, "InnoDB Disk I/O and File Space Management".

在InnoDB的恢复能力里，会使用一个叫做doublewrite buffer的文件刷新技术(默认是开启的，innodb_doublewrite=ON)。doublewrite buffer会在崩溃或者电源故障后安全恢复数据库，还能在众多Unix平台上减少fsync()操作来改善性能。如果你非常关心数据的完整性或者可能的故障，那么建议开启innodb_doublewrite这个参数。关于doublewrite buffer的更多信息可以查看Section 14.10, "InnoDB Disk I/O and File Space Management"。

 

Caution

 

If reliability is a consideration for your data, do not configure InnoDB to use data files or log files on NFS volumes. Potential problems vary according to OS and version of NFS, and include such issues as lack of protection from conflicting writes, and limitations on maximum file sizes.

如果非常关注数据的可靠性，那么不要把InnoDB的数据文件或者日志文件配置到NFS卷上。不同的OS和NFS的版本会有很多潜在的问题：例如冲突写造成的保护不足，还有最大文件大小的限制。

 

Specifying the Location and Size for InnoDB Tablespace Files

 

To set up the InnoDB tablespace files, use the innodb_data_file_path option in the [mysqld] section of the my.cnf option file. On Windows, you can use my.ini instead. The value of innodb_data_file_path should be a list of one or more data file specifications. If you name more than one data file, separate them by semicolon (";") characters:

可以在my.cnf的[mysqld]章节里使用innodb_data_file_path参数来设置InnoDB表空间文件。在Windows上，使用的是my.ini。innodb_data_file_path的值可以是一个或者多个文件的列表，通过分号(";")分割：

 

innodb_data_file_path=datafile_spec1[;datafile_spec2]...

 

For example, the following setting explicitly creates a minimally sized system tablespace:

下面的例子显式地创建了一个最小尺寸的系统表空间：

 

[mysqld]

innodb_data_file_path=ibdata1:12M:autoextend

 

This setting configures a single 12MB data file named ibdata1 that is auto-extending. No location for the file is given, so by default, InnoDB creates it in the MySQL data directory.

这里配置了单个12MB的数据文件，名字是ibdata1，并自动扩展。没有指定文件的位置，所以会十一on个默认的在MySQL的数据目录下。

 

Sizes are specified using K, M, or G suffix letters to indicate units of KB, MB, or GB.

大小使用K, M, or G来表示KB, MB, or GB。

 

A tablespace containing a fixed-size 50MB data file named ibdata1 and a 50MB auto-extending file named ibdata2 in the data directory can be configured like this:

下面的例子配置了表空间有两个文件，一个是带笑傲固定50MB，名字是ibdata1，另一个也是50MB，但是是自动扩展的，名字叫ibdata2。它们都在数据目录里。

 

[mysqld]

innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend

 

The full syntax for a data file specification includes the file name, its size, and several optional attributes:

指定数据文件的语法规范包括文件名，大小，以及一些其他的属性：

 

file_name:file_size[:autoextend[:max:max_file_size]]

 

The autoextend and max attributes can be used only for the last data file in the innodb_data_file_path line.

autoextend和max属性只能用于innodb_data_file_path里的最后的一个数据文件。

 

If you specify the autoextend option for the last data file, InnoDB extends the data file if it runs out of free space in the tablespace. The increment is 8MB at a time by default. To modify the increment, change the innodb_autoextend_increment system variable.

如果你为最后一个文件设定了autoextend，那么InnoDB超过空余表空间的时候会自动扩展数据文件。默认的一次增量值是8MB。通过innodb_autoextend_increment系统变量可以修改增量值。

 

If the disk becomes full, you might want to add another data file on another disk. For tablespace reconfiguration instructions, see Section 14.5.1, "Resizing the InnoDB System Tablespace".

如果磁盘要满了，你可以在另一个磁盘上添加一个新的文件。对于表空间的配置指定，可以查看Section 14.5.1, "Resizing the InnoDB System Tablespace"。

 

InnoDB is not aware of the file system maximum file size, so be cautious on file systems where the maximum file size is a small value such as 2GB. To specify a maximum size for an auto-extending data file, use the max attribute following the autoextend attribute. Use the max attribute only in cases where constraining disk usage is of critical importance, because exceeding the maximum size causes a fatal error, possibly including a crash. The following configuration permits ibdata1 to grow up to a limit of 500MB:

InnoDB不会关注文件系统的最大文件限制，所以要关注文件系统的最大文件限制，例如能不能超过2GB。根据下面的例子可以使用autoextend熟悉的max属性来指定自动扩展文件的最大限制。使用max属性只是用来约束磁盘空间的使用情况，因为超过最大的限制还会引起一个致命的错误，甚至包括系统崩溃。下面的配置允许ibdata1能够增长到 500MB：

 

[mysqld]

innodb_data_file_path=ibdata1:12M:autoextend:max:500M

 

InnoDB creates tablespace files in the MySQL data directory by default. To specify a location explicitly, use the innodb_data_home_dir option. For example, to use two files named ibdata1 and ibdata2 but create them in the /ibdata directory, configure InnoDB like this:

InnoDB默认在MySQL数据目录里创建表空间。也可以使用innodb_data_home_dir参数来显式指定位置。例如，这样可以配置在/ibdata目录里创建两个文件：

 

[mysqld]

innodb_data_home_dir = /ibdata

innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend

 

Note

 

InnoDB does not create directories, so make sure that the /ibdata directory exists before you start the server. This is also true of any log file directories that you configure. Use the Unix or DOS mkdir command to create any necessary directories.

InnoDB不会自己创建目录，所以在服务启动之前要确保/ibdata目录是存在的。对于日志文件目录也是同样的。使用Unix或者DOS的mkdir命令来创建必要的目录。

 

Make sure that the MySQL server has the proper access rights to create files in the data directory. More generally, the server must have access rights in any directory where it needs to create data files or log files.

还有要确保MySQL有适当的访问权限在数据目录里创建文件。一般地说，对于MySQL要创建的数据文件或者日志文件的任何目录都要有适当的权限。

 

InnoDB forms the directory path for each data file by textually concatenating the value of innodb_data_home_dir to the data file name, adding a path name separator (slash or backslash) between values if necessary. If the innodb_data_home_dir option is not specified in my.cnf at all, the default value is the "dot" directory ./, which means the MySQL data directory. (The MySQL server changes its current working directory to its data directory when it begins executing.)

InnoDB也可以通过innodb_data_home_dir的值连接文件名来作为每个文件的目录路径。如果my.cnf里面没有指定innodb_data_home_dir，默认的值是"./"，这就意味着是MySQL的数据目录。（MySQL可以把当面的工作工作改到它的数据目录。）

 

If you specify innodb_data_home_dir as an empty string, you can specify absolute paths for the data files listed in the innodb_data_file_path value. The following example is equivalent to the preceding one:

如果你指定了innodb_data_home_dir是一个空字符串，那也可以在innodb_data_file_path里面为数据文件使用绝对路径。下面的例子等价于前面的例子：

 

[mysqld]

innodb_data_home_dir =

innodb_data_file_path=/ibdata/ibdata1:50M;/ibdata/ibdata2:50M:autoextend

 

Specifying InnoDB Configuration Options

 

Sample my.cnf file for small systems. Suppose that you have a computer with 512MB RAM and one hard disk. The following example shows possible configuration parameters in my.cnf or my.ini for InnoDB, including the autoextend attribute. The example suits most users, both on Unix and Windows, who do not want to distribute InnoDB data files and log files onto several disks. It creates an auto-extending data file ibdata1 and two InnoDB log files ib_logfile0 and ib_logfile1 in the MySQL data directory.

小系统可以参考下面的my.cnf样例。假设计算机的内存是512MB，只有一个硬盘。下面的额配置样例包括了autoextend属性。这个例子适合于大多数的用户，在Unix和Windows上都适用，但是不适用于那些把InnoDB数据文件和日志文件分布到不同的磁盘。它会在MySQL数据目录里创建一个自动扩展的数据文件ibdata1以及两个InnoDB日志文件ib_logfile0和ib_logfile1。

 

[mysqld]

# You can write your other MySQL server options here

# ...

# Data files must be able to hold your data and indexes.

# Make sure that you have enough free disk space.

innodb_data_file_path = ibdata1:12M:autoextend

#

# Set buffer pool size to 50-80% of your computer‘s memory

innodb_buffer_pool_size=256M

innodb_additional_mem_pool_size=20M

#

# Set the log file size to about 25% of the buffer pool size

innodb_log_file_size=64M

innodb_log_buffer_size=8M

#

innodb_flush_log_at_trx_commit=1

 

Note

 

Data files must be less than 2GB in some file systems. The combined size of the log files can be up to 512GB. The combined size of data files must be slightly larger than 10MB.

一些文件系统里数据文件必须要小于2GB。日志文件的组合可以达到512GB。数据文件组合后的大小要至少大于10MB。

 

Setting Up the InnoDB System Tablespace

 

When you create an InnoDB system tablespace for the first time, it is best that you start the MySQL server from the command prompt. InnoDB then prints the information about the database creation to the screen, so you can see what is happening. For example, on Windows, if mysqld is located in C:\Program Files\MySQL\MySQL Server 5.6\bin, you can start it like this:

当你第一次创建一个InnoDB系统表空间的时候，最好从命令行启动MySQL服务。InnoDB会打印出数据库创建的信息到屏幕上，这样你就能看到发生了什么。例如，在Windows上，如果mysqld在C:\Program Files\MySQL\MySQL Server 5.6\bin里，你可以这样启动它：

 

C:\> "C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqld" --console

 

If you do not send server output to the screen, check the server‘s error log to see what InnoDB prints during the startup process.

如果没有输出到屏幕，那么可以通过error log来查看InnoDB的启动的时候的打印信息。

 

Editing the MySQL Configuration File

 

You can place InnoDB options in the [mysqld] group of any option file that your server reads when it starts. The locations for option files are described in Section 4.2.6, "Using Option Files".

你可以把InnoDB参数放置在任何配置文件的[mysqld]组里，让服务在启动的时候去读取它。关于参数文件的位置信息可以查看Section 4.2.6, "Using Option Files"。

 

If you installed MySQL on Windows using the installation and configuration wizards, the option file will be the my.ini file located in your MySQL installation directory. See Section 2.3.3, "Installing MySQL on Microsoft Windows Using MySQL Installer".

如果你在Windows上安装MySQL使用的是安装配置向导，配置文件my.ini会放在MySQL的安装目录里。详见Section 2.3.3, "Installing MySQL on Microsoft Windows Using MySQL Installer"。

 

If your PC uses a boot loader where the C: drive is not the boot drive, your only option is to use the my.ini file in your Windows directory (typically C:\WINDOWS). You can use the SET command at the command prompt in a console window to print the value of WINDIR:

如果你的C盘不是启动盘，那么则需要把my.ini文件放到Windows目录里（通常是C:\WINDOWS）。你可以在命令行使用SET命令来打印出WINDIR的值：

 

C:\> SET WINDIR

windir=C:\WINDOWS

 

To make sure that mysqld reads options only from a specific file, use the --defaults-file option as the first option on the command line when starting the server:

为了确保mysqld读取的参数是来自于指定的文件，在启动服务的时候可以指定--defaults-file参数：

 

mysqld --defaults-file=your_path_to_my_cnf

 

Sample my.cnf file for large systems. Suppose that you have a Linux computer with 2GB RAM and three 60GB hard disks at directory paths /, /dr2 and /dr3. The following example shows possible configuration parameters in my.cnf for InnoDB.

适用于大系统的my.cnf样例。假设Linux系统有2GB的内存，三个60GB的硬盘目录分别是/, /dr2 and /dr3。下面的例子显示了InnoDB可用的配置参数。

 

[mysqld]

# You can write your other MySQL server options here

# ...

innodb_data_home_dir =

#

# Data files must be able to hold your data and indexes

innodb_data_file_path = /db/ibdata1:2000M;/dr2/db/ibdata2:2000M:autoextend

#

# Set buffer pool size to 50-80% of your computer‘s memory,

# but make sure on Linux x86 total memory usage is < 2GB

innodb_buffer_pool_size=1G

innodb_additional_mem_pool_size=20M

innodb_log_group_home_dir = /dr3/iblogs

#

# Set the log file size to about 25% of the buffer pool size

innodb_log_file_size=250M

innodb_log_buffer_size=8M

#

innodb_flush_log_at_trx_commit=1

innodb_lock_wait_timeout=50

#

# Uncomment the next line if you want to use it

#innodb_thread_concurrency=5

 

Determining the Maximum Memory Allocation for InnoDB

Warning

 

On 32-bit GNU/Linux x86, be careful not to set memory usage too high. glibc may permit the process heap to grow over thread stacks, which crashes your server. It is a risk if the value of the following expression is close to or exceeds 2GB:

在32位的GNU/Linux x86系统上，注意不要把可用内存设置得过高。glibc版可能允许进程堆堵塞thread stack，这会使得实例崩溃。如果下面表达式的值接近或者超过2GB就会有风险：

 

innodb_buffer_pool_size

+ key_buffer_size

+ max_connections*(sort_buffer_size+read_buffer_size+binlog_cache_size)

+ max_connections*2MB

 

Each thread uses a stack (often 2MB, but only 256KB in MySQL binaries provided by Oracle Corporation.) and in the worst case also uses sort_buffer_size + read_buffer_size additional memory.

每个线程使用一个stack（通常是2MB,但是Oracle公司提供的MySQL只有256KB），最坏的情况是还使用了额外的sort_buffer_size + read_buffer_size内存。

 

Tuning other mysqld server parameters. The following values are typical and suit most users:

调整其他的mysqld实例参数。下面的是比较常见的适用于大多数的用户：

 

[mysqld]

skip-external-locking

max_connections=200

read_buffer_size=1M

sort_buffer_size=1M

#

# Set key_buffer to 5 - 50% of your RAM depending on how much

# you use MyISAM tables, but keep key_buffer_size + InnoDB

# buffer pool size < 80% of your RAM

key_buffer_size=value

 

On Linux, if the kernel is enabled for large page support, InnoDB can use large pages to allocate memory for its buffer pool and additional memory pool. See Section 8.12.5.2, "Enabling Large Page Support".

在Linux平台上，如果内核已经开启对大页的支持，InnoDB也可以为它的buffer pool和additional memory pool使用大页来分配内存。详见Section 8.12.5.2, "Enabling Large Page Support"。

 

14.4.2 Configuring InnoDB for Read-Only Operation

 

You can now query InnoDB tables where the MySQL data directory is on read-only media, by enabling the --innodb-read-only configuration option at server startup.

你现在能够通过开启--innodb-read-only配置参数，把MySQL的数据目录放在只读的媒介上，对InnoDB表进行只读操作。

 

How to Enable

 

To prepare an instance for read-only operation, make sure all the necessary information is flushed to the data files before storing it on the read-only medium. Run the server with change buffering disabled (innodb_change_buffering=0) and do a slow shutdown.

要准备实例只读，要确保所有必要的信息在存储到只读媒介之前都刷新到了数据文件上。是运行实例的时候关闭change buffer(innodb_change_buffering=0)，并进行一个缓慢关闭(do a slow shutdown)。

 

To enable read-only mode for an entire MySQL instance, specify the following configuration options at server startup:

在服务启动的时候可以指定下面的配置参数来开启对于整个MySQL实例的只读模式：

 

• --innodb-read-only=1

 

• If the instance is on read-only media such as a DVD or CD, or the /var directory is not writeable by all: --pid-file=path_on_writeable_media and --event-scheduler=disabled
• 如果实例是在只读媒介上，例如DVD或者CD，又或者/var不可写：--pid-file=path_on_writeable_media and --event-scheduler=disabled

 

Usage Scenarios

 

This mode of operation is appropriate in situations such as:

这种模式适用于以下的场景：

 

• Distributing a MySQL application, or a set of MySQL data, on a read-only storage medium such as a DVD or CD.
• MySQL应用或者一组MySQL数据在只读的存储媒介上，例如DVD or CD。

 

• Multiple MySQL instances querying the same data directory simultaneously, typically in a data warehousing configuration. You might use this technique to avoid bottlenecks that can occur with a heavily loaded MySQL instance, or you might use different configuration options for the various instances to tune each one for particular kinds of queries.
• 多个MySQL实例同时查询同个数据目录，通常在数据仓库里会有这样的配置。你可以使用这种方法来避免加重一个MySQL实例的负载，又或者可以使用对不同的实例 使用不同的配置参数。

 

• Querying data that has been put into a read-only state for security or data integrity reasons, such as archived backup data.
• 因安全性或者数据完整性的原因以只读的状态查询数据，例如归档的备份数据。

 

Note

 

This feature is mainly intended for flexibility in distribution and deployment, rather than raw performance based on the read-only aspect. See Section 8.5.3, "Optimizing InnoDB Read-Only Transactions" for ways to tune the performance of read-only queries, which do not require making the entire server read-only.

将来主要的目的是数据分布部署的灵活性，而不是基于只读方面的性能优势。对于调整只读查询的方式可以查看Section 8.5.3, "Optimizing InnoDB Read-Only Transactions"，这就不需要把整个实例置于只读状态。

 

How It Works

 

When the server is run in read-only mode through the --innodb-read-only option, certain InnoDB features and components are reduced or turned off entirely:

当实例通过--innodb-read-only参数工作在只读模式，一些主要的InnoDB特性和部件将会被关闭：

 

• No change buffering is done, in particular no merges from the change buffer. To make sure the change buffer is empty when you prepare the instance for read-only operation, disable change buffering (innodb_change_buffering=0) and do a slow shutdown first.
• 没有change buffer会工作，尤其是将不再会有来自change buffer的合并。当你准备要把实例配置成只读的时候要确保change buffer是空的，可以挂壁change buffer(innodb_change_buffering=0) 然后进行一个slow shutdown。

 

• There is no crash recovery phase at startup. The instance must have performed a slow shutdown before being put into the read-only state.
• 在启动的时候就不会再有崩溃恢复的阶段。在要把实例方知道只读状态之前要实例必须要执行一个slow shutdown。

 

• Because the redo log is not used in read-only operation, you can set innodb_log_file_size to the smallest size possible (1 MB) before making the instance read-only.
• 因为redo log不会用于只读的操作，你可以在配置实例只读之前设置innodb_log_file_size为最小可能的值(1 MB) 。

 

• All background threads other than I/O read threads are turned off. As a consequence, a read-only instance cannot encounter any deadlocks.
• 除了I/O读线程外所有的后台线程要关闭。因此，一个只读的实例不会遭遇到任何的死锁状况。

 

• Information about deadlocks, monitor output, and so on is not written to temporary files. As a consequence, SHOW ENGINE INNODB STATUS does not produce any output.
• 死锁，监控输出等信息将 不会写入到临时文件。因此，SHOW ENGINE INNODB STATUS将不会产生任何的输出。

 

• If the MySQL server is started with --innodb-read-only but the data directory is still on writeable media, the root user can still perform DCL operations such as GRANT and REVOKE.
• 如果MySQL实例使用--innodb-read-only启动的，但是数据目录任然是在可写的媒介上的，root用户任然可以执行例如GRANT和REVOKE的DCL操作。

 

• Changes to configuration option settings that would normally change the behavior of write operations, have no effect when the server is in read-only mode.
• 修改配置参数的指定通常会改变写操作的行为，但是实例在只读模式下这将不会有效果。

 

• The MVCC processing to enforce isolation levels is turned off. All queries read the latest version of a record, because update and deletes are not possible.
• MVCC处理使用强制隔离级别会被关闭。所有查询都会读最近版本的记录，因为update和delete是不可用的。

 

• The undo log is not used. Disable any settings for the innodb_undo_tablespaces and innodb_undo_directory configuration options.
• undo log不会被使用。将会关闭任何关于innodb_undo_tablespaces和innodb_undo_directory的配置参数。

 

14.4.3 InnoDB Buffer Pool Configuration

 

14.4.3.1 Configuring InnoDB Buffer Pool Prefetching (Read-Ahead)

14.4.3.2 Configuring the Rate of InnoDB Buffer Pool Flushing

14.4.3.3 Making the Buffer Pool Scan Resistant

14.4.3.4 Using Multiple Buffer Pool Instances

14.4.3.5 Preloading the InnoDB Buffer Pool for Faster Restart

14.4.3.6 Tuning InnoDB Buffer Pool Flushing

 

This section provides performance related configuration information for the InnoDB buffer pool. For additional information, see Section 8.10.1, "The InnoDB Buffer Pool".

这一章节提供了InnoDB buffer pool相关的性能的配置信息。另外还可以查看Section 8.10.1, "The InnoDB Buffer Pool"。

 

14.4.3.1 Configuring InnoDB Buffer Pool Prefetching (Read-Ahead)

 

A read-ahead request is an I/O request to prefetch multiple pages in the buffer pool asynchronously, in anticipation that these pages will be needed soon. The requests bring in all the pages in one extent. InnoDB uses two read-ahead algorithms to improve I/O performance:

预读请求是一种I/O的请求，以异步方式从buffer pool中预读取多个将来锁需要的数据页。这个请求会把所有的数据页放到一个extent里面。InnoDB会使用两种预读的算法来改善I/O性能:

 

Linear read-ahead is a technique that predicts what pages might be needed soon based on pages in the buffer pool being accessed sequentially. You control when InnoDB performs a read-ahead operation by adjusting the number of sequential page accesses required to trigger an asynchronous read request, using the configuration parameter innodb_read_ahead_threshold. Before this parameter was added, InnoDB would only calculate whether to issue an asynchronous prefetch request for the entire next extent when it read in the last page of the current extent.

线性(Linear)预读方式是基于那些顺序访问的已经存在在buffer pool里面数据页来预测那些将来可能需要的数据页。当InnoDB在执行一个预读操作的时候，你可以通过使用配置参数innodb_read_ahead_threshold来调整顺序访问数据页的数量来控制触发一个异步读的请求。在添加这个参数之前，InnoDB只会计算是否要在读当前extent的最后一个数据页的时候是否要为下个extent执行异步预读请求。

 

The configuration parameter innodb_read_ahead_threshold controls how sensitive InnoDB is in detecting patterns of sequential page access. If the number of pages read sequentially from an extent is greater than or equal to innodb_read_ahead_threshold, InnoDB initiates an asynchronous read-ahead operation of the entire following extent. It can be set to any value from 0-64. The default value is 56. The higher the value, the more strict the access pattern check. For example, if you set the value to 48, InnoDB triggers a linear read-ahead request only when 48 pages in the current extent have been accessed sequentially. If the value is 8, InnoDB would trigger an asynchronous read-ahead even if as few as 8 pages in the extent were accessed sequentially. You can set the value of this parameter in the MySQL configuration file, or change it dynamically with the SET GLOBAL command, which requires the SUPER privilege.

配置参数innodb_read_ahead_threshold控制了InnoDB顺序数据页访问检测模式的敏感度。如果一个extent里面顺序读取的数据页的数量大于或者等于innodb_read_ahead_threshold定义的数量，InnoDB就会为后面的extent启动异步的预读操作。这个参数的值可以设置为0-64之间的任何值，默认的是56.值越高，访问检测的模式越严格。例如，你设置的值是48，InnoDB只会在当前extent有48个数据页被顺序访问了才会触发一个线性的预读请求。如果值是8，即使extent里面只有8个数据页被顺序访问也会触发一个异步的预读请求。你可以在MySQL的配置文件里设置这个参数，或者使用SET GLOBAL命令动态修改，当然这是需要SUPER权限的。

 

Random read-ahead is a technique that predicts when pages might be needed soon based on pages already in the buffer pool, regardless of the order in which those pages were read. If 13 consecutive pages from the same extent are found in the buffer pool, InnoDB asynchronously issues a request to prefetch the remaining pages of the extent. To enable this feature, set the configuration variable innodb_random_read_ahead to ON.

随机(Random)预读也是基于buffer pool里面的数据页对不久之后需要的数据页进行预测的，但是它不会管buffer pool里面那些数据页被读取的顺序。如果在buffer pool里面看到同一个extent里面有13个连续的数据页，InnoDB就会会这个extent剩下的数据页异步地执行一个预读请求。要开启这个特性，则需要把配置变量innodb_random_read_ahead设置成ON（默认是OFF）。

 

The SHOW ENGINE INNODB STATUS command displays statistics to help you evaluate the effectiveness of the read-ahead algorithm. Statistics include counter information for the Innodb_buffer_pool_read_ahead, Innodb_buffer_pool_read_ahead_evicted, and Innodb_buffer_pool_read_ahead_rnd global status variables. This information can be useful when fine-tuning the innodb_random_read_ahead setting.

SHOW ENGINE INNODB STATUS命令显示的统计信息能够帮助你评估预读算法的效能。统计信息包含了Innodb_buffer_pool_read_ahead, Innodb_buffer_pool_read_ahead_evicted, and Innodb_buffer_pool_read_ahead_rnd这几个全局状态变量的计数信息。这些信息对微调innodb_random_read_ahead的设定是非常有用的。

 

For more information about I/O performance, see Section 8.5.8, "Optimizing InnoDB Disk I/O" and Section 8.12.3, "Optimizing Disk I/O".

更多关于I/O性能的信息可以查看Section 8.5.8, "Optimizing InnoDB Disk I/O" and Section 8.12.3, "Optimizing Disk I/O"。

 

14.4.3.2 Configuring the Rate of InnoDB Buffer Pool Flushing

 

InnoDB performs certain tasks in the background, including flushing of dirty pages (those pages that have been changed but are not yet written to the database files) from the buffer pool. InnoDB flushes buffer pool pages if the percentage of dirty pages in the buffer pool exceeds innodb_max_dirty_pages_pct. As of MySQL 5.7.5, InnoDB flushes buffer pool pages if the percentage of dirty pages in the buffer pool is greater than or equal to innodb_max_dirty_pages_pct (Bug#13029450).

InnoDB会在后台执行某些任务，这其中就包括从buffer pool中刷新脏数据页（已经被修改了的但是还未写入到数据库文件的数据页）。如果buffer pool中的脏数据的百分比达到innodb_max_dirty_pages_pct所设定的值InnoDB就会刷新buffer pool中的数据页。从MySQL5.7.5开始，如果buffer pool中脏数据的百分比大于或者等于innodb_max_dirty_pages_pct所设定的值InnoDB就会刷新buffer pool的数据页(Bug#13029450)。

 

InnoDB uses an algorithm to estimate the required rate of flushing, based on the speed of redo log generation and the current rate of flushing. The intent is to smooth overall performance by ensuring that buffer flush activity keeps up with the need to keep the buffer pool "clean". Automatically adjusting the rate of flushing can help to avoid sudden dips in throughput, when excessive buffer pool flushing limits the I/O capacity available for ordinary read and write activity.

InnoDB会基于redo log产生的速度和当前刷新的比例来估算请求的刷新比例。这样做的目的是通过确保buffer刷新的动作让buffer pool保持"干净(clean)"来平滑整体的性能。自动调整刷新比例能够在过多的buffer pool刷新操作限制普通读写操作的I/O能力时候避免突然的吞吐量的下降。

 

InnoDB uses its log files in a circular fashion. Before reusing a portion of a log file, InnoDB flushes to disk all dirty buffer pool pages whose redo entries are contained in that portion of the log file, a process known as a sharp checkpoint. If a workload is write-intensive, it generates a lot of redo information, all written to the log file. If all available space in the log files is used up, a sharp checkpoint occurs, causing a temporary reduction in throughput. This situation can happen even though innodb_max_dirty_pages_pct is not reached.

InnoDB以循环的方式使用日志文件。在再利用一个日志文件的一部分之前，InnoDB会刷新这部分redo log上的脏buffer pool数据到磁盘上，这个操作称之为sharp checkpoint。如果负载是写密集型的，会产生大量的redo信息，这些信息都会写入到日志文件里。如果日志文件里面可用空间都用完了，就会发生sharp checkpoint，这就会引起临时性的吞吐量下降。即使还没有达到innodb_max_dirty_pages_pct设定的值这种情况还是会发生的。

 

InnoDB uses a heuristic-based algorithm to avoid such a scenario, by measuring the number of dirty pages in the buffer pool and the rate at which redo is being generated. Based on these numbers, InnoDB decides how many dirty pages to flush from the buffer pool each second. This self-adapting algorithm is able to deal with sudden changes in the workload.

InnoDB使用了一种基于启发式的算法来避免这一情况：测量buffer pool中脏数据页的数量以及redo产生的比例。基于这些数字，InnoDB决定每秒钟要重buffer pool中刷新多少的脏数据页。这种自适应的算法能够处理负载突然变化的情况。

 

Internal benchmarking has also shown that this algorithm not only maintains throughput over time, but can also improve overall throughput significantly.

内部标杆也显示出这种算法不仅仅能够维护不同时间的吞吐量，还能显著改善整体的吞吐量。

 

Because adaptive flushing can significantly affect the I/O pattern of a workload, the innodb_adaptive_flushing configuration parameter lets you turn off this feature. The default value for innodb_adaptive_flushing is TRUE, enabling the adaptive flushing algorithm. You can set the value of this parameter in the MySQL option file (my.cnf or my.ini) or change it dynamically with the SET GLOBAL command, which requires the SUPER privilege.

因为自适应刷新能够明显影响工作负载的I/O模式，innodb_adaptive_flushing配置参数能够让你关闭这一特性。innodb_adaptive_flushing默认的值是TRUE，是开启是适应刷新算法的。你可以在MySQL的配置文件或者SET GLOBAL命名动态地设置这个参数的值，当然这是需要SUPER权限的。

 

For more information about InnoDB I/O performance, see Section 8.5.8, "Optimizing InnoDB Disk I/O".

更多关于InnoDB I/0性能的信息可以查看Section 8.5.8, "Optimizing InnoDB Disk I/O"。

 

14.4.3.3 Making the Buffer Pool Scan Resistant

 

Rather than using a strictly LRU algorithm, InnoDB uses a technique to minimize the amount of data that is brought into the buffer pool and never accessed again. The goal is to make sure that frequently accessed ("hot") pages remain in the buffer pool, even as read-ahead and full table scans bring in new blocks that might or might not be accessed afterward.

相比使用严格的LRU算法，InnoDB会使用一种算法来最小化把那些不会再被访问的数据带出buffer pool。主要的目标是当预读和全表扫描会带进来一些可能不会再访问的新数据页的时候，还要确保那些频繁访问的（"热"）数据页保留在buffer pool里。

 

Newly read blocks are inserted into the middle of the LRU list. All newly read pages are inserted at a location that by default is 3/8 from the tail of the LRU list. The pages are moved to the front of the list (the most-recently used end) when they are accessed in the buffer pool for the first time. Thus pages that are never accessed never make it to the front portion of the LRU list, and "age out" sooner than with a strict LRU approach. This arrangement divides the LRU list into two segments, where the pages downstream of the insertion point are considered "old" and are desirable victims for LRU eviction.

最近读取的数据页会插入到LRU列表的中间位置。所有新读取的数据页会被插入到LRU列表尾部的默认的3/8的位置。当这些buffer pool中的数据页第一次被访问的时候会移动到列表的前部（最近使用的尾端）。因此不再使用的数据页就不会到LRU列表的前部，而且相比严格的LRU算法会更早地"淡"出列表。这种布置把LRU列表分割成了两部分，插入点下游的数据页会被看成"旧"的数据而被驱逐出LRU列表。

 

For an explanation of the inner workings of the InnoDB buffer pool and the specifics of its LRU replacement algorithm, see Section 8.10.1, "The InnoDB Buffer Pool".

对于InnoDB buffer pool内部工作原理和其LRU替换算法的细节，可以查看Section 8.10.1, "The InnoDB Buffer Pool"。

 

You can control the insertion point in the LRU list, and choose whether InnoDB applies the same optimization to blocks brought into the buffer pool by table or index scans. The configuration parameter innodb_old_blocks_pct controls the percentage of "old" blocks in the LRU list. The default value of innodb_old_blocks_pct is 37, corresponding to the original fixed ratio of 3/8. The value range is 5 (new pages in the buffer pool age out very quickly) to 95 (only 5% of the buffer pool is reserved for hot pages, making the algorithm close to the familiar LRU strategy).

你可以控制LRU列表的插入点，并选择让InnoDB是通过表还是索引扫描来把相同的优化数据页带入到buffer pool里。控制参数innodb_old_blocks_pct控制了LRU列表的"旧"数据页的百分比，默认值是37，相当于原始的3/8。这个值可以设定到5（buffer pool里的新数据页非常快就会被清除出去）到95（buffer pool里面只有 5%保留为热数据页，使得这种算法接近于通常的LRU方式）之间。

 

The optimization that keeps the buffer pool from being churned by read-ahead can avoid similar problems due to table or index scans. In these scans, a data page is typically accessed a few times in quick succession and is never touched again. The configuration parameter innodb_old_blocks_time specifies the time window (in milliseconds) after the first access to a page during which it can be accessed without being moved to the front (most-recently used end) of the LRU list. The default value of innodb_old_blocks_time is 1000. Increasing this value makes more and more blocks likely to age out faster from the buffer pool.

通过预读预防buffer pool被搅拌的优化方式可以避免因表或者索引扫描带来类似的问题。在这些扫描里，一个数据页通常会被接连几次访问，而在那之后就不再会被访问了。配置参数innodb_old_blocks_time指定了一个数据页在第一次访问之后多久（毫秒级）而不会移动到LRU列表的前部（最近使用的尾端），默认值是1000。增加这个值会使得越来越多的数据页迅速从buffer pool里淡出去。

 

Both innodb_old_blocks_pct and innodb_old_blocks_time are dynamic, global and can be specified in the MySQL option file (my.cnf or my.ini) or changed at runtime with the SET GLOBAL command. Changing the setting requires the SUPER privilege.

innodb_old_blocks_pct and innodb_old_blocks_time都是动态的，可以在MySQL的配置文件里指定或者用SET GLOBAL命令来设置，当让也是要有SUPER权限的。

 

To help you gauge the effect of setting these parameters, the SHOW ENGINE INNODB STATUS command reports additional statistics. The BUFFER POOL AND MEMORY section looks like:

为了帮助你测量这些参数的影响，可以通过SHOW ENGINE INNODB STATUS命令来列出额外的统计信息。BUFFER POOL AND MEMORY章节的内容如下：

 

Total memory allocated 1107296256; in additional pool allocated 0

Dictionary memory allocated 80360

Buffer pool size 65535

Free buffers 0

Database pages 63920

Old database pages 23600

Modified db pages 34969

Pending reads 32

Pending writes: LRU 0, flush list 0, single page 0

Pages made young 414946, not young 2930673

1274.75 youngs/s, 16521.90 non-youngs/s

Pages read 486005, created 3178, written 160585

2132.37 reads/s, 3.40 creates/s, 323.74 writes/s

Buffer pool hit rate 950 / 1000, young-making rate 30 / 1000 not 392 / 1000

Pages read ahead 1510.10/s, evicted without access 0.00/s

LRU len: 63920, unzip_LRU len: 0

I/O sum[43690]:cur[221], unzip sum[0]:cur[0]

 

• Old database pages is the number of pages in the "old" segment of the LRU list.
• Old database pages指的是LRU列表里"旧"的段的数据页的数量。

 

• Pages made young and not young is the total number of "old" pages that have been made young or not respectively.
• Pages made young and not young分别指的是变得（or not）新的"旧"数据页的数量。

 

• youngs/s and non-young/s is the rate at which page accesses to the "old" pages have resulted in making such pages young or otherwise respectively since the last invocation of the command.
• youngs/s and non-young/s表示自上次调用以来访问旧数据页而导致这些数据页变新（or not）的比例。

 

• young-making rate and not provides the same rate but in terms of overall buffer pool accesses instead of accesses just to the "old" pages.
• young-making rate and not提供了相同的比例，但是只的是整个的buffer pool而不只是访问的旧的数据页。

 

Note

 

Per second averages provided in InnoDB Monitor output are based on the elapsed time between the current time and the last time InnoDB Monitor output was printed.

InnoDB Monitor输出的每秒平均值是基于当前的时间和上次InnoDB Monitor打印输出的所消逝的时间。

 

Because the effects of these parameters can vary widely based on your hardware configuration, your data, and the details of your workload, always benchmark to verify the effectiveness before changing these settings in any performance-critical or production environment.

因为这些配置基于你的硬件配置，数据，工作负载的细节，影响是非常广泛的，所以要在任何性能关键的生产环境更改这些设置之前，要进行基准测试来验证有效性。

 

In mixed workloads where most of the activity is OLTP type with periodic batch reporting queries which result in large scans, setting the value of innodb_old_blocks_time during the batch runs can help keep the working set of the normal workload in the buffer pool.

对于混合型的工作负载，绝大多数的活动是OLTP型的，以及周期性的有大量扫描的批量表报查询，可以在运行批处理的时候设置innodb_old_blocks_time的值可以让结果集在普通的工作负载下也保持在buffer pool里面。

 

When scanning large tables that cannot fit entirely in the buffer pool, setting innodb_old_blocks_pct to a small value keeps the data that is only read once from consuming a significant portion of the buffer pool. For example, setting innodb_old_blocks_pct=5 restricts this data that is only read once to 5% of the buffer pool.

当要扫描大表而无法全部读放入到buffer pool的时候，可以把innodb_old_blocks_pct设置成一个教小的值来阻止那些只会读一次的数据进入到buffer pool消耗很大的那部分。例如，设置innodb_old_blocks_pct=5就可以约束那些只会读一次的数据只会占到buffer pool的5%。

 

When scanning small tables that do fit into memory, there is less overhead for moving pages around within the buffer pool, so you can leave innodb_old_blocks_pct at its default value, or even higher, such as innodb_old_blocks_pct=50.

当要扫描的小表能够放入到内存的时候，把数据页移动到buffer pool里几乎没有损耗，所以你可以把 innodb_old_blocks_pct设置成默认的值，或者更高，例如innodb_old_blocks_pct=50。

 

The effect of the innodb_old_blocks_time parameter is harder to predict than the innodb_old_blocks_pct parameter, is relatively small, and varies more with the workload. To arrive at an optimal value, conduct your own benchmarks if the performance improvement from adjusting innodb_old_blocks_pct is not sufficient.

innodb_old_blocks_time参数的影响相比innodb_old_blocks_pct更难预测，相对也更小，对于不同的工作负载变化更大。如果调整innodb_old_blocks_pct性能改善不明显，对innodb_old_blocks_time的修改则要更慎重，最好进行一定的基准测试。

 

For more information about the InnoDB buffer pool, see Section 8.10.1, "The InnoDB Buffer Pool".

更多关于InnoDB buffer pool的信息可以查看Section 8.10.1, "The InnoDB Buffer Pool"。

 

14.4.3.4 Using Multiple Buffer Pool Instances

 

For systems with buffer pools in the multi-gigabyte range, dividing the buffer pool into separate instances can improve concurrency, by reducing contention as different threads read and write to cached pages. This feature is typically intended for systems with a buffer pool size in the multi-gigabyte range. Multiple buffer pool instances are configured using the innodb_buffer_pool_instances configuration option, and you might also adjust the innodb_buffer_pool_size value.

对于一个buffer pool有好几个G的系统，把buffer pool分割成几个实例能够提升并发性，减少不同线程读写cache中数据页的争用。这种特性通常是针对于哪种buffer pool有好几个G的系统。使用innodb_buffer_pool_instances配置参数配置多个buffer pool instance，你也可以调整innodb_buffer_pool_size的值。

 

When the InnoDB buffer pool is large, many data requests can be satisfied by retrieving from memory. You might encounter bottlenecks from multiple threads trying to access the buffer pool at once. You can enable multiple buffer pools to minimize this contention. Each page that is stored in or read from the buffer pool is assigned to one of the buffer pools randomly, using a hashing function. Each buffer pool manages its own free lists, flush lists, LRUs, and all other data structures connected to a buffer pool, and is protected by its own buffer pool mutex.

当InnoDB buffer pool非常大的时候，要满足很多个对内存里的数据请求。多个线程试图在同一时刻访问buffer pool时可能会遇到瓶颈。你可以开启多个buffer pool来最小化这种争用。每个读取或者存储到buffer pool的数据页会用hash函数随机分配到一个buffer pool里。每个buffer pool读管理它自己的空闲列表，刷新列表，LRU列表，以及连接到buffer pool的其他数据结构，而且它也由它自己的buffer pool mutex来保护。

 

To enable multiple buffer pool instances, set the innodb_buffer_pool_instances configuration option to a value greater than 1 (the default) up to 64 (the maximum). This option takes effect only when you set the innodb_buffer_pool_size to a size of 1 gigabyte or more. The total size you specify is divided among all the buffer pools. For best efficiency, specify a combination of innodb_buffer_pool_instances and innodb_buffer_pool_size so that each buffer pool instance is at least 1 gigabyte.

要开启多个buffer pool instance，可以把innodb_buffer_pool_instances配置参数设置到1（默认）到64（最大）。这个参数只有在你把innodb_buffer_pool_size设置得大于等于1G的时候才会有影响。你所指定的数值是分割整个buffer pool的数量。要达到最佳的效果，innodb_buffer_pool_instances and innodb_buffer_pool_size值的设定要使得每个buffer pool instance最少有1G。

 

For more information about the InnoDB buffer pool, see Section 8.10.1, "The InnoDB Buffer Pool".

更多关于InnoDB buffer pool的信息可以查看Section 8.10.1, "The InnoDB Buffer Pool"。

 

14.4.3.5 Preloading the InnoDB Buffer Pool for Faster Restart

 

To avoid a lengthy warmup period after restarting the server, particularly for instances with large InnoDB buffer pools, you can save the InnoDB buffer pool state at server shutdown and restore the buffer pool to the same state at server startup.

为了避免在实例重启后冗长的数据预热的过程，特别是对于那些有巨大InnoDB buffer pool的实例，你可以在实例shutdown之前保存InnoDB buffer pool的状态，实例再次启动之后再恢复这个状态。

 

After you restart a busy server, there is typically a warmup period with steadily increasing throughput, as disk pages that were in the InnoDB buffer pool are brought back into memory (as the same data is queried, updated, and so on). The ability to restore the buffer pool to the pre-shutdown state shortens the warmup period as it allows you to immediately reload disk pages that were in the buffer pool before the restart, rather than waiting for DML operations to access the corresponding rows. The I/O requests can be performed in large batches, making the overall I/O faster. The page loading happens in the background, and does not delay the database startup.

在你重启一个繁忙的实例之后，通常周的数据预热会增加吞吐量，InnoDB buffer pool在磁盘上的数据页需要带回到内存里（查询修改的数据）。保存实例关闭之前buffer pool的状态可以缩短数据预热的时间，这能让你在实例启动之前立刻把磁盘上的数据页重新加载到buffer pool里，而不需要等待DML操作去访问相关的行记录。这里的I/O请求都是批量处理的，这会使得整体的I/O速度更快。数据页的加载是在后台运行的，所以不会延迟数据库的启动时间。

 

In addition to saving the buffer pool state at shutdown and restoring it at startup, you can also save and restore the buffer pool state at any time, while the server is running. For example, you