title: Wondering about opened_tables!
body: <p>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</p>
  <p>There is one more status variable named Open_tables that's very much related.</p>
  <blockquote>
  <p>Open_tables - number of tables that are open at the moment<br />
  Opened_tables - number of tables that have been opened since startup</p>
  </blockquote>
  <p>Let's come to my questions:</p>
  <blockquote>
  <p>Question #1: Eventhough Mysql states
  Open_tables show number of &quot;tables&quot;
  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 &gt; &quot;total
  number of tables present on the
  server&quot;, 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?</p>
  </blockquote>
  <p>And then, I've a Mysql server that has got around 965 tables (MyISAM - 712 &amp; 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:</p>
  <pre><code>| Open_tables | 6 |
  | Opened_tables | 12 |
  </code></pre>
  <p>And that difference (here it's 6) remains like that for some time:</p>
  <pre><code>| Open_tables | 133 |
  | Opened_tables | 139 |
  </code></pre>
  <p>But some time later, the difference increases (here, it's 12):</p>
  <pre><code>| Open_tables | 134 |
  | Opened_tables | 146 |
  </code></pre>
  <blockquote>
  <p>Question #2: So can someone tell me
  how that difference occurs?</p>
  <p>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?</p>
  </blockquote>
  <p>Any response is much appreciated!</p>