I am running into something weird with our server (Redhat Linux ES, MySQL 4.0.20, Apache 2.0, Dual Proc 2.66G Intel HT, 2.5G Mem)...

I set Table_cache to 1024 and have Opened_tables growing quickly...

Open_tables 188
Opened_tables 5810
Uptime 7399

What I can't figure out is why MySQL keeps on opening up new tables while the table cache still has plenty of room.

I've searched in these forums and Google but all that I find is the standard table cache parameter optimization tips.

I suspect maybe our server has a memory limitation which is keeping the Open_tables artificially low. Top shows 2.2-2.5G of memory used and 1.6-1.8G of that memory active.

Another thought is that some of our scripts create temporary tables from select statements explicity in our software code. One guess is perhaps, anytime we create a temporary table in a script that causes Opened_table to increment though does not keep it cached because it is temporary.

Basically, I am trying to reduce the number of Opened_tables. Any help is appreciated. Thanks!

Follow up... I did a test on a development server and found that anytime we ran a CGI script that created a temporary table within the script, that caused Opened_tables to increment. On the same server, if I accessed a script that did not create a temporary table, but instead read from existing tables, then Opened_tables would not increment. During this test, I was the only one who could access this server.

Curiously, it appears that the Opened_tables counter was incremented twice for each temporary table created within the CGI script. There were selects and joins using these temporary tables after they were created, so maybe that is what is accounting for these extra opens.

Thus from what I have seen, it appears that if you are creating/using temporary tables in CGI scripts, that the Opened_tables counter will increment for each invocation of the script. So, in this case it appears that having the Open_tables counter less than the Table_cache is the only real indicator that will help determine whether the existing Table_cache setting is appropriate.

Me thinks a cached table will only work if the table is permanent IE it keeps the reference for as long as required. If its a temporary table then depending on the CGI script it may or may not retain the reference. Almost certainily when the script terminates the table is destroyed (or at least the pointer to the table is destroyed). Depending on the implementation its possible the MySQL server may not have been explicitly told to delete the table, and therefore is retaining it. I always prefer to explcitly destroy any objects created, especially those created as temporary, just in case the housekeeping routines don't kick in. Still you could always ditch CGI in favour of PHP

I don't mind that the temporary table is destroyed (unlinked) after the script finishes -- actually, that is the goal as it is a table we likely won't need again. I just found it odd that the Opened_table counter was being incremented for each temporary table created in the script. This makes it more difficult to determine when the table cache parameter is set appropriately.

We've moved all of our GGI scripts to PerlRun and from there we will be migrating them to mod-perl. Slightly off-topic -- how do you think PHP compares to mod-perl?

Only used Perl in a trial environment - didn't like it or get to grips with it. Found the learning curve on PHP very easy, but my background is VB / VBA / Pascal etc.... so wouldn't expect a problem from that quarter

It's very easy to integrate to a data back end, lots of libraries & modules out there to ease the burden of getting a site up.

Everything I've read suggests that the scripting will almost certainly be more robust and probably has a performance improvement. I would have no hesitation using PHP or ASP.

I never really saw the benefits of CGI, but then I was late on the web devlopment scene but I get the impression CGI was THE way a few years ago but not so sure now and in the future (thats just my prejudice)

Good to know. I'll have to look into it. Thanks!