MySQL 5.7資料庫引數優化

feelpurple發表於2016-05-10
MySql資料庫優化
--連線相關引數

max_connections      允許客戶端併發連線的最大數量，預設值是151，一般將該引數設定為500-2000

max_connect_errors     如果客戶端嘗試連線的錯誤數量超過這個引數設定的值，則伺服器不再接受新的客戶端連線。可以通過清空主機的快取來解除伺服器的這種阻止新連線的狀態，通過FLUSH HOSTS或mysqladmin 
flush-hosts命令來清空快取。這個引數的預設值是100，一般將該引數設定為100000。

interactive_timeout     Mysql關閉互動連線前的等待時間，單位是秒，預設是8小時，建議不要將該引數設定超過24小時，即86400

wait_timeout      Mysql關閉非互動連線前的等待時間，單位是秒，預設是8小時，建議不要將該引數設定超過24小時，即86400

skip_name_resolve     如果這個引數設為OFF，則MySQL服務在檢查客戶端連線的時候會解析主機名；如果這個引數設為ON，則MySQL服務只會使用IP，在這種情況下，授權表中的Host欄位必須是IP地址或localhost。
這個引數預設是關閉的

back_log      MySQL伺服器連線請求佇列所能處理的最大連線請求數，如果佇列放滿了，後續的連線才會拒絕。當主要的MySQL執行緒在很短時間內獲取大量連線請求時，這個引數會生效。接下來，MySQL主執行緒會花費很短的時間去檢查連線，然後開啟新的執行緒。這個引數指定了MySQL的TCP/IP監聽佇列的大小。如果MySQL伺服器在短時間內有大量的連線，可以增加這個引數。

--檔案相關引數

sync_binlog      控制二進位制日誌被同步到磁碟前二進位制日誌提交組的數量。當這個引數為0的時候，二進位制日誌不會被同步到磁碟；當這個引數設為0以上的數值時，就會有設定該數值的二進位制提交組定期同步日誌到磁碟。當這個引數設為1的時候，所有事務在提交前會被同步到二進位制日誌中，因而即使MySQL伺服器發生意外重啟，任何二進位制日誌中沒有的事務只會處於準備狀態，這會導致MySQL伺服器自動恢復以回滾這些事務。這樣就會保證二進位制日誌不會丟失事務，是最安全的選項；同時由於增加了磁碟寫，這對效能有一定降低。將這個引數設為1以上的數值會提高資料庫的效能，但同時會伴隨資料丟失的風險。建議將該引數設為2、4、6、8、16。

expire_logs_days     二進位制日誌自動刪掉的時間間隔。預設值為0，代表不會自動刪除二進位制日誌。想手動刪除二進位制日誌，可以執行 PURGE BINARY LOGS。

max_binlog_size     二進位制日誌檔案的最大容量，當寫入的二進位制日誌超過這個值的時候，會完成當前二進位制的寫入，向新的二進位制日誌寫入日誌。這個引數最小值時4096位元組；最大值和預設值時1GB。相同事務中的語句都會寫入同一個二進位制日誌，當一個事務很大時，二進位制日誌實際的大小會超過max_binlog_size引數設定的值。如果max_relay_log_size引數設為0，則max_relay_log_size引數會使用和max_binlog_size引數同樣的大小。建議將此引數設為512M。

local_infile      是否允許客戶端使用LOAD DATA INFILE語句。如果這個引數沒有開啟，客戶端不能在LOAD DATA語句中使用LOCAL引數。

open_files_limit     作業系統允許MySQL服務開啟的檔案數量。這個引數實際的值以系統啟動時設定的值、max_connections和table_open_cache為基礎，使用下列的規則：
1) 10 + max_connections + (table_open_cache * 2)
2) max_connections * 5
3) MySQL啟動時指定open_files_limit的值

--快取控制引數

binlog_cache_size     在事務中二進位制日誌使用的快取大小。如果MySQL伺服器支援所有的儲存引擎且啟用二進位制日誌，每個客戶端都會被分配一個二進位制日誌快取。如果資料庫中有很多大的事務，增大這個快取可以獲得更好的效能。Binlog_cache_use和Binlog_cache_disk_use這兩個引數對於binlog_cache_size引數的優化很有用。binlog_cache_size引數只設定事務所使用的快取，非事務SQL語句所使用的快取由binlog_stmt_cache_size系統引數控制。建議不要將這個引數設為超過64MB，以防止客戶端連線多而影響MySQL服務的效能。

max_binlog_cache_size     如果一個事務需要的記憶體超過這個引數，伺服器會報錯"Multi-statement transaction required more than 'max_binlog_cache_size' bytes"。這個引數最大的推薦值是4GB，這是因為MySQL不能在二進位制日誌設為超過4GB的情況下正常的工作。建議將該引數設為binlog_cache_size*2。

binlog_stmt_cache_size     這個引數決定二進位制日誌處理非事務性語句的快取。如果MySQL服務支援任何事務性的儲存引擎且開啟了二進位制日誌，每個客戶端連線都會被分配二進位制日誌事務和語句快取。如果資料庫中經常執行大的事務，增加這個快取可以獲得更好的效能。

table_open_cache     所有執行緒能開啟的表的數量。

thread_cache_size     MySQL服務快取以重用的執行緒數。當客戶端斷開連線的時候，如果執行緒快取沒有使用滿，則客戶端的執行緒被放入快取中。如果有客戶端斷開連線後再次連線到MySQL服務且執行緒在快取中，則MySQL服務會優先使用快取中的執行緒；如果執行緒快取沒有這些執行緒，則MySQL伺服器會建立新的執行緒。如果資料庫有很多的新連線，可以增加這個引數來提升效能。如果MySQL伺服器每秒有上百個連線，可以增大thread_cache_size引數來使MySQL伺服器使用快取的執行緒。通過檢查Connections和Threads_created狀態引數，可以判斷執行緒快取是否足夠。這個引數預設的值是由下面的公式來決定的：
8 + (max_connections / 100)
建議將此引數設定為300~500。執行緒快取的命中率計算公式為(1-thread_created/connections)*100%，可以通過這個公式來優化和調整thread_cache_size引數。

query_cache_size     為查詢結果所分配的快取。預設這個引數是沒有開啟的。這個引數的值應設為整數的1024倍，如果設為其他值則會被自動調整為接近此數值的1024倍。這個引數最小需要40KB。建議不要將此引數設為大於256MB，以免佔用太多的系統記憶體。

query_cache_min_res_unit     查詢快取所分配的最小塊的大小。預設值是4096(4KB)。

query_cache_type     設定查詢快取的型別。當這個引數為0或OFF時，則MySQL伺服器不會啟用查詢快取；當這個引數為1或ON時，則MySQL伺服器會快取所有查詢結果（除了帶有SELECT SQL_NO_CACHE的語句）；當這個引數為2或DEMAND時，則MySQL伺服器只會快取帶有SELECT SQL_CACHE的語句。

sort_buffer_size      每個會話執行排序操作所分配的記憶體大小。想要增大max_sort_length引數，需要增大sort_buffer_size引數。如果在SHOW GLOBAL STATUS輸出結果中看到每秒輸出的Sort_merge_passes狀態引數很大，可以考慮增大sort_buffer_size這個值來提高ORDER BY 和 GROUP BY的處理速度。建議設定為1~4MB。當個別會話需要執行大的排序操作時，在會話級別增大這個引數。

read_buffer_size     為每個執行緒對MyISAm表執行順序讀所分配的記憶體。如果資料庫有很多順序讀，可以增加這個引數，預設值是131072位元組。這個引數的值需要是4KB的整數倍。這個引數也用在下面場景中：
當執行ORDER BY操作時，快取索引到臨時檔案（不是臨時表）中；
執行批量插入到分割槽表中；
快取巢狀查詢的執行結果。

read_rnd_buffer_size     這個引數用在MyISAM表和任何儲存引擎表隨機讀所使用的記憶體。當從MyISAM表中以鍵排序讀取資料的時候，掃描的行將使用這個快取以避免磁碟的掃描。將這個值設到一個較大的值可以顯著提升ORDER BY的效能。然後，這個引數會應用到所有的客戶端連線，所有不應該將這個引數在全域性級別設為一個較大的值；在執行大查詢的會話中，在會話級別增大這個引數即可。

join_buffer_size     MySQL伺服器用來作普通索引掃描、範圍索引掃描和不使用索引而執行全表掃描這些操作所用的快取大小。通常，獲取最快連線的方法是增加索引。當不能增加索引的時候，使全連線變快的方法是增大join_buffer_size引數。對於執行全連線的兩張表，每張表都被分配一塊連線記憶體。對於沒有使用索引的多表複雜連線，需要多塊連線記憶體。通常來說，可以將此引數在全域性上設定一個較小的值，而在需要執行大連線的會話中在會話級別進行設定。預設值是256KB。

net_buffer_length     每個客戶端執行緒和連線快取和結果快取互動，每個快取最初都被分配大小為net_buffer_length的容量，並動態增長，直至達到max_allowed_packet引數的大小。當每條SQL語句執行完畢後，結果快取會縮小到net_buffer_length大小。不建議更改這個引數，除非你的系統有很少的記憶體，可以調整這個引數。如果語句需要的記憶體超過了這個引數的大小，則連線快取會自動增大。net_buffer_length引數最大可以設定到1MB。不能在會話級別設定這個引數。

max_allowed_packet     網路傳輸時單個資料包的大小。預設值是4MB。包資訊快取的初始值是由net_buffer_length指定的，但是包可能會增長到max_allowed_packet引數設定的值。如果要使用BLOB欄位或長字串，需要
增加這個引數的值。這個引數的值需要設定成和最大的BLOB欄位一樣的大小。max_allowed_packet引數的協議限制是1GB。這個引數應該是1024整數倍。

bulk_insert_buffer_size     MyISAM表使用一種特殊的樹狀快取來提高批量插入的速度，如INSERT ... SELECT,INSERT ... VALUES (...),(...), ...,對空表執行執行LOAD DATA INFILE。這個引數每個執行緒的樹狀快取大小。將這個引數設為0會關閉這個引數。這個引數的預設值是8MB。

max_heap_table_size     這個引數設定使用者建立的MEMORY表允許增長的最大容量，這個引數用來MEMORY表的MAX_ROWS值。設定這個引數對已有的MEMORY表沒有影響，除非表重建或執行ALTER TABLE、TRUNCATE TABLE語句。
這個引數也和tmp_table_size引數一起來現在內部in-memory表的大小。如果記憶體表使用頻繁，可以增大這個引數的值。

tmp_table_size     內部記憶體臨時表的最大記憶體。這個引數不會應用到使用者建立的MEMORY表。如果記憶體臨時表的大小超過了這個引數的值，則MySQL會自動將超出的部分轉化為磁碟上的臨時表。在MySQL 5.7.5版本，internal_tmp_disk_storage_engine儲存引擎將作為磁碟臨時表的預設引擎。在MySQL 5.7.5之前的版本，會使用MyISAM儲存引擎。如果有很多的GROUP BY查詢且系統記憶體充裕，可以考慮增大這個引數。

innodb_buffer_pool_dump_at_shutdown

指定在MySQL服務關閉時，是否記錄InnoDB快取池中的快取頁，以縮短下次重啟時的預熱過程。通常和innodb_buffer_pool_load_at_startup引數搭配使用。innodb_buffer_pool_dump_pct引數定義了保留的最近使用快取頁的百分比。

innodb_buffer_pool_dump_now

立刻記錄InnoDB緩衝池中的快取頁。通常和innodb_buffer_pool_load_now搭配使用。

innodb_buffer_pool_load_at_startup

指定MySQL服務在啟動時，InnoDB緩衝池通過載入之前的快取頁資料來自動預熱。通常和innodb_buffer_pool_dump_at_shutdown引數搭配使用。

innodb_buffer_pool_load_now

立刻通過載入資料頁來預熱InnoDB緩衝池，無需重啟資料庫服務。可以用來在效能測試時，將快取改成到一個已知的狀態；或在資料庫執行報表查詢或維護後，將資料庫改成到一個正常的狀態。

--MyISAM引數

key_buffer_size      所有執行緒所共有的MyISAM表索引快取，這塊快取被索引塊使用。增大這個引數可以增加索引的讀寫效能，在主要使用MyISAM儲存引擎的系統中，可設定這個引數為機器總記憶體的25%。如果將這個引數設定很大，比如設為機器總記憶體的50%以上，機器會開始page且變得異常緩慢。可以通過SHOW STATUS 語句檢視 Key_read_requests, Key_reads, Key_write_requests, and Key_writes這些狀態值。正常情況下Key_reads/Key_read_requests 比率應該小於0.01。資料庫更新和刪除操作頻繁的時候，Key_writes/Key_write_requests 比率應該接近1。

key_cache_block_size     key快取的塊大小，預設值是1024位元組。

myisam_sort_buffer_size     在REPAIR TABLE、CREATE INDEX 或 ALTER TABLE操作中，MyISAM索引排序使用的快取大小。

myisam_max_sort_file_size     當重建MyISAM索引的時候，例如執行REPAIR TABLE、 ALTER TABLE、 或 LOAD DATA INFILE命令，MySQL允許使用的臨時檔案的最大容量。如果MyISAM索引檔案超過了這個值且磁碟還有充裕的空間，增大這個引數有助於提高效能。

myisam_repair_threads      如果這個引數的值大於1，則MyISAM表在執行Repair操作的排序過程中，在建立索引的時候會啟用並行，預設值為1。

--InnoDB引數

innodb_buffer_pool_size     InnDB儲存引擎快取表和索引資料所使用的記憶體大小。預設值是128MB。在以InnDB儲存引擎為主的系統中，可以將這個引數設為機器實體記憶體的80%。同時需要注意：
設定較大實體記憶體時是否會引擎頁的交換而導致效能下降；
InnoDB儲存引擎會為快取和控制表結構資訊使用部分記憶體，因而實際花費的記憶體會比設定的值大於10%；
這個引數設定的越大，初始化記憶體池的時間越長。在MySQL 5.7.5版本，可以以chunk為單位增加或減少記憶體池的大小。chunk的大小可以通過innodb_buffer_pool_chunk_size引數設定，預設值是128MB。記憶體池的大小可以等於或是innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的整數倍。

innodb_buffer_pool_instances     InnoDB快取池被分成的區域數。對於1GB以上大的InnoDB快取，將快取分成多個部分可以提高MySQL服務的併發性，減少不同執行緒讀快取頁的讀寫競爭。每個快取池有它單獨的空閒列表、重新整理列表、LRU列表和其他連線到記憶體池的資料結構，它們被mutex鎖保護。這個引數只有將innodb_buffer_pool_size引數設為1GB或以上時才生效。建議將每個分成的記憶體區域設為1GB大小。

innodb_max_dirty_pages_pct     當Innodb快取池中髒頁所佔的百分比達到這個引數的值時，InnoDB會從快取中向磁碟寫入資料。預設值是75。

innodb_thread_concurrency     InnoDB儲存引擎可以併發使用的最大執行緒數。當InnoDB使用的執行緒超過這引數的值時，後面的執行緒會進入等待狀態，以先進先出的演算法來處理。等待鎖的執行緒不計入這個引數的值。這個引數的範圍是0~1000。預設值是0。當這個引數為0時，代表InnoDB執行緒的併發數沒有限制，這樣會導致MySQL建立它所需要的儘可能多的執行緒。設定這個引數可以參考下面規則：
如果使用者執行緒的併發數小於64，可以將這個引數設為0；
如果系統併發很嚴重，可以先將這個引數設為128，然後再逐漸將這個引數減小到96, 80, 64或其他數值，直到找到效能較好的一個數值。

innodb_flush_method     指定重新整理資料到InnoDB資料檔案和日誌檔案的方法，重新整理方法會對I/O有影響。如果這個引數的值為空，在類Unix系統上，這個引數的預設值為fsync；在Windows系統上，這個引數的預設值為async_unbuffered。在類Unix系統上，這個引數可設定的值如下：
fsync:InnoDB使用fsync()系統函式來重新整理資料和日誌檔案，fsync是預設引數。
O_DSYNC:InnoDB使用O_SYNC函式來開啟和重新整理日誌檔案，使用fsync()函式重新整理資料檔案
littlesync:這個選項用在內部效能的測試，目前MySQL尚不支援，使用這個引數又一定的風險
nosync:這個選項用在內部效能的測試，目前MySQL尚不支援，使用這個引數又一定的風險
O_DIRECT:InnoDB使用O_DIRECT（或者directio()在Solaris）函式開啟資料檔案，使用fsync()重新整理資料檔案和日誌檔案
O_DIRECT_NO_FSYNC:在重新整理I/O時，InnoDB使用O_DIRECT方式。
在有RAID卡和寫快取的系統中，O_DIRECT有助於避免InnoDB快取池和作業系統快取之間的雙重快取。在InnoDB資料和日誌檔案放在SAN儲存上面的系統，預設值或O_DSYNC方法會對以讀為主的資料庫起到加速作用。

innodb_data_home_dir     InnoDB系統表空間所使用的資料檔案的物理路徑，預設路徑是MySQL資料檔案路徑。如果這個引數的值為空，可以在innodb_data_file_path引數裡使用絕對路徑

innodb_data_file_path     InnoDB資料檔案的路徑和大小。

innodb_file_per_table     當這個引數啟用的時候，InnoDB會將新建表的資料和索引單獨存放在.ibd格式的檔案中，而不是存放在系統表空間中。當這張表被刪除或TRUNCATE時，InnoDB表所佔用的儲存會被釋放。這個設定會開啟InnoDB的一些其他特性，比如表的壓縮。當這個引數關閉的時候，InnoDB會將表和索引的資料存放到系統表空間的ibdata檔案中，這會有一個問題，因為系統表空間不會縮小，這樣設定會導致空間無法回放。

innodb_undo_directory     InnoDB undo日誌所在表空間的物理路徑。和innodb_undo_logs、innodb_undo_tablespaces引數配合，來設定undo日誌的路徑，預設路徑是資料檔案路徑。

innodb_undo_logs     指定InnoDB使用的undo日誌的個數。在MySQL 5.7.2版本，32個undo日誌被臨時表預留使用，並且這些日誌存放在臨時表表空間(ibtmp1)中。如果undo日誌只存放在系統表空間中，想要額外分配供資料修改事務用的undo日誌，innodb_undo_logs引數必須設定為32以上的整數。如果你配置了單獨的undo表空間，要將innodb_undo_logs引數設為33以上來分配額外供資料修改事務使用的undo日誌。每個undo日誌最多可以支援1024個事務。如果這個引數沒有設定，則它會設為預設值128。

innodb_undo_tablespaces     undo日誌的表空間檔案數量。預設，所有的undo日誌都是系統表空間的一部分。因為在執行大的事務時，undo日誌會增大，將undo日誌設定在多個表空間中可以減少一個表空間的大小。undo表空間檔案建立在innodb_undo_directory引數指定的路徑下，以undoN格式命名，N是以0開頭的一系列整數。undo表空間的預設大小為10M。需要在初始化InnoDB前設定innodb_undo_tablespaces這個引數。在MySQL 5.7.2版本，在128個undo日誌中，有32個undo日誌是為臨時表所預留的，有95個undo日誌供undo表空間使用。

innodb_log_files_in_group     InnoDB日誌組包含的日誌個數。InnoDB以迴圈的方式寫入日誌。這個引數的預設值和推薦值均是2。日誌的路徑由innodb_log_group_home_dir引數設定。

innodb_log_group_home_dir     InnoDB重做日誌檔案的物理路徑，重做日誌的數量由innodb_log_files_in_group引數指定。如果不指定任何InnoDB日誌引數，MySQL預設會在MySQL資料檔案路徑下面建立兩個名為ib_logfile0、ib_logfile1的兩個重做日誌檔案，它們的大小由innodb_log_file_size引數設定。

innodb_log_file_size     日誌組中每個日誌檔案的位元組大小。所有日誌檔案的大小(innodb_log_file_size * innodb_log_files_in_group)不能超過512GB。

innodb_log_buffer_size      InnoDB寫入磁碟日誌檔案所使用的快取位元組大小。如果innodb_page_size引數為32K，則預設值是8MB；如果innodb_page_size引數為64K，則預設值是16MB。如果日誌的快取設定較大，則MySQL在處理大事務時，在提交事務前無需向磁碟寫入日誌檔案。建議設定此引數為4~8MB。

innodb_flush_log_at_trx_commit     當提交相關的I/O操作被批量重新排列時，這個引數控制提交操作的ACID一致性和高效能之間的平衡。可以改變這個引數的預設值來提升資料庫的效能，但是在資料庫當機的時候會丟失少量的事務。這個引數的預設值為1，代表資料庫遵照完整的ACID模型，每當事務提交時，InnoDB日誌快取中的內容均會被重新整理到日誌檔案，並寫入到磁碟。當這個引數為0時，InnDB日誌快取大概每秒重新整理一次日誌檔案到磁碟。當事務提交時，日誌快取不會立刻寫入日誌檔案，這樣的機制不會100%保證每秒都向日誌檔案重新整理日誌，當mysqld程式宕掉的時候可能會丟失持續時間為1秒左右的事務資料。當這個引數為2時，當事務提交後，InnoDB日誌快取中的內容會寫入到日誌檔案且日誌檔案，日誌檔案以大概每秒一次的頻率重新整理到磁碟。在MySQL 5.6.6版本，InnoDB日誌重新整理頻率由innodb_flush_log_at_timeout引數決定。通常將個引數設為1。

innodb_flush_log_at_timeout     寫入或重新整理日誌的時間間隔。這個引數是在MySQL 5.6.6版本引入的。在MySQL 5.6.6版本之前，重新整理的頻率是每秒重新整理一次。innodb_flush_log_at_timeout引數的預設值也是1秒重新整理一次。

innodb_lock_wait_timeout     InnDB事務等待行鎖的時間長度。預設值是50秒。當一個事務鎖定了一行，這時另外一個事務想訪問並修改這一行，當等待時間達到innodb_lock_wait_timeout引數設定的值時，MySQL會報錯"ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction"，同時會回滾語句（不是回滾整個事務）。如果想回滾整個事務，需要使用--innodb_rollback_on_timeout引數啟動MySQL。在高互動性的應用系統或OLTP系統上，可以減小這個引數來快速顯示使用者的反饋或把更新放入佇列稍後處理。在資料倉儲中，為了更好的處理執行時間長的操作，可以增大這個引數。這個引數只應用在InnoDB行鎖上，這個引數對錶級鎖無效。這個引數不適用於死鎖，因為發生死鎖時，InnoDB會立刻檢測到死鎖並將發生死鎖的一個事務回退。

innodb_fast_shutdown      InnoDB關庫模式。如果這個引數為0，InnoDB會做一個緩慢關機，在關機前會做完整的重新整理操作，這個級別的關庫操作會持續數分鐘，當快取中的資料量很大時，甚至會持續幾個小時；如果資料庫要執行版本升級或降級，需要執行這個級別的關庫操作，以保證所有的資料變更都寫入到資料檔案。如果這個引數的值是1（預設值），為了節省關庫時間，InnoDB會跳過新操作，而是在下一次開機的時候通過crash recovery方式執行重新整理操作。如果這個引數的值是2，InnoDB會重新整理日誌並以冷方式關庫，就像MySQL當機一樣，沒有提交的事務會丟失，在下一次開啟資料庫時，crash recovery所需要的時間更長；在緊急或排錯情形下，需要立刻關閉資料庫時，會使用這種方式停庫。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26506993/viewspace-2097297/，如需轉載，請註明出處，否則將追究法律責任。

相關文章