Re: 50 000 tables - table_cache
In the last episode (Nov 18), Jrgen Winqvist said: > I'm working with an application that uses a lot of tables. Apx 50k > tables are accessed every 5 minuts and they are openened/closed > constantly. There is a performance problem and i've changed the > filesystem to reiserfs for better handling many files. I also > increased the open-file limit in both mysql and linux but i can't > increase table_cache more than 16384 tables. Switching to InonDB may help you here, since all tables are stored in just a few tablespace files and the table cache is unused. Also, the table cache is just a linked list, so if you're thinking about rebuilding mysql with a higher hard table_cache limit, consider whether you want it to be scanning a 50k-element list every time it needs a table :) 16384 is probably too high as it stands. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: 50 000 tables - table_cache
I can't believe I'm saying this, but MySQL may not be the db of choice for your particular app... I figure you have few options: 1- keep db structure same, keep mysql, suffer performance issues 2- keep db structure, switch db, suffer migration costs 3- change db structure, keep mysql, suffer app modification 4- change db structure, switch db, suffer migration and app mod I know it sounds like nihlistic (sp?), that all of your options involve suffering, but I'm guessing that this is an organically grown app architecture, that it didn't start off with 50,000 tables. IMHO, your app is at a classic architecture inflection point, where you need to either redesign it, or truss it up with hardware changes. PS What about the idea of setting up a MySQL cluster, so that hopefully, various access will be spread out, and therefore file access... don't know about this one, but it's a though > -Original Message- > From: Jörgen Winqvist [mailto:[EMAIL PROTECTED] > Sent: Tuesday, November 18, 2003 11:02 AM > To: Chris Nolan > Cc: [EMAIL PROTECTED] > Subject: Re: 50 000 tables - table_cache > > > Hi Chris, > > I know its not good to have that many tables but that's the way its > done. I can't see why a merge would help. It still has to > open all the > tables. > > regards > > /jorgen > > Chris Nolan wrote: > > > Hi! > > > > Looking through the docs, MySQL's internals don't seem to lend > > themselves well to having this many tables. Is there any chance you > > could use MERGE tables to chuck a few of them together > (admittedly I'm > > guessing here). > > > > Regards, > > > > Chris > > > > Jörgen Winqvist wrote: > > > >> I'm working with an application that uses a lot of tables. Apx 50k > >> tables are accessed every 5 minuts and they are openened/closed > >> constantly. There is a performance problem and i've changed the > >> filesystem to reiserfs for better handling many files. I also > >> increased the open-file limit in both mysql and linux but i can't > >> increase table_cache more than 16384 tables. > >> > >> Any ideas? > >> > >> Regards - Jorgen > >> > >> > > > > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 50 000 tables - table_cache
Hi Chris, I know its not good to have that many tables but that's the way its done. I can't see why a merge would help. It still has to open all the tables. regards /jorgen Chris Nolan wrote: Hi! Looking through the docs, MySQL's internals don't seem to lend themselves well to having this many tables. Is there any chance you could use MERGE tables to chuck a few of them together (admittedly I'm guessing here). Regards, Chris Jörgen Winqvist wrote: I'm working with an application that uses a lot of tables. Apx 50k tables are accessed every 5 minuts and they are openened/closed constantly. There is a performance problem and i've changed the filesystem to reiserfs for better handling many files. I also increased the open-file limit in both mysql and linux but i can't increase table_cache more than 16384 tables. Any ideas? Regards - Jorgen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 50 000 tables - table_cache
Hi! Looking through the docs, MySQL's internals don't seem to lend themselves well to having this many tables. Is there any chance you could use MERGE tables to chuck a few of them together (admittedly I'm guessing here). Regards, Chris Jörgen Winqvist wrote: I'm working with an application that uses a lot of tables. Apx 50k tables are accessed every 5 minuts and they are openened/closed constantly. There is a performance problem and i've changed the filesystem to reiserfs for better handling many files. I also increased the open-file limit in both mysql and linux but i can't increase table_cache more than 16384 tables. Any ideas? Regards - Jorgen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
