mysql 設定預設值_MySQL參數設定01

innodb_buffer_pool_size InnoDB Buffer Pool 的大小是由參數 innodb_buffer_pool_size 确定的，一般建議設定成可用實體記憶體的 60%~80%。

innodb_max_dirty_pages_pct 是髒頁比例上限，預設值是 75%.髒頁比例是通過 Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total 得到的，具體的指令參考下面的代碼：

select VARIABLE_VALUE into @a from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_dirty';

select VARIABLE_VALUE into @b from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_total';

select @a/@b;

innodb_flush_neighbors 參數就是用來控制刷髒頁時是否刷相鄰的資料頁髒頁，值為 1 的時候會有“連坐”機制，值為 0 時表示不找鄰居，自己刷自己的。

在準備刷一個髒頁的時候，如果這個資料頁旁邊的資料頁剛好是髒頁，就會把這個“鄰居”也帶着一起刷掉；而且這個把“鄰居”拖下水的邏輯還可以繼續蔓延，也就是對于每個鄰居資料頁，如果跟它相鄰的資料頁也還是髒頁的話，也會被放到一起刷。 找“鄰居”這個優化在機械硬碟時代是很有意義的，可以減少很多随機 IO。機械硬碟的随機 IOPS 一般隻有幾百，相同的邏輯操作減少随機 IO 就意味着系統性能的大幅度提升。而如果使用的是 SSD 這類 IOPS 比較高的裝置的話，我就建議你把 innodb_flush_neighbors 的值設定成 0。因為這時候 IOPS 往往不是瓶頸，而“隻刷自己”，就能更快地執行完必要的刷髒頁操作，減少 SQL 語句響應時間。在 MySQL 8.0 中，innodb_flush_neighbors 參數的預設值已經是 0 了。

innodb_file_per_table  表資料既可以存在共享表空間裡，也可以是單獨的檔案。這個行為是由參數 innodb_file_per_table 控制的

這個參數設定為 OFF 表示的是，表的資料放在系統共享表空間，也就是跟資料字典放在一起；這個參數設定為 ON 表示的是，每個 InnoDB 表資料存儲在一個以 .ibd 為字尾的檔案中。從 MySQL 5.6.6 版本開始，它的預設值就是 ON 了。

sort_buffer_size MySQL 為排序開辟的記憶體(sort_buffer)的大小。如果要排序的資料量小于 sort_buffer_size，排序就在記憶體中完成。但如果排序資料量太大，記憶體放不下，則不得不利用磁盤臨時檔案輔助排序。MySQL為每個線程配置設定sort_buffer_size。

max_length_for_sort_data MySQL 中專門控制用于排序的行資料的長度的一個參數。它的意思是，如果單行的長度超過這個值，MySQL 就認為單行太大，要換一個算法(不使用全字段排序)。

tmp_table_size 記憶體臨時表的大小，預設值是 16M。如果臨時表大小超過了 tmp_table_size，那麼記憶體臨時表就會轉成磁盤臨時表。

sys.schema_table_lock_waits 直接找出造成阻塞的 process id 表級鎖？

sys.innodb_lock_waits 誰占着這個寫鎖 行鎖

innodb_lock_wait_timeout 死鎖等待逾時時間，InnoDB引擎預設值是50s。

innodb_deadlock_detect 設定死鎖自動檢測是否開啟，on為開啟

binlog_cache_size 用于控制單個線程内 binlog cache 所占記憶體的大小。如果超過了這個參數規定的大小(預設32K)，就要暫存到磁盤。

sync_binlog 控制 binlog 的寫入政策

• sync_binlog=0 的時候，表示每次送出事務都隻 write，不 fsync；
• sync_binlog=1 的時候，表示每次送出事務都會執行 fsync；
• sync_binlog=N(N>1) 的時候，表示每次送出事務都 write，但累積 N 個事務後才 fsync。

innodb_flush_log_at_trx_commit 控制 redo log 的寫入政策

• 設定為 0 的時候，表示每次事務送出時都隻是把 redo log 留在 redo log buffer 中 ;
• 設定為 1 的時候，表示每次事務送出時都将 redo log 直接持久化到磁盤；
• 設定為 2 的時候，表示每次事務送出時都隻是把 redo log 寫到 page cache。

innodb_log_file_size ：該參數決定着mysql事務日志檔案( redo log)(ib_logfile0)的大小；

設定的太小：當一個日志檔案寫滿後，innodb會自動切換到另外一個日志檔案，而且會觸發資料庫的檢查點(Checkpoint)，這會導緻innodb緩存髒頁的小批量重新整理，會明顯降低innodb的性能。由于日志切換更頻繁，也就直接導緻更多的BUFFER FLUSH，由于日志切換的時候是不能BUFFER FLUSH的， BUFFER寫不下去，導緻沒有多餘的buffer 寫redo， 那麼整個MYSQL就HANG住，還有一種情況是如果有一個大的事務，把所有的日志檔案寫滿了，還沒有寫完，這樣就會導緻日志不能切換(因為執行個體恢複還需要，不能被循環複寫)這樣mysql就hang住了。可以根據檔案修改時間來判斷日志檔案的旋轉頻率，旋轉頻率太頻繁，說明日志檔案太小了。

設定的太大：設定很大以後減少了checkpoint，并且由于redo log是順序I/O，大大提高了I/O性能。但是如果資料庫意外出現了問題，比如意外當機，那麼需要重放日志并且恢複已經送出的事務(也就是執行個體恢複中的前滾, 利用redo從演變化來恢複buffer cache中的資料)，如果日志很大，那麼将會導緻恢複時間很長。甚至到我們不能接受的程度。

如果對 Innodb 資料表有大量的寫入操作，那麼選擇合适的 innodb_log_file_size 值對提升MySQL性能很重要

一般來說，日志檔案的全部大小，應該足夠容納伺服器一個小時的活動内容。

具體依據如下：我經常設定為 64-512MB

首先在業務高峰期，計算出1分鐘寫入的redo量，然後評估出一個小時的redo量；

innodb_log_files_in_group 控制事務日志(redo log)檔案數。預設值為2。mysql 事務日志檔案是循環覆寫的。需要注意的是：innodb_log_files_in_group是靜态的變量，需要以“幹淨”的方式更改并重新啟動，否則mysql啟動不起來。也就是說如果想把原來是2的修改成3，這樣的話你需要先關閉mysql服務，把原來的ib_logfile0和ib_logfile1檔案删掉，然後啟動mysql，否則報錯

innodb_log_buffer_size 確定有足夠大的日志緩沖區來儲存髒資料在被寫入到日志檔案之前。

binlog_group_commit_sync_delay 表示延遲多少微秒後才組送出 fsync binlog ( 由檔案系統的page cache 永久化到磁盤)

binlog_group_commit_sync_no_delay_count 表示累積多少次以後才組送出 fsync binlog ( 由檔案系統的page cache 永久化到磁盤)

innodb_thread_concurrency 控制 InnoDB 的并發線程上限。也就是說，一旦并發線程數達到這個值，InnoDB 在接收到新請求的時候，就會進入等待狀态，直到有線程退出。通常情況下，我們建議把 innodb_thread_concurrency 設定為 64~128 之間的值。線上程進入鎖等待以後，并發線程的計數會減一，也就是說等行鎖(也包括間隙鎖)的線程是不算在裡面的。MySQL 這樣設計是非常有意義的。因為，進入鎖等待的線程已經不吃 CPU 了；更重要的是，必須這麼設計，才能避免整個系統鎖死。

net_buffer_length 控制net_buffer大小，預設是 16k。MySQL 是“邊讀邊發的”，擷取一行，寫到 net_buffer 中。直到 net_buffer 寫滿，調用網絡接口發出去。

read_rnd_buffer_size Multi-Range Read，回表過程是一行行地查資料，會出現随機通路，如果按照主鍵的遞增順序查詢的話，對磁盤的讀比較接近順序讀，能夠提升讀性能。

這就是 MRR 優化的設計思路。此時，語句的執行流程變成了這樣：

1. 根據索引 a，定位到滿足條件的記錄，将 id 值放入 read_rnd_buffer 中 ;
2. 将 read_rnd_buffer 中的 id 進行遞增排序；
3. 排序後的 id 數組，依次到主鍵 id 索引中查記錄，并作為結果傳回。