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=arch...@jab.org
