正文
open/close table on mysql
小程序：扫一扫查出行【扫一扫了解最新限行尾号】
复制小程序
http://hidba.org/?p=170
我们知道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 that
mysqld
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。
我们知道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 that mysqld 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。
