table_cache max usable value?

MySQL & MariaDB Other MySQL® Questions
1

Hi,

I’ve read around that table_cache has some performance issues if put too big. But I’ve not found any mentions what setting is too big.

I have huge about on databases with lots of tables of myisam.
So the tables just can’t fit in to table_cache and mysql opens
something like 20 tables/s which isn’t nice.

So I would like to use as big table_cache as possible. At this moment it is 2048.

There is need for filehandles for the table cache, open_files_limit is set to 16384.

Systems ulimit for open files is set to 64k.

Mysql is 5.0.45 from centos (rhel).

And the question is, how big table_cache setting i can have? and what to do sith open_files_limit (any other settings for same thing or similar thing?) and system’s ulimit.

2

[URL=“http://MySQL :: MySQL 8.0 Reference Manual :: 8.4.3.1 How MySQL Opens and Closes Tables”]http://dev.mysql.com/doc/refman/5.0/en/table-cache.html[/URL]

There are other considerations besides the time it takes to open a table (which is actually pretty small).

Don’t forget every MyISAM table needs two file descriptors, as does every connection, and every temporary disk table, etc. You could probably set table_cache to 6K and be okay.

Are you on a 64-bit host? How big are those tables? In another thread, to many opened large tables might be causing MySQL to crash.

3
[B]MarkRose wrote on Sat, 21 March 2009 20:10[/B]

Are you on a 64-bit host? How big are those tables? In another thread, to many opened large tables might be causing MySQL to crash.

It’s 64bit and those tables are usually tiny.

I tested 8k and it seemed slow, but after thinking about it, I think i didn’t let the server to fill caches.

And about those 16k total fd and 64k system limit, I can change them also if that would be recommended.