MySQL Forums
Forum List  »  Merge Storage Engine

File descriptors, table caches, and lots of tables...
Posted by: Brian O'Donnell
Date: October 09, 2006 04:05AM

Hi...

We are currently using a MERGE table (4.1.10 on Win 2003 server, quad CPU large disks etc etc) which has, at present, 500 'underlying' tables. A new table is being added every 5 minutes. In production, this would be a maximum of once daily, but we're trying to find out what MySQL can cope with, hence the 5 minutely addition.

We keep running out of resources on the server, with error code 24 when trying to access the table, either to query it, or to perform an UPDATE ... UNION=(...) type query to add a new entry to the MERGE list.

We've tried a variety of combinations of table_cache and open_files_limit but don't quite understand how MySQL uses these in combination with MERGE tables. I'm guessing that for each table in the MERGE UNION, MySQL will hit the table cache once, so for every read/write, we could see MySQL open over 500 tables, which is 1000 file descriptors, if I understand this correctly?

Additionally, another process is trying to perform queries against this database AND another database both on the same MySQL server, reading from the MERGE table and writing into a variety of tables, which could come to around 100 tables being accessed in addition to the 500 forming the MERGE. The other process runs intermittently, every few minutes or so, but can take anything up to 40minutes to complete, so there is a fair amount of contention for resources, I expect!

open-files-limit is currently 2048 - setting this any higher has no effect so I imagine Windows has an upper limit of 2048 [any ideas how to change this?]

table-cache is currently 1000 but we tried 64, 128, 512, and 32, the latter caused the system to go into meltdown and keep reconnecting, obviously wasn't a happy bunny.

With these settings, its still unable to create the new MERGE entry.

mysql> show status like '%file%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Created_tmp_files | 0 |
| Open_files | 1550 |
+-------------------+-------+

and

| Open_tables | 33 |
| Opened_tables | 98 |

we have seen open_tables go to a few hundred and opened_tables go to many thousand, given enough time (a few days for example).

So the questions are...
1) What exactly is the table cache used for ? It can't possibly be loading table data into memory (each of the underlying tables in the MERGE will be several Gb in size).
2) Has anyone managed to get their open-files-limit to be more than 2048 and if so, how ? (bear in mind this is a Windows server)
3) What is a realistic size for the table_cache, given all the above information, and is there an upper limit? Should we be trying to 'get' all 500+ underlying tables into the cache?
4) Are we going about this the wrong way? Are there other, more relevant, tuning parameters we should be using?

Switching to MySQL 5 isn't an option at present, before anyone suggests that!

Many thanks
Brian

Options: ReplyQuote


Subject
Views
Written By
Posted
File descriptors, table caches, and lots of tables...
12422
October 09, 2006 04:05AM


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.