Wondering about opened_tables!

Question

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!

Answer 1

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.