Wondering about opened_tables!

I've some experience with Mysql DBA, however dare to add an expert tag to myself yet. To be honest, I've had lots of doubts about Mysql variables and status variables in the past and could clear most of them through extensive testing and some of them through some great websites. However, there have been a couple of开发者_开发技巧 them I wasn't really convinced with my understanding and one such item is Mysql's status variable: Opened_tables

There is one more status variable named Open_tables that's very much related.

Open_tables - number of tables that are open at the moment

Opened_tables - number of tables that have been opened since startup

Let's come to my questions:

Question #1: Eventhough Mysql states Open_tables show number of "tables" that are open at the moment, I've read in the past that it's not actually the number of tables opened, but the number of table file descriptors. It's said that if multiple threads try to open the same table simultaneously, multiple file descriptors are created. I've noticed myself that in some circumstances Open_tables was > "total number of tables present on the server", so that seem to justify the above claim. I've also read that tmp_tables also get added into this which seem to be incorrect from my experience. Can someone confirm this?

And then, I've a Mysql server that has got around 965 tables (MyISAM - 712 & InnoDB - 253) and I've set table_cache to 1536. However, as soon as I start the Mysql service (within a couple of seconds), I notice this:

| Open_tables | 6 |
| Opened_tables | 12 |

And that difference (here it's 6) remains like that for some time:

| Open_tables | 133 |
| Opened_tables | 139 |

But some time later, the difference increases (here, it's 12):

| Open_tables | 134 |
| Opened_tables | 146 |

Question #2: So can someone tell me how that difference occurs?

Is it because a) Mysql closed 12 tables in between? If so, why did it close those tables instead of keeping them in the cache? b) Mysql adds the count of something else (other than opened tables) into the opened_tables variable?

Any response is much appreciated!

In my understanding, Opened_tables shows how many tables have been opened above and beyond the number held in table_open_cache. This is a cumulative amount for the lifetime of a MySQL instance, so if your table_open_cache is too low you'll see this value steadily increase, but if it never gets exceeded then you could conceivably have Opened_tables always at 0.

1. may be it's using system tables from information_schema, if you restart mysql, and do nothing ,the Open_tables > 0 but the Opened_tables = 0

2. i had try to create a temporary table and execute select clause on it, the open_tables status not changes

   try :

   mysql> flush tables;
   mysql> show status like '%Open%';
   +--------------------------+-------+
   | Variable_name            | Value |
   +--------------------------+-------+
   | Com_ha_open              | 0     |
   | Com_show_open_tables     | 0     |
   | Open_files               | 4     |
   | Open_streams             | 0     |
   | Open_table_definitions   | 0     |
   | Open_tables              | 0     |
   | Opened_files             | 68    |
   | Opened_table_definitions | 2     |
   | Opened_tables            | 2     |
   | Slave_open_temp_tables   | 0     |
   +--------------------------+-------+
   10 rows in set (0.00 sec)
   
   
   mysql> create temporary table demo(id int);
   mysql> flush tables;
   mysql> select * from t5;
   Empty set (0.00 sec)
   
   mysql> show status like '%Open%';
   +--------------------------+-------+
   | Variable_name            | Value |
   +--------------------------+-------+
   | Com_ha_open              | 0     |
   | Com_show_open_tables     | 0     |
   | Open_files               | 4     |
   | Open_streams             | 0     |
   | Open_table_definitions   | 0     |
   | Open_tables              | 0     |
   | Opened_files             | 68    |
   | Opened_table_definitions | 2     |
   | Opened_tables            | 2     |
   | Slave_open_temp_tables   | 0     |
   +--------------------------+-------+
   10 rows in set (0.00 sec)
   

   you can see that, the Open_tables not change

3. your table_open_cache is not big enough or you have do some operation like FLUSH TABLES; from mysql manual:

   table_open_cache is related to max_connections. For example, for 200 concurrent running connections, you should have a table cache size of at least 200 * N, where N is the maximum number of tables per join in any of the queries which you execute. You must also reserve some extra file descriptors for temporary tables and files.