[MySQL 5.6] MySQL5.6新参数

 

以下罗列了我所感兴趣的MySQL5.6新参数,不定期更新本文,完善参数的说明,先大概列一下,做简单说明,以后在一个个补上

/////////////////////////////////////////////// 

#mysqld

table_open_cache_instances #table cache进行划分,减少锁竞争

metadata_locks_hash_instances # server层的metalock hash进行划分,

metadata_locks_cache_size# metadata lock cache的大小,这是总的大小,可以适当调大来提升并发度

 

#replication

#crash safe

relay_log_info_repository #是以文件(FILE) 还是表(table)的方式记录relaylog坐标信息

master_info_repository # 是以文件(FILE) 还是表(table)的方式记录binlog信息

 

#gtid

gtid_mode # 设置为ON,表示打开gtid,默认为OFF

enforce_gtid_consistency #如果设置该选项,则只允许记录事务安全的语句

gtid_next

gtid_owned

gtid_purged

gtid_executed

 

 

#control option

 

slave_parallel_workers #备库复制worker线程数

slave_checkpoint_group #在并发复制时总共执行这么多次事务后做一次checkpoint,更新show slave status的数据

slave_checkpoint_period #在复制执行这么长时间后做一次checkpoint

slave_pending_jobs_size_max #在多线程复制时,在队列中Pending的事件所占用的最大内存,默认为16M,如果内存富余,或者延迟较大时,可以适当调大;注意这个值要比主库的max_allowed_packet

binlog_checksum   #

binlog_max_flush_queue_time #

binlog_order_commits   #

binlog_row_image

binlog_rows_query_log_events #

 

#innodb

#innodb buffer pool

innodb_buffer_pool_dump_at_shutdown #shutdown时导出bp中的pageno

innodb_buffer_pool_load_at_startup #启动时读入之前导出的bp中的page

 

#flush strategy

innodb_flush_method = O_DIRECT_NO_FSYNC #新参数,在IO时使用O_DIRECT,但不再随后做fsync,可以参考bug#45892的描述;这种配置不适合一些文件系统,因为metadata没有被fsync到磁盘。

 

innodb_lru_scan_depth #影响page cleaner线程一次扫描LRU/UNZIP_LRU的深度,默认为1024IO有空余可以适当调大;

详细见http://mysqllover.com/?p=485

 

innodb_adaptive_flushing_lwm #该值表示redo log的一个最低容量限制百分比,默认为10,当没有达到这个值时,则不会page cleaner线程不会根据redo来判断是否刷页,细节见函数af_get_pct_for_lsn

 

innodb_max_dirty_pages_pct_lwm #防止在到达innodb_max_dirty_pages_pct时疯狂刷新,而是在达到这样一个限定值时,开始优雅的做刷新脏页(预刷新)。详细见函数af_get_pct_for_dirty

 

innodb_io_capacity_max #flush操作落后太多时,可能会做一些非常有侵略性的刷新(超过指定的innodb_io_capacity),这会影响到正常的业务,指定这个值,可以限制io capacity的上限,减少对正常应用的影响

 

innodb_flushing_avg_loops #这个选项可以控制adaptive flush对工作负载变化的响应速度。在这么多次loop内,innodb会保持上次的刷新状态快照不变,增加这个值有助于刷新操作更加平稳,而减小这个值有助于对工作负载的变化更快的调整adaptive flush,不过,如果设置的过小的话,在突然增大/减小的工作的负载中,容易引起性能尖峰。

 

Innodb内核层面来看,以上三个参数实际上影响了page cleaner线程刷新的脏页的数量。

刷脏页可以由两个因素影响:

1.    根据redo log计算比例(pct_for_lsn),当小于innodb_adaptive_flushing_lwm时,pct_for_lsn值为0当小于异步刷redo的比例(log_sys->max_modified_age_async)时且关闭选项innodb_adaptive_flushing时,pct_for_lsn也为0,否则,计算:

((innodb_io_capacity_max/innodb_io_capacity)

 *(lsn_age_factor * sqrt((double)lsn_age_factor)))/7.5

其中lsn_age_factor为当前的(redo比例*100)/max_modified_age_async

2.    根据脏页计算比例(pct_for_dirty),当innodb_max_dirty_pages_pct_lwm设置为0时,和以前的行为类似,如果脏页比例大于innodb_max_dirty_pages_pct时,pct_for_dirty设置为100,否则如果脏页比大于innodb_max_dirty_pages_pct_lwmpct_for_dirty值为

(dirty_pct * 100)/( innodb_max_dirty_pages_pct+1)

3.    最近innodb_flushing_avg_loops次平均刷脏页的数量,同时还考虑上次统计时候的平均数量,再除以2

   avg_page_rate= ((sum_pages / srv_flushing_avg_loops) + avg_page_rate) / 2

            另外也会计算lsn的刷新速率

lsn_rate= (cur_lsn – prev_lsn) / srv_flushing_avg_loops;

lsn_avg_rate= (lsn_avg_rate + lsn_rate) / 2;

 

然后根据上述两个值计算要刷新的page数:

pct_total = ut_max(pct_for_dirty, pct_for_lsn);

n_pages = (PCT_IO(pct_total) + avg_page_rate) / 2;

if (n_pages > srv_max_io_capacity) {

     n_pages =srv_max_io_capacity;

}

除了脏页的数量外,还要确定一个lsn下限值(lsn_limit)oldest_modification小于lsn_limitblock需要被刷新掉,其计算方式也受参数innodb_flushing_avg_loops影响:

innodb_flushing_avg_loops次循环,计算

lsn_rate = (cur_lsn – prev_lsn) /srv_flushing_avg_loops;

lsn_avg_rate = (lsn_avg_rate + lsn_rate) / 2;

lsn_avg_rate表示LSN最近的平均推进速率

然后计算lsn_limit:

if (last_pages && cur_lsn – last_lsn >lsn_avg_rate / 2) {

     age_factor= prev_pages / last_pages;

}

lsn_limit = oldest_lsn + lsn_avg_rate * (age_factor +1)

prev_pages表示上一次刷新时,试图刷的page数,last_pages表示上次实际刷新的page数,age_factor总是大于等于1的。

Oldest_lsn表示当前bp中的最老LSN.

 

在确定了需要刷新的page数及哪些Page需要被刷新后,就可以调用函数page_cleaner_do_flush_batch-> buf_flush_list做实际的操作了。

 

innodb_flush_log_at_timeout #每隔这么多秒刷一次日志,只有在innodb_flush_log_at_trx_commit=2时才生效.master线程中判断,见函数srv_sync_log_buffer_in_background,在两个函数中会调用:

1.srv_master_do_active_tasks

2.srv_master_do_idle_tasks

从命名也可以理解,master线程在系统繁忙及空闲时,都会去做判断。在5.6master线程函数srv_master_thread中,每sleep 1秒,会根据当前的系统负载是否繁忙去调用上面这两个函数,相比之前的函数,master线程函数要清爽很多了。每隔innodb_flush_log_at_timeout秒,会调用log_buffer_sync_in_background(TRUE)去刷日志。

 

# control option

innodb_print_all_deadlocks #保存全部死锁信息

innodb_adaptive_max_sleep_delay #表示在使用automic进行进入innodb层并发控制时的自适应sleep时间的最大值

innodb_purge_threads # purge线程数,可以加快purge速度

innodb_online_alter_log_max_size #在做online ddl时,保持增量更新的日志缓冲区的最大值;如果超过这个值,所有未提交的事务回滚,ALTER TABLE操作失败

innodb_sort_buffer_size #在创建Index时做归并排序时每个归并块值,适当调大可以加快建索引速度,同时这个值也是在做online ddl时增量日志缓冲每次扩展的大小。

innodb_read_only #设置该参数可以在只读媒介上启动Innodb,这也意味着我们可以开启多个实例,对同一份数据集做纯读操作

innodb_log_file_size #1MB ~ 1/LOG_GROUP*BP, 48G BP& 4 instance的话,12GB为上限过大的值不兼容

default_tmp_storage_engine    #设置临时表使用的存储引擎 

 

#statistics

innodb_stats_persistent #物化统计信息,默认打开

innodb_stats_persistent_sample_pages #当打开innodb_stats_persistent选项时,这个设置才生效

innodb_stats_transient_sample_pages #当关闭innodb_stats_persistent选项时生效,采样page数(尤其是后者)不应该设置的太大,否则会产生额外的IO开销,但也不应设置的太小,否则会导致查询计划不准确

innodb_stats_auto_recalc #用于决定是否在表上存在大量更新时(超过10%的记录更新)重新计算统计信息。默认打开,如果关闭该选项,就需要在每次创建索引或者更改列之后,运行一次ANALYZE TABLE命令来更新统计信息,否则可能选择错误的执行计划。同样的,也可以在CREATE TABLE/ALTER TABLE命令中指定STATS_AUTO_RECALC

 

# Innodb compressed table

innodb_compression_level #定义压缩表的压缩级别,在具有较好压缩特性的数据集上,可以适当调小该值,还获得更好的TPS性能

innodb_compression_failure_threshold_pct #该参数和下面的参数来自facebook对压缩表的改进,当一个非压缩page无法压缩到指定size时,会产生索引分裂,这会大大影响性能,我们可以给非压缩页留一些空白,少存一点数据,这样会降低压缩失败率,但也有可能减小压缩比,该选项表示当压缩失败率高于这个值时,进行apdative padding.

innodb_compression_pad_pct_max #一个非压缩page上最大允许留白的百分比

innodb_cmp_per_index_enabled #表示是否统计每个表的每个索引的压缩状态,如果打开,信息会进行收集,并显示在information_schemaINNODB_CMP_PER_INDEX/INNODB_CMP_PER_INDEX_RESET中,这会有一定的性能损耗

 

#memcache_plugin 

daemon_memcached_enable_binlog

daemon_memcached_engine_lib_name

daemon_memcached_engine_lib_path

daemon_memcached_option

daemon_memcached_r_batch_size

daemon_memcached_w_batch_size

 

#以下是不向下兼容的参数

innodb_undo_tablespaces #undo 表空间的个数,将所有的回滚段平分到这么多个ibd表空间文件中,这个值一旦设置,则不可更改。配置这个及下面的选项,是为了将undologIbdata中独立出来,并且由于undo log是随机写,可以放到SSD盘上来提高性能

innodb_undo_directory #表示undo log表空间文件的路径,启动前设置

innodb_undo_logs #等同于老版本的innodb_rollback_segments

innodb_checksum_algorithm #更快的checksum算法(crc32)

 

原创文章,转载请注明: 转载自Simple Life

本文链接地址: [MySQL 5.6] MySQL5.6新参数

Post Footer automatically generated by wp-posturl plugin for wordpress.


Comments

Leave a Reply

Your email address will not be published. Name and email are required


Current month ye@r day *