264
人物
MySQL Innodb性能優化
默認情況下，innodb的參數設置的非常小，在生產環境中遠遠不夠用。比如最重要的兩個參數：
innodb_buffer_pool_size 默認是8M
innodb_flush_logs_at_trx_commit 默認設置的是1 也就是同步刷新log(可以這麼理解)
innodb_buffer_pool_size： 這是InnoDB最重要的設置，對InnoDB性能有決定性的影響。默認的
設置隻有8M，所以默認的數據庫設置下麵InnoDB性能很差。在隻有 InnoDB存儲引擎的數據庫服
務器上麵，可以設置60-80%的內存。更精確一點，在內存容量允許的情況下麵設置比InnoDB
tablespaces大10%的內存大小。
innodb_data_file_path：指定表數據和索引存儲的空間，可以是一個或者 多個文件。最後一個數
據文件必須是自動擴充的，也隻有最後一個文件允許自動擴充。這樣，當空間用完後，自動擴充數
據文件就會自動增長（以8MB為單位）以 容納額外的數據。例如：
innodb_data_file_path=/disk1 /ibdata1:900M;/disk2/ibdata2:50M:autoextend兩個數據文件
放在不同的磁盤上。數據首先放在ibdata1 中，當達到900M以後，數據就放在ibdata2中。一旦
達到50MB，ibdata2將以8MB為單位自動增長。如果磁盤滿了，需要在另外的磁盤上麵 增加一個
數據文件。
innodb_data_home_dir：放置表空間數據的目錄，默認在mysql的數據目錄，設置到和MySQL
安裝文件不同的分區可以提高性能。
innodb_log_file_size：該參數決定了recovery speed。太大的話recovery就會比較慢，太小了
影響查詢性能，一般取256M可以兼顧性能和recovery的速度
。
innodb_log_buffer_size：磁盤速度是很慢的，直接將log寫道磁盤會影響InnoDB的性能，該參數
設定了log buffer的大小，一般4M。如果有大的blob操作，可以適當增大。
innodb_flush_logs_at_trx_commit=2： 該參數設定了事務提交時內存中log信息的處理。
1) =1時，在每個事務提交時，日誌緩衝被寫到日誌文件，對日誌文件做到磁盤操作的刷新。Truly
ACID。速度慢。
2) =2時，在每個事務提交時，日誌緩衝被寫到文件，但不對日誌文件做到磁盤操作的刷新。隻有
操作係統崩潰或掉電才會刪除最後一秒的事務，不然不會丟失事務。
3) =0時， 日誌緩衝每秒一次地被寫到日誌文件，並且對日誌文件做到磁盤操作的刷新。任何
mysqld進程的崩潰會刪除崩潰前最後一秒的事務
innodb_file_per_table：可以存儲每個InnoDB表和它的索引在它自己的文件中。
transaction-isolation=READ-COMITTED: 如果應用程序可以運行在READ-COMMITED隔離級別，
做此設定會有一定的性能提升。
innodb_flush_method： 設置InnoDB同步IO的方式：
1) Default – 使用fsync（）。
2) O_SYNC 以sync模式打開文件，通常比較慢。
3) O_DIRECT，在Linux上使用Direct IO。可以顯著提高速度，特別是在RAID係統上。避免額外
的數據複製和double buffering（mysql buffering 和OS buffering）。
innodb_thread_concurrency： InnoDB kernel最大的線程數。
1) 最少設置為(num_disks+num_cpus)*2。
2) 可以通過設置成1000來禁止這個限製
=========================================
介紹：
InnoDB給MySQL提供了具有提交，回滾和崩潰恢複能力的事務安全（ACID兼容）存 儲引擎。
InnoDB鎖定在行級並且也在SELECT語句提供一個Oracle風格一致的非鎖定讀。這些特色增加了
多用戶部署和性能。沒有在InnoDB 中擴大鎖定的需要，因為在InnoDB中行級鎖定適合非常小的空
間。InnoDB也支持FOREIGN KEY強製。在SQL查詢中，你可以自由地將InnoDB類型的表與其它
MySQL的表的類型混合起來，甚至在同一個查詢中也可以混合。
Innodb 的創始人：Heikki Tuuri
Heikki Tuuri在Ｉｎｎｏｄｂ的Ｂｕｇ社區裏也是很活躍的，如果遇到Ｂｕｇ也可以直接提到社區，
得到作者的解答。
為什麼要學習Ｉｎｎｏｄｂ的調優：
目前來說：InnoDB是為Mysql處理巨大數據量時的最大性能設計。它的CPU效率可能是任何
其它基於磁盤的關係數據庫引擎所不能匹敵的。在數據量大的網站或是應用中Innodb是倍受青睞
的。
另一方麵，在數據庫的複製操作中Innodb也是能保證master和slave數據一致有一定的作用。
參數調優內容：
1. 內存利用方麵
2. 日值控製方麵
3. 文件IO分配，空間占用方麵
4. 其它相關參數
１.內存利用方麵：
首先介紹一個Innodb最重要的參數：
innodb_buffer_pool_size
這個參數和MyISAM的key_buffer_size有相似之處，但也是有差別的。這個參數主要緩存
innodb表的索引，數據，插入數據時的緩衝。為Innodb加速優化首要參數。
該參數分配內存的原則：這個參數默認分配隻有8M，可以說是非常小的一個值。如果是一個
專用ＤＢ服務器，那麼他可以占到內存的70%-80%。這個參數 不能動態更改，所以分配需多考慮。
分配過大，會使Swap占用過多，致使Mysql的查詢特慢。如果你的數據比較小，那麼可分配是你
的數據大小＋１０％左 右做為這個參數的值。例如：數據大小為５０Ｍ,那麼給這個值分配
innodb_buffer_pool_size＝６４Ｍ
設置方法：
innodb_buffer_pool_size=4G
這個參數分配值的使用情況可以根據show innodb status\G;中的
———————-
BUFFER POOL AND MEMORY
———————-
Total memory allocated 4668764894;
去確認使用情況。
第二個：
innodb_additional_mem_pool：
開源時代 2010年1月刊 - 59 - 投稿郵箱：rmzhou@staff.chinaunix.net
ChinaUnix.net —–全球最大的Linux/Unix應用與開發者中文社區
作用：用來存放Innodb的內部目錄
這個值不用分配太大，係統可以自動調。不用設置太高。通常比較大數據設置16Ｍ夠用了，如果表
比較多，可以適當的增大。如果這個值自動增加，會在error log有中顯示的。
分配原則：
用show innodb status\G;去查看運行中的ＤＢ是什麼狀態（參考BUFFER POOL AND MEMORY
段中），然後可以調整到適當的值。
———————-
BUFFER POOL AND MEMORY
———————-
Total memory allocated 4668764894; in additional pool allocated 16777216
參考：in additional pool allocated 16777216
根據你的參數情況，可以適當的調整。
設置方法：
innodb_additional_mem_pool=16M
２.關於日值方麵：
innodb_log_file_size
作用：指定日值的大小
分配原則：幾個日值成員大小加起來差不多和你的innodb_buffer_pool_size相等。上限為每個日
值上限大小為4G.一般控製在幾個ＬＯＧ文件相加大小在２Ｇ以內為佳。具體情況還需要看你的事
務大小，數據大小為依據。
說明：這個值分配的大小和數據庫的寫入速度，事務大小，異常重啟後的恢複有很大的關係。
設置方法：
innodb_log_file_size=256M
innodb_log_files_in_group
作用：指定你有幾個日值組。
分配原則： 一般我們可以用２－３個日值組。默認為兩個。
設置方法：
innodb_log_files_in_group=3
innodb_log_buffer_size：
作用：事務在內存中的緩衝。
分配原則：控製在2-8M.這個值不用太多的。他裏麵的內存一般一秒鍾寫到磁盤一次。具體寫入方
式和你的事務提交方式有關。在Ｏｒａｃｌｅ等數據庫了解這個，一般最大指定為３Ｍ比較合適。
參考：Innodb_os_log_written(show global status 可以拿到)
如果這個值增長過快，可以適當的增加innodb_log_buffer_size
另外如果你需要處理大理的ＴＥＸＴ，或是ＢＬＯＢ字段，可以考慮增加這個參數的值。
設置方法：
innodb_log_buffer_size=3M
innodb_flush_logs_at_trx_commit
開源時代 2010年1月刊 - 60 - 投稿郵箱：rmzhou@staff.chinaunix.net
ChinaUnix.net —–全球最大的Linux/Unix應用與開發者中文社區
作用：控製事務的提交方式
分配原則：這個參數隻有３個值，０，１，２請確認一下自已能接受的級別。默認為１，主庫請不
要更改了。
性能更高的可以設置為０或是２，但會丟失一秒鍾的事務。
說明：
這個參數的設置對Ｉｎｎｏｄｂ的性能有很大的影響，所以在這裏給多說明一下。
當這個值為1時：innodb 的事務LOG在每次提交後寫入日值文件，並對日值做刷新到磁盤。這個
可以做到不丟任何一個事務。
當 這個值為2時：在每個提交，日誌緩衝被寫到文件，但不對日誌文件做到磁盤操作的刷新,在對日
誌文件的刷新在值為2的情況也每秒發生一次。但需要注意的是， 由於進程調用方麵的問題，並不
能保證每秒１００％的發生。從而在性能上是最快的。但操作係統崩潰或掉電才會刪除最後一秒的
事務。
當這個值為0時：日誌緩衝每秒一次地被寫到日誌文件，並且對日誌文件做到磁盤操作的刷新，但
是在一個事務提交不做任何操作。mysqld進程的崩潰會刪除崩潰前最後一秒的事務。
從以上分析，當這個值不為１時，可以取得較好的性能，但遇到異常會有損失，所以需要根據自已
的情況去衡量。
設置方法：
innodb_flush_logs_at_trx_commit=1
3. 文件IO分配，空間占用方麵
innodb_file_per_table
作用：使每個Innodb的表，有自已獨立的表空間。如刪除文件後可以回收那部分空間。
分配原則：隻有使用不使用。但ＤＢ還需要有一個公共的表空間。
設置方法：
innodb_file_per_table=1
innodb_file_io_threads
作用：文件讀寫ＩＯ數，這個參數隻在Windows上起作用。在LINUX上隻會等於４
設置方法：
innodb_file_io_threads=4
innodb_open_files
作用：限製Innodb能打開的表的數據。
分配原則：如果庫裏的表特別多的情況，請增加這個。這個值默認是３００。
設置方法：
innodb_open_files=800
請適當的增加table_cache
4. 其它相關參數
這裏說明一個比較重要的參數：
innodb_flush_method
作用：Ｉｎｎｏｄｂ和係統打交道的一個ＩＯ模型
分配原則：Windows不用設置。
Ｕｎｉｘ可以設置：fsync() or O_SYNC/O_DSYNC
如果係統可以禁止係統的Ｃａｃｈｅ那就把他禁了。
Ｌｉｎｕｘ可以選擇：O_DIRECT
直接寫入磁盤，禁止係統Ｃａｃｈｅ了
設置方法：
innodb_flush_method=O_DIRECT
innodb_max_dirty_pages_pct
作用：控製Innodb的髒頁在緩衝中在那個百分比之下，值在範圍1-100,默認為90.
這個參數的另一個用處：當Ｉｎｎｏｄｂ的內存分配過大，致使Ｓｗａｐ占用嚴重時，可以適當的
減小調整這個值，使達到Ｓｗａｐ空間釋放出來。建義：這個值最大在９０％，最小在１５％。太
大，緩存中每次更新需要致換數據頁太多，太小，放的數據頁太小，更新操作太慢。
設置方法：
innodb_max_dirty_pages_pct＝90
動態更改需要有Ｓｕｐｅｒ權限：
set global innodb_max_dirty_pages_pct=50;
總結：
這裏隻算是列出了Ｉｎｎｏｄｂ部分的重要參數，不能認為是對Ｍｙｓｑｌ的整體調優。Ｍｙ
ｓｑｌ的參數一般分為：全局參數，具體引擎的參數。全局參數方麵請參考
https://imysql.cn/2007_12_08_optimize_mysql_under_linux yejr的那個Ｍｙｓｑｌ調優的ＰＰＴ。
========================================
通過這次MySQL InnoDB的調優經曆，發現一些和MySQL官方推薦配置不符合的疑惑之處，值得
思考和探索：
1、innodb_flush_method究竟應不應該使用O_DIRECT？
所有MySQL調優的建議都說，如果硬件沒有預讀功能，那麼使用O_DIRECT將極大降低InnoDB的
性能，因為O_DIRECT跳過了操作係統的文件係統Disk Cache，讓MySQL直接讀寫磁盤了。
但是在我的實踐中來看，如果不使用O_DIRECT，操作係統被迫開辟大量的Disk Cache用於
innodb的讀寫緩存，不但沒有提高讀寫性能，反而造成讀寫性能急劇下降。而且buffer pool的數
據緩存和操作係統Disk Cache緩存造成了Double buffer的浪費，顯然從我這個實踐來看，浪費
得非常厲害。
說O_DIRECT造成MySQL直接讀寫磁盤造成得性能下降問題，我覺得完全是杞人憂天。因為從
JavaEye的數據庫監測來看，Innodb 的 buffer pool命中率非常高，有98%以上，真正的磁盤操
作是微乎其微的。為了1%的磁盤操作能夠得到Disk Cache，而浪費了98%的double buffer內存
空間，無論從性能上看，還是從內存資源的消耗來看，都是非常不明智的。
2、innodb_log_file_size究竟應該大一點，還是小一點？
所有MySQL調優建議都說，innodb_log_file_size要越大越好，避免無謂的buffer pool的flush操
作。
但是在我的實踐中來看，innodb_log_file_size開得太大，會明顯增加innodb的log寫入操作，而
且會造成操作係統需要更多的Disk Cache開銷。
因此從我的經驗來看，innodb_flush_method=O_DIRECT是必須的，而innodb_log_file_size也
不宜太大。
原文鏈接：https://blog.csdn.net/binger819623/archive/2009/11/28/4882382.aspx
最後更新：2017-01-04 22:34:34