mysql配置以及效能最佳化

標籤：run   ack   並發   sort   addition   理解   lin   安全   隊列大小   

MySQL設定檔my.cnf中文詳解，附mysql效能最佳化方法分享

=================================================================================================

Mysql參數最佳化對於新手來講，是比較難懂的東西，其實這個參數最佳化，是個很複雜的東西，對於不同的網站，及其線上量，訪問量，貼文量，網路情況，以及機器硬體設定都有關係，最佳化不可能一次性完成，需要不斷的觀察以及調試，才有可能得到最佳效果。

下面先說我的伺服器的硬體以及論壇情況，
CPU: 2顆四核IntelXeon 2.00GHz
記憶體: 4GB DDR
硬碟: SCSI 146GB
論壇：線上會員 一般在5000 人左右 – 最高記錄是 13264.
下面，我們根據以上硬體設定結合一份已經做過一次最佳化的my.cnf進行分析說明：有些參數可能還得根據論壇的變化情況以及程式員的程式進行再調整。
[mysqld]
port = 3306
serverid = 1
socket = /tmp/mysql.sock
skip-locking # 避免MySQL的外部鎖定，減少出錯幾率增強穩定性。

 skip-name-resolve

禁止MySQL對外部串連進行DNS解析，使用這一選項可以消除MySQL進行DNS解析的時間。但需要注意，如果開啟該選項，則所有遠程主機串連授權都要使用IP地址方式，否則MySQL將無法正常處理串連請求!
back_log = 500
要求 MySQL 能有的串連數量。當主要MySQL線程在一個很短時間內得到非常多的串連請求，這就起作用，然後主線程花些時間(儘管很短)檢查串連並且啟動一個新線程。
back_log值指出在MySQL暫時停止回答新請求之前的短時間內多少個請求可以被存在堆棧中。只有如果期望在一個短時間內有很多串連，你需要增加它，換句話說，這值對到來的TCP/IP串連的偵聽隊列的大小。你的作業系統在這個隊列大小上有它自己的限制。試圖設定back_log高於你的作業系統的限制將是無效的。當你觀察你的主機處理序列表，發現大量 264084 | unauthenticated user |xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL 的待串連進程時，就要加大 back_log 的值了。預設數值是50，我把它改為500。
key_buffer_size= 384M
# key_buffer_size指定用於索引的緩衝區大小，增加它可得到更好處理的索引(對所有讀和多重寫)，到你能負擔得起那樣多。如果你使它太大，系統將開始換頁並且真的變慢了。對於記憶體在4GB左右的伺服器該參數可設定為384M或512M。通過檢查狀態值Key_read_requests和 Key_reads,可以知道key_buffer_size設定是否合理。比例key_reads / key_read_requests應該儘可能的低，至少是1:100，1:1000更好(上述狀態值可以使用SHOW STATUS LIKE ‘key_read%‘獲得)。注意：該參數值設定的過大反而會是伺服器整體效率降低!
max_allowed_packet= 32M
增加該變數的值十分安全，這是因為僅當需要時才會分配額外記憶體。例如，僅當你發出長查詢或mysqld必須返回大的結果行時mysqld才會分配更多記憶體。該變數之所以取較小預設值是一種預防措施，以捕獲用戶端和伺服器之間的錯誤資訊包，並確保不會因偶然使用大的資訊包而導致記憶體溢出。
table_cache =512（5.1之後叫做table_open_cache）
table_cache指定表快取的大小。每當MySQL訪問一個表時，如果在表緩衝區中還有空間，該表就被開啟並放入其中，這樣可以更快地訪問表內容。通過檢查峰值時間的狀態值Open_tables和Opened_tables，可以決定是否需要增加table_cache的值。如果你發現 open_tables等於table_cache，並且opened_tables在不斷增長，那麼你就需要增加table_cache的值了(上述狀態值可以使用SHOW STATUS LIKE ‘Open%tables‘獲得)。注意，不能盲目地把table_cache設定成很大的值。如果設定得太高，可能會造成檔案描述符不足，從而造成效能不穩定或者串連失敗。
sort_buffer_size= 4M
查詢排序時所能使用的緩衝區大小。注意：該參數對應的分配記憶體是每串連獨佔!如果有100個串連，那麼實際分配的總共排序緩衝區大小為100 × 4 = 400MB。所以，對於記憶體在4GB左右的伺服器推薦設定為4-8M。
read_buffer_size= 4M
讀查詢操作所能使用的緩衝區大小。和sort_buffer_size一樣，該參數對應的分配記憶體也是每串連獨享!
join_buffer_size= 8M
聯集查詢操作所能使用的緩衝區大小，和sort_buffer_size一樣，該參數對應的分配記憶體也是每串連獨享!
myisam_sort_buffer_size= 64M
MyISAM表發生變化時重新排序所需的緩衝
query_cache_size= 64M
指定MySQL查詢緩衝區的大小。可以通過在MySQL控制台執行以下命令觀察：
# > SHOWVARIABLES LIKE ‘%query_cache%‘; # > SHOW STATUS LIKE ‘Qcache%‘; # 如果Qcache_lowmem_prunes的值非常大，則表明經常出現緩衝不夠的情況;
如果Qcache_hits的值非常大，則表明查詢緩衝使用非常頻繁，如果該值較小反而會影響效率，那麼可以考慮不用查詢緩衝;Qcache_free_blocks，如果該值非常大，則表明緩衝區中片段很多。
thread_cache_size= 64
可以複用的儲存在中的線程的數量。如果有，新的線程從緩衝中取得，當中斷連線的時候如果有空間，客戶的線置在緩衝中。如果有很多新的線程，為了提高效能可以提高這個變數值。通過比較 Connections 和 Threads_created 狀態的變數，可以看到這個變數的作用
tmp_table_size =256M
max_connections= 1000
指定MySQL允許的最大串連進程數。如果在訪問論壇時經常出現Too Many Connections的錯誤提示，則需要增大該參數值。
max_connect_errors= 10000000
對於同一主機，如果有超出該參數值個數的中斷錯誤串連，則該主機將被禁止串連。如需對該主機進行解禁，執行：FLUSH HOST;。
wait_timeout =10
指定一個請求的最大連線時間，對於4GB左右記憶體的伺服器可以設定為5-10。
thread_concurrency =8
該參數取值為伺服器邏輯CPU數量×2，在本例中，伺服器有2顆物理CPU，而每顆物理CPU又支援H.T超執行緒，所以實際取值為4 × 2 = 8
skip-networking
開啟該選項可以徹底關閉MySQL的TCP/IP串連方式，如果WEB伺服器是以遠端連線的方式訪問MySQL資料庫伺服器則不要開啟該選項!否則將無法正常串連!
long_query_time= 10
log-slow-queries=
log-queries-not-using-indexes
開啟慢查詢日誌( slow query log )
慢查詢日誌對於跟蹤有問題的查詢非常有用。它記錄所有超過過long_query_time的查詢，如果需要，還可以記錄不使用索引的記錄。下面是一個慢查詢日誌的例子：
開啟慢查詢日誌，需要設定參數log_slow_queries、long_query_times、log-queries-not-using-indexes。
log_slow_queries指定記錄檔，如果不提供檔案名稱，MySQL將自己產生預設檔案名稱。long_query_times指定慢查詢的閾值，預設是10秒。log-queries-not-using-indexes是4.1.0以後引入的參數，它指示記錄不使用索引的查詢。設定 long_query_time=10
外附上使用show status命令查看mysql狀態相關的值及其含義：
使用show status命令
含義如下:
aborted_clients 用戶端非法中斷串連次數
aborted_connects串連mysql失敗次數
com_xxx xxx命令執行次數,有很多條
connections 串連mysql的數量
Created_tmp_disk_tables在磁碟上建立的暫存資料表
Created_tmp_tables在記憶體裡建立的暫存資料表
Created_tmp_files臨時檔案數
Key_read_requestsThe number of requests to read a key block from the cache
Key_reads Thenumber of physical reads of a key block from disk
Max_used_connections同時使用的串連數
Open_tables 開放的表
Open_files 開放的檔案
Opened_tables 開啟的表
Questions 提交到server的查詢數
Sort_merge_passes如果這個值很大,應該增加my.cnf中的sort_buffer值
Uptime 伺服器已經工作的秒數
提升效能的建議:
1.如果opened_tables太大,應該把my.cnf中的table_cache變大

2.如果Key_reads太大,則應該把my.cnf中key_buffer_size變大.可以用Key_reads/Key_read_requests計算出cache失敗率

3.如果Handler_read_rnd太大,則你寫的SQL語句裡很多查詢都是要掃描整個表,而沒有發揮索引的鍵的作用

4.如果Threads_created太大,就要增加my.cnf中thread_cache_size的值.可以用Threads_created/Connections計算cache命中率

5.如果Created_tmp_disk_tables太大,就要增加my.cnf中tmp_table_size的值,用基於記憶體的暫存資料表代替基於磁碟的

==========================================================================================================
儲存引擎是什嗎？MySQL中的資料用各種不同的技術儲存在檔案(或者內 正確的編譯方法固然重要，但它只是提高MySQL伺服器效能工作的一部分。MySQL伺服器的許多參數會影響伺服器的效能表現，而且我們可以把這些參數儲存到設定檔，使得每次MySQL伺服器啟動時這些參數都自動發揮作用。這個設定檔就是my.cnf。
MySQL伺服器提供了my.cnf檔案的幾個樣本，它們可以在/usr/local/mysql/share/mysql/目錄下找到，名字分別為 my-small.cnf、my-medium.cnf、my-large.cnf以及my-huge.cnf。檔案名稱字中關於規模的說明描述了該設定檔適用的系統類別型。例如，如果運行MySQL伺服器的系統記憶體不多，而且MySQL只是偶爾使用，那麼使用my-small.cnf設定檔最為理想，這個設定檔告訴mysqld daemon使用最少的系統資源。反之，如果MySQL伺服器用於支援一個大規模的線上商場，系統擁有2G的記憶體，那麼使用mysql-huge.cnf 最為合適。
要使用上述樣本設定檔，我們應該先複製一個最適合要求的設定檔，並把它命名為my.cnf。這個複製得到的設定檔可以按照如下三種方式使用：
全域：把這個my.cnf檔案複製到伺服器的/etc目錄，此時檔案中所定義的參數將全域有效，即對該伺服器上啟動並執行所有MySQL資料庫伺服器都有效。
局部：把這個my.cnf檔案複製到[MYSQL-INSTALL-DIR]/var/將使該檔案只對指定的伺服器有效，其中[MYSQL-INSTALL-DIR]表示安裝MySQL的目錄。
使用者：最後，我們還可以把該檔案的作用範圍局限到指定的使用者，這隻需把my.cnf檔案複製到使用者的根目錄即可。
那麼，如何設定my.cnf檔案中的參數呢？或者進一步說，哪些參數是我們可以設定的呢？所有這些參數都對MySQL伺服器有著全域性的影響，但同時每一個參數都和MySQL的特定部分關係較為密切。例如，max_connections參數屬於mysqld一類。那麼，如何才能得知這一點呢？這隻需執行如下命令：

% >/usr/local/mysql/libexec/mysqld–help
該命令將顯示出和mysqld有關的各種選項和參數。要尋找這些參數非常方便，因為這些參數都在“Possiblevariables for option –set-variable (-O) are”這行內容的後面。找到這些參數之後，我們就可以在my.cnf檔案中按照如下方式設定所有這些參數：

set-variable = max_connections=100

這行代碼的效果是：同時串連MySQL伺服器的最大串連數量限制為100。不要忘了在my.cnf檔案[mysqld]小節加上一個set-variable指令，具體請參見設定檔中的樣本。

MySQL的max_connections參數用來設定最大串連（使用者）數。每個串連MySQL的使用者均算作一個串連，max_connections的預設值為100。本文將講解此參數的詳細作用與效能影響。

[max_connections]

=================================================================================================

MySQL無論如何都會保留一個用於管理員（SUPER）登陸的串連，用於管理員串連資料庫進行維護操作，即使當前串連數已經達到了max_connections。因此MySQL的實際最大可串連數為max_connections+1；
這個參數實際起作用的最大值（實際最大可串連數）為16384，即該參數最大值不能超過16384，即使超過也以16384為準；
增加max_connections參數的值，不會佔用太多系統資源。系統資源（CPU、記憶體）的佔用主要取決於查詢的密度、效率等；
該參數設定過小的最明顯特徵是出現”Too many connections”錯誤；

我們先來看下如何查看當前mysql的max_connections的值：

如下sql

複製代碼代碼如下:
show variables like "max_connections";

 

顯示的結果如下格式

+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 100   |
+-----------------+-------+

可以通過下面的sql語句將max_connections的值設定為200，當然前提是當前登入的使用者有足夠的許可權：

set global max_connections = 200;

這個設定會馬上生效，但是當mysql重啟時這個設定會失效，更好的辦法是修改mysql的ini設定檔my.ini

找到mysqld塊，修改或者添加下面的設定：

max_connections=200

這樣修改之後，即便重啟mysql也會預設載入這個配置了

 不過為了安全期間，建議大家直接到my.ini裡修改，麼有可以加上。

調整max_connections參數的值

調整此參數的方法有幾種，既可以在編譯的時候設定，也可以在MySQL設定檔 my.cnf 中設定，也可以直接使用命令調整並立即生效。

1、在編譯的時候設定預設最大串連數

開啟MySQL的源碼，進入sql目錄，修改mysqld.cc檔案：

複製代碼代碼如下:
{"max_connections", OPT_MAX_CONNECTIONS,
"The number of simultaneous clients allowed.", (gptr*) &max_connections,
(gptr*) &max_connections, 0, GET_ULONG, REQUIRED_ARG, 100, 1, 16384, 0, 1,
0},

紅色的”100″即為該參數的預設值，修改為想要的數值，存檔退出。然後執行

複製代碼代碼如下:
./configure;make;make install

重新編譯安裝MySQL；注意，由於編譯安裝且修改了MySQL源碼，此操作最好在安裝MySQL之前進行；

 

2、在設定檔my.cnf中設定max_connections的值

開啟MySQL設定檔my.cnf

複製代碼代碼如下:
[[email protected] ~]# vi /etc/my.cnf

找到max_connections一行，修改為（如果沒有，則自己添加），

複製代碼代碼如下:
max_connections = 1000

上面的1000即該參數的值。

 

3、即時（臨時）修改此參數的值

首先登陸mysql，執行如下命令：

複製代碼代碼如下:
[[email protected] ~]# mysql -uroot -p

然後輸入MySQL Root的密碼。

 

查看當前的Max_connections參數值：

複製代碼代碼如下:
mysql> SELECT @@MAX_CONNECTIONS AS ‘Max Connections‘;

設定該參數的值：

複製代碼代碼如下:
mysql> set GLOBAL max_connections=1000;

（注意上面命令的大小寫）

 

修改完成後即時生效，無需重啟MySQL。

總體來說，該參數在伺服器資源夠用的情況下應該盡量設定大，以滿足多個用戶端同時串連的需求。否則將會出現類似”Too many connections”的錯誤。

 

【THREAD_CACHE】

=================================================================================================

MySQL裡面為了提高用戶端請求建立串連過程的效能,提供了一個串連池也就是 Thread_Cache池,將閒置連接線程放在串連池中,而不是立即銷毀.這樣的好處就是,當又有一個新的請求的時候,mysql不會立即去建立串連 線程,而是先去Thread_Cache中去尋找閒置連接線程,如果存在則直接使用,不存在才建立新的連接線程.

有關Thread_Cache在MySQL有幾個重要的參數,簡單介紹如下:

thread_cache_size

Thread_Cache 中存放的最大連接線程數.在短連線應用程式中Thread_Cache的功效非常明顯,因為在應用中資料庫的串連和建立是非常頻繁的,如果不使用 Thread_Cache那麼消耗的資源是非常可觀的!在長串連中雖然帶來的改善沒有短串連的那麼明顯,但是好處是顯而易見的.但並不是越大越好大了反而 浪費資源這個的確定一般認為和實體記憶體有一定關係,如下:

複製代碼代碼如下:
1G —> 8
2G —> 16
3G —> 32
>3G —> 64

如果短串連多的話可以適當加大.

 

thread_stack

每個串連被建立的時候,mysql分配給它的記憶體.這個值一般認為預設就可以應用於大部分情境了,除非必要非則不要動它.

thread_handing

運用Thread_Cache處理串連的方式,5.1.19添加的新特性.有兩個值可選[no-threads|one-thread-per-connection] 看字面意思大家也該猜出八九分了,呵呵,no-threads 伺服器使用一個線程,one-thread-per-connection 伺服器為每個用戶端請求使用一個線程.原手冊中提到,no-threads是在Linux下調試用的.

複製代碼代碼如下:
mysql> show variables like ‘thread%‘;
+——————-+—————————+
| Variable_name     | Value                     |
+——————-+—————————+
| thread_cache_size | 32                        |
| thread_handling   | one-thread-per-connection |
| thread_stack      | 196608                    |
+——————-+—————————+
3 rows in set (0.01 sec)

 

mysql> show status like ‘%connections%‘;
+———————-+——–+
| Variable_name        | Value  |
+———————-+——–+
| Connections          | 199156 |
| Max_used_connections | 31     |
+———————-+——–+
2 rows in set (0.00 sec)

mysql> show status like ‘%thread%‘;
+————————+——–+
| Variable_name          | Value  |
+————————+——–+
| Delayed_insert_threads | 0      |
| Slow_launch_threads    | 0      |
| Threads_cached         | 3      |
| Threads_connected      | 6      |
| Threads_created        | 8689   |
| Threads_running        | 5      |
+————————+——–+
6 rows in set (0.00 sec)

通過以上3個命令,可以看到伺服器的 thread_cache池中最多可以存放32個連接線程,為每個用戶端球使用一個線程.為每個串連的線程分配192k的記憶體空間.

 

服 務器總共有199156次串連,最大並發串連數為31,當前在thread_cashe池中的串連數為3個,串連數為6個,處於活躍狀態的有5個,共建立 了8689次串連.顯然這裡以短串連為主.可以算出thread_cache命中率,公式為:

 

複製代碼代碼如下:
Thread_Cache_Hit=(Connections-Thread_created)/Connections*100%

 

當前伺服器的Thread_cache命中率約為95.6%這個結果我還是比較滿意的.但是可以看出 thread_cache_size有點多餘改成16或8更合理一些.

 

 

【TABLE_OPEN_CACHE】

==========================================================================================================

由於MySQL是多線程的機制,為了提高效能,每個線程都是獨自開啟自己需要的表的檔案描 述符,而不是通過共用已經開啟的.針對不同儲存引擎處理的方法當然也不一樣.

在myisam表引擎中,資料檔案的描述符 (descriptor)是不共用的,但是索引檔案的描述符卻是所有線程共用的.Innodb中和使用資料表空間類型有關,假如是共用資料表空間那麼實際就一個數 據檔案,當然佔用的資料檔案描述符就會比獨立資料表空間少.

個人感覺有點像php裡面的fopen開啟一個串連,操作完資料之後,並不立即 關閉,而是緩衝起來,等待下一個串連這個檔案的請求就不必去重新開啟檔案了,不知樣理解對不對,哈.

手冊上有段關於開啟表時的描述:

複製代碼代碼如下:
A MyISAM table is opened for each concurrent access. This means the table needs to be opened twice if two threads access the same table or if a thread accesses the table twice in the same query (for example, by joining the table to itself). Each concurrent open requires an entry in the table cache. The first open of any MyISAM table takes two file descriptors: one for the data file and one for the index file. Each additional use of the table takes only one file descriptor for the data file. The index file descriptor is shared among all threads.

如果你正用 HANDLER tbl_name OPEN語句開啟一個表，將為該線程專門分配一個表。該表不被其它線程共用，只有線程調用HANDLER tbl_name CLOSE或線程終止後才被關閉。表關閉後，被拉回表緩衝中(如果緩衝不滿)。

 

mysql手冊上給的建議大小 是:table_cache=max_connections*n

n表示查詢語句中最大表數, 還需要為暫存資料表和檔案保留一些額外的檔案描述符。

這個資料遭到很多質疑,table_cache夠用就好,檢查 Opened_tables值,如果這個值很大,或增長很快那麼你就得考慮加大table_cache了.

在下面的條件下，未使用的表 將被關閉並從表緩衝中移出：

當緩衝滿了並且一個線程試圖開啟一個不在緩衝中的表時。

當緩衝包含超過table_cache個條目，並且緩衝中的表不再被任何線程使用。

當表重新整理操作發生。當執行FLUSH TABLES語句或執行mysqladmin flush-tables或mysqladmin refresh命令時會發生。

當表緩衝滿時，伺服器使用下列過程找到一個緩衝入口來使用：

當前未使用的表被釋放，以最近最少使用順序。

如果緩衝滿了並且沒有表可以釋放，但是一個新表需要開啟，緩衝必須臨時被擴大。

如果緩衝處於一個臨時擴大狀態並且一個表從在用變為不在用狀態，它被關閉並從緩衝中釋放。

幾個關於table_cache的 狀態值:

1. table_cache：所有線程開啟的表的數目。增大該值可以增加mysqld需要的檔案描述符的數量。預設值是64.

2. open_tables：當前開啟的表的數量.

3. opened_tables ：Number of table cache misses，如果opened_tables較大，table_cache 值可能太小.

4. Open_table_definitions : The number of cached .frm files. This variable was added in MySQL 5.1.3.

5. Opened_table_definitions : The number of .frm files that have been cached. This variable was added in MySQL 5.1.24.

 

mysql配置以及效能最佳化