初识 MySQL 5.5 新功能、参数
摘要：
MySQL5.5 针对 MySQL5.1 各个方面提升了很多，特别在性能和一些新参数上面，现在看看大致提升了哪些方面，不全请补充。
一：性能上面的提升
1，默认存储引擎的改变，从默认的MyISAM 到 INNODB。
2，INNODB Plugin 的改变：5.1虽然也支持Innodb plugin，但是需要在My.cnf 上添加：下面2行参数。
ignore-builtin-innodb
plugin-load=innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;innodb_locks=ha_innodb_plugin.so;innodb_cmp=ha_innodb_plugin.so; innodb_cmp_reset=ha_innodb_plugin.so;innodb_cmpmem=ha_innodb_plugin.so;innodb_cmpmem_reset=ha_innodb_plugin.so; INNODB_LOCK_WAITS=ha_innodb_plugin.so
而5.5.X不需要添加，默认就是支持Innodb Plugin。
3，充分利用CPU多核的能力：
在 MySQL5.1.X 版本，innodb_file_io_threads 参数默认是 4，这个参数的作用是,innodb 使用后台线程处理数据页上的读写 I/O(输入输出)请求。
在 MySQL5.5.X 版本,或者在 Innodb Plugin1.0.4 以后,用两个新的参数取代了 innodb_file_io_threads,那就是innodb_read_io_threads 和 innodb_write_io_threads,你就可以根据你的 CPU 核数来更改,默认是 4。
注:这两个参数不支持动态改变,需要把该参数加入到 my.cnf 里,修改完后重启 MySQL 服务,允许值的范围从 1-64。
调整后,你可以用命令 show engine innodb status\G;来查看:
View Code
show engine innodb status -------- FILE I/O -------- I/O thread 0 state: waiting for completed aio requests (insert buffer thread) I/O thread 1 state: waiting for completed aio requests (log thread) I/O thread 2 state: waiting for completed aio requests (read thread) I/O thread 3 state: waiting for completed aio requests (read thread) I/O thread 4 state: waiting for completed aio requests (read thread) I/O thread 5 state: waiting for completed aio requests (read thread) I/O thread 6 state: waiting for completed aio requests (write thread) I/O thread 7 state: waiting for completed aio requests (write thread) I/O thread 8 state: waiting for completed aio requests (write thread) I/O thread 9 state: waiting for completed aio requests (write thread)
4，提高刷新脏页数量和合并插入数量，改善磁盘 IO 处理能力。
在 MySQL5.1.X 版本,由于代码写死,最多只会刷新 100 个脏页到磁盘,合并 20 个插入缓冲,即使磁盘有能力处理更多的请求,也只会处理这么多,这样在更新量较大(比如大批量 INSERT)的时候,脏页刷新可能会跟不上,导致性能下降。而在 MySQL5.5.X 版本里,innodb_io_capacity 参数可以动态调整刷新脏页的数量,在一定程度上解决了这一问题。innodb_io_capacity 参数默认是 200,单位页。设置的大小取决于你的硬盘的IOPS,即每秒的输入输出量(或读写次数)。
注:此参数支持动态改变,但需要 SUPER 权限。
SET GLOBAL innodb_io_capacity = 1000;
合并插入缓存的时候，数量为该参数设置的值的5%；刷写脏页的时候，刷写的数量为该参数设置的值
5，增加了自适应刷新脏页功能。innodb_adaptive_flushing
这个功能是在 InnoDB Plugin 引入的。InnoDB 刷新脏页的规则是,当超过innodb_max_dirty_pages_pct 设定的值后,或者当重做日志 ib_logfile 文件写满了以后,或者是机器空闲的时候,这三种情况下才会把 InnoDB_Buffer_Pool 的脏页刷入磁盘。
当写操作很频繁的时候,重做日志 ib_logfile 切换的次数就会很频繁,每当一个写满后,就会进行大批量将脏页刷入磁盘,会对系统的整体性能造成不小的影响。为了避免过大的磁盘IO,innodb_adaptive_flushing 自适应刷新,会根据重做日志 ib_logfile 生成的速度和刷新频率来将脏页刷入磁盘,这样重做日志 ib_logfile 还没有写满时,也可以刷新一定的量。
注:innodb_adaptive_flushing 参数默认开启,可动态更新
6，更好的避免buffer pool 的污染，失效。
通过 innodb_old_blocks_pct、innodb_old_blocks_time 参数能在一定程度上控制。详情见这里
7，加快了 InnoDB 的数据恢复时间。通过算法和内存管理上的改进,将 crash recovery 大大缩短了
8，INNODB 同时支持多个 BufferPool 实例。
InnoDB 用来缓存它的数据和索引的内存缓冲区的大小。你把这个值设得越高,访问表中数据需要得磁盘 I/O 越少。在一个专用的数据库服务器上,你可以设置这个参数达机器物理内存大小的80%。尽管如此,还是不要把它设置得太大,因为对物理内存的竞争可能在操作系统上导致内存调度。
innodb_buffer_pool_size 是 InnoDB 性能的决定性因素,当你的数据库小于 innodb_buffer_pool_size 设置的缓冲池大小,那么此时数据库的性能是最好的,因为客户端访问的数据都在内存里。
InnoDB_Buffer_Pool 缓冲池复制管理着 free list(初始化空闲页等),flush list(缓冲池产生的脏页(数据库被修改,但未写入磁盘),当 innodb_max_dirty_pages_pct 超过设置的值,会把修改时间越早的 page 刷进磁盘), LRU(在内存中但最近又不用的数据块,按照最近最少使用算法,MySQL会根据哪些数据属于 LRU 而将其移出内存而腾出空间来加载另外的数据。)等,当 InnoDB_Buffer_Pool 缓冲池达到好几十 G 时,某个线程正在更新缓冲池而造成其它线程必须等待的瓶颈。
在 MySQL5.5 里,可以通过 innodb_buffer_pool_instances 参数来增加InnoDB_Buffer_Pool 实例的个数,使用哈希函数将读取缓存的数据页随机分配到一个缓冲池里面,这样每个缓冲区实例就可以分别管理着自己的 free list, flushlist, LRU,来解决此问题。
注:innodb_buffer_pool_size 必须大于 1G,生成 InnoDB_Buffer_Pool 多实例才有效,最多支持 64 个 InnoDB_Buffer_Pool 实例。修改 my.cnf 配置文件,添加如下:
innodb_buffer_pool_instances = 3
调整后,你可以用命令 show engine innodb status\G;来查看:
View Code
---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 2146271232; in additional pool allocated 0 Dictionary memory allocated 73994 Buffer pool size 127995 Free buffers 120615 Database pages 7380 Old database pages 2783 Modified db pages 0 ... ... ---------------------- INDIVIDUAL BUFFER POOL INFO ---------------------- ---BUFFER POOL 0 Buffer pool size 42665 Free buffers 40064 Database pages 2601 Old database pages 980 ... ... ---BUFFER POOL 1 Buffer pool size 42665 Free buffers 40336 Database pages 2329 Old database pages 879 Modified db pages 0 ... ... ---BUFFER POOL 2 Buffer pool size 42665 Free buffers 40215 Database pages 2450 Old database pages 924 Modified db pages 0 ... ...
9，可选用内存分配程序使用控制。
MySQL 5.5 选项文件 my.cnf 中设置新的系统配置参数 innodb_use_sys_malloc,可方便地进行控制。默认设置值为 1,表示 InnoDB 使用操作系统的内存分配程序。
10，提高了默认 innodb 线程并发数。
从 MySQL5.5.X 版本开始,innodb_thread_concurrency 被默认设置为 0,表示不限制并发数。注:innodb_thread_concurrency = 0 时,innodb_thread_sleep_delay 参数就无效了。同样 innodb_concurrency_tickets 也没了意思,这里推荐设置为 0,更好去发挥 CPU多核处理能力,提高并发量。
11，实现了异步 I/O和组提交，默认即可。
同步 I/O,当一个 I/O 操作执行时,应用程序必须等待,直到此 I/O 执行完。相反,异步 I/O 操作在后台运行,I/O 操作和应用程序可以同时运行,提高了系统性能。因此像数据库等应用往往会利用异步 I/O,使得多个 I/O 操作同时执行。从 MySQL5.5.X 版本开始,在 Linux 系统上实现异步 I/O 功能,也就是 linux native AIO。
通过 innodb_use_native_aio 参数来选择是否启用异步 I/O,默认是 ON,可以开启的,此参数不支持动态修改。
12，使用多个回滚段提升性能。默认即可。
MySQL5.5 可以支持高达 128K 的并发事务处理操作,创建回滚数据(undo data)(来自插入、更新、和删除操作)。这种该进措施减少了在单个回滚段上的互斥争用,增加了吞吐量。
13，改善清除程序进度。
InnoDB 中的清除操作是一类定期回收无用数据的操作。在之前的几个版本中,清除操作是主线程的一部分,这意味着运行时它可能会堵塞其它的数据库操作。
从 MySQL5.5.X 版本开始,该操作运行于独立的线程中,并支持更多的并发数。用户可通过设置 innodb_purge_threads 配置参数来选择清除操作是否使用单
独线程,默认情况下参数设置为 0(不使用单独线程),设置为 1 时表示使用单独的清除线程。建议为1。
注:innodb_purge_threads 参数不支持动态修改,需要添加到 my.cnf 里修改,并重启生效。当设置为 1 时,需要结合 innodb_purge_batch_size 参数来使用,默认
值是 20,最大可设置 5000,这个参数一般不用调整,默认即可或则300。
14，添加了删除缓冲和清除缓冲。类似插入缓存。见 【innodb 内幕 2.41】
当向一个表进行 insert、delte 或 update 时,里面的索引(聚集索引和非聚集索引)也会随即更新,主键(聚集索引)是按照顺序进行插入的,而非聚集索引则是分散性的插入。顺序读写的速度要比随机读写的速度快,表越大就越明显,插入的性能就会变低。
InnoDB 引入了一种优化措施,因此在 Mysql5.1.X 版本里,当一个表做 insert操作非聚集索引更新时,如果该非聚集索引页被读入 Innodb_Buffer_Pool 缓冲池里,那么就直接更新非聚集索引,并使用正常的写脏数据块方法闪存到磁盘中;如果没有读入缓冲池里,则使用插入缓冲区来缓存非聚集索引页的变化,直到该页被读入 Innodb_Buffer_Pool 缓冲池里,执行插入缓存合并操作,并使用正常的写脏数据块方法闪存到磁盘中,从而提高了插入性能。
从 MySQL5.5.X 版本开始,还为删除操作扩展了同样的功能(首先是删除标记操作,然后使用收集/清除所有已删除记录的清除操作)。现在可以使用innodb_change_buffering 配置参数来控制删除缓冲和既有插入缓冲功能,默认是all,此参数支持动态设置:
SET GLOBAL innodb_change_buffering = all;
注意：该缓存占用一定的的buffer pool，要是数据全部都缓存到BP中，或则只有很少的二级索引，该参数可以关闭。
15，控制自旋锁 Spin Lock 轮训间隔，实现保护共享资源而提出一种锁机制。
自旋锁不会引起调用者睡眠,如果自旋锁已经被别的执行单元保持,调用者就一直循环在那里看是否该自旋锁的保持者已经释放了锁。
innodb_spin_wait_delay 默认为6。
set global innodb_spin_wait_delay=6;
16，快速创建、删除、更改索引。( innodb plugin )
对于聚集索引和原先一样，需要copy data，而非聚集索引不需要copy data，能提高创建速度。
17，支持压缩页，新格式( innodb plugin )
innodb_file_format = Barracuda，innodb_file_per_table = 1
创建表的时候 在最后加：
ROW_FORMAT=COMPRESSED
KEY_BLOCK_SIZE=8;
被请求的数据页小于 InnoDB_Buffer_Pool 缓冲池大小,未压缩的性能要稍好于压缩过的,因为压缩会带来额外的 CPU 消耗,总体上差异不大。被请求的数据页大于 InnoDB_Buffer_Pool 缓冲池大小,压缩的性能要好于压缩过的,吞吐量也提高。批量插入数据的时候，压缩的比没有压缩的要慢，而且性能也更差。可以参考这里
18，关闭 InnoDB 更新元数据统计功能。
innodb_stats_on_metadata 参数的作用是:查询 information_schema 元数据库里的表时,Innodb 还会随机提取其他数据库每个表索引页的部分数据,当你的表很大,并且数量很多时,耗费的时间就会很长,很多不访问的数据也会进入到Innodb_Buffer_Pool 缓冲池里,那么就会把缓冲池所污染。并且 ANALYZE TABLE和 SHOW TABLE STATUS 语句也会造成 Innodb 随机提取数据。
从 MySQL5.5.X 版本开始,你可以动态关闭 innodb_stats_on_metadata,默认是开启的，建议关闭。
set global innodb_stats_on_metadata = OFF;
19，复制方面：增加了半同步复制，relay-log的自我修复。
在 MySQL5.5.X 版本开始,增加了 relay_log_recovery 参数,这个参数的作用是:当 slave 从库宕机后,假如 Relay-Log 损坏了,导致一部分中继日志没有处理,则自动放弃所有未执行的 relay-log,并且重新从 MASTER 上获取日志,这样保证 relay-log 的完整。默认情况下该功能是关闭的,将 relay_log_recovery 的值设置为 1 时,可在 slave 从库上开启该功能,建议开启。
20，可以动态修改表空间模式和锁超时时间。
set global innodb_file_per_table = 1; set global innodb_lock_wait_timeout = 10;
二，新参数说明和设置
1，innodb_adaptive_flushing
解释:自适应刷新脏页,在没有达到 innodb_max_dirty_pages_pct 设置的值也会刷新,默认为 ON,可动态更改,建议不要更改。
2，innodb_buffer_pool_instances
解释:可以设置多个 buffer_pool 管理各自的缓冲池,这样会减少某个线程正在更新缓冲池而造成其它线程必须等待的瓶颈。默认为 1,不支持动态更改,innodb_buffer_pool_size 必须大于 1G,生成 InnoDB_Buffer_Pool 多实例才有效,最多支持 64 个 InnoDB_Buffer_Pool 实例。可根据实际内存大小,加以设置多个缓冲池。
3，innodb_change_buffering
解释:可以设置插入 inserts 合并缓冲、删除 deletes 合并缓冲、清除 purges 合并缓冲,changes(Buffer both inserts and delete-marking),none(Do not buffer any
operations)。默认是 all(buffer insert, delete-marking, and purge operations),可动态更改。一般情况下,采用默认值 all 即可。
4，innodb_file_format
解释:innodb 的文件格式为 Antelope,innodb-plugin 的文件格式支持 Barracuda,开启数据压缩页 8K,那么就必须设置为 Barracuda,需要把innodb_file_per_table = 1。默认为 Antelope,可动态更改,建议改为 Barracuda。
5，innodb_file_format_check
解释: 用来检查共享表空间文件格式,如果共享表空间的文件格式高于当前版本,并且 innodb_file_format_check 设置为 on,在服务器启动时会报告错误:
InnoDB: Error: the system tablespace is in a file format that this version doesn't support.
如果设置为 off,服务器可以启动,并收到一个警告信息,但这样很不安全,数据有可能会损坏。默认是 on,不支持动态更改,建议不要修改。
6， innodb_file_format_max
解释:设置文件格式最高版本,默认是 Antelope,可动态更改,建议更改为Barracuda。
7，innodb_io_capacity
解释:5.1.X 版本,由于代码写死,每秒只能刷新 100 个脏页,现在可以设置刷新脏页的数量,提高磁盘 I/O 的吞吐率,默认是 200,可动态更改,一般六块 15000转的磁盘做 RAID10,设置 2000 较合适。
8，innodb_old_blocks_pct
解释:控制进入缓冲区 sublist of old blocks 区域的数量,默认是 37,占整个缓冲池的比例为 3/8,可动态更改,除非有很多全表扫描语句,一般采用默认即可。
9，innodb_old_blocks_time
解释:控制进入缓冲区 sublist of old blocks 区域停留的时间,超过后移动到 sublist of new blocks 区域,默认是 0,有访问即移动,可动态更改,除非有很多全表扫描语句,一般采用默认即可。
10，innodb_purge_batch_size
解释: 当开启独立线程清除 undo 页时,表示一次删除多少个页。默认是 20,支持动态修改。
11，innodb_purge_threads
解释:是否开启开启独立线程清除 undo 页。默认是 0,不开启,建议开启,不支持动态修改。
12，innodb_read_ahead_threshold
解释:当顺序读取 extent 块(包含 64 个 page)innodb_read_ahead_threshold 设置的 page 页数量时,触发一个异步读取请求,将下一个页提前读取到 buffer pool中。默认为 56,不支持动态修改,一般采用默认即可。
13，innodb_read_io_threads/innodb_write_io_threads
解释:控制后台线程处理数据页上的读/写请求。默认是 4,不支持动态修改。建议根据服务器的核数以及读写请求的比例加以调整。
14，innodb_stats_method
解释:收集 Innodb 表索引值分布统计时,服务器如何处理 NULL 值,该参数有3 个可选值,nulls_equal=所有的 null 被看成相等的一个值,nulls_unequal=每个null 被看成单独的一个值,nulls_igored=null 被忽略,默认值为 nulls_equal。
15，innodb_spin_wait_delay
解释:控制自旋锁轮训时间间隔,默认是 6 秒,可动态修改,一般默认即可
16，innodb_stats_sample_pages
解释:每当查询 information_schema 元数据库里的表时,Innodb 还会随机提取其他数据库每个表 8 个索引页的部分数据,来更新 information_schema.STATISTICS表,来返回刚才你查询的结果。默认是 8 个页,可动态更改。如果你关闭了innodb_stats_on_metadata,那么这个参数被忽略。
17，innodb_strict_mode
解释:开启 InnoDB 严格检查模式,尤其采用了页数据压缩功能,建议开启。此功能是当 CREATE TABLE, ALTER TABLE and CREATE INDEX 语句时,如果写法有错误,不会有警告信息,而是直接抛出错误,好处是直接将问题扼杀在摇篮里。默认是关闭。
18，innodb_use_native_aio
解释:选择是否启用 Linux 系统的异步 I/O,默认是 ON,开启的,此参数不支持动态修改。如果在启动时 InnoDB 检测到一个潜在的问题,例如不支持 AIO 在 tmpfs 文件系统上,会自动将其关闭,一般采用默认即可。
19，innodb_use_sys_malloc
解释:使用 MySQL 自带的内存分配程序,还是使用当前部署的操作系统中现有的更高效的内存分配程序。默认设置值为 1,不支持动态更改,表示 InnoDB 使用操作系统的内存分配程序。
20，innodb_replication_delay
解释: innodb_thread_concurrency 线程已满，slave 端复制线程的延迟时间(ms),默认是 0,不延迟,可动态修改。
21，relay_log_recovery
解释:当 slave 从库宕机后,假如 Relay-Log 损坏了,导致一部分中继日志没有处理,则自动放弃所有未执行的 relay-log,并且重新从 MASTER 上获取日志,这样保证 relay-log 的完整。默认情况下该功能是关闭的,不支持动态修改,建议开启。
22，sync_relay_log
解释:这个参数和 sync_binlog 是一样的,当设置为 1 时,Slave 的 IO 线程每次接收到 Master 发送过来的 binlog 日志都要写入系统缓冲区然后刷入到 relay log 中继日志里,这样是最安全的,因为在崩溃的时候,你最多会丢失一个事务,但会造成磁盘的大量 I/O。当设置为 0 时,并不是马上就刷入中继日志里,而且由操作系统决定何时来写入,虽然安全性降低,但减少了大量的磁盘 I/O 操作。这个值默认是 0,可动态修改,建议采用默认值。
23，sync_relay_log_info
解释:这个参数和 sync_relay_log 参数一样,当设置为 1 时,Slave 的 IO 线程每次 接 收 到 Master 发 送 过 来 的 binlog 日 志 都 要 写 入 系 统 缓 冲 区 然 后 刷 入 到
relay-log.info 里,这样是最安全的,因为在崩溃的时候,你最多会丢失一个事务,但会造成磁盘的大量 I/O。当设置为 0 时,并不是马上就刷入 relay-log.info 里,而且由操作系统决定何时来写入,虽然安全性降低,但减少了大量的磁盘 I/O 操作。这个值默认是 0,可动态修改,建议采用默认值。
24，sync_master_info
解释:这个参数和 sync_relay_log_info 参数一样,当设置为 1 时,Slave 的 IO 线程每次接收到 Master 发送过来的 binlog 日志都要写入系统缓冲区然后刷入master.info 里,这样是最安全的,因为在崩溃的时候,你最多会丢失一个事务,但会造成磁盘的大量 I/O。当设置为 0 时,并不是马上就刷入 master.info 里,而且由操作系统决定何时来写入,虽然安全性降低,但减少了大量的磁盘 I/O 操作。这个值默认是 0,可动态修改,建议采用默认值。
25：半同步复制相关参数：
rpl_semi_sync_master_enabled
解释:表示是否开启半同步复制功能,默认是关闭的,采用异步复制,可动态修改。
rpl_semi_sync_master_timeout
解释:表示主库在某次事务中,如果等待时间超过 10 秒,那么则降级为异步复制模式,不再等待 SLAVE 从库。如果主库再次探测到,SLAVE 从库恢复了,则会自动再次回到半同步复制模式。默认为 10000 毫秒,等于 10 秒,这个参数动态可调。
rpl_semi_sync_master_trace_level
解释:在 Master 上,开启半同步复制模式时的调试级别,默认是 32,可动态修改,一般采用默认值即可。
1 = general level (for example, time function failures)
16 = detail level (more verbose information)
32 = net wait level (more information about network waits)
64 = function level (information about function entry and exit)
rpl_semi_sync_master_wait_no_slave
解释:是否允许 master 每个事务提交后都要等待 slave 的接收确认信号。默认为on,每一个事务都会等待。如果为 off,则 slave 追赶上后,也不会开启半同步复
制模式,需要手工开启,可动态修改。
rpl_semi_sync_slave_enabled
解释:表示在 slave 上已经是否开启半同步复制模式,默认是不开启,可动态修改。
rpl_semi_sync_slave_trace_level
解释: 在Slave 上,开启半同步复制模式时的调试级别,默认是 32,可动态修改,一般采用默认值即可。
1 = general level (for example, time function failures)
16 = detail level (more verbose information)
32 = net wait level (more information about network waits)
64 = function level (information about function entry and exit)
