MySQL效能最佳化之參數配置

MySQL效能最佳化之參數配置

1、目的：

通過根據伺服器目前狀況，修改MySQL的系統參數，達到合理利用伺服器現有資源，最大合理的提高MySQL效能。

2、伺服器參數：

32G記憶體、4個CPU,每個CPU 8核。

3、MySQL目前安裝狀況。

    MySQL目前安裝，用的是MySQL預設的最大支援配置。拷貝的是my-huge.cnf.編碼已修改為UTF-8.具體修改及安裝MySQL,可以參考<<Linux系統上安裝MySQL 5.5>>協助文檔  。

4、修改MySQL配置

開啟MySQL設定檔my.cnf

vi  /etc/my.cnf
 

4.1 MySQL非緩衝參數變數介紹及修改

4.1.1修改back_log參數值:由預設的50修改為500.（每個串連256kb,佔用：125M）

          back_log=500

back_log值指出在MySQL暫時停止回答新請求之前的短時間內多少個請求可以被存在堆棧中。也就是說，如果MySql的串連資料達到max_connections時，新來的請求將會被存在堆棧中，以等待某一串連釋放資源，該堆棧的數量即back_log，如果等待串連的數量超過back_log，將不被授予串連資源。將會報：unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL 的待串連進程時.

back_log值不能超過TCP/IP串連的偵聽隊列的大小。若超過則無效，查看當前系統的TCP/IP串連的偵聽隊列的大小命令：cat /proc/sys/net/ipv4/tcp_max_syn_backlog目前系統為1024。對於Linux系統推薦設定為小於512的整數。修改系統核心參數，）

查看mysql 當前系統預設back_log值，命令：

show variables like 'back_log'; 查看當前數量
 

4.1.2修改wait_timeout參數值，由預設的8小時，修改為30分鐘。(本次不用)

          wait_timeout=1800（單位為妙）

我對wait-timeout這個參數的理解：MySQL用戶端的資料庫連接閑置最大時間值。

說得比較通俗一點，就是當你的MySQL串連閑置超過一定時間後將會被強行關閉。MySQL預設的wait-timeout  值為8個小時，可以通過命令show variables like 'wait_timeout'查看結果值;。

設定這個值是非常有意義的，比如你的網站有大量的MySQL連結請求（每個MySQL串連都是要記憶體資源開銷的 ），由於你的程式的原因有大量的串連請求空閑啥事也不幹，白白佔用記憶體資源，或者導致MySQL超過最大串連數從來無法建立串連導致“Too many connections”的錯誤。在設定之前你可以查看一下你的MYSQL的狀態（可用show processlist)，如果經常發現MYSQL中有大量的Sleep進程，則需要 修改wait-timeout值了。

interactive_timeout：伺服器關閉互動式串連前等待活動的秒數。互動式用戶端定義為在mysql_real_connect()中使用CLIENT_INTERACTIVE選項的用戶端。

wait_timeout:伺服器關閉非互動串連之前等待活動的秒數。線上程啟動時，根據全域wait_timeout值或全域 interactive_timeout值初始化會話wait_timeout值，取決於用戶端類型(由mysql_real_connect()的串連選項CLIENT_INTERACTIVE定義).

這兩個參數必須配合使用。否則單獨設定wait_timeout無效

4.1.3修改max_connections參數值，由預設的151，修改為3000（750M）。

    max_connections=3000

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

MySQL伺服器允許的最大串連數16384；

查看系統當前最大串連數：

show variables like 'max_connections';

4.1..4修改max_user_connections值，由預設的0，修改為800

    max_user_connections=800

 max_user_connections是指每個資料庫使用者的最大串連

針對某一個帳號的所有用戶端並行串連到MYSQL服務的最大並行串連數。簡單說是指同一個帳號能夠同時串連到mysql服務的最大串連數。設定為0表示不限制。

目前預設值為：0不受限制。

這兒順便介紹下Max_used_connections:它是指從這次mysql服務啟動到現在，同一時刻並行串連數的最大值。它不是指當前的串連情況，而是一個比較值。如果在過去某一個時刻，MYSQL服務同時有1000個請求串連過來，而之後再也沒有出現這麼大的並發請求時，則Max_used_connections=1000.請注意與show variables 裡的max_user_connections的區別。預設為0表示無限大。

查看max_user_connections值

show variables like 'max_user_connections';

--------------------------------------分割線 --------------------------------------

Ubuntu 14.04下安裝MySQL

《MySQL權威指南(原書第2版)》清晰中文掃描版 PDF

Ubuntu 14.04 LTS 安裝 LNMP Nginx\PHP5 (PHP-FPM)\MySQL

Ubuntu 14.04下搭建MySQL主從伺服器

Ubuntu 12.04 LTS 構建高可用分布式 MySQL 叢集

Ubuntu 12.04下原始碼安裝MySQL5.6以及Python-MySQLdb

MySQL-5.5.38通用二進位安裝

--------------------------------------分割線 --------------------------------------

 

更多詳情見請繼續閱讀下一頁的精彩內容：

1 2 3 下一頁