可以改善mysql性能的InnoDB配置參數(shù)
更新時(shí)間：2011年05月07日 21:57:36 作者：
MySQL與MSSQL 有一個(gè)區(qū)別在于MySQL建表的時(shí)候需要選擇存儲(chǔ)引擎,常用的存儲(chǔ)引擎有MyISAM和InnoDB
而由于InnoDB是一個(gè)健壯的事務(wù)型存儲(chǔ)引擎，已經(jīng)有10多年的歷史，一些重量級(jí)的互聯(lián)網(wǎng)公司(Yahoo,Google Netease ，Taobao)也經(jīng)常使用
我的日常工作也經(jīng)常接觸InnoDB,現(xiàn)在就InnoDB一部分可以改善性能的參數(shù)列舉
1. innodb_additional_mem_pool_size
除了緩存表數(shù)據(jù)和索引外,可以為操作所需的其他內(nèi)部項(xiàng)分配緩存來(lái)提升InnoDB的性能。這些內(nèi)存就可以通過(guò)此參數(shù)來(lái)分配。推薦此參數(shù)至少設(shè)置為2MB，實(shí)際上，是需要根據(jù)項(xiàng)目的InnoDB表的數(shù)目相應(yīng)地增加
2.innodb_data_pool_size
此參數(shù)類(lèi)似于MySQL的key_buffer參數(shù)，但特定用于InnoDB表.這個(gè)參數(shù)確定了要預(yù)留多少內(nèi)存來(lái)緩存表數(shù)據(jù)和索引。與key_buffer一樣，更高的設(shè)置會(huì)提升性能，可以是服務(wù)器的內(nèi)存70-80%
3.innodb_data_file_path
參數(shù)的名字和實(shí)際的用途有點(diǎn)出入，它不僅指定了所有InnoDB數(shù)據(jù)文件的路徑，還指定了初始大小分配，最大分配以及超出起始分配界線時(shí)是否應(yīng)當(dāng)增加文件的大小。此參數(shù)的一般格式如下:
path-to-datafile:size-allocation[:autoextend[:max-size-allocation]]
例如，假設(shè)希望創(chuàng)建一個(gè)數(shù)據(jù)文件sales，初始大小為100MB，并希望在每次達(dá)到當(dāng)前大小限制時(shí)，自動(dòng)增加8MB（8MB是指定autoextend時(shí)的默認(rèn)擴(kuò)展大小).但是，不希望此文件超過(guò)1GB，可以使用如下配置:
innodb_data_home_dir =
innodb_data_file_path = /data/sales:100M:autoextend:8M: max:1GB
如果此文件增加到預(yù)定的1G的限制，可以再增加另外一個(gè)數(shù)據(jù)文件,如下:
innodb_data_file_path = /data/sales:100M:autoextend:8M: max:1GB;innodb_data_file_path = /data2/sales2:100M:autoextend:8M: max:2GB
要注意的是，在這些示例中，inndb_data_home_dir參數(shù)開(kāi)始設(shè)置為空，因?yàn)樽罱K數(shù)據(jù)文件位于單獨(dú)的位置(/data/和/data2/）.如果希望所有 InnoDB數(shù)據(jù)文件都位于相同的位置，就可以使用innodb_data_home_dir來(lái)指定共同位置，然后在通過(guò) inndo_data_file_path來(lái)指定文件名即可。如果沒(méi)有定義這些值，將在datadir中創(chuàng)建一個(gè)sales。
4 innodb_data_home_dir
此參數(shù)指定創(chuàng)建InnoDB表空間的路徑的公共部分,默認(rèn)情況下，這是MySQL的默認(rèn)數(shù)據(jù)，由MySQL參數(shù)datadir指定
5. innodb_file_io_threads
此參數(shù)指定InnoDB表可用的文件I／O線程數(shù)，MySQL開(kāi)發(fā)人員建議在非Windows平臺(tái)中這個(gè)參數(shù)設(shè)置為4
6. innodb_flush_log_at_trx_commit
如果將此參數(shù)設(shè)置為1，將在每次提交事務(wù)后將日志寫(xiě)入磁盤(pán)。為提供性能，可以設(shè)置為0或2，但要承擔(dān)在發(fā)生故障時(shí)丟失數(shù)據(jù)的風(fēng)險(xiǎn)。設(shè)置為0表示事務(wù)日志寫(xiě)入日志文件，而日志文件每秒刷新到磁盤(pán)一次。設(shè)置為2表示事務(wù)日志將在提交時(shí)寫(xiě)入日志，但日志文件每次刷新到磁盤(pán)一次。
7.innodb_log_archive
因?yàn)镸ySQL目前使用自己的日志文件恢復(fù)InnoDB表，此參數(shù)可設(shè)置為0
8.innodb_log_arch_dir
MySQL目前忽略此參數(shù)，但會(huì)在未來(lái)的版本中使用。目前，應(yīng)當(dāng)將其設(shè)置為與innodb_log_group_home_dir相同的值
9.innodb_log_buffer_size
此參數(shù)確定些日志文件所用的內(nèi)存大小，以M為單位。緩沖區(qū)更大能提高性能，但意外的故障將會(huì)丟失數(shù)據(jù).MySQL開(kāi)發(fā)人員建議設(shè)置為1－8M之間
10. innodb_log_file_size
此參數(shù)確定數(shù)據(jù)日志文件的大小，以M為單位，更大的設(shè)置可以提高性能，但也會(huì)增加恢復(fù)故障數(shù)據(jù)庫(kù)所需的時(shí)間
11.innodb_log_files_in_group
為提高性能，MySQL可以以循環(huán)方式將日志文件寫(xiě)到多個(gè)文件。推薦設(shè)置為3M
12. innodb_log_group_home_dir
此參數(shù)確定日志文件組中的文件的位置，日志組中文件的個(gè)數(shù)由innodb_log_files_in_group確定，此位置設(shè)置默認(rèn)為MySQL的datadir
13.innodb_lock_wait_timeout
InnoDB 有其內(nèi)置的死鎖檢測(cè)機(jī)制，能導(dǎo)致未完成的事務(wù)回滾。但是，如果結(jié)合InnoDB使用MyISAM的lock tables 語(yǔ)句或第三方事務(wù)引擎,則InnoDB無(wú)法識(shí)別死鎖。為消除這種可能性，可以將innodb_lock_wait_timeout設(shè)置為一個(gè)整數(shù)值，指示 MySQL在允許其他事務(wù)修改那些最終受事務(wù)回滾的數(shù)據(jù)之前要等待多長(zhǎng)時(shí)間(秒數(shù))
14.skip-innodb
啟用此參數(shù)能防止夾雜InnoDB表驅(qū)動(dòng)程序，不使用InnoDB表時(shí)推薦此設(shè)置
我的日常工作也經(jīng)常接觸InnoDB,現(xiàn)在就InnoDB一部分可以改善性能的參數(shù)列舉
1. innodb_additional_mem_pool_size
除了緩存表數(shù)據(jù)和索引外,可以為操作所需的其他內(nèi)部項(xiàng)分配緩存來(lái)提升InnoDB的性能。這些內(nèi)存就可以通過(guò)此參數(shù)來(lái)分配。推薦此參數(shù)至少設(shè)置為2MB，實(shí)際上，是需要根據(jù)項(xiàng)目的InnoDB表的數(shù)目相應(yīng)地增加
2.innodb_data_pool_size
此參數(shù)類(lèi)似于MySQL的key_buffer參數(shù)，但特定用于InnoDB表.這個(gè)參數(shù)確定了要預(yù)留多少內(nèi)存來(lái)緩存表數(shù)據(jù)和索引。與key_buffer一樣，更高的設(shè)置會(huì)提升性能，可以是服務(wù)器的內(nèi)存70-80%
3.innodb_data_file_path
參數(shù)的名字和實(shí)際的用途有點(diǎn)出入，它不僅指定了所有InnoDB數(shù)據(jù)文件的路徑，還指定了初始大小分配，最大分配以及超出起始分配界線時(shí)是否應(yīng)當(dāng)增加文件的大小。此參數(shù)的一般格式如下:
path-to-datafile:size-allocation[:autoextend[:max-size-allocation]]
例如，假設(shè)希望創(chuàng)建一個(gè)數(shù)據(jù)文件sales，初始大小為100MB，并希望在每次達(dá)到當(dāng)前大小限制時(shí)，自動(dòng)增加8MB（8MB是指定autoextend時(shí)的默認(rèn)擴(kuò)展大小).但是，不希望此文件超過(guò)1GB，可以使用如下配置:
innodb_data_home_dir =
innodb_data_file_path = /data/sales:100M:autoextend:8M: max:1GB
如果此文件增加到預(yù)定的1G的限制，可以再增加另外一個(gè)數(shù)據(jù)文件,如下:
innodb_data_file_path = /data/sales:100M:autoextend:8M: max:1GB;innodb_data_file_path = /data2/sales2:100M:autoextend:8M: max:2GB
要注意的是，在這些示例中，inndb_data_home_dir參數(shù)開(kāi)始設(shè)置為空，因?yàn)樽罱K數(shù)據(jù)文件位于單獨(dú)的位置(/data/和/data2/）.如果希望所有 InnoDB數(shù)據(jù)文件都位于相同的位置，就可以使用innodb_data_home_dir來(lái)指定共同位置，然后在通過(guò) inndo_data_file_path來(lái)指定文件名即可。如果沒(méi)有定義這些值，將在datadir中創(chuàng)建一個(gè)sales。
4 innodb_data_home_dir
此參數(shù)指定創(chuàng)建InnoDB表空間的路徑的公共部分,默認(rèn)情況下，這是MySQL的默認(rèn)數(shù)據(jù)，由MySQL參數(shù)datadir指定
5. innodb_file_io_threads
此參數(shù)指定InnoDB表可用的文件I／O線程數(shù)，MySQL開(kāi)發(fā)人員建議在非Windows平臺(tái)中這個(gè)參數(shù)設(shè)置為4
6. innodb_flush_log_at_trx_commit
如果將此參數(shù)設(shè)置為1，將在每次提交事務(wù)后將日志寫(xiě)入磁盤(pán)。為提供性能，可以設(shè)置為0或2，但要承擔(dān)在發(fā)生故障時(shí)丟失數(shù)據(jù)的風(fēng)險(xiǎn)。設(shè)置為0表示事務(wù)日志寫(xiě)入日志文件，而日志文件每秒刷新到磁盤(pán)一次。設(shè)置為2表示事務(wù)日志將在提交時(shí)寫(xiě)入日志，但日志文件每次刷新到磁盤(pán)一次。
7.innodb_log_archive
因?yàn)镸ySQL目前使用自己的日志文件恢復(fù)InnoDB表，此參數(shù)可設(shè)置為0
8.innodb_log_arch_dir
MySQL目前忽略此參數(shù)，但會(huì)在未來(lái)的版本中使用。目前，應(yīng)當(dāng)將其設(shè)置為與innodb_log_group_home_dir相同的值
9.innodb_log_buffer_size
此參數(shù)確定些日志文件所用的內(nèi)存大小，以M為單位。緩沖區(qū)更大能提高性能，但意外的故障將會(huì)丟失數(shù)據(jù).MySQL開(kāi)發(fā)人員建議設(shè)置為1－8M之間
10. innodb_log_file_size
此參數(shù)確定數(shù)據(jù)日志文件的大小，以M為單位，更大的設(shè)置可以提高性能，但也會(huì)增加恢復(fù)故障數(shù)據(jù)庫(kù)所需的時(shí)間
11.innodb_log_files_in_group
為提高性能，MySQL可以以循環(huán)方式將日志文件寫(xiě)到多個(gè)文件。推薦設(shè)置為3M
12. innodb_log_group_home_dir
此參數(shù)確定日志文件組中的文件的位置，日志組中文件的個(gè)數(shù)由innodb_log_files_in_group確定，此位置設(shè)置默認(rèn)為MySQL的datadir
13.innodb_lock_wait_timeout
InnoDB 有其內(nèi)置的死鎖檢測(cè)機(jī)制，能導(dǎo)致未完成的事務(wù)回滾。但是，如果結(jié)合InnoDB使用MyISAM的lock tables 語(yǔ)句或第三方事務(wù)引擎,則InnoDB無(wú)法識(shí)別死鎖。為消除這種可能性，可以將innodb_lock_wait_timeout設(shè)置為一個(gè)整數(shù)值，指示 MySQL在允許其他事務(wù)修改那些最終受事務(wù)回滾的數(shù)據(jù)之前要等待多長(zhǎng)時(shí)間(秒數(shù))
14.skip-innodb
啟用此參數(shù)能防止夾雜InnoDB表驅(qū)動(dòng)程序，不使用InnoDB表時(shí)推薦此設(shè)置
相關(guān)文章
MYSQL ERROR 1045 (28000): Access denied for user (using pass
Mysql中添加用戶之后可能出現(xiàn)登錄時(shí)提示ERROR 1045 (28000): Access denied for user的錯(cuò)誤.2009-07-07
這篇文章主要介紹了MySQL EXPLAIN語(yǔ)句的使用示例，幫助大家更好的理解和學(xué)習(xí)使用MySQL，感興趣的朋友可以了解下2021-03-03
這篇文章主要介紹了MySQL主從復(fù)制延遲原因以及解決方案，幫助大家更好的理解和使用數(shù)據(jù)庫(kù)，感興趣的朋友可以了解下2020-09-09
這篇文章主要介紹了mac下安裝mysql忘記密碼的修改方法,需要的朋友可以參考下2017-06-06
MySQL重啟之后無(wú)法寫(xiě)入數(shù)據(jù)的問(wèn)題排查及解決
客戶在給系統(tǒng)打補(bǔ)丁之后需要重啟服務(wù)器,數(shù)據(jù)庫(kù)在重啟之后,read_only 的設(shè)置與標(biāo)準(zhǔn)配置 文件中不一致,導(dǎo)致主庫(kù)在啟動(dòng)之后無(wú)法按照預(yù)期寫(xiě)入,所以本文給大家介紹了MySQL重啟之后無(wú)法寫(xiě)入數(shù)據(jù)的問(wèn)題排查及解決,需要的朋友可以參考下2024-05-05
MySQL 處理插入過(guò)程中的主鍵唯一鍵重復(fù)值的解決方法
本篇文章主要介紹在插入數(shù)據(jù)到表中遇到鍵重復(fù)避免插入重復(fù)值的處理方法，主要涉及到IGNORE,ON DUPLICATE KEY UPDATE,REPLACE的相關(guān)知識(shí)，感興趣的朋友一起學(xué)習(xí)吧2016-04-04
通過(guò)Mycat代理實(shí)現(xiàn)MySQL的讀寫(xiě)分離涉及準(zhǔn)備工作、配置文件修改、權(quán)限設(shè)置、啟動(dòng)方式選擇等關(guān)鍵步驟,首先,安裝JDK1.8并配置環(huán)境變量；接著,對(duì)Mycat的server.xml和schema.xml進(jìn)行配置,特別是schema.xml中對(duì)數(shù)據(jù)庫(kù)的配置需關(guān)注2024-11-11
所謂子查詢是指在一個(gè)查詢中嵌套了其他的若干查詢,即在一個(gè)SELECT查詢語(yǔ)句的WHERE或FROM子句中包含另一個(gè)SELECT查詢語(yǔ)句,下面這篇文章主要給大家介紹了關(guān)于MySQL子查詢的相關(guān)資料,需要的朋友可以參考下2023-03-03
MySQL INNER JOIN 的底層實(shí)現(xiàn)原理分析
這篇文章主要介紹了MySQL INNER JOIN 的底層實(shí)現(xiàn)原理,INNER JOIN的工作分為篩選和連接兩個(gè)步驟，連接時(shí)可以使用多種算法，通過(guò)本文，我們深入了解了MySQL中INNER JOIN的底層實(shí)現(xiàn)原理，需要的朋友可以參考下2023-06-06
在MySQL中使用通配符時(shí)應(yīng)該注意的問(wèn)題
這篇文章主要介紹了在MySQL中使用通配符時(shí)應(yīng)該注意的問(wèn)題,主要是下劃線的使用容易引起的錯(cuò)誤,需要的朋友可以參考下2015-05-05