Home > database > open/close table on mysql

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)则打开的文件描述符则较多。

知道了上面的知识后,来看下面的参数:

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。

Categories: database Tags:
  1. No comments yet.
  1. No trackbacks yet.
You must be logged in to post a comment.