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)
