介紹
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數據一致有一定的作用
參數調優內容
內存利用方面
日志控制方面
文件IO分配空間占用方面
其它相關參數
１內存利用方面
首先介紹一個Innodb最重要的參數
innodb_buffer_pool_size
這個參數和MyISAM的key_buffer_size有相似之處但也是有差別的這個參數主要緩存innodb表的索引數據插入數據時的緩沖為Innodb加速優化首要參數
該參數分配內存的原則這個參數默認分配只有M可以說是非常小的一個值如果是一個專用ＤＢ服務器那麼他可以占到內存的%%這個參 數不能動態更改所以分配需多考慮分配過大會使Swap占用過多致使Mysql的查詢特慢如果你的數據比較小那麼可分配是你的數據大小＋１０％ 左右做為這個參數的值例如數據大小為５０Ｍ那麼給這個值分配innodb_buffer_pool_size＝６４Ｍ
設置方法
innodb_buffer_pool_size=G
這個參數分配值的使用情況可以根據show innodb status\G;中的
BUFFER POOL AND MEMORY
Total memory allocated ;
去確認使用情況
第二個
innodb_additional_mem_pool
作用用來存放Innodb的內部目錄
這個值不用分配太大系統可以自動調不用設置太高通常比較大數據設置Ｍ夠用了如果表比較多可以適當的增大如果這個值自動增加會在error log有中顯示的
分配原則
用show innodb status\G;去查看運行中的ＤＢ是什麼狀態（參考BUFFER POOL AND MEMORY段中）然後可以調整到適當的值
BUFFER POOL AND MEMORY
Total memory allocated ; in additional pool allocated
參考in additional pool allocated
根據你的參數情況可以適當的調整
設置方法
innodb_additional_mem_pool=M
２關於日值方面
innodb_log_file_size
作用指定日值的大小
分配原則幾個日值成員大小加起來差不多和你的innodb_buffer_pool_size相等上限為每個日值上限大小為G一般控制在幾個ＬＯＧ文件相加大小在２Ｇ以內為佳具體情況還需要看你的事務大小數據大小為依據
說明這個值分配的大小和數據庫的寫入速度事務大小異常重啟後的恢復有很大的關系
設置方法
innodb_log_file_size=M
innodb_log_files_in_group
作用指定你有幾個日值組
分配原則 一般我們可以用２－３個日值組默認為兩個
設置方法
innodb_log_files_in_group=
innodb_log_buffer_size
作用事務在內存中的緩沖
分配原則控制在M這個值不用太多的他裡面的內存一般一秒鐘寫到磁盤一次具體寫入方式和你的事務提交方式有關在Ｏｒａｃｌｅ等數據庫了解這個一般最大指定為３Ｍ比較合適
參考Innodb_os_log_written(show global status 可以拿到)
如果這個值增長過快可以適當的增加innodb_log_buffer_size
另外如果你需要處理大理的ＴＥＸＴ或是ＢＬＯＢ字段可以考慮增加這個參數的值
設置方法
innodb_log_buffer_size=M
innodb_flush_logs_at_trx_commit
作用控制事務的提交方式
分配原則這個參數只有３個值０１２請確認一下自已能接受的級別默認為１主庫請不要更改了
性能更高的可以設置為０或是２但會丟失一秒鐘的事務
說明
這個參數的設置對Ｉｎｎｏｄｂ的性能有很大的影響所以在這裡給多說明一下
當這個值為時innodb 的事務LOG在每次提交後寫入日值文件並對日值做刷新到磁盤這個可以做到不丟任何一個事務
當這個值為時在每個提交日志緩沖被寫到文件但不對日志文件做到磁盤操作的刷新在對日志文件的刷新在值為的情況也每秒發生一次但需要注意的 是由於進程調用方面的問題並不能保證每秒１００％的發生從而在性能上是最快的但操作系統崩潰或掉電才會刪除最後一秒的事務
當這個值為時日志緩沖每秒一次地被寫到日志文件並且對日志文件做到磁盤操作的刷新但是在一個事務提交不做任何操作mysqld進程的崩潰會刪除崩潰前最後一秒的事務
從以上分析當這個值不為１時可以取得較好的性能但遇到異常會有損失所以需要根據自已的情況去衡量
設置方法
innodb_flush_logs_at_trx_commit=
文件IO分配空間占用方面
innodb_file_per_table
作用使每個Innodb的表有自已獨立的表空間如刪除文件後可以回收那部分空間
分配原則只有使用不使用但ＤＢ還需要有一個公共的表空間
設置方法
innodb_file_per_table=
innodb_file_io_threads
作用文件讀寫ＩＯ數這個參數只在Windows上起作用在LINUX上只會等於４
設置方法
innodb_file_io_threads=
innodb_open_files
作用限制Innodb能打開的表的數據
分配原則如果庫裡的表特別多的情況請增加這個這個值默認是３００
設置方法
innodb_open_files=
請適當的增加table_cache
其它相關參數
這裡說明一個比較重要的參數
innodb_flush_method
作用Ｉｎｎｏｄｂ和系統打交道的一個ＩＯ模型
分配原則Windows不用設置
Ｕｎｉｘ可以設置fsync() or O_SYNC/O_DSYNC
如果系統可以禁止系統的Ｃａｃｈｅ那就把他禁了
Ｌｉｎｕｘ可以選擇O_DIRECT
直接寫入磁盤禁止系統Ｃａｃｈｅ了
設置方法
innodb_flush_method=O_DIRECT
innodb_max_dirty_pages_pct
作用控制Innodb的髒頁在緩沖中在那個百分比之下值在范圍默認為
這個參數的另一個用處當Ｉｎｎｏｄｂ的內存分配過大致使Ｓｗａｐ占用嚴重時可以適當的減小調整這個值使達到Ｓｗａｐ空間釋放出來建義這個值最大在９０％最小在１５％太大緩存中每次更新需要致換數據頁太多太小放的數據頁太小更新操作太慢
設置方法
innodb_max_dirty_pages_pct＝
動態更改需要有Ｓｕｐｅｒ權限
set global innodb_max_dirty_pages_pct=;
總結
這裡只算是列出了Ｉｎｎｏｄｂ部分的重要參數不能認為是對Ｍｙｓｑｌ的整體調優Ｍｙｓｑｌ的參數一般分為全局參數具體引擎的參數全局參數方面請參考linux>___optimize_mysql_under_linux yejr的那個Ｍｙｓｑｌ調優的ＰＰＴ
From:http://tw.wingwit.com/Article/program/MySQL/201311/29325.html
