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=arch...@jab.org
Re: Is table_open_cache a private cache of a session?
On Mon, Feb 1, 2010 at 2:21 PM, Cui Shijun rancp...@gmail.com 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
Re: Is table_open_cache a private cache of a session?
hi, 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 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 vegiv...@tuxera.be: On Mon, Feb 1, 2010 at 2:21 PM, Cui Shijun rancp...@gmail.com 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Is table_open_cache a private cache of a session?
On Mon, Feb 1, 2010 at 4:51 PM, Cui Shijun rancp...@gmail.com 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
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 vegiv...@tuxera.be: On Mon, Feb 1, 2010 at 4:51 PM, Cui Shijun rancp...@gmail.com 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=arch...@jab.org
Re: Is table_open_cache a private cache of a session?
Is FD_SETSIZE regards to an open_file_limit? WB 2010/2/1 Cui Shijun rancp...@gmail.com 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 vegiv...@tuxera.be: On Mon, Feb 1, 2010 at 4:51 PM, Cui Shijun rancp...@gmail.com 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=wagnerbianch...@gmail.com
Re: Is table_open_cache a private cache of a session?
Sorry, *open_files_limit... 2010/2/1 Wagner Bianchi wagnerbianch...@gmail.com Is FD_SETSIZE regards to an open_file_limit? WB 2010/2/1 Cui Shijun rancp...@gmail.com 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 vegiv...@tuxera.be: On Mon, Feb 1, 2010 at 4:51 PM, Cui Shijun rancp...@gmail.com 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=wagnerbianch...@gmail.com -- Wagner Bianchi - Web System Developer and Database Administrator Phone: (31) 8654-9510 / 3272-0226 E-mail: wagnerbianch...@gmail.com Lattes: http://lattes.cnpq.br/2041067758113940 Twitter: http://twitter.com/wagnerbianchi Skype: infodbacet
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 wagnerbianch...@gmail.com: Sorry, *open_files_limit... 2010/2/1 Wagner Bianchi wagnerbianch...@gmail.com Is FD_SETSIZE regards to an open_file_limit? WB 2010/2/1 Cui Shijun rancp...@gmail.com 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 vegiv...@tuxera.be: On Mon, Feb 1, 2010 at 4:51 PM, Cui Shijun rancp...@gmail.com 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=wagnerbianch...@gmail.com -- Wagner Bianchi - Web System Developer and Database Administrator Phone: (31) 8654-9510 / 3272-0226 E-mail: wagnerbianch...@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=arch...@jab.org
