open/close table on mysql

我們知道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）則打開的檔案描述符則較多。

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

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

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;

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%’;

| 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%’;

(4)session5：

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

session1:

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

| open_table_definitions   | 3     |

| 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%’;

| 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%’;

| opened_table_definitions | 40    |

| opened_tables            | 41    |

第四步：

session4：

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

第五步：

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

| 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%’;

| open_table_definitions   | 4     |

| open_tables              | 3     |

| opened_table_definitions | 42    |

| opened_tables            | 43    |

上面中有兩個參數：

<a href="http://dev.mysql.com/doc/refman/5.1/en/server-status-variables.html#statvar_open_table_definitions"><code>open_table_definitions</code></a>

the number of cached <code>.frm</code> files. this variable was added in mysql 5.1.3.

<a href="http://dev.mysql.com/doc/refman/5.1/en/server-status-variables.html#statvar_open_tables"><code>open_tables</code></a>

the number of tables that are open.

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

set global table_open_cache=m；

root@test 01:25:00&gt;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的檔案描述符限制來設定參數的值。

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

mysql soft nofiles 65535

mysql hard nofiles 65535。