MySQL Forums
Forum List  »  Merge Storage Engine

Re: File descriptors, table caches, and lots of tables...
Posted by: Ingo Strüwing
Date: October 11, 2006 12:54PM

Hi.

1) Normally, at the beginning of an SQL statement all participating tables are opened. At the end of a statement they are all closed again. When a table is openend, an internel TABLE structure is allocated and filled with information about the table (not its data), mostly from the .frm file. This is a relatively time consuming process. It would be a pity to have to repeat it for _every_ statement. So we put the TABLE structure into the table_cache. We do not close the table. The next statement finds it in the cache and doesn't need to open it again. If a second session wants to use the same table it needs to create a new TABLE structure. It puts it in the table_cache at the end of the statement too. So the table_cache can contain multiple instances of the same table. Hence it is not easy to tell how big the table_cache should be for a certain site.

The number of filedescriptors needed for an open table depends on the storage engine. MyISAM uses one fd for the index file of a table and one fd per session for the data file. The first statement opening the table uses two fds, every session opening the same table at the same time requires one additional fd. So 7 sessions reading the same table require 8 fds for this table.

A MERGE table doesn't need a fd for itself, but one or two for every MyISAM table. So the first session opening your MERGE table takes 1000 fds, every concurrent session on the same MERGE table takes just 500 additional fds. However only the MERGE table needs a TABLE structure in the table_cache. The underlying MyISAM tables don't appear in the table_cache unless they are used directly (not through the MERGE table).

2) No. I don't know how to do this with Windows.

3) I suggest to use the maximum number of tables used by a statement (MERGE counts as 1) times the number of sessions active in parallel. Add some for system use. I don't know of an upper limit, but you don't need big numbers as you can see.

4) The most important thing I can think of is to try to use less sub-tables of the MERGE table.

Regards
Ingo

Ingo Strüwing, Senior Software Developer - Storage Engines
MySQL AB, www.mysql.com

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: File descriptors, table caches, and lots of tables...
6298
October 11, 2006 12:54PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.