[MySQL]Innodb參數優化

[MySQL]Innodb參數優化

innodb_buffer_pool_size

innodb_buffer_pool_size 參數用來設置Innodb 最主要的Buffer(Innodb_Buffer_Pool)的大小，也就是緩存用戶表及索引數據的最主要緩存空間，對Innodb 整體性能影響也最大。

對於一台單獨給MySQL 使用的主機，並假設只使用innodb引擎，一般建議該參數為物流內存的75%左右。

當系統上線之後，我們可以通過Innodb 存儲引擎提供給我們的關於Buffer Pool 的實時狀態信息作出進一步分析，來確定系統中Innodb 的Buffer Pool 使用情況是否正常高效：

mysql> show status like 'Innodb_buffer_pool_%';
+-----------------------------------------+---------------+
| Variable_name                           | Value         |
+-----------------------------------------+---------------+
| Innodb_buffer_pool_pages_data           | 999020        | 
| Innodb_buffer_pool_pages_dirty          | 47643         | 
| Innodb_buffer_pool_pages_flushed        | 474668167     | 
| Innodb_buffer_pool_pages_LRU_flushed    | 365125        | 
| Innodb_buffer_pool_pages_free           | 0             | 
| Innodb_buffer_pool_pages_made_not_young | 0             | 
| Innodb_buffer_pool_pages_made_young     | 203410903     | 
| Innodb_buffer_pool_pages_misc           | 49552         | 
| Innodb_buffer_pool_pages_old            | 368697        | 
| Innodb_buffer_pool_pages_total          | 1048572       | 
| Innodb_buffer_pool_read_ahead_rnd       | 0             | 
| Innodb_buffer_pool_read_ahead           | 66348855      | 
| Innodb_buffer_pool_read_ahead_evicted   | 3716819       | 
| Innodb_buffer_pool_read_requests        | 3215992991498 | 
| Innodb_buffer_pool_reads                | 65634998      | 
| Innodb_buffer_pool_wait_free            | 651           | 
| Innodb_buffer_pool_write_requests       | 21900970785   | 
+-----------------------------------------+---------------+

從上面的值我們可以看出總共1048572個 pages，其中放數據的有999020個 pages，且已沒有free狀態的page。
read 請求3215992991498次，其中有65634998次所請求的數據在buffer pool 中沒有，也就是說有65634998 次是通過讀取物理磁盤來讀取數據的，所以很容易也就得出了Innodb Buffer Pool 的Read 命中率大概在為：(3215992991498- 65634998)/ 3215992991498* 100% = 99.998%。

innodb_buffer_pool_instances

該參數將innodb_buffer_pool劃分為不同的instance，每個instance獨立的LRU、FLUSH、FREE、獨立的mutex控制。

對於比較大的innodb_buffer_pool_size，建議設置多個instances，避免內存鎖的爭用。

innodb_log_file_size

設置innodb redo log file的大小，從性能角度來看，日志文件越大越好，可以減少buffer pool checkpoint的頻率，但是在MySQL的官方版本中，innodb_log_files_in_group*innodb_log_files_in_group不能超過4G。

日志文件越大，也意味著MySQL實例crash之後恢復的時間越長，不過一般生成系統都會配置主從庫，因此這個因素可以忽略不考慮。

一般來說，在我個人維護的環境中，比較偏向於將事務日志設置為3 組，每個日志設置為256MB 大小，整體效果還算不錯。

innodb_log_buffer_size

顧名思義，這個參數就是用來設置Innodb 的Log Buffer 大小的，系統默認值為1MB。Log Buffer的主要作用就是緩沖Log 數據，提高寫Log 的IO 性能。一般來說，如果你的系統不是“寫負載非常高且以大事務居多”的話，8MB 以內的大小就完全足夠了。

我們也可以通過系統狀態參數提供的性能統計數據來分析Log 的使用情況：

mysql> show status like 'innodb_log%';
+---------------------------+------------+
| Variable_name             | Value      |
+---------------------------+------------+
| Innodb_log_waits          | 0          | 
| Innodb_log_write_requests | 3486920147 | 
| Innodb_log_writes         | 352577360  | 
+---------------------------+------------+

如果Innodb_log_waits不等於0的話，表示出現過Log Buffer的寫等待，表示innodb_log_buffer_size有可能過小。

innodb_thread_concurrency

該參數表示innodb最大線程並發量，官方推薦設為0，表示由innodb自己控制，但實踐證明，當並發過大時，innodb自己會控制不當，可能導致MySQL hang死，所以一般建議為CPU核心數（不含超線程）

innodb_io_capacity

表示每秒鐘IO設備處理數據頁的上限，如果硬盤性能比較好，可以設大一些（如1000）。

innodb_max_dirty_pages_pct

表示innodb從buffer中刷新髒頁的比例不超過這個值，每次checkpoint的髒頁刷新為：innodb_max_dirty_pages_pct*innodb_io_capacity

Innodb_flush_method

用來設置Innodb 打開和同步數據文件以及日志文件的方式，不過只有在Linux & Unix 系統上面有效。當我們設置為O_DSYNC，則系統以O_SYNC 方式打開和刷新日志文件， 通過fsync() 來打開和刷新數據文件。而設置為O_DIRECT 的時候， 則通過O_DIRECT（Solaris 上為directio()）打開數據文件，同時以fsync()來刷新數據和日志文件。
總的來說，innodb_flush_method 的不同設置主要影響的是Innodb 在不同運行平台下進行IO 操作的時候所調用的操作系統IO 借口的區別。而不同的IO 操作接口對數據的處理方式會有一定的區別，所以處理性能也會有一定的差異。一般來說，如果我們的磁盤是通過RAID 卡做了硬件級別的RAID，建議可以使用O_DIRECT，可以一定程度上提高IO 性能，但如果RAID Cache 不夠的話，還是需要謹慎對待。

innodb_file_per_table

一般建議開啟，因為不同的表空間可以靈活設置數據目錄的地址，避免共享表空間產生的IO競爭。

innodb_flush_log_at_trx_commit

innodb_flush_log_at_trx_commit = 0，Innodb 中的Log Thread 每隔1 秒鐘會將log buffer中的數據寫入到文件，同時還會通知文件系統進行文件同步的flush 操作，保證數據確實已經寫入到磁盤上面的物理文件。但是，每次事務的結束（commit 或者是rollback）並不會觸發Log Thread 將log buffer 中的數據寫入文件。所以，當設置為0 的時候，當MySQL Crash 和OS Crash 或者主機斷電之後，最極端的情況是丟失1 秒時間的數據變更。

innodb_flush_log_at_trx_commit = 1，這也是Innodb 的默認設置。我們每次事務的結束都會觸發Log Thread 將log buffer 中的數據寫入文件並通知文件系統同步文件。這個設置是最安全的設置，能夠保證不論是MySQL Crash 還是OS Crash 或者是主機斷電都不會丟失任何已經提交的數據。

innodb_flush_log_at_trx_commit = 2，當我們設置為2 的時候，Log Thread 會在我們每次事務結束的時候將數據寫入事務日志，但是這裡的寫入僅僅是調用了文件系統的文件寫入操作。而我們的文件系統都是有緩存機制的，所以Log Thread 的這個寫入並不能保證內容真的已經寫入到物理磁盤上面完成持久化的動作。文件系統什麼時候會將緩存中的這個數據同步到物理磁盤文件Log Thread 就完全不知道了。所以，當設置為2 的時候，MySQL Crash 並不會造成數據的丟失，但是OS Crash 或者是主機斷電後可能丟失的數據量就完全控制在文件系統上了。

從上面的分析我們可以看出，當innodb_flush_log_at_trx_commit 設置為1 的時候是最安全的，但是由於所做的IO 同步操作也最多，所以性能也是三種設置中最差的一種。如果設置為0，則每秒有一次同步，性能相對高一些。如果設置為2，可能性能是三這種最好的。但是也可能是出現Crash後丟失數據最多的。到底該如何設置設置，就要根據具體的場景來分析了。一般來說，如果完全不能接受數據的丟失，那麼我們肯定會通過犧牲一定的性能來換取數據的安全性，選擇設置為1。而如果我們可以丟失很少量的數據(比如說1 秒之內)，那麼我們可以設置為0。當然，如果大家覺得我們的OS 足夠穩定，主機硬件設備，而且主機的供電系統也足夠安全，我們也可以將innodb_flush_log_at_trx_commit 設置為2 讓系統的整體性能盡可能的高。

transaction-isolation

對於高並發應用來說，為了盡可能保證數據的一致性，避免並發可能帶來的數據不一致問題，自然是事務隔離級別越高越好。但是，對於Innodb 來說，所使用的事務隔離級別越高，實現復雜度自然就會更高，所需要做的事情也會更多，整體性能也就會更差。

所以，我們需要分析自己應用系統的邏輯，選擇可以接受的最低事務隔離級別。以在保證數據安全一致性的同時達到最高的性能。
雖然Innodb 存儲引擎默認的事務隔離級別是REPEATABLE READ，但實際上在我們大部分的應用場景下，都只需要READ COMMITED 的事務隔離級別就可以滿足需求了。

sync_binlog

表示每次刷新binlog到磁盤的數目。

對於核心系統，我們需要采用雙1模式，即：innodb_flush_log_at_trx_commit=1, sync_binlog=1，這樣可以保證主備庫數據一致，不會有數據丟失。