[MySQL 5.6] MySQL5.6新參數

以下羅列了我所感興趣的mysql5.6新參數，不定期更新本文，完善參數的說明，先大概列一下，做簡單說明，以後在一個個補上

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

table_open_cache_instances #對table cache進行劃分，減少鎖競争

metadata_locks_hash_instances # 對server層的metalock hash進行劃分，

metadata_locks_cache_size# metadata lock cache的大小，這是總的大小，可以适當調大來提升并發度

relay_log_info_repository #是以檔案(file) 還是表（table）的方式記錄relaylog坐标資訊

master_info_repository # 是以檔案(file) 還是表（table）的方式記錄binlog資訊

gtid_mode # 設定為on，表示打開gtid，預設為off

enforce_gtid_consistency #如果設定該選項，則隻允許記錄事務安全的語句

gtid_next

gtid_owned

gtid_purged

gtid_executed

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_buffer_pool_dump_at_shutdown #shutdown時導出bp中的pageno

innodb_buffer_pool_load_at_startup #啟動時讀入之前導出的bp中的page

innodb_flush_method = o_direct_no_fsync #新參數，在io時使用o_direct，但不再随後做fsync，可以參考bug#45892的描述；這種配置不适合一些檔案系統，因為metadata沒有被fsync到磁盤。

innodb_lru_scan_depth #影響page cleaner線程一次掃描lru/unzip_lru的深度，預設為1024，io有空餘可以适當調大;

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_lwm，pct_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_limit的block需要被重新整理掉，其計算方式也受參數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.6的master線程函數srv_master_thread中，每sleep 1秒，會根據目前的系統負載是否繁忙去調用上面這兩個函數，相比之前的函數，master線程函數要清爽很多了。每隔innodb_flush_log_at_timeout秒，會調用log_buffer_sync_in_background(true)去刷日志。

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    #設定臨時表使用的存儲引擎 

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_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_schema的innodb_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表空間檔案中，這個值一旦設定，則不可更改。配置這個及下面的選項，是為了将undolog從ibdata中獨立出來，并且由于undo log是随機寫，可以放到ssd盤上來提高性能

innodb_undo_directory #表示undo log表空間檔案的路徑，啟動前設定

innodb_undo_logs #等同于老版本的innodb_rollback_segments

innodb_checksum_algorithm #更快的checksum算法(crc32)