MyISAM key cache and open tables

[Brian O'Neill]

I have recently switched from using InnoDB to MyISAM (issues with InnoDB file size and select speed). I'm running on a server with a large amount of memory (8G). When using InnoDB I had it's key cache set very high and it filled it and I had a very high hitrate on my key buffer. Now, with MyISAM I'm down to around 35% hitrate on the same data.

My thought on the problem is it has to do with the number of open tables. I currently have about 14,000 tables and my table_cache value is set to 500. If I increase this value my hitrate increases, but, the time it takes to do my inserts goes up 50-75% which is worse then my slow selects. Right now I've found that about 500 tables in the table_cache is my sweet spot. I normally have about 70-80 threads running at a time.

From what I can gather mysql has to have the index file for a MyISAM table open for it to reference it in the key buffer. This seems very odd to me. Can anyone confirm this? I know it's not an issue with my key_buffer size as it's set to 4G and it's currently using only 86M.

Any thoughts?