List:General Discussion« Previous MessageNext Message »
From:Elizabeth Mattijsen Date:February 10 2011 7:14pm
Subject:Re: High disk usage
View as plain text  
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 <vegivamp@stripped>wrote:
> 
>> On Thu, Feb 10, 2011 at 2:15 PM, Santiago Soares <santiagosoares@stripped
>>> 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
>> 

Thread
High disk usageSantiago Soares10 Feb
  • Re: High disk usageSantiago Soares10 Feb
    • Re: High disk usageJohnny Withers10 Feb
  • Re: High disk usageJohan De Meersman10 Feb
    • Re: High disk usageSantiago Soares10 Feb
      • Re: High disk usageSantiago Soares10 Feb
      • Re: High disk usageElizabeth Mattijsen10 Feb
Re: High disk usageSantiago Soares10 Feb