看板 Knuckles_note
作者 標題 [MySQL] InnoDB 的磁碟存取效能問題
時間 2013年01月23日 Wed. PM 11:36:05
MySQL 使用 InnoDB 做為主要資料庫引擎時
主要是依 innodb_buffer_pool_size 的設定值將DB放在記憶體裡操作
所以對於一個主要用來當資料庫server的主機
要將 innodb_buffer_pool_size 設為實體記憶體的 70%~80%
但在操作資料庫時，還是會有大量的磁碟存取操作
必需再 my.cnf 加一些設定效能才會好
如果遇到實體記憶體還有空間，Swap卻爆滿，CPU的wa飆高時
可能就是需要加下面這些設定了
memlock
加這行強制讓mysql的東西存在實體記憶體，而不會移到Swap
innodb_flush_method=O_DIRECT
加這行讓mysql直接對磁碟存取，而不再透過Linux的cache、buffer機制
innodb_flush_log_at_trx_commit = 0
加這行設定記憶體的資料每秒鐘跟磁碟上的資料同步一次
而不要每個指令都同步一次
參考：
阿里巴巴集团数据库技术团队 » MySQL如何避免使用swap
這個參數決定了Linux是傾向於使用swap，還是傾向於釋放文件系統cache。在內存緊張的情況下，數值越低越傾向於釋放文件系統cache。
當然，這個參數只能減少使用swap的概率，並不能避免Linux使用swap。
2、修改MySQL的配置參數innodb_flush_method，開啟O_DIRECT模式。
這個參數會強迫mysqld進程的地址空間一直被鎖定在物理內存上，對於os來說是非常霸道的一個要求。必須要用root帳號來啟動MySQL才能生效。
還有一個比較複雜的方法，指定MySQL使用大頁內存（Large Page）。Linux上的大頁內存是不會被換出物理內存的，和memlock有異曲同工之妙。具體的配置方法可以參考：http://harrison-fisk.blogspot.com/2009/01/enabling-innodb-large-pages-on-linux.html
Linux有很多很好的內存、IO調度機制，但是並不會適用於所有場景。對於DBA來說Linux比較讓人頭疼的一個地方是，它不會因為MySQL很重要就避免將分配給MySQL的地址空間映射到swap上。對於頻繁進行讀寫操作的系統而言，數據看似在內存而實際上在磁盤是非常糟糕的，響應時間的增長很可能直接拖垮整個系統。這篇blog主要講講我們作為DBA，怎樣盡量避免MySQL慘遭swap的毒手。
首先我們要了解點基礎的東西，比如說為什麼會產生swap。假設我們的物理內存是16G，swap是4G。如果MySQL本身已經佔用了12G物理內存，而同時其他程序或者係統模塊又需要6G內存，這時候操作系統就可能把MySQL所擁有的一部分地址空間映射到swap上去。
cp一個大文件，或用mysqldump導出一個很大的數據庫的時候，文件系統往往會向Linux申請大量的內存作為cache，一不小心就會導致L使用swap。這個情景比較常見，以下是最簡單的三個調整方法：
1、/proc/sys/vm/swappiness的內容改成0（臨時），/etc/sysctl.conf上添加vm.swappiness=0（永久）這個參數決定了Linux是傾向於使用swap，還是傾向於釋放文件系統cache。在內存緊張的情況下，數值越低越傾向於釋放文件系統cache。
當然，這個參數只能減少使用swap的概率，並不能避免Linux使用swap。
2、修改MySQL的配置參數innodb_flush_method，開啟O_DIRECT模式。
這種情況下，InnoDB的buffer pool會直接繞過文件系統cache來訪問磁盤，但是redo log依舊會使用文件系統cache。值得注意的是，Redo log是覆寫模式的，即使使用了文件系統的cache，也不會佔用太多。
3、添加MySQL的配置參數memlock 這個參數會強迫mysqld進程的地址空間一直被鎖定在物理內存上，對於os來說是非常霸道的一個要求。必須要用root帳號來啟動MySQL才能生效。
還有一個比較複雜的方法，指定MySQL使用大頁內存（Large Page）。Linux上的大頁內存是不會被換出物理內存的，和memlock有異曲同工之妙。具體的配置方法可以參考：http://harrison-fisk.blogspot.com/2009/01/enabling-innodb-large-pages-on-linux.html
innodb_flush_log_at_trx_commit和sync_binlog innodb_flush_method _人人IT網
innodb_flush_log_at_trx_commit
主要控制了innodb將log buffer中的數據寫入日志文件並flush磁盤的時間點，取值分別為0、1、2三個。
0，表示當事務提交時，不做日志寫入操作，而是每秒鍾將log buffer中的數據寫入日志文件並flush磁盤一次；
1，則在每秒鍾或是每次事物的提交都會引起日志文件寫入、flush磁盤的操作，確保了事務的ACID；
設置為2，每次事務提交引起寫入日志文件的動作，但每秒鍾完成一次flush磁盤操作。
innodb_flush_method
影響了服務器flush數據或日志文件的方法。有三個選值：
預設的default，innodb使用fsync()函數flush數據和日志文件；
O_DIRECT，innodb使用O_DIRECT的方式打開數據文件，並使用fsync()函數flush數據和日志文件；
O_DSYNC，innodb使用O_SYNC打開並flush日志文件，使用fsync()函數flush數據文件。
![[圖]]()
sync_binlog:
做過一些相應的測試，獲得一些以上三參數搭配使用的結論：
①（0， default）每秒鍾調用fsync()同步一次innodb logfile，io壓力小，性能高，但可能損失數據；
②（1， default）每秒鍾和每次commit調用fsync()同步一次innodb logfile，保證數據完整的同時，加大了io壓力，吞吐量相對低；
③（2， default）速度介於前兩者之間，也不能完全保證數據的安全；
④（0，O_DSYNC）每秒同步日志及數據文件，吞吐量等情況應與（0， default）基本一致；
⑤（1，O_DSYNC）每秒鍾和每次commit同步數據及日志文件，相應於（1， default）情況一致；
innodb_flush_log_at_trx_commit
主要控制了innodb將log buffer中的數據寫入日志文件並flush磁盤的時間點，取值分別為0、1、2三個。
0，表示當事務提交時，不做日志寫入操作，而是每秒鍾將log buffer中的數據寫入日志文件並flush磁盤一次；
1，則在每秒鍾或是每次事物的提交都會引起日志文件寫入、flush磁盤的操作，確保了事務的ACID；
設置為2，每次事務提交引起寫入日志文件的動作，但每秒鍾完成一次flush磁盤操作。
顯然，設置為0或2可以減小系統的io壓力，特別是0時，速度最快，提高mysql寫操作的吞吐量，但mysql或操作系統的崩潰、斷電都可能會引起數據的丟失，設置為2時os的崩潰和斷電可能會引起數據的丟失。
innodb_flush_method
影響了服務器flush數據或日志文件的方法。有三個選值：
預設的default，innodb使用fsync()函數flush數據和日志文件；
O_DIRECT，innodb使用O_DIRECT的方式打開數據文件，並使用fsync()函數flush數據和日志文件；
O_DSYNC，innodb使用O_SYNC打開並flush日志文件，使用fsync()函數flush數據文件。
注：在類unix操作系統中，文件的打開方式為O_DIRECT會最小化緩沖對io的影響，該文件的io是直接在用戶空間的buffer上操作的，並且io操作是同步的，因此不管是read()系統調用還是write()系統調用，數據都保證是從磁盤上讀取的；O_SYNC方式表示以同步io的方式打開文件，任何寫操作都將阻塞到數據寫入物理磁盤後才返回。fsync(int filedes)函數只對由文件描述符filedes指定的單一文件起作用，並且等待寫磁盤操作結束，然後返回。fdatasync(int filedes)函數類似於fsync，但它只影響文件的數據部分。而除數據外，fsync還會同步更新文件的元信息到磁盤。
![[圖]](http://rritw.com/uploads/allimg/2012-11-28/innodb_flush_method-1344419259.png)
sync_binlog:
sync_binlog=N表示每寫緩沖N次，就同步到磁盤，對於innodb事務，即使事務還未commit，sync_binlog=1時，還會寫日志。如果此時崩潰，這個事務會被回滾，但binlog已經記錄了該事務信息，無法回滾。可以通過設置：innodb_support_xa=1解決。
影響了binary log的flush，當為1時，每個事物提交後，mysql將用fdatasync()函數將二進制日志同步到磁盤上；當為0時，mysql不會做額外的flush，而是依靠os的flush。
做過一些相應的測試，獲得一些以上三參數搭配使用的結論：
1）O_DIRECT的flush_method更適合於操作系統內存有限的情況下（可以避免不必要的對交換空間的讀寫操作），否則，它會由於禁用了os的緩沖降低對數據的讀寫操作的效能。
2）Sync_binlog為1時，每個含有修改操作的事物提交後，mysql將把二進制日志同步到磁盤上，增大了io壓力，引起相應瓶頸，會降低對數據寫操作的效率。而select操作不寫入binary log，所以不受任何影響。
3）對於innodb_flush_log_at_trx_commit與innodb_flush_method的不同組合（0|1|2，default|O_DSYNC）（對於O_DIRECT的情況特殊，已經在1）中單獨做了總結在此不累述）分析得：
①（0， default）每秒鍾調用fsync()同步一次innodb logfile，io壓力小，性能高，但可能損失數據；
②（1， default）每秒鍾和每次commit調用fsync()同步一次innodb logfile，保證數據完整的同時，加大了io壓力，吞吐量相對低；
③（2， default）速度介於前兩者之間，也不能完全保證數據的安全；
④（0，O_DSYNC）每秒同步日志及數據文件，吞吐量等情況應與（0， default）基本一致；
⑤（1，O_DSYNC）每秒鍾和每次commit同步數據及日志文件，相應於（1， default）情況一致；
⑥（2，O_DSYNC）雖然innodb_flush_log_at_trx_commit設置為2，innodb被告知每次事務提交引起寫入日志文件的動作，每秒鍾完成一次flush磁盤操作，但由於O_DSYNC的設置使得os對日志自動做了同步工作，所以吞吐量等情況與（1， default）和（1，O_DSYNC）相一致。
--
※ 作者: Knuckles 時間: 2013-01-23 23:36:05
※ 編輯: Knuckles 時間: 2013-01-24 16:54:45
※ 看板: KnucklesNote 文章推薦值: 0 目前人氣: 0 累積人氣: 1969
回列表(←)
分享