In my previous post I looked into how large table_cache actually can decrease performance. The “miss” path is getting more expensive very quickly as table cache growths so if you’re going to have high miss ratio anyway you’re better off with small table cache.
What I have not checked though is how does table_cache (or table_open_cache in newer version) size affects the hit path.
I started with the same test as last time – created 100000 tables and read all of them at once to make sure all table cache entries are populated. When I tried repeatedly reading 1000 empty tables with table_cache of 20000 and 2000. With Table Cache of 2000 I got about 16400 selects/sec with Table Cache of 20000 13500 selects/sec. So there is some slow down in hit path as well though it is not as large as with miss path.
I also tested with very small table cache of 5, which would cause opening 1000 tables in the loop to generate misses. This way I got 8000 selects/sec – note this is basically best case scenario for opening tables as MyISAM table headers which are being modified were fully cached in this case.
What does this tell us ? If you can fit your tables in table cache and eliminate (or almost eliminate) table cache misses it is best to size table_cache large enough – even in the best case scenario for misses hit from large table_cache is faster.
There are also possible optimization for “hit” path with large table cache though this should only affect someone who runs really large amount of simple queries – this is when opening table can be significant contributer to total query time.
