Thank you for your answers. They are all in the same schema, the file system is ext3. I cannot modify the app so it uses a table of table names, instead of show tables. I'll try a renice on the mysql process, so it doesn't kill the server.
Santiago Soares Fone: (41) 8488-0537 On Thu, Feb 10, 2011 at 5:20 PM, Singer X.J. Wang <w...@singerwang.com>wrote: > Also, are they in one schema? And what's your file system? > > Singer > > > On Thu, Feb 10, 2011 at 14:19, Singer X.J. Wang <w...@singerwang.com>wrote: > >> Okay, I'm going to chime in here and make a suggestion to re-design your >> schema. You're looking at almost 500,000 files in your case if not even >> more. I'd suggest bumping up the open table limit in MySQL and open file >> limit in the OS as a first step, but its a stop gap. >> >> >> >> On Thu, Feb 10, 2011 at 14:14, Elizabeth Mattijsen <l...@dijkmat.nl>wrote: >> >>> FWIW, I've found MySQL information_schema related features to be quite >>> useless when you're dealing with that many tables. Not only do they take >>> long, they also activate a lot of locks (or so it seems). So I created a >>> script that runs every minute on each db server that basically does an "ls" >>> in the data directory, writes a text file and then loads that file into a >>> table. Then you need to use SELECT in that table to find out which tables >>> there are. >>> >>> YMMV. >>> >>> >>> Elizabeth Mattijsen >>> ======================================= >>> On Feb 10, 2011, at 7:48 PM, Santiago Soares wrote: >>> > Hello, thanks for your answers. >>> > All of my tables are MyISAM. >>> > I found out that the application runs a lot of 'show tables' commands. >>> > These commands take a lot of time to run, due to the high number of >>> tables >>> > (~160k). >>> > Is there anything I can do to make 'show tables' run faster? >>> > >>> > Santiago Soares >>> > Fone: (41) 8488-0537 >>> > >>> > >>> > On Thu, Feb 10, 2011 at 12:58 PM, Johan De Meersman < >>> vegiv...@tuxera.be>wrote: >>> > >>> >> On Thu, Feb 10, 2011 at 2:15 PM, Santiago Soares < >>> santiagosoa...@gmail.com >>> >>> wrote: >>> >> >>> >>> With a show global status I see a strange behavior: >>> >>> | Open_files | 286 | >>> >>> | Opened_files | 1050743 | >>> >>> >>> >>> At this time the database has just started (about 10 minutes). >>> >>> >>> >> >>> >> That's quite a lot for ten minutes, yes. Increasing the open table >>> cache, >>> >> query cache and/or index cache might help, depending on data size, >>> table >>> >> types (I'd assume mostly InnoDB based off your pool size), et cetera. >>> >> >>> >> Basically it's a matter of finding out why you open and close so many >>> >> files, as that's likely to be one of the causes of the I/O waits. >>> >> >>> >> -- >>> >> 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=w...@singerwang.com >>> >>> >> > -- > The best compliment you could give Pythian for our service is a referral. > > >
