This post( http://bugs.mysql.com/bug.php?id=48929 ) shows that If
the number of opened file grows too big, mysql will get error.
I'm also confused by the difference & relationship between "open
table" and "open file descriptor" by the table cache.
As far as I understand, when a thread ask the global cache for a table:
* if the table is opened before and currently not used by other
thread, the request thread will get this table
* if no table in table cache is available( currently used by other
thread, or not opened before ), the request thread will open this
Once open a table, mysql *might?( I'm not sure )* open a file
descriptor corresponding to the data file of the table. In that case,
when the number of table opened simultaneously goes too big, mysql
will use too much file descriptors and then hit the bug 48929.
Your experience( "I've had one occurrence where it grew to 26.000
open tables" ) seems to show there must be something wrong with my
understanding, Hmm... :-(
2010/2/1 Johan De Meersman <vegivamp@stripped>:
> On Mon, Feb 1, 2010 at 2:21 PM, Cui Shijun <rancpine@stripped> wrote:
>> "To minimize the problem with multiple client sessions having
>> different states on the same table, the table is opened independently
>> by each concurrent session."
> That literally means that every time a table gets referenced, it is "opened"
> anew. Even if you use the same table twice in a single select, it gets
> opened twice.
>> Are sessions using private table caches?
> No, as far as I understand the internals there's a global cache, and as soon
> as a table is no longer in use, the object gets returned for subsequent use
> by another thread (or free()ing if there's not enough room for something
>> if I have M concurrent sessions and N tables, assume each session
>> will access tables one by one, will mysql open M*N tables?
> Yes, this is correct. This also implies that you need to make sure your
> system allows sufficient filehandles for the MySQL user/process.
> Table cache objects don't really use a lot of space, so feel free to set it
> to a large enough value. I've got hosts where it's set to 30.000, although
> it's quite rare that they ever get there - I've had one occurrence where it
> grew to 26.000 open tables, with up to 20 tables being opened per second.
> Graph it (Open_tables variable) and make sure you don't run into the limit.
> Bier met grenadyn
> Is als mosterd by den wyn
> Sy die't drinkt, is eene kwezel
> Hy die't drinkt, is ras een ezel