List:General Discussion« Previous MessageNext Message »
From:Santiago Soares Date:February 10 2011 8:16pm
Subject:Re: High disk usage
View as plain text  
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 <wang@stripped>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 <wang@stripped>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 <liz@stripped>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 <
>>> 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
>>> >>
>>>
>>>
>>> --
>>> MySQL General Mailing List
>>> For list archives: http://lists.mysql.com/mysql
>>> To unsubscribe:
>>> http://lists.mysql.com/mysql?unsub=1
>>>
>>>
>>
> --
> The best compliment you could give Pythian for our service is a referral.
>
>
>

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