可以改善mysql性能的InnoDB配置參數(shù)
更新時間:2011年05月07日 21:57:36 作者:
MySQL與MSSQL 有一個區(qū)別在于MySQL建表的時候需要選擇存儲引擎,常用的存儲引擎有MyISAM和InnoDB
而由于InnoDB是一個健壯的事務(wù)型存儲引擎,已經(jīng)有10多年的歷史,一些重量級的互聯(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)分配緩存來提升InnoDB的性能。這些內(nèi)存就可以通過此參數(shù)來分配。推薦此參數(shù)至少設(shè)置為2MB,實(shí)際上,是需要根據(jù)項(xiàng)目的InnoDB表的數(shù)目相應(yīng)地增加
2.innodb_data_pool_size
此參數(shù)類似于MySQL的key_buffer參數(shù),但特定用于InnoDB表.這個參數(shù)確定了要預(yù)留多少內(nèi)存來緩存表數(shù)據(jù)和索引。與key_buffer一樣,更高的設(shè)置會提升性能,可以是服務(wù)器的內(nèi)存70-80%
3.innodb_data_file_path
參數(shù)的名字和實(shí)際的用途有點(diǎn)出入,它不僅指定了所有InnoDB數(shù)據(jù)文件的路徑,還指定了初始大小分配,最大分配以及超出起始分配界線時是否應(yīng)當(dāng)增加文件的大小。此參數(shù)的一般格式如下:
path-to-datafile:size-allocation[:autoextend[:max-size-allocation]]
例如,假設(shè)希望創(chuàng)建一個數(shù)據(jù)文件sales,初始大小為100MB,并希望在每次達(dá)到當(dāng)前大小限制時,自動增加8MB(8MB是指定autoextend時的默認(rèn)擴(kuò)展大小).但是,不希望此文件超過1GB,可以使用如下配置:
innodb_data_home_dir =
innodb_data_file_path = /data/sales:100M:autoextend:8M: max:1GB
如果此文件增加到預(yù)定的1G的限制,可以再增加另外一個數(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ù)開始設(shè)置為空,因?yàn)樽罱K數(shù)據(jù)文件位于單獨(dú)的位置(/data/和/data2/).如果希望所有 InnoDB數(shù)據(jù)文件都位于相同的位置,就可以使用innodb_data_home_dir來指定共同位置,然后在通過 inndo_data_file_path來指定文件名即可。如果沒有定義這些值,將在datadir中創(chuàng)建一個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開發(fā)人員建議在非Windows平臺中這個參數(shù)設(shè)置為4
6. innodb_flush_log_at_trx_commit
如果將此參數(shù)設(shè)置為1,將在每次提交事務(wù)后將日志寫入磁盤。為提供性能,可以設(shè)置為0或2,但要承擔(dān)在發(fā)生故障時丟失數(shù)據(jù)的風(fēng)險。設(shè)置為0表示事務(wù)日志寫入日志文件,而日志文件每秒刷新到磁盤一次。設(shè)置為2表示事務(wù)日志將在提交時寫入日志,但日志文件每次刷新到磁盤一次。
7.innodb_log_archive
因?yàn)镸ySQL目前使用自己的日志文件恢復(fù)InnoDB表,此參數(shù)可設(shè)置為0
8.innodb_log_arch_dir
MySQL目前忽略此參數(shù),但會在未來的版本中使用。目前,應(yīng)當(dāng)將其設(shè)置為與innodb_log_group_home_dir相同的值
9.innodb_log_buffer_size
此參數(shù)確定些日志文件所用的內(nèi)存大小,以M為單位。緩沖區(qū)更大能提高性能,但意外的故障將會丟失數(shù)據(jù).MySQL開發(fā)人員建議設(shè)置為1-8M之間
10. innodb_log_file_size
此參數(shù)確定數(shù)據(jù)日志文件的大小,以M為單位,更大的設(shè)置可以提高性能,但也會增加恢復(fù)故障數(shù)據(jù)庫所需的時間
11.innodb_log_files_in_group
為提高性能,MySQL可以以循環(huán)方式將日志文件寫到多個文件。推薦設(shè)置為3M
12. innodb_log_group_home_dir
此參數(shù)確定日志文件組中的文件的位置,日志組中文件的個數(shù)由innodb_log_files_in_group確定,此位置設(shè)置默認(rèn)為MySQL的datadir
13.innodb_lock_wait_timeout
InnoDB 有其內(nèi)置的死鎖檢測機(jī)制,能導(dǎo)致未完成的事務(wù)回滾。但是,如果結(jié)合InnoDB使用MyISAM的lock tables 語句或第三方事務(wù)引擎,則InnoDB無法識別死鎖。為消除這種可能性,可以將innodb_lock_wait_timeout設(shè)置為一個整數(shù)值,指示 MySQL在允許其他事務(wù)修改那些最終受事務(wù)回滾的數(shù)據(jù)之前要等待多長時間(秒數(shù))
14.skip-innodb
啟用此參數(shù)能防止夾雜InnoDB表驅(qū)動程序,不使用InnoDB表時推薦此設(shè)置
我的日常工作也經(jīng)常接觸InnoDB,現(xiàn)在就InnoDB一部分可以改善性能的參數(shù)列舉
1. innodb_additional_mem_pool_size
除了緩存表數(shù)據(jù)和索引外,可以為操作所需的其他內(nèi)部項(xiàng)分配緩存來提升InnoDB的性能。這些內(nèi)存就可以通過此參數(shù)來分配。推薦此參數(shù)至少設(shè)置為2MB,實(shí)際上,是需要根據(jù)項(xiàng)目的InnoDB表的數(shù)目相應(yīng)地增加
2.innodb_data_pool_size
此參數(shù)類似于MySQL的key_buffer參數(shù),但特定用于InnoDB表.這個參數(shù)確定了要預(yù)留多少內(nèi)存來緩存表數(shù)據(jù)和索引。與key_buffer一樣,更高的設(shè)置會提升性能,可以是服務(wù)器的內(nèi)存70-80%
3.innodb_data_file_path
參數(shù)的名字和實(shí)際的用途有點(diǎn)出入,它不僅指定了所有InnoDB數(shù)據(jù)文件的路徑,還指定了初始大小分配,最大分配以及超出起始分配界線時是否應(yīng)當(dāng)增加文件的大小。此參數(shù)的一般格式如下:
path-to-datafile:size-allocation[:autoextend[:max-size-allocation]]
例如,假設(shè)希望創(chuàng)建一個數(shù)據(jù)文件sales,初始大小為100MB,并希望在每次達(dá)到當(dāng)前大小限制時,自動增加8MB(8MB是指定autoextend時的默認(rèn)擴(kuò)展大小).但是,不希望此文件超過1GB,可以使用如下配置:
innodb_data_home_dir =
innodb_data_file_path = /data/sales:100M:autoextend:8M: max:1GB
如果此文件增加到預(yù)定的1G的限制,可以再增加另外一個數(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ù)開始設(shè)置為空,因?yàn)樽罱K數(shù)據(jù)文件位于單獨(dú)的位置(/data/和/data2/).如果希望所有 InnoDB數(shù)據(jù)文件都位于相同的位置,就可以使用innodb_data_home_dir來指定共同位置,然后在通過 inndo_data_file_path來指定文件名即可。如果沒有定義這些值,將在datadir中創(chuàng)建一個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開發(fā)人員建議在非Windows平臺中這個參數(shù)設(shè)置為4
6. innodb_flush_log_at_trx_commit
如果將此參數(shù)設(shè)置為1,將在每次提交事務(wù)后將日志寫入磁盤。為提供性能,可以設(shè)置為0或2,但要承擔(dān)在發(fā)生故障時丟失數(shù)據(jù)的風(fēng)險。設(shè)置為0表示事務(wù)日志寫入日志文件,而日志文件每秒刷新到磁盤一次。設(shè)置為2表示事務(wù)日志將在提交時寫入日志,但日志文件每次刷新到磁盤一次。
7.innodb_log_archive
因?yàn)镸ySQL目前使用自己的日志文件恢復(fù)InnoDB表,此參數(shù)可設(shè)置為0
8.innodb_log_arch_dir
MySQL目前忽略此參數(shù),但會在未來的版本中使用。目前,應(yīng)當(dāng)將其設(shè)置為與innodb_log_group_home_dir相同的值
9.innodb_log_buffer_size
此參數(shù)確定些日志文件所用的內(nèi)存大小,以M為單位。緩沖區(qū)更大能提高性能,但意外的故障將會丟失數(shù)據(jù).MySQL開發(fā)人員建議設(shè)置為1-8M之間
10. innodb_log_file_size
此參數(shù)確定數(shù)據(jù)日志文件的大小,以M為單位,更大的設(shè)置可以提高性能,但也會增加恢復(fù)故障數(shù)據(jù)庫所需的時間
11.innodb_log_files_in_group
為提高性能,MySQL可以以循環(huán)方式將日志文件寫到多個文件。推薦設(shè)置為3M
12. innodb_log_group_home_dir
此參數(shù)確定日志文件組中的文件的位置,日志組中文件的個數(shù)由innodb_log_files_in_group確定,此位置設(shè)置默認(rèn)為MySQL的datadir
13.innodb_lock_wait_timeout
InnoDB 有其內(nèi)置的死鎖檢測機(jī)制,能導(dǎo)致未完成的事務(wù)回滾。但是,如果結(jié)合InnoDB使用MyISAM的lock tables 語句或第三方事務(wù)引擎,則InnoDB無法識別死鎖。為消除這種可能性,可以將innodb_lock_wait_timeout設(shè)置為一個整數(shù)值,指示 MySQL在允許其他事務(wù)修改那些最終受事務(wù)回滾的數(shù)據(jù)之前要等待多長時間(秒數(shù))
14.skip-innodb
啟用此參數(shù)能防止夾雜InnoDB表驅(qū)動程序,不使用InnoDB表時推薦此設(shè)置
相關(guān)文章
mysql數(shù)據(jù)庫腳本如何為表添加字段并設(shè)置備注
這篇文章主要介紹了mysql數(shù)據(jù)庫腳本如何為表添加字段并設(shè)置備注方式,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2024-04-04
MySQL 8.2 Command Line Client打開時一閃而過閃退問題
MySQL8.2安裝成功后,發(fā)現(xiàn)打開MySQL 8.0 Command Line Client時出現(xiàn)一閃而過,打不開的情況,所以下面這篇文章主要給大家介紹了關(guān)于MySQL 8.2 Command Line Client打開時一閃而過閃退問題的解決,需要的朋友可以參考下2024-01-01
MySQL同步ES(Elasticsearch)的四種常見方案分享
MySQL和Elasticsearch(ES)是兩個非常重要的數(shù)據(jù)存儲和搜索技術(shù),MySQL是一種關(guān)系型數(shù)據(jù)庫,而ES則是一種文檔型數(shù)據(jù)庫,在許多情況下,我們需要將MySQL中的數(shù)據(jù)同步到ES中,本文將介紹四種常見的MySQL同步ES方案,需要的朋友可以參考下2023-07-07
MYSQL數(shù)據(jù)庫主從同步設(shè)置的實(shí)現(xiàn)步驟
本文主要介紹了MYSQL數(shù)據(jù)庫主從同步設(shè)置的實(shí)現(xiàn)步驟,文中通過示例代碼介紹的非常詳細(xì),具有一定的參考價值,感興趣的小伙伴們可以參考一下2022-03-03
M1芯片安裝mysql8.0數(shù)據(jù)庫的實(shí)現(xiàn)步驟(圖文)
這篇文章主要介紹了M1芯片安裝mysql8.0數(shù)據(jù)庫的實(shí)現(xiàn)實(shí)現(xiàn)步驟,文中通過示例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2021-02-02
Windows 8.1下MySQL5.7 忘記root 密碼的解決方法
最近學(xué)習(xí)碰到了一件挺令人尷尬的事情,我把MySQL的密碼給忘記了,所以MySQL登錄不進(jìn)去。在網(wǎng)上找的解決方案都不靠譜,下面小編給大家分享Windows 8.1下MySQL5.7 忘記root 密碼的解決方法,需要的朋友一起看看吧2017-07-07
Mysql分組查詢每組最新的一條數(shù)據(jù)的五種實(shí)現(xiàn)方法
在寫報表功能時遇到一個需要根據(jù)用戶id分組查詢最新一條錢包明細(xì)數(shù)據(jù)的需求,本文主要介紹了Mysql分組查詢每組最新的一條數(shù)據(jù)的五種實(shí)現(xiàn)方法,感興趣的可以了解一下2024-08-08
關(guān)于mysql 8.x 中insert ignore的性能問題
這篇文章主要介紹了關(guān)于mysql 8.x 中insert ignore的性能問題,具有很好的參考價值,希望對大家有所幫助。2022-08-08

