innodb_log_buffer_size和innodb_buffer_pool_size参数说明

innodb_log_buffer_size

Command-Line Format --innodb_log_buffer_size=#
System Variable Name    innodb_log_buffer_size
Variable Scope  Global
Dynamic Variable    No
Permitted Values    Type    integer
Default 8388608
Min Value   262144
Max Value   4294967295

The size in bytes of the buffer that InnoDB uses to write to the log files on disk.
The default value is 8MB. 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.
For general I/O tuning advice, see Section 8.5.8, “Optimizing InnoDB Disk I/O”.

buffer 大小单位bytes,InnoDB 用于写log files 到磁盘,默认值是8M,

一个大的log buffer 让一个大的事务运行不需要写日志到磁盘在事务提交前,因此,如果你有事务比如update,insert或者delete 很多的记录, 让log buffer 足够大来节约磁盘I/O.


对于常规的I/O 调优建议,see Section 8.5.8, “Optimizing InnoDB Disk I/O”.


The size in bytes of the buffer pool, the memory area where InnoDB caches table and index data. The default value is 128MB.

The maximum value depends on the CPU architecture; the maximum is 4294967295 (232-1) on 32-bit systems and 18446744073709551615 (264-1) on 64-bit systems.

On 32-bit systems, the CPU architecture and operating system may impose a lower practical maximum size than the stated maximum.



buffer pool 的大小单位字节, InnoDB 的内存区域用于cached 表和索引数据,默认是128MB。

最大值依赖CPU 结构, maximum是 4294967295 (232-1) 在32位系统上  在64位系统上是 18446744073709551615 (264-1)

在32位系统上,CPU 结构和操作系统可能施加较低的实际的最大值 相比规定的最大值

When the size of the buffer pool is greater than 1GB, setting innodb_buffer_pool_instances to a value greater than 1 can improve the scalability on a busy server.

The larger you set this value, the less disk I/O is needed to access the same data in tables more than once. On a dedicated database server,
you might set this to up to 80% of the machine physical memory size. Be prepared to scale back this value if these other issues occur:

Competition for physical memory might cause paging in the operating system.


当 buffer pool 大小大于1GB,设置innodb_buffer_pool_instances 大于1来改善 可扩展性在一个繁忙的server上



值你设置的越大,越少的disk I/O 访问相同的数据, 在一个专用的database server.

你可以设置最多到80%的机器物理内存大小，物理内存的竞争可能导致操作系统中的交换

InnoDB reserves additional memory for buffers and control structures, so that the total allocated space is approximately 10% greater than the specified size.

The address space must be contiguous, which can be an issue on Windows systems with DLLs that load at specific addresses.

The time to initialize the buffer pool is roughly proportional to its size. On large installations, this initialization time might be significant.

For example, on a modern Linux x86_64 server, initialization of a 10GB buffer pool takes approximately 6 seconds. See Section 8.10.1, “The InnoDB Buffer Pool”.

InnoDB储备额外的内存用于缓冲区和控制结构, 因此总共分配的空间是大约10%大于指定值。

地址空间必须是连续的, 在Windows上可能是一个问题用DLLs来加载特定的地址