​innodb存儲引擎優化

innodb存儲引擎優化

       目前來說：InnoDB是為Mysql處理巨大資料量時的最大性能設計。它的CPU效率可能是任何其它基于磁盤的關系資料庫引擎所不能匹敵的。在資料量大 的網站或是應用中Innodb是倍受青睐的。另一方面，在資料庫的複制操作中Innodb也是能保證master和slave資料一緻有一定的作用。

設定内容：

1. 記憶體利用方面

    參數：innodb_buffer_pool_size

    預設配置設定隻有8M。如果是一個專用ＤＢ伺服器，那麼他可以占到記憶體的70%-80%。這個參數不能動态更改，是以配置設定需 多考慮。配置設定過大，會使Swap占用過多，緻使Mysql的查詢特慢。如果你的資料比較小，那麼可配置設定是你的資料大小＋１０％左右做為這個參數的值。show innodb status，執行此指令，去确認大小。

    例如：資料大小為５０Ｍ,那麼給這個值配置設定innodb_buffer_pool_size＝６４Ｍ

    參數: innodb_additional_mem_pool 

   系統可以自動調，不用設定太高。

2. 日值控制方面

    參數：innodb_log_file_size

    這個值配置設定的大小和資料庫的寫入速度，事務大小，異常重新開機後的恢複有很大的關系。設定方法：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

作用：控制事務的送出方式配置設定原則：這個參 數隻有３個值，０，１，２請确認一下自已能接受的級别。預設為１，主庫請不要更改了。性能更高的可以設定為０或是２，但會丢失一秒鐘的事務。說明：這個參 數的設定對Ｉｎｎｏｄｂ的性能有很大的影響，是以在這裡給多說明一下。當這個值為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

作用：Ｉｎｎｏｄｂ和系統打交道的一個ＩＯ模型配置設定原則，Ｕｎｉｘ可以設定：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; 

本文轉自birdinroom 51CTO部落格，原文連結：http://blog.51cto.com/birdinroom/1602603，如需轉載請自行聯系原作者