[MySQL] Innodb參數最佳化，mysqlinnodb

[MySQL] Innodb參數最佳化，mysqlinnodb

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，這樣可以保證主備庫資料一致，不會有資料丟失。

對於MySQL Innodb參數的設定問題

1。你的使用的命令沒有錯。
2。確定你的mysql還能不能正常使用。
3。很可能，你在修改時，不小心所設定檔中的某個設定給改動了。
 
mysql最佳化

1，最佳化sql，從慢查日誌中找到查詢過慢的sql
2，大表做分區
3，針對錶引擎，加大對應配置參數（innodb為innodb_buffer_pool_size，myisam是key_buffer）
4，做讀寫分離
5，分庫分表
6，硬體升級，做磁碟陣列，或者改用ssd

(1).資料庫設計方面,這是DBA和Architect的責任,設計結構良好的資料庫,必要的時候,去正規化(英文是這個:denormalize,中文翻譯成啥我不知道),允許部分資料冗餘,避免JOIN操作,以提高查詢效率
(2).系統架構設計方面,表散列,把海量資料散列到幾個不同的表裡面.快慢表,快表只留最新資料,慢表是曆史存檔.叢集,主伺服器Read & write,從伺服器read only,或者N台伺服器,各機器互為Master
(3).(1)和(2)超越PHP Programmer的要求了,會更好,不會沒關係.檢查有沒有少加索引
(4).寫高效的SQL語句,看看有沒有寫低效的SQL語句,比如產生笛卡爾積的全串連啊,大量的Group By和order by,沒有limit等等.必要的時候,把資料庫邏輯封裝到DBMS端的預存程序裡面.緩衝查詢結果,explain每一個sql語句
(5).所得皆必須,只從資料庫取必需的資料,比如查詢某篇文章的評論數,select count(*) ... where article_id = ? 就可以了,不要先select * ... where article_id = ?然後msql_num_rows.
只傳送必須的SQL語句,比如修改文章的時候,如果使用者只修改了標題,那就update ... set title = ? where article_id = ?不要set content = ?(大文本)
(6).必要的時候用不同的儲存引擎.比如InnoDB可以減少死結.HEAP可以提高一個數量級的查詢速度