默認情況下，innodb的參數(shù)設置的非常小，在生產(chǎn)環(huán)境中遠遠不夠用

比如最重要的兩個參數(shù)

innodb_buffer_pool_size 默認是8M

innodb_flush_logs_at_trx_commit 默認設置的是1 也就是同步刷新log(可以這么理解)

innodb_buffer_pool_size： 這是InnoDB最重要的設置，對InnoDB性能有決定性的影響。默認的設置只有8M，所以默認的數(shù)據(jù)庫設置下面InnoDB性能很差。在只有 InnoDB存儲引擎的數(shù)據(jù)庫服務器上面，可以設置60-80%的內(nèi)存。更精確一點，在內(nèi)存容量允許的情況下面設置比InnoDB tablespaces大10%的內(nèi)存大小。

innodb_data_file_path：指定表數(shù)據(jù)和索引存儲的空間，可以是一個或者 多個文件。最后一個數(shù)據(jù)文件必須是自動擴充的，也只有最后一個文件允許自動擴充。這樣，當空間用完后，自動擴充數(shù)據(jù)文件就會自動增長（以8MB為單位）以 容納額外的數(shù)據(jù)。例如： innodb_data_file_path=/disk1 /ibdata1:900M;/disk2/ibdata2:50M:autoextend兩個數(shù)據(jù)文件放在不同的磁盤上。數(shù)據(jù)首先放在ibdata1 中，當達到900M以后，數(shù)據(jù)就放在ibdata2中。一旦達到50MB，ibdata2將以8MB為單位自動增長。如果磁盤滿了，需要在另外的磁盤上面 增加一個數(shù)據(jù)文件。

innodb_data_home_dir：放置表空間數(shù)據(jù)的目錄，默認在mysql的數(shù)據(jù)目錄，設置到和MySQL 安裝文件不同的分區(qū)可以提高性能。

innodb_log_file_size：該參數(shù)決定了recovery speed。太大的話recovery就會比較慢，太小了影響查詢性能，一般取256M可以兼顧性能和recovery的速度

。

innodb_log_buffer_size： 磁盤速度是很慢的，直接將log寫道磁盤會影響InnoDB的性能，該參數(shù)設定了log buffer的大小，一般4M。如果有大的blob操作，可以適當增大。

innodb_flush_logs_at_trx_commit=2： 該參數(shù)設定了事務提交時內(nèi)存中l(wèi)og信息的處理。

? 1) =1時，在每個事務提交時，日志緩沖被寫到日志文件，對日志文件做到磁盤操作的刷新。Truly ACID。速度慢。

? 2) =2時，在每個事務提交時，日志緩沖被寫到文件，但不對日志文件做到磁盤操作的刷新。只有操作系統(tǒng)崩潰或掉電才會刪除最后一秒的事務，不然不會丟失事務。

? 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系統(tǒng)上。避免額外的數(shù)據(jù)復制和double buffering（mysql buffering 和OS buffering）。

innodb_thread_concurrency： InnoDB kernel最大的線程數(shù)。

? 1) 最少設置為(num_disks+num_cpus)*2。

? 2) 可以通過設置成1000來禁止這個限制

=========================================

介紹：

　　InnoDB給MySQL提供了具有提交，回滾和崩潰恢復能力的事務安全（ACID兼容）存 儲引擎。InnoDB鎖定在行級并且也在SELECT語句提供一個Oracle風格一致的非鎖定讀。這些特色增加了多用戶部署和性能。沒有在InnoDB 中擴大鎖定的需要，因為在InnoDB中行級鎖定適合非常小的空間。InnoDB也支持FOREIGN KEY強制。在SQL查詢中，你可以自由地將InnoDB類型的表與其它MySQL的表的類型混合起來，甚至在同一個查詢中也可以混合。

　 Innodb 的創(chuàng)始人：Heikki Tuuri

　Heikki Tuuri在Ｉｎｎｏｄｂ的Ｂｕｇ社區(qū)里也是很活躍的，如果遇到Ｂｕｇ也可以直接提到社區(qū)，得到作者的解答。

為什么要學習Ｉｎｎｏｄｂ的調(diào)優(yōu)：

　　目前來說：InnoDB是為Mysql處理巨大數(shù)據(jù)量時的最大性 能設計。它的CPU效率可能是任何其它基于磁盤的關(guān)系數(shù)據(jù)庫引擎所不能匹敵的。在數(shù)據(jù)量大的網(wǎng)站或是應用中Innodb是倍受青睞的。

　　另一方 面，在數(shù)據(jù)庫的復制操作中Innodb也是能保證master和slave數(shù)據(jù)一致有一定的作用。

參數(shù)調(diào)優(yōu)內(nèi)容：

　　1. 內(nèi)存利用方面

　　2. 日值控制方面

　　3.　文件IO分 配，空間占用方面

　　4. 其它相關(guān)參數(shù)

１.內(nèi)存利用方面：

首先介紹一個Innodb最重要的參數(shù)：

innodb_buffer_pool_size

　 　這個參數(shù)和MyISAM的key_buffer_size有相似之處，但也是有差別的。這個參數(shù)主要緩存innodb表的索引，數(shù)據(jù)，插入數(shù)據(jù)時的緩 沖。為Innodb加速優(yōu)化首要參數(shù)。

　　該參數(shù)分配內(nèi)存的原則：這個參數(shù)默認分配只有8M，可以說是非常小的一個值。如果是一個專用ＤＢ服務 器，那么他可以占到內(nèi)存的70%-80%。這個參數(shù)不能動態(tài)更改，所以分配需多考慮。分配過大，會使Swap占用過多，致使Mysql的查詢特慢。如果你 的數(shù)據(jù)比較小，那么可分配是你的數(shù)據(jù)大?。保埃プ笥易鰹檫@個參數(shù)的值。例如：數(shù)據(jù)大小為５０Ｍ,那么給這個值分配 innodb_buffer_pool_size＝６４Ｍ

設置方法：

innodb_buffer_pool_size=4G

這 個參數(shù)分配值的使用情況可以根據(jù)show innodb sinnodbtatus/G; 中的

----------------------

BUFFER POOL AND MEMORY

----------------------

Total memory allocated 4668764894;

去確認使用情況。

第二個：

innodb_additional_mem_pool：

作用：用來存放 Innodb的內(nèi)部目錄

這個值不用分配太大，系統(tǒng)可以自動調(diào)。不用設置太高。通常比較大數(shù)據(jù)設置16Ｍ夠用了，如果表比較多，可以適當?shù)脑龃?。?果這個值自動增加，會在error log有中顯示的。

分配原則：

用show innodb status/G; 去 查看運行中的ＤＢ是什么狀態(tài)（參考BUFFER POOL AND MEMORY段中），然后可以調(diào)整到適當?shù)闹怠?/p>

----------------------

BUFFER POOL AND MEMORY

----------------------

Total memory allocated 4668764894; in additional pool allocated 16777216

參考：in additional pool allocated 16777216

根據(jù)你的參數(shù)情況，可以適當?shù)恼{(diào)整。

設置方法：

innodb_additional_mem_pool=16M

２.關(guān)于日值方面：

innodb_log_file_size

作用：指定日值的大小

分 配原則：幾個日值成員大小加起來差不多和你的innodb_buffer_pool_size相等。上限為每個日值上限大小為4G.一般控制在幾個ＬＯＧ 文件相加大小在２Ｇ以內(nèi)為佳。具體情況還需要看你的事務大小，數(shù)據(jù)大小為依據(jù)。

說明：這個值分配的大小和數(shù)據(jù)庫的寫入速度，事務大小，異常重啟后 的恢復有很大的關(guān)系。

設置方法：

innodb_log_file_size=256M

innodb_log_files_in_group

作用：指定你有幾個日值組。

分配 原則：　一般我們可以用２－３個日值組。默認為兩個。

設置方法：

innodb_log_files_in_group=3

innodb_log_buffer_size：

作用：事務在內(nèi)存中的緩沖。

分配原 則：控制在2-8M.這個值不用太多的。他里面的內(nèi)存一般一秒鐘寫到磁盤一次。具體寫入方式和你的事務提交方式有關(guān)。在Ｏｒａｃｌｅ等數(shù)據(jù)庫了解這個，一 般最大指定為３Ｍ比較合適。

參考：Innodb_os_log_written(show global status 可以拿到)

如果 這個值增長過快，可以適當?shù)脑黾觟nnodb_log_buffer_size

另外如果你需要處理大理的ＴＥＸＴ，或是ＢＬＯＢ字段，可以考慮增 加這個參數(shù)的值。

設置方法：

innodb_log_buffer_size=3M

innodb_flush_logs_at_trx_commit

作用：控制事務的提交方式

分 配原則：這個參數(shù)只有３個值，０，１，２請確認一下自已能接受的級別。默認為１，主庫請不要更改了。

性能更高的可以設置為０或是２，但會丟失一秒 鐘的事務。

說明：

這個參數(shù)的設置對Ｉｎｎｏｄｂ的性能有很大的影響，所以在這里給多說明一下。

當 這個值為1時：innodb 的事務LOG在每次提交后寫入日值文件，并對日值做刷新到磁盤。這個可以做到不丟任何一個事務。

當這個值為2時：在 每個提交，日志緩沖被寫到文件，但不對日志文件做到磁盤操作的刷新,在對日志文件的刷新在值為2的情況也每秒發(fā)生一次。但需要注意的是，由于進程調(diào)用方面 的問題，并不能保證每秒１００％的發(fā)生。從而在性能上是最快的。但操作系統(tǒng)崩潰或掉電才會刪除最后一秒的事務。

當這個值為0時：日志緩沖每秒一次 地被寫到日志文件，并且對日志文件做到磁盤操作的刷新，但是在一個事務提交不做任何操作。mysqld進程的崩潰會刪除崩潰前最后一秒的事務。

從以上分析，當這個值不為１時，可以取得較好的性能，但遇到異常會有損失，所以需要根據(jù)自已的情況去衡量。

設置方法：

innodb_flush_logs_at_trx_commit=1

3.　文件IO分配，空間占用方面

innodb_file_per_table

作用：使每個 Innodb的表，有自已獨立的表空間。如刪除文件后可以回收那部分空間。

分配原則：只有使用不使用。但ＤＢ還需要有一個公共的表空間。

設 置方法：

innodb_file_per_table=1

innodb_file_io_threads

作用：文件讀寫ＩＯ數(shù)，這個參數(shù)只在Windows上起 作用。在LINUX上只會等于４

設置方法：

innodb_file_io_threads=4

innodb_open_files

作用：限制Innodb能打開的表的數(shù)據(jù)。

分配原則：如果 庫里的表特別多的情況，請增加這個。這個值默認是３００。

設置方法：

innodb_open_files=800

請 適當?shù)脑黾觮able_cache

4. 其它相關(guān)參數(shù)

這里說明一個比較重要的參數(shù)：

innodb_flush_method

作 用：Ｉｎｎｏｄｂ和系統(tǒng)打交道的一個ＩＯ模型

分配原則：Windows不用設置。

Ｕｎｉｘ可以設置：fsync() or O_SYNC/O_DSYNC

如果系統(tǒng)可以禁止系統(tǒng)的Ｃａｃｈｅ那就把他禁了。

Ｌｉｎｕｘ可以選擇：O_DIRECT

直接寫入 磁盤，禁止系統(tǒng)Ｃａｃｈｅ了

設置方法：

innodb_flush_method=O_DIRECT

innodb_max_dirty_pages_pct

作用：控制Innodb的臟頁在緩沖中在那個 百分比之下，值在范圍1-100,默認為90.

這個參數(shù)的另一個用處：當Ｉｎｎｏｄｂ的內(nèi)存分配過大，致使Ｓｗａｐ占用嚴重時，可以適當?shù)臏p小調(diào) 整這個值，使達到Ｓｗａｐ空間釋放出來。建義：這個值最大在９０％，最小在１５％。太大，緩存中每次更新需要致?lián)Q數(shù)據(jù)頁太多，太小，放的數(shù)據(jù)頁太小，更新 操作太慢。

設置方法：

innodb_max_dirty_pages_pct＝90

動態(tài)更改需要有Ｓｕｐｅｒ權(quán)限：

set global innodb_max_dirty_pages_pct=50;

總結(jié)：

　　這里只算是列出了Ｉｎｎｏｄｂ部分的重要參數(shù)，不能認為是對Ｍｙｓｑｌ的整體調(diào)優(yōu)。 Ｍｙｓｑｌ的參數(shù)一般分為：全局參數(shù)，具體引擎的參數(shù)。全局參數(shù)方面請參考http://imysql.cn/2007_12_08_optimize_mysql_under_linux yejr的那個Ｍｙｓｑｌ調(diào)優(yōu)的ＰＰＴ。

========================================

通過這次MySQL InnoDB的調(diào)優(yōu)經(jīng)歷，發(fā)現(xiàn)一些和MySQL官方推薦配置不符合的疑惑之處，值得思考和探索：

1、innodb_flush_method究竟應不應該使用O_DIRECT？

所有MySQL調(diào)優(yōu)的建議都說，如果硬件沒有預讀功能，那么使用O_DIRECT將極大降低InnoDB的性能，因為O_DIRECT跳過了操作系 統(tǒng)的文件系統(tǒng)Disk Cache，讓MySQL直接讀寫磁盤了。

但是在我的實踐中來看，如果不使用O_DIRECT，操作系統(tǒng)被迫開辟大量的Disk Cache用于innodb的讀寫緩存，不但沒有提高讀寫性能，反而造成讀寫性能急劇下降。而且buffer pool的數(shù)據(jù)緩存和操作系統(tǒng)Disk Cache緩存造成了Double buffer的浪費，顯然從我這個實踐來看，浪費得非常厲害。

說O_DIRECT造成MySQL直接讀寫磁盤造成得性能下降問題，我覺得完全是杞人憂天。因為從JavaEye的數(shù)據(jù)庫監(jiān)測來看，Innodb的 buffer pool命中率非常高，有98%以上，真正的磁盤操作是微乎其微的。為了1%的磁盤操作能夠得到Disk Cache，而浪費了98%的double buffer內(nèi)存空間，無論從性能上看，還是從內(nèi)存資源的消耗來看，都是非常不明智的。

2、innodb_log_file_size究竟應該大一點，還是小一點？

所有MySQL調(diào)優(yōu)建議都說，innodb_log_file_size要越大越好，避免無謂的buffer pool的flush操作。

但是在我的實踐中來看，innodb_log_file_size開得太大，會明顯增加innodb的log寫入操作，而且會造成操作系統(tǒng)需要更多 的Disk Cache開銷。

因此從我的經(jīng)驗來看，innodb_flush_method =O_DIRECT 是必須的，而innodb_log_file_size也不宜太大。