Opened vs. Open tables

[Kanapox]

I installed the tool status.php and I noticed something strange:

Opened vs. Open tables: 579 (should be <= 80)
(table_cache)    

This is in localhost in my PC. Of course, there's no one using that forum but me.  I have installed, however, the database of the live forum, a small forum with less than 100 users and less than 1200 messages. Anyway, my question is: What's the meaning of that value? Is this a potential risk for a forum?

[Kanapox]

Er... no one?

[青山 素子]

Having a bit of patience is always appreciated.

Opened tables is the number of tables that have had to be opened since the server started. Open tables is the number of currently open tables.

In this case, there isn't enough memory to hold all the tables in memory, so they have to keep being closed and re-opened. If you do some tweaking so that MySQL can cache more in memory, it would probably speed things up a little, assuming you are unhappy with the speed currently.

Also, you really shouldn't run a public server on your workstation. The work loads are very different and make it difficult to tweak one side for best performance without hurting the other. In addition, it makes the effect of any compromise that may happen much worse (now those people can get all your personal files) and more likely as security of things is likely to be much less. It is also against the Terms of Service for most residential ISPs and can get you banned from their service.

[Kanapox]

Thanks for the answer Motoko-chan. My localhost server is only for development and testing of features, outside access is blocked by my firewall. The forum is just a part of my site. I'm aware of security risks, the production site is hosted in a professional  server, I have enough work creating contents for my site.

I installed status.php in both the production site and localhost at my PC just to see the differences, the number of that value at the production site keeps growing and growing and I thought it could be a problem in the future. Unfortunately, the company where I host my site doesn't give me access to modify the settings of MySQL, so the cache size will keep as it is now. Right now the value of Opened vs. Open tables at the production site is 3932 and two days ago was around 1200. The speed of the production site seems to be normal most of the time, so I guess it's ok.

[青山 素子]

Yeah, that particular number being high isn't the end of the world. However, when looking for speed increases, it becomes a good target for optimization.

[rsw686]

You need to evaluate the opened_tables number in relation to the uptime of the mySQL server. Increase the table_cache value until the opened_tables number increases only slowly over time or doesn't increase at all.