- 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来加载特定的地址