mysql資料庫績效參數配置(轉)

標籤：資料庫   架構   linux   伺服器   效能   

 

• max_connections

MySql的最大串連數，如果伺服器的並發串連請求量比較大，建議調高此值，以增加並行串連數量，當然這建立在機器能支撐的情況下，因為如果串連數越多，MySql會為每個串連提供串連緩衝區，就會開銷越多的記憶體，串連數太大，伺服器消耗的記憶體越多，以至於影響伺服器效能，所以要根據伺服器的配置適當調整該值，不能盲目提高設值。可以過‘conn%‘萬用字元查看目前狀態的串連數量，以定奪該值的大小。

show variables like ‘max_connections‘ 最大串連數，show status like ‘max_used_connections‘響應的串連數。如下：

mysql> show variables like ‘max_connections‘;

+-----------------------+-------+

| Variable_name　| Value |

+-----------------------+-------+

| max_connections | 256　　|

+-----------------------+-------+

mysql> show status like ‘max_used_connections‘;

+-----------------------+-------+

| Variable_name　 | Value |

+----------------------------+-------+

| max_used_connections | 256|

+----------------------------+-------+

max_used_connections / max_connections * 100%（理想值≈ 85%），如果max_used_connections跟max_connections相同，那麼就是max_connections設定過低或者超過伺服器負載上限了，低於10%則設定過大。

• back_log

MySQL能暫存的串連數量。當主要MySQL線程在一個很短時間內得到非常多的串連請求，這就起作用。如果MySQL的串連資料達到max_connections時，新來的請求將會被存在堆棧中，以等待某一串連釋放資源，該堆棧的數量即back_log，如果等待串連的數量超過back_log，將不被授予串連資源。

back_log值指出在MySQL暫時停止回答新請求之前的短時間內有多少個請求可以被存在堆棧中。只有如果期望在一個短時間內有很多串連，你需要增加它，換句話說，這值對到來的TCP/IP串連的偵聽隊列的大小。

在mysql中back_log的設定取決於作業系統，在linux下這個參數的值不能大於系統參數tcp_max_syn_backlog的值。通過以下命令可以查看tcp_max_syn_backlog的當前值 cat /proc/sys/net/ipv4/tcp_max_syn_backlog。

預設數值是50，可調優為128。我把它改為500。 

• interactive_timeout

一個互動串連在被伺服器在關閉前等待行動的秒數。一個互動的客戶被定義為對mysql_real_connect()使用CLIENT_INTERACTIVE 選項的客戶。 預設數值是28800，可調優為7200。

• key_buffer_size

key_buffer_size指定索引緩衝的大小，它決定索引處理的速度，尤其是索引讀的速度。通過檢查狀態值Key_read_requests和Key_reads，可以知道key_buffer_size設定是否合理。比例key_reads / key_read_requests應該儘可能的低，至少是1:100，1:1000更好（上述狀態值可以使用SHOW STATUS LIKE ‘key_read%’獲得）。

key_buffer_size只對MyISAM表起作用。即使你不使用MyISAM表，但是內部的臨時磁碟表是MyISAM表，也要使用該值。可以使用檢查狀態值created_tmp_disk_tables得知詳情。舉例如下：

mysql> show variables like ‘key_buffer_size‘;

+-------------------+------------+

| Variable_name | Value |

+---------------------+------------+

| key_buffer_size | 536870912 |

+------------ ----------+------------+

key_buffer_size為512MB，我們再看一下key_buffer_size的使用方式：

mysql> show global status like ‘key_read%‘;

+------------------------+-------------+

| Variable_name　 | Value |

+------------------------+-------------+

| Key_read_requests| 27813678764 |

| Key_reads　　　| 6798830 |

+------------------------+-------------+

一共有27813678764個索引讀取請求，有6798830個請求在記憶體中沒有找到直接從硬碟讀取索引，計算索引未命中緩衝的機率：

key_cache_miss_rate ＝Key_reads / Key_read_requests * 100%，設定在1/1000左右較好。

預設配置數值是8388600(8M)，主機有4GB記憶體，可以調優值為268435456(256MB)。

• query_cache_size

使用查詢緩衝，MySQL將查詢結果存放在緩衝區中，今後對於同樣的SELECT語句（區分大小寫），將直接從緩衝區中讀取結果。

通過檢查狀態值Qcache_*，可以知道query_cache_size設定是否合理（上述狀態值可以使用SHOW STATUS LIKE ‘Qcache%’獲得）。如果Qcache_lowmem_prunes的值非常大，則表明經常出現緩衝不夠的情況，如果Qcache_hits的值也非常大，則表明查詢緩衝使用非常頻繁，此時需要增加緩衝大小；如果Qcache_hits的值不大，則表明你的查詢重複率很低，這種情況下使用查詢緩衝反而會影響效率，那麼可以考慮不用查詢緩衝。此外，在SELECT語句中加入SQL_NO_CACHE可以明確表示不使用查詢緩衝。

與查詢緩衝有關的參數還有query_cache_type、query_cache_limit、query_cache_min_res_unit。

query_cache_type指定是否使用查詢緩衝，可以設定為0、1、2，該變數是SESSION級的變數。

query_cache_limit指定單個查詢能夠使用的緩衝區大小，預設為1M。

query_cache_min_res_unit是在4.1版本以後引入的，它指定分配緩衝區空間的最小單位，預設為4K。檢查狀態值Qcache_free_blocks，如果該值非常大，則表明緩衝區中片段很多，這就表明查詢結果都比較小，此時需要減小query_cache_min_res_unit。

舉例如下：

mysql> show global status like ‘qcache%‘;

+-------------------------------+-----------------+

| Variable_name | Value　 |

+-------------------------------+-----------------+

| Qcache_free_blocks　 | 22756　 |

| Qcache_free_memory　 | 76764704 |

| Qcache_hits　　　　　 | 213028692 |

| Qcache_inserts　　　　 | 208894227 |

| Qcache_lowmem_prunes | 4010916 |

| Qcache_not_cached　| 13385031 |

| Qcache_queries_in_cache | 43560　|

| Qcache_total_blocks | 111212　 |

+-------------------------------+-----------------+

mysql> show variables like ‘query_cache%‘;

+--------------------------------------+--------------+

| Variable_name　　　　　 | Value　 |

+--------------------------------------+-----------+

| query_cache_limit　　　　　 | 2097152 |

| query_cache_min_res_unit　 | 4096　　 |

| query_cache_size　　　　　 | 203423744 |

| query_cache_type　　　　　 | ON　 |

| query_cache_wlock_invalidate | OFF　 |

+--------------------------------------+---------------+

查詢快取片段率= Qcache_free_blocks / Qcache_total_blocks * 100%，如果查詢快取片段率超過20%，可以用FLUSH QUERY CACHE整理緩衝片段，或者試試減小query_cache_min_res_unit，如果你的查詢都是小資料量的話。

查詢快取利用率= (query_cache_size - Qcache_free_memory) / query_cache_size * 100%，查詢快取利用率在25%以下的話說明query_cache_size設定的過大，可適當減小；查詢快取利用率在80％以上而且Qcache_lowmem_prunes > 50的話說明query_cache_size可能有點小，要不就是片段太多。

查詢快取命中率= (Qcache_hits - Qcache_inserts) / Qcache_hits * 100%，樣本伺服器查詢快取片段率＝20.46％，查詢快取利用率＝62.26％，查詢快取命中率＝1.94％，命中率很差，可能寫操作比較頻繁吧，而且可能有些片段。

• record_buffer_size

每個進行一個順序掃描的線程為其掃描的每張表分配這個大小的一個緩衝區。如果你做很多順序掃描，你可能想要增加該值。預設數值是131072(128K)，可改為16773120 (16M)。

• read_rnd_buffer_size

隨機讀緩衝區大小。當按任意順序讀取行時(例如，按照排序次序)，將分配一個隨機讀緩衝區。進行排序查詢時，MySQL會首先掃描一遍該緩衝，以避免磁碟搜尋，提高查詢速度，如果需要排序大量資料，可適當調高該值。但MySQL會為每個客戶串連發放該緩衝空間，所以應盡量適當設定該值，以避免記憶體開銷過大。一般可設定為16M。

• sort_buffer_size

每個需要進行排序的線程分配該大小的一個緩衝區。增加這值加速ORDER BY或GROUP BY操作。預設數值是2097144(2M)，可改為16777208 (16M)。

• join_buffer_size

聯集查詢操作所能使用的緩衝區大小。

record_buffer_size，read_rnd_buffer_size，sort_buffer_size，join_buffer_size為每個線程獨佔，也就是說，如果有100個線程串連，則佔用為16M*100。

• table_cache

表快取的大小。每當MySQL訪問一個表時，如果在表緩衝區中還有空間，該表就被開啟並放入其中，這樣可以更快地訪問表內容。通過檢查峰值時間的狀態值Open_tables和Opened_tables，可以決定是否需要增加table_cache的值。如果你發現open_tables等於table_cache，並且opened_tables在不斷增長，那麼你就需要增加table_cache的值了（上述狀態值可以使用SHOW STATUS LIKE ‘Open%tables’獲得）。注意，不能盲目地把table_cache設定成很大的值。如果設定得太高，可能會造成檔案描述符不足，從而造成效能不穩定或者串連失敗。

1G記憶體機器，推薦值是128－256。記憶體在4GB左右的伺服器該參數可設定為256M或384M。

• max_heap_table_size

使用者可以建立的記憶體表(memory table)的大小。這個值用來計算記憶體表的最大行數值。這個變數支援動態改變，即set @max_heap_table_size=#。

這個變數和tmp_table_size一起限制了內部記憶體表的大小。如果某個內部heap（堆積）表大小超過tmp_table_size，MySQL可以根據需要自動將記憶體中的heap表改為基於硬碟的MyISAM表。

• tmp_table_size

通過設定tmp_table_size選項來增加一張暫存資料表的大小，例如做進階GROUP BY操作產生的暫存資料表。如果調高該值，MySQL同時將增加heap表的大小，可達到提高聯結查詢速度的效果，建議盡量最佳化查詢，要確保查詢過程中產生的暫存資料表在記憶體中，避免暫存資料表過大導致產生基於硬碟的MyISAM表。

mysql> show global status like ‘created_tmp%‘;

+--------------------------------+---------+

| Variable_name　　 | Value　|

+----------------------------------+---------+

| Created_tmp_disk_tables | 21197 |

| Created_tmp_files　　　| 58　　|

| Created_tmp_tables　　| 1771587 |

+--------------------------------+-----------+

每次建立暫存資料表，Created_tmp_tables增加，如果暫存資料表大小超過tmp_table_size，則是在磁碟上建立暫存資料表，Created_tmp_disk_tables也增加，Created_tmp_files表示MySQL服務建立的臨時檔案檔案數，比較理想的配置是：Created_tmp_disk_tables / Created_tmp_tables * 100% <= 25%比如上面的伺服器Created_tmp_disk_tables / Created_tmp_tables * 100% ＝1.20%，應該相當好了。

預設為16M，可調到64-256最佳，線程獨佔，太大可能記憶體不夠I/O堵塞。

• thread_cache_size

可以複用的儲存在記憶體中的線程的數量。如果有，新的線程從緩衝中取得，當中斷連線的時候如果有空間，客戶的線置將儲存在緩衝中。如果有很多新的線程，為了提高效能可以增加這個變數值。通過比較 Connections和Threads_created狀態的變數，可以看到這個變數的作用，如下：

mysql> show global status like ‘thread%‘;

Threads_cached 0Threads_connected 51Threads_created 655068Threads_running 48

Threads_cached :代表當前此時此刻線程緩衝中有多少空閑線程。

Threads_connected :代表當前已建立串連的數量，因為一個串連就需要一個線程，所以也可以看成當前被使用的線程數。

Threads_created :代表從最近一次服務啟動，已建立線程的數量。

Threads_running :代表當前啟用的（非睡眠狀態）線程數。並不是代表正在使用的線程數，有時候串連已建立，但是串連處於sleep狀態，這裡相對應的線程也是sleep狀態。

預設值為110，可調優為80。

• wait_timeout

指定一個請求的最大連線時間，對於4GB左右記憶體的伺服器可以設定為5-10。

• innodb_buffer_pool_size

對於InnoDB表來說，innodb_buffer_pool_size的作用就相當於key_buffer_size對於MyISAM表的作用一樣。InnoDB使用該參數指定大小的記憶體來緩衝資料和索引。對於單獨的MySQL資料庫伺服器，最大可以把該值設定成實體記憶體的80%。

根據MySQL手冊，對於2G記憶體的機器，推薦值是1G（50%）。

• innodb_flush_log_at_trx_commit

主要控制了innodb將log buffer中的資料寫入記錄檔並flush磁碟的時間點，取值分別為0、1、2三個。0，表示當事務提交時，不做日誌寫入操作，而是每秒鐘將log buffer中的資料寫入記錄檔並flush磁碟一次；1，則在每秒鐘或是每次事物的提交都會引起記錄檔寫入、flush磁碟的操作，確保了事務的ACID；設定為2，每次事務提交引起寫入記錄檔的動作，但每秒鐘完成一次flush磁碟操作。

實際測試發現，該值對插入資料的速度影響非常大，設定為2時插入10000條記錄只需要2秒，設定為0時只需要1秒，而設定為1時則需要229秒。因此，MySQL手冊也建議盡量將插入操作合并成一個事務，這樣可以大幅提高速度。

根據MySQL手冊，在允許丟失最近部分事務的危險的前提下，可以把該值設為0或2。

• innodb_log_buffer_size

log緩衝大小，一般為1-8M，預設為1M，對於較大的事務，可以增大緩衝大小。

可設定為4M或8M。

innodb_additional_mem_pool_size

該參數指定InnoDB用來儲存資料字典和其他內部資料結構的記憶體池大小。預設值是1M。通常不用太大，只要夠用就行，應該與表結構的複雜度有關係。如果不夠用，MySQL會在錯誤記錄檔中寫入一條警告資訊。

根據MySQL手冊，對於2G記憶體的機器，推薦值是20M，可適當增加。

• innodb_thread_concurrency

線程並發數，推薦設定為 2*(NumCPUs+NumDisks)，預設一般為8。

(一)串連

    串連通常來自Web伺服器，下面列出了一些與串連有關的參數，以及該如何設定它們。

    1、max_connections

    這是Web伺服器允許的最大串連數，記住每個串連都要使用會話記憶體(關於會話記憶體，文章後面有涉及)。

    2、max_packet_allowed

    最大資料包大小，通常等於你需要在一個大塊中返回的最大資料集的大小，如果你在使用遠程mysqldump，那它的值需要更大。

    3、aborted_connects

    檢查系統狀態的計數器，確定其沒有增長，如果數量增長說明用戶端串連時遇到了錯誤。

    4、thread_cache_size

    入站串連會在MySQL中建立一個新的線程，因為MySQL中開啟和關閉串連都很廉價，速度也快，它就沒有象其它資料庫，如Oracle那麼多持續串連了，但線程預先建立並不會節約時間，這就是為什麼要MySQL線程緩衝的原因了。

    如果在增長請密切注意建立的線程，讓你的線程緩衝更大，對於2550或100的thread_cache_size，記憶體佔用也不多。

    (二)查詢快取

    (三)暫存資料表

    記憶體速度是相當快的，因此我們希望所有的排序操作都在記憶體中進行，我們可以通過調整查詢讓結果集更小以實現記憶體排序，或將變數設定得更大。

    tmp_table_size

    max_heap_table_size

    無論何時在MySQL中建立暫存資料表，它都會使用這兩個變數的最小值作為臨界值，除了在磁碟上構建暫存資料表外，還會建立許多會話，這些會話會搶佔有 限制的資源，因此最好是調整查詢而不是將這些參數設定得更高，同時，需要注意的是有BLOB或TEXT欄位類型的表將直接寫入磁碟。 深入淺出MySQL雙向複製技術

    (四)會話記憶體

    MySQL中每個會話都有其自己的記憶體，這個記憶體就是分配給SQL查詢的記憶體，因此你想讓它變得儘可能大以滿足需要。但你不得不平衡同一時間數 據庫內一致性會話的數量。這裡顯得有點黑色藝術的是MySQL是按需分配緩衝的，因此，你不能只添加它們並乘以會話的數量，這樣估算下來比MySQL典型 的使用要大得多。

    最佳做法是啟動MySQL，串連所有會話，然後繼續關注頂級會話的VIRT列，mysqld行的數目通常保持相對穩定，這就是實際的記憶體 總用量，減去所有的靜態MySQL記憶體地區，就得到了實際的所有會話記憶體，然後除以會話的數量就得到平均值。

    1、read_buffer_size

    緩衝連續掃描的塊，這個緩衝是跨儲存引擎的，不只是MyISAM表。

    2、sort_buffer_size

    執行排序緩衝區的大小，最好將其設定為1M-2M，然後在會話中設定，為一個特定的查詢設定更高的值。

    3、join_buffer_size

    執行聯集查詢分配的緩衝區大小，將其設定為1M-2M大小，然後在每個會話中再單獨按需設定。

    4、read_rnd_buffer_size

    用於排序和order by操作，最好將其設定為1M，然後在會話中可以將其作為一個會話變數設定為更大的值。

    (五)慢速查詢日誌

    慢速查詢日誌是MySQL很有用的一個特性。

    1、log_slow_queries

    MySQL參數中log_slow_queries參數在my.cnf檔案中設定它，將其設定為on，預設情況下，MySQL會將檔案放到資料目錄，檔案以“主機名稱-slow.log”的形式命名，但你在設定這個選項的時候也可以為其指定一個名字。

    2、long_query_time

    預設值是10秒，你可以動態設定它，值從1到將其設定為on，如果資料庫啟動了，預設情況下，日誌將關閉。截至5.1.21和安裝了 Google補丁的版本，這個選項可以以微秒設定，這是一個了不起的功能，因為一旦你消除了所有查詢時間超過1秒的查詢，說明調整非常成功，這樣可以協助 你在問題變大之前消除問題SQL。

    3、log_queries_not_using_indexes

    開啟這個選項是個不錯的主意，它真實地記錄了返回所有行的查詢。

    小結

    我們介紹了MySQL參數的五大類設定，平時我們一般都很少碰它們，在進行MySQL效能調優和故障診斷時這些參數還是非常有用的。

    MySQL中的緩衝查詢包括兩個解析查詢計劃，以及返回的資料集，如果基礎資料表資料或結構有變化，將會使查詢快取中的項目無效。

    1、query_cache_min_res_unit

    MySQL參數中query_cache_min_res_unit查詢快取中的塊是以這個大小進行分配的，使用下面的公式計算查詢快取的平均大小，根據計算結果設定這個變數，MySQL就會更有效地使用查詢快取，緩衝更多的查詢，減少記憶體的浪費。

    2、query_cache_size

    這個參數設定查詢快取的總大小。

    3、query_cache_limit

    這個參數告訴MySQL丟掉大於這個大小的查詢，一般大型查詢還是比較少見的，如運行一個批處理執行一個大型報表的統計，因此那些大型結果集不應該填滿查詢快取。