open/closetableonmysql

我們知道mysql是一個支援多執行緒的資料庫，尤其在innodb儲存引擎出現後，對mysql的事務，併發，鎖支援得到了極大提高。在高併發的訪問的應用場景中，應用端大量併發的程式發問資料庫，而資料庫中的資料表在磁碟上以資料檔案存放，在unix，linux的系統呼叫中，是依賴於檔案描述符的。不同的os對檔案描述符的限制不同（非Unix/linux 作業系統無檔案描述符概念，在windows中稱作檔案控制程式碼），如在linux中/etc/security/limits.conf配置檔案中設定他們的檔案描述符極限。

在瞭解mysql開啟表的過程前，需要了解一些知識：

table cache：對於不同的儲存引擎，table cache的作用是不同的，對於MyISAM表，每一個客戶端執行緒開啟任何一個MyISAM表的資料檔案都需要開啟一個檔案描述符，但如果是索引檔案，則可以多個執行緒共享同一個索引檔案的描述符，table cache的主要作用應該用於快取檔案描述符，當有新的請求時不需要重新的開啟，使用結束時也不用立即關閉。

對於InnoDB的儲存引擎來說，開啟表的方式與myisam是不同：

Unlike MyISAM Innodb does not have to keep open file descriptor when table is open – open table is purely logical state and appropriate .ibd file may be open or closed，InnoDB uses a single, global file descriptor for each .ibd file.

InnoDB has its own per-table cache, variously called a table definition cache or data dictionary, which you cannot configure.When InnoDB opens a table, it adds a corresponding object to the data dictionary. Each table can take up 4 KB or more of memory(although much less space is required in MySQL 5.1). Tables are not removed from the data dictionary when they are closed.

在引擎上，innodb把table cache 叫做了資料字典，表的定義都快取在資料字典中（data dictionary），檔案描述符上使用一個global file descriptor來處理每個ibd檔案，如果使用的是共享表空間來儲存資料，則開啟的檔案描述符就比較少，但如果使用的是獨享表空間方式（innodb_file_per_table=1）則開啟的檔案描述符則較多。

知道了上面的知識後，來看下面的引數：

Table_cache：在MySQL 5.1.3版本中為table_open_cache，其預設值為64，官方文件中對該引數的解釋為：

The number of open tables for all threads. Increasing this value increases the number of file descriptors thatmysqld requires.

所有threads開啟表的數量，增加這個引數需要在mysqld啟動的時候增加檔案描述符；

第一個問題：mysql是如何開啟和關閉表的？

在官方文件中描述的很清晰了：

MySQL is multi-threaded, so there may be many clients issuing queries for a given table simultaneously. To minimize the problem with multiple client sessions having different states on the same table, the table is opened independently by each concurrent session

Mysql在訪問一張表的時候，將其放入到cache中，如果資料庫中有許多的表，通常將其放入到cache中，對效能的提升帶來幫助。

那麼在不斷的新表開啟中，cache被慢慢填滿（table_open_cache—-full），如果新開啟的表沒有在cache中，mysql會將一些沒有使用的table清除掉：

(1)Session 1

root@test 10:56:22>set global table_open_cache=2;

Query OK, 0 rows affected (0.00 sec)

root@test 11:07:50>flush tables;

Query OK, 0 rows affected (0.00 sec)

root@test 11:08:58>show global status like ‘open%table%’;

+————————–+——-+

| Variable_name            | Value |

+————————–+——-+

| Open_table_definitions   | 0     |

| Open_tables              | 0     |

| Opened_table_definitions | 28    |

| Opened_tables            | 28    |

(2)Sessioin 2：

root@test 10:56:03>select * from t1;

session 3：

root@test 10:56:03>select * from t2;

session 1：

root@test 11:09:17>show global status like ‘open%table%’;

+————————–+——-+

| Variable_name            | Value |

+————————–+——-+

| Open_table_definitions   | 2     |

| Open_tables              | 2     |

| Opened_table_definitions | 30    |

| Opened_tables            | 30    |

(3)Session 4：

root@test 10:52:22>select * from t1;

Session1：

root@test 11:11:08>show global status like ‘open%table%’;

+————————–+——-+

| Variable_name            | Value |

+————————–+——-+

| Open_table_definitions   | 2     |

| Open_tables              | 2     |

| Opened_table_definitions | 30    |

| Opened_tables            | 30    |

(4)Session5：

root@test 10:52:39>select * from test_1;

Session1:

root@test 11:13:03>show global status like ‘open%table%’;

+————————–+——-+

| Variable_name            | Value |

+————————–+——-+

| Open_table_definitions   | 3     |

| Open_tables              | 2     |

| Opened_table_definitions | 31    |

| Opened_tables            | 31    |

我們可以看到，第一步：session1： 開始cache中執行 flush tables後，open_tables為0，Open_table_definitions 為0；

第二步：Session2,3：執行兩個表的查詢，session1中查詢open_tables， Open_table_definitions 為2；

第三步：session 4:執行session2的查詢，session1中查詢open_tables， Open_table_definitions 沒有變，保持2；

第四步：session5：執行新的查詢，session中查詢open_tables為2，Open_table_definitions為3；

從實驗上看是滿足上述的情況的。

如果沒有table能夠被釋放，cache將會根據需要臨時擴充套件，當有table關閉或者unused，cache將會被釋放：

第一步：Session1： root@test 11:26:58>flush tables;

root@test 11:33:35>show global status like ‘open%table%’;

+————————–+——-+

| Variable_name            | Value |

+————————–+——-+

| Open_table_definitions   | 0     |

| Open_tables              | 0     |

| Opened_table_definitions | 38    |

| Opened_tables            | 39    |

第二步：

Session2：

root@test 11:10:43>HANDLER t1 open;
session3

root@test 11:10:46>HANDLER t2 open;

第三步：session1

root@test 11:33:41>show global status like ‘open%table%’;

+————————–+——-+

| Variable_name            | Value |

+————————–+——-+

| Open_table_definitions   | 2     |

| Open_tables              | 2     |

| Opened_table_definitions | 40    |

| Opened_tables            | 41    |

第四步：

session4：

root@test 11:10:49>select * from t3;

第五步：

Session1：

root@test 11:34:06>show global status like ‘open%table%’;

+————————–+——-+

| Variable_name            | Value |

+————————–+——-+

| Open_table_definitions   | 3     |

| Open_tables              | 2     |

| Opened_table_definitions | 41    |

| Opened_tables            | 42    |

第六步：

Session5： root@test 11:29:59>HANDLER test_1 open;

第七步：

root@test 11:34:19>show global status like ‘open%table%’;

+————————–+——-+

| Variable_name            | Value |

+————————–+——-+

| Open_table_definitions   | 4     |

| Open_tables              | 3     |

| Opened_table_definitions | 42    |

| Opened_tables            | 43    |

上面中有兩個引數：

Open_table_definitions

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

Open_tables

The number of tables that are open.

我們看到在使用HANDLER open開啟表的時候，該table不會被mysql清出cache，當cache被填滿後，在使用HANDLER open，cache將會被擴充套件；直到使用handler close關閉後釋放。

那table_open_cache設為多少值合理，是不是越大table_open_cache引數設定會帶來效能的上的線性提升？當我們的資料庫中上千數量的表的時候，查詢中有涉及複雜的多表連線，並且同時有多個connection連到mysql中執行這些query，那麼就可能很快用完檔案描述符cache（table_open_cache），mysql使用LRU演算法，把最近最少使用的描述符關閉掉，用於存放新的描述符。但是在查詢要關閉的描述符中，查詢時間會隨著cache中的快取數量增加而增加（O(n),n為cache的items數量），檔案開啟的時間等於檔案關閉的時間，從而導致了效能上的下降。

在官方文件中對table_open_cache引數的設定限於對os的檔案描述符的上，當然還一些相應記憶體開銷，通常在設定table_open_cache引數的時候，我們會在業務的高峰時期，檢查open_Tables的值，如果open_Tables的值與table_open_cache的值相等，並且opened_tales的值在不斷的增加，這個時候就需要對table_open_cache的值增加了；

set global table_open_cache=M；

root@test 01:25:00>show global status like ‘open%tables’;

+—————+———+

| Variable_name | Value   |

+—————+———+

| Open_tables   | 56       |

| Opened_tables | 2139150 |

第二個問題：os檔案描述符對該引數的限制，

當我們在調整table_open_cache的時候，還需要考慮一個引數就是os的檔案描述符，如果table_open_cache引數設定的很大，mysql有可能用完檔案描述符，導致mysql拒絕其他連線請求，這時候就需要根據os的檔案描述符限制來設定引數的值。

調整檔案描述符：open_files_limit和open-files-limit這兩個引數：

官方文件中告訴我們可以通過再mysqld_safe啟動中加入open-files-limit引數，mysqld_safe –open-files-limit=N,來改變open_files_limit值；

在配置檔案中，我們也可以看到open_files_limit引數，但是如果你設定該引數，重啟主機後，該引數的值還是以系統的檔案描述符為準，所以在安裝mysql的時候，在/etc/security/limits.conf配置好mysql使用者對最大開啟檔案數的限制，建議設定到最大：

Mysql soft nofiles 65535

Mysql hard nofiles 65535。