List:General Discussion« Previous MessageNext Message »
From:Jeremy Zawodny Date:November 6 2003 5:04pm
Subject:Re: INNODB flush holdup
View as plain text  
On Thu, Nov 06, 2003 at 09:40:41AM -0700, Nihal wrote:
> Is there anything that can be done to increase the speed of a flush?
> Can it be run while other queries are running?

Are you actually running the FLUSH TABLES command?  If so, why?

> 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?

Increase disk I/O capacity or add some caching on the disk controller.

Jeremy

> -----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.
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
> 

-- 
Jeremy D. Zawodny     |  Perl, Web, MySQL, Linux Magazine, Yahoo!
<Jeremy@stripped>  |  http://jeremy.zawodny.com/

MySQL 4.0.15-Yahoo-SMP: up 53 days, processed 2,004,112,227 queries (431/sec. avg)
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