【MySQL】关于table cache的相关参数

一、状态值和变量：

1.1

在MySQL的show status中有2个状态值：Open_tables和Opened_tables。这2个值代表的意思如下：

Open_tables  ：代表当前打开表的数量。

Opened_tables：代表自从MySQL启动后，打开表的数量。

关于MySQL怎么打开关闭表的具体细节参考文档：<How MySQL Opens and Closes Tables>。

（1）对于myisam存储引擎，打开1张表需要2个文件描述符（一个.MYD文件，一个.MYI文件）。

（2）对于innodb存储引擎，开启表的独立表空间（innodb_file_per_table）打开1张表只需要1个文件描述符（一个.ibd文件）。

【MySQL Variable】

对于上面的状态值，对应的5.1.3版本后的MySQL变量参数为table_open_cache，而早期版本为：table_cache，该参数值的代表MySQL可以缓存的打开表时候的最大文件描述符。

1.2

在MySQL 5.1.3之后，还添加了2个状态值：Open_table_definitions和Opened_table_definitions。这2个值代表的意思如下：

Open_table_definitions  ：代表当前缓存了多少.frm文件。

Opened_table_definitions：代表自从MySQL启动后，缓存了.frm文件的数量。

需要注意的是.frm文件是MySQL用于存放表结构的文件，对应myisam和innodb存储引擎都必须有的。

【MySQL Variable】

对于上面的状态值，对应的5.1.3版本后的MySQL变量参数为table_definition_cache，该参数值的代表MySQL可以缓存的表定义的数量。和前面的table cache不同的是，表定义的缓存占用空间很小，而且不需要使用文件描述符，也就是只要打开.frm文件，缓存表定义，然后就可以关闭.frm文件。

1.3

另外，还有2个状态值：Open_files和Opened_files。这2个值的意思同上类似：

Open_files  ：代表当前打开的文件。对应存储引擎（如：innodb）使用存储引擎自己内部函数打开的话，这个值是不会增加的。

Opened_files：代表使用MySQL的my_open()函数打开过的文件数。如果不是使用这个函数打开文件的话，这个值是不会增加的。

【MySQL Variable】

对于上面的状态值，对应的MySQL变量参数为open_files_limit。

二、测试

测试的MySQL版本：

mysql> select version();

+————+

| version()  |

+————+

| 5.1.47-log |

+————+

1 row in set (0.00 sec)

2.1 myisam存储引擎

2.1.1

重启MySQL服务器，可以看到启动时候MySQL已经自动打开相关的系统表。

mysql> show global status like ‘Open%_table_definitions’;show global status like ‘Open%_tables’;show global status like ‘Open%_files’;

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

| Variable_name            | Value |

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

| Open_table_definitions   | 15    |

| Opened_table_definitions | 15    |

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

2 rows in set (0.00 sec)

+—————+——-+

| Variable_name | Value |

+—————+——-+

| Open_tables   | 8     |

| Opened_tables | 15    |

+—————+——-+

2 rows in set (0.00 sec)

+—————+——-+

| Variable_name | Value |

+—————+——-+

| Open_files    | 19    |

| Opened_files  | 62    |

+—————+——-+

2 rows in set (0.00 sec)

在上面的状态输出，可以看到Open_tables的值为8，代表了当前打开了8张表，同样的通过mysqladmin status命令也可以看到：

[mysql@xentest8-vm1 test]$ mysqladmin status

Uptime: 631  Threads: 2  Questions: 14  Slow queries: 0  Opens: 15  Flush tables: 1  Open tables: 8 Queries per second avg: 0.22

可以通过SHOW OPEN TABLES命令查看，当前的table cache到底打开了哪些表：

mysql> show open tables;

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

| Database | Table        | In_use | Name_locked |

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

| mysql    | servers      |      0 |           0 |

| mysql    | db           |      0 |           0 |

| mysql    | host         |      0 |           0 |

| mysql    | columns_priv |      0 |           0 |

| mysql    | user         |      0 |           0 |

| mysql    | procs_priv   |      0 |           0 |

| mysql    | event        |      0 |           0 |

| mysql    | tables_priv  |      0 |           0 |

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

8 rows in set (0.00 sec)

可以看到打开了mysql库里面的8张系统表（都是myisam存储引擎），另外，还可以通过lsof（lsof的用法参见<使用lsof查找打开的文件>）查看MySQL打开的对应文件：

[zhuxu@xentest8-vm1 ~]$ sudo lsof -u mysql | grep /home/mysql/mysql-5.1.47/data/mysql/

mysqld    14683 mysql   16u   REG              253,1      2048 87889757 /home/mysql/mysql-5.1.47/data/mysql/host.MYI

mysqld    14683 mysql   17u   REG              253,1         0 87889758 /home/mysql/mysql-5.1.47/data/mysql/host.MYD

mysqld    14683 mysql   18u   REG              253,1      2048 87889760 /home/mysql/mysql-5.1.47/data/mysql/user.MYI

mysqld    14683 mysql   19u   REG              253,1       380 87889761 /home/mysql/mysql-5.1.47/data/mysql/user.MYD

mysqld    14683 mysql   20u   REG              253,1      5120 87889754 /home/mysql/mysql-5.1.47/data/mysql/db.MYI

mysqld    14683 mysql   21u   REG              253,1       880 87889755 /home/mysql/mysql-5.1.47/data/mysql/db.MYD

mysqld    14683 mysql   22u   REG              253,1      4096 87889772 /home/mysql/mysql-5.1.47/data/mysql/tables_priv.MYI

mysqld    14683 mysql   23u   REG              253,1         0 87889773 /home/mysql/mysql-5.1.47/data/mysql/tables_priv.MYD

mysqld    14683 mysql   24u   REG              253,1      4096 87889775 /home/mysql/mysql-5.1.47/data/mysql/columns_priv.MYI

mysqld    14683 mysql   25u   REG              253,1         0 87889776 /home/mysql/mysql-5.1.47/data/mysql/columns_priv.MYD

mysqld    14683 mysql   26u   REG              253,1      4096 87889808 /home/mysql/mysql-5.1.47/data/mysql/procs_priv.MYI

mysqld    14683 mysql   27u   REG              253,1         0 87889809 /home/mysql/mysql-5.1.47/data/mysql/procs_priv.MYD

mysqld    14683 mysql   28u   REG              253,1      1024 87889769 /home/mysql/mysql-5.1.47/data/mysql/servers.MYI

mysqld    14683 mysql   29u   REG              253,1         0 87889770 /home/mysql/mysql-5.1.47/data/mysql/servers.MYD

mysqld    14683 mysql   30u   REG              253,1      2048 87889817 /home/mysql/mysql-5.1.47/data/mysql/event.MYI

mysqld    14683 mysql   31u   REG              253,1         0 87889818 /home/mysql/mysql-5.1.47/data/mysql/event.MYD

2.1.2

接着打开一张myisam存储引擎的表：

mysql> select count(*) from myisam_1;

+———-+

| count(*) |

+———-+

|        0 |

+———-+

1 row in set (0.00 sec)

mysql> show global status like ‘Open%_table_definitions’;show global status like ‘Open%_tables’;show global status like ‘Open%_files’;

# 可以看到Opened_table_definitions/Opened_table_definitions/Open_tables/Opened_tables的值都加1

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

| Variable_name            | Value |

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

| Open_table_definitions   | 16    |

| Opened_table_definitions | 16    |

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

2 rows in set (0.00 sec)

+—————+——-+

| Variable_name | Value |

+—————+——-+

| Open_tables   | 9     |

| Opened_tables | 16    |

+—————+——-+

2 rows in set (0.00 sec)

# 可以看到Open_files的值（19+2）加2，Opened_files的值（62+3）加3

+—————+——-+

| Variable_name | Value |

+—————+——-+

| Open_files    | 21    |

| Opened_files  | 65    |

+—————+——-+

2 rows in set (0.00 sec)

mysql> show create table myisam_1\G

*************************** 1. row ***************************

Table: myisam_1

Create Table: CREATE TABLE `myisam_1` (

`a` int(11) DEFAULT NULL,

KEY `idx_myisam_1` (`a`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

mysql> show open tables from test;

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

| Database | Table    | In_use | Name_locked |

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

| test     | myisam_1 |      0 |           0 |

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

1 row in set (0.00 sec)

[zhuxu@xentest8-vm1 ~]$ sudo lsof -u mysql | grep /home/mysql/mysql-5.1.47/data/test/

mysqld    14683 mysql  33u REG              253,1      1024 87851510 /home/mysql/mysql-5.1.47/data/test/myisam_1.MYI

mysqld    14683 mysql   34u REG              253,1         0 87851511 /home/mysql/mysql-5.1.47/data/test/myisam_1.MYD

# 可见，除了需要打开.frm文件（打开，缓存后，就关闭），对于myisam存储引擎，打开1张表需要2个文件描述符（一个.MYD文件，一个.MYI文件）

2.1.3

对应table cache中的缓存的表，可以通过flush tables命令来把它人工移出table cache:

mysql> flush tables myisam_1;

Query OK, 0 rows affected (0.00 sec)

mysql> show open tables from test;

Empty set (0.00 sec)

mysql> show global status like ‘Open%_table_definitions’;show global status like ‘Open%_tables’;show global status like ‘Open%_files’;

# 可以看到Open_table_definitions和Open_tables的值都减1

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

| Variable_name            | Value |

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

| Open_table_definitions   | 15    |

| Opened_table_definitions | 16    |

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

2 rows in set (0.00 sec)

+—————+——-+

| Variable_name | Value |

+—————+——-+

| Open_tables   | 8     |

| Opened_tables | 16    |

+—————+——-+

2 rows in set (0.00 sec)

# 可以看到Open_files的值（21-2）减2

+—————+——-+

| Variable_name | Value |

+—————+——-+

| Open_files    | 19    |

| Opened_files  | 65 |

+—————+——-+

2 rows in set (0.00 sec)

[zhuxu@xentest8-vm1 ~]$ sudo lsof -u mysql | grep /home/mysql/mysql-5.1.47/data/test/ | wc -l

0

2.2 innodb存储引擎

2.2.1

打开一张innodb存储引擎的表：

mysql> show variables like ‘innodb_file_per_table’;

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

| Variable_name         | Value |

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

| innodb_file_per_table | ON    |

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

1 row in set (0.00 sec)

mysql> select count(*) from t0001;

+———-+

| count(*) |

+———-+

|        2 |

+———-+

1 row in set (0.01 sec)

mysql> show global status like ‘Open%_table_definitions’;show global status like ‘Open%_tables’;show global status like ‘Open%_files’;

# 可以看到Opened_table_definitions/Opened_table_definitions/Open_tables/Opened_tables的值都加1

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

| Variable_name            | Value |

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

| Open_table_definitions   | 16    |

| Opened_table_definitions | 17    |

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

2 rows in set (0.00 sec)

+—————+——-+

| Variable_name | Value |

+—————+——-+

| Open_tables   | 9     |

| Opened_tables | 17    |

+—————+——-+

2 rows in set (0.00 sec)

# 可以看到Open_files的值不变（即innodb使用存储引擎自己内部函数打开的话，这个值是不会增加）

# Opened_files的值（65+1）加1（需要打开.frm文件，缓存后，就关闭）

+—————+——-+

| Variable_name | Value |

+—————+——-+

| Open_files    | 19    |

| Opened_files  | 66    |

+—————+——-+

2 rows in set (0.00 sec)

mysql> show open tables from test;

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

| Database | Table | In_use | Name_locked |

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

| test     | t0001 |      0 |           0 |

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

1 row in set (0.00 sec)

mysql> show create table t0001\G

*************************** 1. row ***************************

Table: t0001

Create Table: CREATE TABLE `t0001` (

`a` int(11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

[zhuxu@xentest8-vm1 ~]$ sudo lsof -u mysql | grep /home/mysql/mysql-5.1.47/data/test/

mysqld    14683 mysql   33uW REG              253,1     98304 87851074 /home/mysql/mysql-5.1.47/data/test/t0001.ibd

2.2.2

对于innodb存储引擎，table cache对其影响其实不是很大，innodb有自己的数据字典，可以缓存相关表信息。

其他相关资料：

（1）在<High.Performance.MySQL>中的The InnoDB Data Dictionary（P280页）中描述：

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.

The main performance issue—besides memory requirements—is opening and computing statistics for the tables, which is expensive because it requires a lot of I/O. In contrast to MyISAM, InnoDB doesn’t store statistics in the tables permanently; it recomputes them each time it starts. This operation is serialized by a global mutex in current versions of MySQL, so it can’t be done in parallel. If you have a lot of tables, your server can take hours to start and fully warm up, during which time it might not be doing much other than waiting for one I/O operation after another. We mention this to make sure you know about it, even though there’s nothing you can do to change it. It’s normally a problem only when you have many (thousands or tens of thousands) large tables, which cause the process to be I/O-bound.

If you use InnoDB’s innodb_file_per_table option (described later in “Configuring the tablespace” on page 291), there’s also a separate limit on the number of .ibd files InnoDB can keep open at any time. This is handled by the InnoDB storage engine, not the MySQL server, and is controlled by innodb_open_files. InnoDB doesn’t open files the same way MyISAM does: whereas MyISAM uses the table cache to hold file descriptors for open tables, in InnoDB there is no direct relationship between open tables and open files. InnoDB uses a single, global file descriptor for each .ibd file. If you can afford it, it’s best to set innodb_open_files large enough that the server can keep all .ibd files open simultaneously.

（2）<How innodb_open_files affects performance>

（3）<SHOW OPEN TABLES – what is in your table cache>里面有一句描述：

Note however if you’re starting MySQL Command line client without “-A” option it opens all tables in the active database to allow tab completion which can screw results.

在实际的测试中，对于5.0.67-log版本会出现上面的情况，对于5.1版本的测试，上面的现象是不存在。

–EOF–