I am running FLUSH TABLES manually. I am doing it each hour to generate a bin file, for incremental backups.
-----Original Message----- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED] Sent: Thursday, November 06, 2003 10:05 AM To: Nihal Cc: [EMAIL PROTECTED] Subject: Re: INNODB flush holdup 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:[EMAIL PROTECTED] > Sent: Wednesday, November 05, 2003 7:09 PM > To: Nihal; [EMAIL PROTECTED] > 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: <[EMAIL PROTECTED]> > 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/[EMAIL PROTECTED] > -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! <[EMAIL PROTECTED]> | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 53 days, processed 2,004,112,227 queries (431/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
