效能最佳化系列八：MYSQL的配置最佳化

標籤：hand   排序   掃描   client   data   usr   關閉   隊列   輸入   

一、關鍵配置1. 設定檔的位置

 MySQL設定檔

 /etc/my.cnf 或者 /etc/my.cnf.d/server.cnf

幾個關鍵的檔案：
.pid檔案，記錄了進程id
.sock檔案，是內部通訊使用的socket介面，比3306快
.log檔案，記錄檔
.cnf或.conf檔案，設定檔
安裝目錄：basedir
資料目錄：datadir

2. 在哪裡儲存你的資料

 基本配置，指定資料目錄，my.cnf或者server.cnf

[mysqld]
user = mysql
port = 3306
socket = /data/3306/mysql.sock，#這裡指定了一個特別的串連
basedir = /usr/local/mysql
datadir = /data/3306/data
[client]
port = 3306
socket = /data/3306/mysql.sock，在用戶端也要聲明它，命令列要用到

3. 查詢快取要不要開  寫入頻繁的資料庫，不要開查詢快取

query_cache_size
Query_cache裡的資料又怎麼處理呢？首先要把Query_cache和該表相關的語句全部置為失效，然後在寫入更新。那麼如果Query_cache非常大，該表的查詢結構又比較多，查詢語句失效也慢，一個更新或是Insert就會很慢，這樣看到的就是Update或是Insert怎麼這麼慢了。所以在資料庫寫入量或是更新量也比較大的系統，該參數不適合分配過大。而且在高並發，寫入量大的系統，建議把該功能禁掉。
query_cache_limit
指定單個查詢能夠使用的緩衝區大小，預設為1M
query_cache_min_res_unit
預設是4KB，設定值大對大資料查詢有好處，但如果你的查詢都是小資料查詢，就容易造成記憶體片段和浪費

說明：禁掉查詢快取的方法就是直接注釋掉查詢快取的配置，如#query_cache_size=1M, 這樣就可以了

4. 其他需要開的緩衝  讀緩衝，線程緩衝，排序緩衝

sort_buffer_size = 2M
connection級參數。太大將導致在串連數增高時，記憶體不足。

max_allowed_packet = 32M
網路傳輸中一次訊息傳輸量的最大值。系統預設值 為1MB，最大值是1GB，必須設定1024的倍數。

join_buffer_size = 2M
和sort_buffer_size一樣，該參數對應的分配記憶體也是每個串連獨享

tmp_table_size = 256M
預設大小是 32M。GROUP BY 多不多的問題

max_heap_table_size = 256M
key_buffer_size = 2048M
索引的緩衝區大小，對於記憶體在4GB左右的伺服器來說，該參數可設定為256MB或384MB。

read_buffer_size = 1M
read_rnd_buffer_size = 16M
進行排序查詢時，MySql會首先掃描一遍該緩衝，以避免磁碟搜尋

bulk_insert_buffer_size = 64M
批量插入資料緩衝大小，可以有效提高插入效率，預設為8M

Innodb緩衝

innodb_buffer_pool_size = 2048M
只需要用Innodb的話則可以設定它高達 70-80% 的可用記憶體。一些應用於 key_buffer 的規則有 ——如果你的資料量不大，並且不會暴增，那麼無需把innodb_buffer_pool_size 設定的太大了。

innodb_additional_mem_pool_size = 16M
網路傳輸中一次訊息傳輸量的最大值。系統預設值為1MB，最大值是1GB，必須設定1024的倍數。

innodb_log_files_in_group = 3
迴圈方式將記錄檔寫到多個檔案。推薦設定為3

innodb_lock_wait_timeout = 120
InnoDB 有其內建的死結檢測機制，能導致未完成的交易回復。innodb_file_per_table = 0 獨享資料表空間，關閉

5. 串連數

open_files_limit = 10240
允許開啟的檔案數

back_log = 600
短時間內的多少個請求可以被存在堆棧中

max_connections = 3000
MySQL預設的最大串連數為100，MySQL伺服器允許的最大串連數16384

max_connect_errors = 6000
設定每個主機的串連請求異常中斷的最大次數，當超過該次數，MYSQL伺服器將禁止host的串連請求

thread_cache_size = 300
重新利用儲存在緩衝中線程的數量

thread_concurrency = 8
thread_concurrency應設為總CPU核心數的2倍

thread_stack = 192K
每個線程的堆棧大小，預設值足夠大，可滿足普通操作。可設定範圍為128K至4GB，預設為192KB。

6. 線程池有關參數

線程池很少配

thread_handling
表示線程池模型。

thread_pool_size
表示線程池的group個數，一般設定為當前CPU核心數目。理想情況下，一個group一個活躍的背景工作執行緒，達到充分利用CPU的目的。

thread_pool_stall_limit
用於timer線程定期檢查group是否“停滯”，參數表示檢測的間隔。

thread_pool_idle_timeout
當一個worker空閑一段時間後會自動結束，保證線程池中的背景工作執行緒在滿足請求的情況下，保持比較低的水平。60秒

thread_pool_oversubscribe
該參數用於控制CPU核心上“超頻”的線程數。這個參數設定值不含listen執行緒計數。

threadpool_high_prio_mode
表示優先隊列的模式。

thread_pool_max_threads
限制線程池最大的線程數，超過將無法再建立更多的線程，預設為100000。

thread_pool_high_prio_tickets
最多語序多少次被放入高優先順序隊列中，預設為4294967295。只有在thread_pool_high_prio_mode為transactions的時候才有效果

說明：

線程處理的最小單位是statement(語句)
線程池實現在server端，通過建立一定數量的線程服務DB請求，相對於one-conection-per-thread的一個線程服務一個串連的方式，線程池服務的最小單位是語句，即一個線程可以對應多個活躍的串連。

7. 慢查詢日誌

slow_query_log
是否開啟慢查詢日誌，1表示開啟，0表示關閉。

log-slow-queries
舊版（5.6以下版本）MySQL資料庫慢查詢日誌儲存路徑。可以不設定該參數，系統則會預設給一個預設的檔案host_name-slow.log

slow-query-log-file
新版（5.6及以上版本）MySQL資料庫慢查詢日誌儲存路徑。可以不設定該參數，系統則會預設給一個預設的檔案host_name-slow.log

long_query_time
慢查詢閾值，當查詢時間多於設定的閾值時，記錄日誌。

log_queries_not_using_indexes
未使用索引的查詢也被記錄到慢查詢日誌中（可選項）。

log_output
日誌儲存方式。log_output=‘FILE‘表示將日誌存入檔案，預設值是‘FILE‘。log_output=‘TABLE‘表示將日誌存入資料庫，這樣日誌資訊就會被寫入到mysql.slow_log表中。MySQL資料庫支援同時兩種日誌儲存方式，配置的時候以逗號隔開即可，如：log_output=‘FILE,TABLE‘。日誌記錄到系統的專用日誌表中，要比記錄到檔案耗費更多的系統資源，因此對於需要啟用慢查詢日誌，又需要能夠獲得更高的系統效能，那麼建議優先記錄到檔案。

二、監控工具1.  innotop工具

安裝：yum install innotop
啟動：innotop -u root -p ‘123’
協助：?

說明：

innotop是一個即時工具，只能查看當前的情況，不能記錄曆史情況

查看協助的時候輸入一個 ？

協助裡面的ABCD.....U就是可以查看對應的指標的情況，如查詢列表輸入Q、查詢緩衝區輸入B、命令統計輸入C

2. lepus工具

http://www.lepus.cc/page/product

 

說明：

lepus是一款開源的資料庫監控工具，能監控各種資料庫，能監控多台資料庫，能查看曆史情況

效能最佳化系列八：MYSQL的配置最佳化