14.4 InnoDB Configuration :InnoDB 配置: 14.4 InnoDB Configuration 14.4.1 InnoDB Startup Configuration 14.4.2 Configuring InnoDB for Read-Only Operation 14.4.3 InnoDB Buffer Pool Configuration 14.4.4 Configuring the Memory Allocator for InnoDB 14.4.5 Configuring InnoDB Change Buffering 14.4.6 Configuring Thread Concurrency for InnoDB 14.4.7 Configuring the Number of Background InnoDB I/O Threads 14.4.8 Configuring the InnoDB Master Thread I/O Rate 14.4.9 Configuring Spin Lock Polling 14.4.10 Configuring InnoDB Purge Scheduling 14.4.11 Configuring Optimizer Statistics for InnoDB 14.4.12 Configuring the Merge Threshold for Index Pages 14.4.1 InnoDB Startup Configuration InnoDB 启动配置: 第一个决定是让InnoDB 配置涉及的配置数据文件,log files,page size,和内存buffers. 建议你定义数据文件,log file和配置size 配置在你创建InnoDB 实例前。 修改数据文件或者log file 配置在InnoDB 实例被创建可能涉及不平凡的过程, 页的大小只能在InnoDB 实例被首次初始化的时候定义: 除了这些主题,这个章节提供信息关于制定InnoDB options 在一个配置文件, 查看InnoDB 的初始化信息, 重要的存储问题。 Specifying Options in a MySQL Configuration File Viewing InnoDB Initialization Information Important Storage Considerations System Tablespace Data File Configuration InnoDB Log File Configuration InnoDB Page Size Configuration InnoDB Undo Tablespace Configuration InnoDB Memory Configuration Specifying Options in a MySQL Configuration File 制定选项在MySQL 配置文件: 因为MySQL 使用数据文件,日志文件,和页大小配置设置在InnoDB 实例初始化的时候, 它是推荐你定义那些配置设置在你的配置文件 在MySQL 启动时读取, 首次初始InnoDB之前。 InnoDB 在Mysql server 启动时被初始化,第一次初始化通常发生在MySQL 第一次启动时 你可以设置InnoDB 选择在[mysqld] 组 [mysqld] basedir=/usr/local/mysql datadir=/data01/mysql socket=/data01/mysql/mysql.sock user=mysql slow_query_log=ON long_query_time=2 为了确保mysqld 只能从指定文件读取选项,使用 --defaults-file option 作为第一个选项在命令行上, 再你启动server的时候 mysqld --defaults-file=path_to_configuration_file 查看InnoDB 初始信息: 查看InnoDB 初始信息在启动阶段,启动mysqld从命令行提示符,当mysqld从命令行启动,初始化的信息打印到 控制台: [root@wx03 ~]# mysqld 2016-06-22 11:00:21 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2016-06-22 11:00:21 14458 [Note] Plugin 'FEDERATED' is disabled. 2016-06-22 11:00:21 14458 [Note] InnoDB: Using atomics to ref count buffer pool pages 2016-06-22 11:00:21 14458 [Note] InnoDB: The InnoDB memory heap is disabled 2016-06-22 11:00:21 14458 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 2016-06-22 11:00:21 14458 [Note] InnoDB: Memory barrier is not used 2016-06-22 11:00:21 14458 [Note] InnoDB: Compressed tables use zlib 1.2.3 2016-06-22 11:00:21 14458 [Note] InnoDB: Using CPU crc32 instructions 2016-06-22 11:00:21 14458 [Note] InnoDB: Initializing buffer pool, size = 128.0M 2016-06-22 11:00:21 14458 [Note] InnoDB: Completed initialization of buffer pool 2016-06-22 11:00:21 14458 [Note] InnoDB: Highest supported file format is Barracuda. 2016-06-22 11:00:21 14458 [Note] InnoDB: 128 rollback segment(s) are active. 2016-06-22 11:00:21 14458 [Note] InnoDB: Waiting for purge to start 2016-06-22 11:00:21 14458 [Note] InnoDB: 5.6.22 started; log sequence number 4240043348 2016-06-22 11:00:21 14458 [Note] Server hostname (bind-address): '*'; port: 3306 2016-06-22 11:00:21 14458 [Note] IPv6 is not available. 2016-06-22 11:00:21 14458 [Note] - '0.0.0.0' resolves to '0.0.0.0'; 2016-06-22 11:00:21 14458 [Note] Server socket created on IP: '0.0.0.0'. 2016-06-22 11:00:21 14458 [Note] Event Scheduler: Loaded 0 events 2016-06-22 11:00:21 14458 [Note] mysqld: ready for connections. Version: '5.6.22-log' socket: '/data01/mysql/mysql.sock' port: 3306 Source distribution Important Storage Considerations 重要的存储考虑: 检查下面的存储相关的注意事项在进行你的启动配置前: 1. 在某些情况下,数据库性能改善如果数据不全部放在同一个相同的磁盘。 放置 log files 到一个不同的磁盘相比数据 对于性能是有好处的。 比如,你可以防止system tablespace 数据文件和log files 到不同的disks. 你也可以使用raw disk 分区(裸设备) 对于InnoDB data files，可以加速I/O. 2.InnoDB 是一个事务-安全(ACID 标准) 存储引擎对于MySQL 有提交,回滚,和实例恢复功能来保护用户数据 。 然而,它不能这样做如果依赖的操作系统或者硬件不按宣传的做。 很多操作系统或者磁盘子系统可能会延迟或者重新排序写操作来提高性能。 在一些操作系统上, fsync() system call 需要等待直到所有的非写入数据对于一个文件已经被刷新 可能实际返回的 在数据被收入到存储器 正因为这个,一个操作系统crash 或者断电可能破坏最近提交的数据,或者最坏的情况下, 甚至破坏数据库因为写操作已经重新安排了。 如果数据完整性对你是重要的, 执行一些拔插头的测试在应用生产前。 在Linux下,建议关闭回写缓存。 2.对于InnoDB的恢复功能来保护用户的数据,InnoDB 使用一个文件刷新技术调用一个结构称为doublewrite buffer, 默认启用(innodb_doublewrite=ON). doublewrite buffer 增加安全来恢复当crash或者断电, 来改善性能对于大部分的Unix 通过降低fsync() operations. 如果你的数据可靠性是一个考虑,不要配置InnoDB 来使用数据文件或者log files 在NFS文件上。 System Tablespace Data File Configuration 系统表空间文件配置: 系统表空间数据文件是通过使用innodb_data_file_path and innodb_data_home_dir 配置选项 innodb_data_file_path 配置选项是用于配置InnoDB 系统表空间数据文件。 mysql> show variables like '%innodb_data_file_path%'; +-----------------------+------------------------+ | Variable_name | Value | +-----------------------+------------------------+ | innodb_data_file_path | ibdata1:12M:autoextend | +-----------------------+------------------------+ 1 row in set (0.00 sec) innodb_data_file_path 的值是一个或者而多个数据文件的一个列表。 这个设置配置是一个单独的12MB的文件叫做 ibdata1是自动增长的。 文件的路径没有给定,默认,InnoDB 创建它在MySQL 数据目录下。 大小可以是K,M.G表示KB,MB或GB 一个表空间包含固定大小50MB数据文件命名为ibdata1和一个50MB的自动增长的文件ibdata2 在你的数据目录 可以配置为: [mysqld] innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend InnoDB 创建表空间文件 在MySQL 数据目录默认清下下(datadir) 显示的指定位置,使用 innodb_data_home_dir 选项。 比如,创建2个文件命名为ibdata1 and ibdata2 在目录/myibdata 配置InnoDB如下: [mysqld] innodb_data_home_dir = /myibdata innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend 注意: InnoDB 不创建目录,确保/myibdata 目录存在在你启动server前 确保 MySQL server 有足够的权限访问创建的文件在数据目录. InnoDB 组织目录路径对于每个文件通过文本把 innodb_data_home_dir 的值到数据文件名字, 增加一个path 名称分隔符(斜杠或者反斜杠)在值之间。 如果你指定innodb_data_home_dir 作为一个空的字符串, 你可以指定绝对的路径对于数据文件在 innodb_data_file_path列表,下面的例子是相当于前面那个: [mysqld] innodb_data_home_dir = innodb_data_file_path=/ibdata/ibdata1:50M;/ibdata/ibdata2:50M:autoextend InnoDB Log File Configuration 日志文件配置: 默认,InnoDB 创建2个48MB日志文件 在MySQL 的数据目录 叫做 ib_logfile0 and ib_logfile1. 下面的选项用于修改默认的配置: [root@wx03 mysql]# pwd /data01/mysql [root@wx03 mysql]# ls -ltr ib ibdata1 ib_logfile0 ib_logfile1 [root@wx03 mysql]# ls -ltr ib_logfile* -rw-rw---- 1 mysql mysql 50331648 Jun 8 20:52 ib_logfile1 -rw-rw---- 1 mysql mysql 50331648 Jun 22 13:50 ib_logfile0 mysql> show variables like '%innodb_log_group_home_dir%'; +---------------------------+-------+ | Variable_name | Value | +---------------------------+-------+ | innodb_log_group_home_dir | ./ | +---------------------------+-------+ 1 row in set (0.00 sec) innodb_log_group_home_dir 定义InnoDB log 文件(redo logs)的路径。 如果这个选项不改变,InnoDB log files 是创建在MySQL 数据目录下。 你可以设置选项来防止InnoDB log files 到不同的物理存储位置相比InnoDB 数据目录 来避免潜在的I/O资源冲突 [mysqld] innodb_log_group_home_dir = /dr3/iblogs innodb_log_files_in_group 定义 日志组里的日志数量。默认和推荐的值为2： mysql> show variables like '%innodb_log_files_in_group%'; +---------------------------+-------+ | Variable_name | Value | +---------------------------+-------+ | innodb_log_files_in_group | 2 | +---------------------------+-------+ 1 row in set (0.00 sec) innodb_log_file_size 定义了每个日志文件的大小。 组合的日志文件大小(innodb_log_file_size * innodb_log_files_in_group） 不能超过一个最大值, 略小于512GB。 一对255GB的日志文件,比如,接近极限但是不能超过它。 默认的日志文件大小是48MB,合理的值范围是从4MB到Buffer pool的1/N-th 范围, N是 日志组里日志文件的数目。 值越大,更少的检查点flush 活动呗需要 在buffer pool， 节省磁盘I/O. InnoDB Undo Tablespace Configuration Undo 表空间配置: 默认情况下, InnoDB undo logs 是system 表空间的一部分。然而, 你可以选择存储InnoDB undo logs 在一个或者多个单独的undo 表空间,通常在不同的存储设备。 innodb_undo_directory 配置选项定义 mysql> show variables like '%innodb_undo_directory%'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | innodb_undo_directory | . | +-----------------------+-------+ 1 row in set (0.00 sec) 这个选项用于结合 innodb_undo_logs and innodb_undo_tablespaces options InnoDB Page Size Configuration innodb_page_size 选项制定 InnoDB 表空间的page 大小。 这个值是当实例被创建,然后保持不变,正确的值是16k (the default), 8k, and 4k. mysql> show variables like '%innodb_page_size%'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | innodb_page_size | 16384 | +------------------+-------+ 1 row in set (0.00 sec) 默认16K 默认大小16K是适合一个很宽的工作负载, 特别对于查询涉及表扫描和DML 操作调用批量更新 小的page sizes 可能变的更加有效对于OLTP 负载 涉及很多的小的写请求, 竞争可能发生当一个单独的page 包含很多行。 小的页 可能有效的对于SSD 存储设备,典型使用小的block size. InnoDB Memory Configuration 内存配置: MySQL 分配内容到各个caches 和buffers 来改善数据库操作的性能。 当分配内存给InnoDB,总是考虑操作系统的内存需要,内存分配给其他应用, 内存分配给其他MySQL buffer和caches. 比如,如果你使用MyISAM tables, 考虑key buffer的内存分配 InnoDB 的buffer 配置使用下面的参数: mysql> show variables like '%innodb_buffer_pool_size%'; +-------------------------+-----------+ | Variable_name | Value | +-------------------------+-----------+ | innodb_buffer_pool_size | 134217728 | +-------------------------+-----------+ 1 row in set (0.00 sec) innodb_buffer_pool_size 定义了 buffer pool的大小, 这是内存区域,持有cached 数据对于 InnoDB 表,indexes和其他辅助buffers. buffer pool 的尺寸是重要的对于系统性能, 它通常建议设置innodb_buffer_pool_size 配置为50到75的系统内存百分比。 在具有大量内存的系统上, 你可以改善并发通过划分buffer pool 到多个buffer pool实例。 buffer pool的实例是由innodb_buffer_pool_instances控制 mysql> show variables like '%innodb_buffer_pool_instances%'; +------------------------------+-------+ | Variable_name | Value | +------------------------------+-------+ | innodb_buffer_pool_instances | 8 | +------------------------------+-------+ 1 row in set (0.00 sec) 默认,InnoDB 创建一个buffer pool实例。 innodb_additional_mem_pool_size 定义 内存pool的大小用于存储数据字典信息和其他内部数据结构。 你应用里的表越多,这里分配的内存越多。 如果InnoDB 在这个pool里的内存耗尽,它开始分配内存从操作系统 ,写告警日志到MySQL error log. mysql> show variables like '%innodb_additional_mem_pool_size%'; +---------------------------------+---------+ | Variable_name | Value | +---------------------------------+---------+ | innodb_additional_mem_pool_size | 8388608 | +---------------------------------+---------+ 1 row in set (0.00 sec) 默认8MB mysql> show variables like '%innodb_log_buffer_size%'; +------------------------+---------+ | Variable_name | Value | +------------------------+---------+ | innodb_log_buffer_size | 8388608 | +------------------------+---------+ 1 row in set (0.00 sec) innodb_log_buffer_size 定义了 buffer 的大小,InnoDB 用于写log 文件。 默认是8MB, 一个大的log buffer 可以让大的事务运行而不需要写log 到磁盘在事务提交前。 如果你有事务 提交,插入,或者删除很多记录