轉：MySQL效能優化配置引數之thread_cache和table_cache詳解

luckyfriends發表於2016-12-30
MySql優化thread

http://www.jb51.net/article/51828.htm
一、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_CACHE(5.1.3及以後 版本又名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.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/14710393/viewspace-2131688/，如需轉載，請註明出處，否則將追究法律責任。

相關文章