List:General Discussion« Previous MessageNext Message »
From:Nihal Date:November 6 2003 4:40pm
Subject:RE: INNODB flush holdup
View as plain text  
Is there anything that can be done to increase the speed of a flush?
Can it be run while other queries are running?

The problem I'm having is that every time a flush is waiting to happen
no new connections can be initiated and all my queries are on hold. If
there is something long running before it everything comes to a halt for
a good 30-40 seconds.

I can't make these long queries any faster. What can I do?

Thanks,
Nihal

-----Original Message-----
From: Matt W [mailto:mysql_lists@stripped] 
Sent: Wednesday, November 05, 2003 7:09 PM
To: Nihal; mysql@stripped
Subject: Re: INNODB flush holdup

Hi,

I don't know what your previous problem was, but I don't think it
affects my answer. :-)

This isn't specific to InnoDB. Yes, when you FLUSH TABLES, all new
queries wait for that to complete (as indicated by "Waiting for table"
in PROCESSLIST). And the tables can't all be flushed (closed) until all
queries complete that were running (the 2 "Sorting result" queries in
your case). Why are those 2 queries running for over 1 and 2 minutes?
Must be examining many rows and/or not indexed properly (if there's a
WHERE that could use an index)...


Hope that helps.


Matt


----- Original Message -----
From: "Nihal"
To: <mysql@stripped>
Sent: Wednesday, November 05, 2003 3:14 PM
Subject: INNODB flush holdup


> OK, I've got another quandary, thanks for the help on the last one,
I'm
> working on it.
>
> | 15203 | root   | web:36810     | ez2000 | Query   | 137  | Sorting
> result    | SELECT from table_a |
> | 15235 | root   | web:36849     | ez2000 | Query   | 67   | Sorting
> result    | SELECT from table_a |
> | 15244 | root   | db:32779      | NULL   | Refresh | 42   | Flushing
> tables   | NULL                |
> | 15246 | root   | runque1:3220  | ez2000 | Query   | 40   | Waiting
for
> table | insert into table_a |
> | 15247 | root   | runque1:3221  | ez2000 | Sleep   | 2    |
> | NULL
> |
> | 15250 | root   | web:36863     | ez2000 | Query   | 34   | Waiting
for
> table | SELECT from table_a |
> | 15251 | root   | web:36864     | ez2000 | Query   | 34   | Waiting
for
> table | select from table_b |
>
> What would cause the flush tables to take so long?
> Have I made my log files to large?
> Does the flush lock all queries until complete?
> And do any prior queries have to finish before it will start?
>
> Sorry, I'm new to INNODB, thanks in advance for the help.

Thread
INNODB flush holdupNihal5 Nov
  • Re: INNODB flush holdupMatt W6 Nov
    • RE: INNODB flush holdupNihal6 Nov
      • Re: INNODB flush holdupJeremy Zawodny6 Nov
        • RE: INNODB flush holdupNihal6 Nov
        • Table CreationArnoldus Th.J. Koeleman6 Nov
          • Re: Table CreationChris Boget6 Nov
          • Re: Table CreationDan Nelson6 Nov
          • Simple question about updating a table fieldAdmin-Stress6 Nov
            • Re: Simple question about updating a table fieldgerald_clark6 Nov