Mysql最佳化配置

Mysql配置最佳化

一、環境介紹

Mysql版本:5.5.27

二、最佳化內容

欄位	介紹	推薦值
skip-locking	避免MySQL的外部鎖定，減少出錯幾率增強穩定性
back_log	MySQL可能的串連數量(linux下推薦小於512)	384
key_buffer_size	key_buffer_size指定用於索引的緩衝區大小，增加它可得到更好的索引處理效能。 對於記憶體在4GB左右的伺服器該參數可設定為256M或384M。 注意：該參數值設定的過大反而會是伺服器整體效率降低!	4G伺服器 256M
max_allowed_packet	當MySQL用戶端或mysqld伺服器收到大於max_allowed_packet位元組的資訊包時，將發出“資訊包過大”錯誤，並關閉串連	4M
thread_stack	主要用來存放每一個線程自身的標識資訊，如線程id，線程運行時基本資料等等，我們可以通過 thread_stack 參數來設定為每一個線程棧分配多大的記憶體	192kb
table_cache	表快取的數目	512
sort_buffer_size	n第一次需要使用這個buffer的時候，一次性分配設定的記憶體	512K
read_buffer_size	讀查詢操作所能使用的緩衝區大小	4M
join_buffer_size	聯集查詢操作所能使用的緩衝區大小	8M
myisam_sort_buffer_size	當在REPAIR TABLE或用CREATE INDEX建立索引或ALTER TABLE過程中排序 MyISAM索引分配的緩衝區。	64M
thread_cache_size	表示可以重新利用儲存在緩衝中線程的數量,當中斷連線時如果緩衝中還有空間,那麼用戶端的線程將被放到緩衝中,如果線程重新被請求，那麼請求將從緩衝中讀取,(3G以上記憶體推薦為64)	64
query_cache_size	查詢快取區的最大長度	64M
tmp_table_size	如果一張暫存資料表超出該大小，MySQL產生一個 The table tbl_name is full 形式的錯誤	256M
max_connections	最大使用者串連數 最大串連數占上限串連數的85％左右	3000
max_connect_errors	它負責阻止過多嘗試失敗的用戶端以防止暴力破解密碼的情況。max_connect_errors的值與效能並無太大關係	10000000
wait_timeout	指定一個請求的最大連線時間，對於4GB左右記憶體的伺服器可以設定為5-10。	10
thread_concurrency	該參數取值為伺服器邏輯CPU數量×2	4
innodb_log_file_size	如果對 Innodb 資料表有大量的寫入操作，那麼選擇合適的 innodb_log_file_size 值對提升MySQL效能很重要	256M
innodb_log_buffer_size	交易記錄檔寫操作緩衝區的最大長度	8M
innodb_flush_logs_at_trx_commit	1) =1時，在每個事務提交時，日誌緩衝被寫到記錄檔，對記錄檔做到磁碟操作的重新整理。Truly ACID。速度慢。 2) =2時，在每個事務提交時，日誌緩衝被寫到檔案，但不對記錄檔做到磁碟操作的重新整理。只有作業系統崩潰或掉電才會刪除最後一秒的事務，不然不會丟失事務。 3) =0時， 日誌緩衝每秒一次地被寫到記錄檔，並且對記錄檔做到磁碟操作的重新整理。任何mysqld進程的崩潰會刪除崩潰前最後一秒的事務	2
innodb_buffer_pool_size	innodb_buffer_pool_size 定義了 InnoDB 儲存引擎的表資料和索引資料的最大記憶體緩衝區大小 在專用資料庫伺服器上,可以考慮該值為實體記憶體大小的 60%-80%	1G
innodb_additional_mem_pool_size	除了緩衝表資料和索引外,可以為操作所需的其他內部項分配緩衝來提升InnoDB的效能。這些記憶體就可以通過此參數來分配。推薦此參數至少設定為2MB	2M

 

三、最佳化重點

1:max_connections

經常會遇見”MySQL: ERROR 1040: Too many connections”的情況，一種是訪問量確實很高，MySQL伺服器抗不住，這個時候就要考慮增加從伺服器分散讀壓力，另外一種情況是MySQL設定檔中max_connections值過小：

比較理想的設定是
Max_used_connections / max_connections * 100% ≈ 85%
最大串連數占上限串連數的85％左右，如果發現比例在10%以下，MySQL伺服器串連數上限設定的過高了。

2:Key_buffer_size

key_buffer_size是對MyISAM表效能影響最大的一個參數：

 

key_cache_miss_rate ＝ Key_reads / Key_read_requests * 100%

key_cache_miss_rate在0.1%以下都很好（每1000個請求有一個直接讀硬碟），如果key_cache_miss_rate在0.01%以下的話，key_buffer_size分配的過多，可以適當減少

3:暫存資料表

 

比較理想的配置是：
Created_tmp_disk_tables / Created_tmp_tables * 100% <= 25%

4:open table

 

Open_tables / Opened_tables * 100% >= 85%
Open_tables / table_cache * 100% <= 95%

5:進程使用方式

如果發現Threads_created值比較大,那麼就可以考慮把thread_cache_size的值設大一些

6:查詢快取

 

 

 

查詢快取利用率 = (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％，命中率很差，可能寫操作比較頻繁吧，而且可能有些片段。

7:檔案開啟數

 

 

比較合適的設定：Open_files / open_files_limit * 100% <= 75％

8:表鎖情況

 

Table_locks_immediate表示立即釋放表鎖數，Table_locks_waited表示需要等待的表鎖數，如果Table_locks_immediate / Table_locks_waited > 5000，最好採用InnoDB引擎，因為InnoDB是行鎖而MyISAM是表鎖，對於高並發寫入的應用InnoDB效果會好些。

9:表掃描情況

 

 

計算表掃描率：
表掃描率 ＝ Handler_read_rnd_next / Com_select
如果表掃描率超過4000，說明進行了太多表掃描，很有可能索引沒有建好，增加read_buffer_size值會有一些好處，但最好不要超過8MB。

四、Mysql指定ip使用者訪問

 

主機部分就是代表允許的主機訪問,%符號代表允許所有的主機
添加使用者授權IP命令例子:

使用myuser/mypassword從ip為61.129.51.8的主機串連到mysql伺服器:

GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'61.129.0.0' IDENTIFIED BY 'mypassword' WITH GRANT OPTION; 
FLUSH PRIVILEGES;

五、總結

在實際配置中,每一台伺服器效能是不一樣,因此Mysql最佳化配置主要參照第三部分為主,先測試回合一段時間遊戲,然後在進入Mysql去查看各個變數的值,然後根據公式去計算各個變數的值,是否在標準範圍內,不在標準範圍內的,都相應的上下調動一下.