MySQL-innodb相關參數

1、設置默認的存儲引擎

default-storage-engine = InnoDB #默認存儲引擎

2、InnoDB為獨立表空間模式，每個資料庫的每個表都會生成一個數據空間

innodb_file_per_table = 1

獨立表空間優點：

1. 每個表都有自已獨立的表空間。

2. 每個表的數據和索引都會存在自已的表空間中。

3. 可以實現單表在不同的資料庫中移動。

4. 空間可以回收（除drop table操作處，表空不能自已回收）

缺點：

單表增加過大，如超過100G

共享表空間在Insert操作上少有優勢。其它都沒獨立表空間表現好。當啟用獨立表空間時，請合理調整：innodb_open_files

3、限制Innodb能打開的表的數據，如果庫里的表特別多的情況，請增加這個。這個值默認是300

innodb_open_files = 500

4、緩衝池大小

innodb_buffer_pool_size = 64M

InnoDB使用一個緩衝池來保存索引和原始數據, 不像MyISAM。默認參數只有8M，這裡設置越大,在存取表裡面數據時所需要的磁碟I/O越少。在一個獨立使用的資料庫伺服器上,可以設置這個變數到伺服器物理內存大小的80%。不要設置過大，否則，由於物理內存的競爭可能導致操作系統的換頁顛簸。

注意在32位系統上每個進程可能被限制在 2G-3.5G 用戶層面內存限制，所以不要設置的太高。

這個參數在5.7之前的版本是沒法動態修改，需要重啟才能生效。5.7以及之後的版本可以直接修改參數直接生效。

5、io線程

innodb_write_io_threads = 4

innodb_read_io_threads = 4

innodb使用後台線程處理數據頁上的讀寫 I/O(輸入輸出)請求,根據你的 CPU 核數來更改,默認是4

注:這兩個參數不支持動態改變,需要把該參數加入到my.cnf里，修改完後重啟MySQL服務,允許值的範圍從 1-64

6、線程並發限制

innodb_thread_concurrency = 0

默認設置為 0,表示不限制並發數，這裡推薦設置為0，更好去發揮CPU多核處理能力，提高並發量

7、回收線程

innodb_purge_threads = 1

InnoDB中的清除操作是一類定期回收無用數據的操作。在之前的幾個版本中，清除操作是主線程的一部分，這意味著運行時它可能會堵塞其它的資料庫操作。

從MySQL5.5.X版本開始，該操作運行於獨立的線程中,並支持更多的並發數。用戶可通過設置innodb_purge_threads配置參數來選擇清除操作是否使用單獨線程,默認情況下參數設置為0(不使用單獨線程),設置為 1 時表示使用單獨的清除線程。

8、寫日誌到磁碟

innodb_flush_log_at_trx_commit = 2

0：如果innodb_flush_log_at_trx_commit的值為0,log buffer每秒就會被刷寫日誌文件到磁碟，提交事務的時候不做任何操作（執行是由mysql的master thread線程來執行的。主線程中每秒會將重做日誌緩衝寫入磁碟的重做日誌文件(REDO LOG)中。不論事務是否已經提交）默認的日誌文件是ib_logfile0,ib_logfile1

1：當設為默認值1的時候，每次提交事務的時候，都會將log buffer刷寫到日誌。

2：如果設為2,每次提交事務都會寫日誌，但並不會執行刷的操作。每秒定時會刷到日誌文件。要注意的是，並不能保證100%每秒一定都會刷到磁碟，這要取決於進程的調度。每次事務提交的時候將數據寫入事務日誌，而這裡的寫入僅是調用了文件系統的寫入操作，而文件系統是有 緩存的，所以這個寫入並不能保證數據已經寫入到物理磁碟，默認值1是為了保證完整的ACID。

當然，可以將這個配置項設為1以外的值來換取更高的性能，但是在系統崩潰的時候，將會丟失1秒的數據。

設為0的話，mysqld進程崩潰的時候，就會丟失最後1秒的事務。設為2,只有在操作系統崩潰或者斷電的時候才會丟失最後1秒的數據。InnoDB在做恢復的時候會忽略這個值。設為1當然是最安全的，但性能頁是最差的（相對其他兩個參數而言，但不是不能接受）。如果對數據一致性和完整性要求不高，完全可以設為2，如果只最求性能，例如高並發寫的日誌伺服器，設為0來獲得更高性能。

9、日誌緩存池大小

innodb_log_buffer_size = 2M

此參數確定些日誌文件所用的內存大小，以M為單位。緩衝區更大能提高性能，但意外的故障將會丟失數據。MySQL開發人員建議設置為1－8M之間

10、日誌文件的大小

innodb_log_file_size = 32M

此參數確定數據日誌文件的大小，更大的設置可以提高性能，但也會增加恢復故障資料庫所需的時間

11、設置日誌文件的個數

innodb_log_files_in_group = 3

為提高性能，MySQL可以以循環方式將日誌文件寫到多個文件。推薦設置為3

12、表空間文件路徑

innodb_log_group_home_dir = /data/data/mysql/datas

13、表空間文件

innodb_data_file_path = ibdata1:512M:autoextend

ibdata1文件的名稱；512M是限制文件的大小；autoextend之自動擴展

14、緩衝池臟數據比率

innodb_max_dirty_pages_pct = 90

innodb主線程刷新緩存池中的數據，使臟數據比例小於90%

15、事務鎖定超時時間

innodb_lock_wait_timeout = 120

InnoDB事務在被回滾之前可以等待一個鎖定的超時秒數。InnoDB在它自己的鎖定表中自動檢測事務死鎖並且回滾事務。InnoDB用LOCK TABLES語句注意到鎖定設置。默認值是50秒

16、多個緩衝池

可以開啟多個內存緩衝池，把需要緩衝的數據hash到不同的緩衝池中，這樣可以並行的內存讀寫。

innodb_buffer_pool_instances = 2

17、文件壓縮設置

innodb_file_format = Barracuda

innodb_file_format_max = Barracuda

Antelope（羚羊）是Built-in-InnoDB（MySQL內置的InnoDB）支持文件格式的代號，有兩種「數據表格式」（row_format）：Redundant（冗餘）、Compact（緊湊）

Barracuda（梭子魚）是InnoDB Plugin支持的文件格式，在原來的基礎上新增了兩種數據表格式的支持：Dynamic 和 Compressed

18、寫數據和日誌文件的方式

innodb_flush_method = O_DIRECT

類Unix系統的innodb_flush_method選項包括：

fsync ： InnoDB使用fsync()系統調用來刷新數據和日誌文件。 fsync是默認設置。

O_DSYNC ： InnoDB使用O_SYNC打開和刷新日誌文件，使用fsync()刷新數據文件。 InnoDB不直接使用O_DSYNC ，因為在很多種Unix上都存在問題。

littlesync ：此選項用於內部性能測試，目前不受支持。 使用風險由您自己承擔。

nosync ：此選項用於內部性能測試，目前不受支持。 使用風險由您自己承擔。

O_DIRECT ： InnoDB使用O_DIRECT （或Solaris上的directio() ）打開數據文件，並使用fsync()刷新數據和日誌文件。 某些GNU / Linux版本，FreeBSD和Solaris上提供了此選項。

Windows系統的innodb_flush_method選項包括：

async_unbuffered ： InnoDB使用Windows非同步I / O和非緩衝I / O. async_unbuffered是Windows系統上的默認設置。

async_unbuffered不支持在Windows上的4K扇區硬碟驅動器上運行MySQL伺服器。 解決方法是使用innodb_flush_method=normal 。

normal ： InnoDB使用模擬非同步I / O和緩衝I / O. 此選項用於內部性能測試，目前不受支持。 使用風險由您自己承擔。

unbuffered ： InnoDB使用模擬非同步I / O和非緩衝I / O. 此選項用於內部性能測試，目前不受支持。 使用風險由您自己承擔。

喜歡這篇文章嗎？立刻分享出去讓更多人知道吧！