Is table_open_cache a private cache of a session?

hi,
I'm a little confused by the
document(http://dev.mysql.com/doc/refman/5.1/en/table-cache. html),
which only says:
"To minimize the problem with multiple client sessions having
different states on the same table, the table is opened independently
by each concurrent session."
Are sessions using private table caches?
if I have M concurrent sessions and N tables, assume each session
will access tables one by one, will mysql open M*N tables?

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Is table_open_cache a private cache of a session?

--0016e647eb4a739df5047e8aa2f6
Content-Type: text/plain; charset=ISO-8859-1

On Mon, Feb 1, 2010 at 2:21 PM, Cui Shijun 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
else).


> 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

--0016e647eb4a739df5047e8aa2f6--

Re: Is table_open_cache a private cache of a session?

hi,
This post( http://bugs.mysql.com/bug.php?id=3D48929 ) 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
table
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 :
> On Mon, Feb 1, 2010 at 2:21 PM, Cui Shijun 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 "open=
ed"
> anew. Even if you use the same table twice in a single select, it gets
> opened twice.
>
>>
>> =A0Are sessions using private table caches?
>
> No, as far as I understand the internals there's a global cache, and as s=
oon
> as a table is no longer in use, the object gets returned for subsequent u=
se
> by another thread (or free()ing if there's not enough room for something
> else).
>
>>
>> =A0if 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, althoug=
h
> 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 limi=
t.
>
>
>
> --
> Bier met grenadyn
> Is als mosterd by den wyn
> Sy die't drinkt, is eene kwezel
> Hy die't drinkt, is ras een ezel
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Is table_open_cache a private cache of a session?

--001636c5a4bd1d4468047e8c5079
Content-Type: text/plain; charset=ISO-8859-1

On Mon, Feb 1, 2010 at 4:51 PM, Cui Shijun wrote:

> I'm also confused by the difference & relationship between "open
> table" and "open file descriptor" by the table cache.
>

"open table" is a MySQL concept. "Open file descriptor" is an OS concept. A
single table (MyISAM) consists of three files: the .frm (description), the
..MYD (data) and the .MYI (indices). Thus, a single open table can correspond
to multiple open files. Additionally, temp tables, sortfiles and whatnot
also consume file descriptors.


> 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
>

"and *there is a cache entry that* is currently not used*" - multiple
entries can exist for the same table.


> * if no table in table cache is available( currently used by other
> thread, or not opened before ), the request thread will open this
> table
>

The thread will get a new cache object that opens that table, yes.


> 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... :-(
>

I just skimmed over it, but the bug seem related specifically to InnoDB, and
to a highly specific file descriptor number being equal to some form of
hardcoded limit - maybe different OSes or linux distro's have different
values for said limit, or maybe it only occurs under specific conditions.



--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--001636c5a4bd1d4468047e8c5079--

Re: Is table_open_cache a private cache of a session?

Got your idea.
Thank you very much. Now I know how table cache works :-)

For the bug, yes, it's related to the value of FD_SETSIZE, which is
limited to 1024 at my RedHat box.
Maybe I should update it to a suitable value.

2010/2/2 Johan De Meersman :
>
> On Mon, Feb 1, 2010 at 4:51 PM, Cui Shijun wrote:
>>
>> I'm also confused by the difference & relationship between "open
>> table" and "open file descriptor" by the table cache.
>
> "open table" is a MySQL concept. "Open file descriptor" is an OS concept.=
A
> single table (MyISAM) consists of three files: the .frm (description), th=
e
> .MYD (data) and the .MYI (indices). Thus, a single open table can corresp=
ond
> to multiple open files. Additionally, temp tables, sortfiles and whatnot
> also consume file descriptors.
>
>>
>> =A0As far as I understand, when a thread ask the global cache for a tabl=
e:
>> * if the table is opened before and currently not used by other
>> thread, the request thread will get this table
>
> "and *there is a cache entry that* is currently not used*" - multiple
> entries can exist for the same table.
>
>>
>> * if no table in table cache is available( currently used by other
>> thread, or not opened before ), the request thread will open this
>> table
>
> The thread will get a new cache object that opens that table, yes.
>
>>
>> =A0Once 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 =A0table opened simultaneously goes too big, mysql
>> will use too much file descriptors and then hit the bug 48929.
>>
>> =A0Your 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... =A0:-(
>
> I just skimmed over it, but the bug seem related specifically to InnoDB, =
and
> to a highly specific file descriptor number being equal to some form of
> hardcoded limit - maybe different OSes or linux distro's have different
> values for said limit, or maybe it only occurs under specific conditions.
>
>
>
> --
> Bier met grenadyn
> Is als mosterd by den wyn
> Sy die't drinkt, is eene kwezel
> Hy die't drinkt, is ras een ezel
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Is table_open_cache a private cache of a session?

--0016e649864c43ce55047e8cb459
Content-Type: text/plain; charset=ISO-8859-1

Is FD_SETSIZE regards to an open_file_limit?

WB

2010/2/1 Cui Shijun

> Got your idea.
> Thank you very much. Now I know how table cache works :-)
>
> For the bug, yes, it's related to the value of FD_SETSIZE, which is
> limited to 1024 at my RedHat box.
> Maybe I should update it to a suitable value.
>
> 2010/2/2 Johan De Meersman :
> >
> > On Mon, Feb 1, 2010 at 4:51 PM, Cui Shijun wrote:
> >>
> >> I'm also confused by the difference & relationship between "open
> >> table" and "open file descriptor" by the table cache.
> >
> > "open table" is a MySQL concept. "Open file descriptor" is an OS concept.
> A
> > single table (MyISAM) consists of three files: the .frm (description),
> the
> > .MYD (data) and the .MYI (indices). Thus, a single open table can
> correspond
> > to multiple open files. Additionally, temp tables, sortfiles and whatnot
> > also consume file descriptors.
> >
> >>
> >> 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
> >
> > "and *there is a cache entry that* is currently not used*" - multiple
> > entries can exist for the same table.
> >
> >>
> >> * if no table in table cache is available( currently used by other
> >> thread, or not opened before ), the request thread will open this
> >> table
> >
> > The thread will get a new cache object that opens that table, yes.
> >
> >>
> >> 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... :-(
> >
> > I just skimmed over it, but the bug seem related specifically to InnoDB,
> and
> > to a highly specific file descriptor number being equal to some form of
> > hardcoded limit - maybe different OSes or linux distro's have different
> > values for said limit, or maybe it only occurs under specific conditions.
> >
> >
> >
> > --
> > Bier met grenadyn
> > Is als mosterd by den wyn
> > Sy die't drinkt, is eene kwezel
> > Hy die't drinkt, is ras een ezel
> >
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=wagnerbianchijr@gmail.com
>

--0016e649864c43ce55047e8cb459--

Re: Is table_open_cache a private cache of a session?

--0016e6d99a2130bf2a047e8cb762
Content-Type: text/plain; charset=ISO-8859-1

Sorry, *open_files_limit...

2010/2/1 Wagner Bianchi

> Is FD_SETSIZE regards to an open_file_limit?
>
> WB
>
> 2010/2/1 Cui Shijun
>
> Got your idea.
>> Thank you very much. Now I know how table cache works :-)
>>
>> For the bug, yes, it's related to the value of FD_SETSIZE, which is
>> limited to 1024 at my RedHat box.
>> Maybe I should update it to a suitable value.
>>
>> 2010/2/2 Johan De Meersman :
>> >
>> > On Mon, Feb 1, 2010 at 4:51 PM, Cui Shijun wrote:
>> >>
>> >> I'm also confused by the difference & relationship between "open
>> >> table" and "open file descriptor" by the table cache.
>> >
>> > "open table" is a MySQL concept. "Open file descriptor" is an OS
>> concept. A
>> > single table (MyISAM) consists of three files: the .frm (description),
>> the
>> > .MYD (data) and the .MYI (indices). Thus, a single open table can
>> correspond
>> > to multiple open files. Additionally, temp tables, sortfiles and whatnot
>> > also consume file descriptors.
>> >
>> >>
>> >> 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
>> >
>> > "and *there is a cache entry that* is currently not used*" - multiple
>> > entries can exist for the same table.
>> >
>> >>
>> >> * if no table in table cache is available( currently used by other
>> >> thread, or not opened before ), the request thread will open this
>> >> table
>> >
>> > The thread will get a new cache object that opens that table, yes.
>> >
>> >>
>> >> 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... :-(
>> >
>> > I just skimmed over it, but the bug seem related specifically to InnoDB,
>> and
>> > to a highly specific file descriptor number being equal to some form of
>> > hardcoded limit - maybe different OSes or linux distro's have different
>> > values for said limit, or maybe it only occurs under specific
>> conditions.
>> >
>> >
>> >
>> > --
>> > Bier met grenadyn
>> > Is als mosterd by den wyn
>> > Sy die't drinkt, is eene kwezel
>> > Hy die't drinkt, is ras een ezel
>> >
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:
>> http://lists.mysql.com/mysql?unsub=wagnerbianchijr@gmail.com
>>
>


--
Wagner Bianchi - Web System Developer and Database Administrator
Phone: (31) 8654-9510 / 3272-0226
E-mail: wagnerbianchijr@gmail.com
Lattes: http://lattes.cnpq.br/2041067758113940
Twitter: http://twitter.com/wagnerbianchi
Skype: infodbacet

--0016e6d99a2130bf2a047e8cb762--

Re: Is table_open_cache a private cache of a session?

FD_SETSIZE is defined at /usr/include/linux/posix_types.h and
/usr/include/bits/typesizes.h ...

Set open_files_limit bigger is a way to let mysql use more file
descriptors( and thus has the chance to reach the FD_SETSIZE limit )
Also notice in the bug description, innodb_open_files was used,
together with innodb_file_per_table. I guess this does not affect the
bug, which occurs at sql/mysqld.cc. :-P

2010/2/2 Wagner Bianchi :
> Sorry, *open_files_limit...
>
> 2010/2/1 Wagner Bianchi
>>
>> Is FD_SETSIZE regards to an open_file_limit?
>>
>> WB
>>
>> 2010/2/1 Cui Shijun
>>>
>>> =A0Got your idea.
>>> =A0Thank you very much. Now I know how table cache works :-)
>>>
>>> =A0For the bug, yes, it's related to the value of FD_SETSIZE, which is
>>> limited to 1024 at my RedHat box.
>>> Maybe I should update it to a suitable value.
>>>
>>> 2010/2/2 Johan De Meersman :
>>> >
>>> > On Mon, Feb 1, 2010 at 4:51 PM, Cui Shijun wrote=
:
>>> >>
>>> >> I'm also confused by the difference & relationship between "open
>>> >> table" and "open file descriptor" by the table cache.
>>> >
>>> > "open table" is a MySQL concept. "Open file descriptor" is an OS
>>> > concept. A
>>> > single table (MyISAM) consists of three files: the .frm (description)=
,
>>> > the
>>> > .MYD (data) and the .MYI (indices). Thus, a single open table can
>>> > correspond
>>> > to multiple open files. Additionally, temp tables, sortfiles and
>>> > whatnot
>>> > also consume file descriptors.
>>> >
>>> >>
>>> >> =A0As 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
>>> >
>>> > "and *there is a cache entry that* is currently not used*" - multiple
>>> > entries can exist for the same table.
>>> >
>>> >>
>>> >> * if no table in table cache is available( currently used by other
>>> >> thread, or not opened before ), the request thread will open this
>>> >> table
>>> >
>>> > The thread will get a new cache object that opens that table, yes.
>>> >
>>> >>
>>> >> =A0Once 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 =A0table opened simultaneously goes too big, mysq=
l
>>> >> will use too much file descriptors and then hit the bug 48929.
>>> >>
>>> >> =A0Your 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... =A0:-(
>>> >
>>> > I just skimmed over it, but the bug seem related specifically to
>>> > InnoDB, and
>>> > to a highly specific file descriptor number being equal to some form =
of
>>> > hardcoded limit - maybe different OSes or linux distro's have differe=
nt
>>> > values for said limit, or maybe it only occurs under specific
>>> > conditions.
>>> >
>>> >
>>> >
>>> > --
>>> > Bier met grenadyn
>>> > Is als mosterd by den wyn
>>> > Sy die't drinkt, is eene kwezel
>>> > Hy die't drinkt, is ras een ezel
>>> >
>>>
>>> --
>>> MySQL General Mailing List
>>> For list archives: http://lists.mysql.com/mysql
>>> To unsubscribe:
>>> =A0http://lists.mysql.com/mysql?unsub=3Dwagnerbianchijr@gmai l.com
>
>
> --
> Wagner Bianchi - Web System Developer and Database Administrator
> Phone: (31) 8654-9510 / 3272-0226
> E-mail: wagnerbianchijr@gmail.com
> Lattes: http://lattes.cnpq.br/2041067758113940
> Twitter: http://twitter.com/wagnerbianchi
> Skype: infodbacet
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg