Pete, ----- Original Message ----- From: "Jeremy Zawodny" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Friday, August 02, 2002 2:40 AM Subject: Re: InnoDB: Looong pause when log file is full?
> On Thu, Aug 01, 2002 at 04:37:05PM -0500, Pete Harlan wrote: > > Hi, > > > > I've read the performance tuning tips for InnoDB > > (http://www.innodb.com/ibman.html#InnoDB_tuning), but am getting bit > > when the log files are full and the buffer pool is checkpointed. InnoDB does 'fuzzy checkpoints'. That means modified database pages in the buffer pool are flushed to disk in small batches. The time when a physical log file becomes full does not affect this continuous background checkpointing activity, since InnoDB sees all the log files as one catenated log file. The pauses you experience are probably caused by high load in general, probably too much disk i/o. You should study your queries, use the InnoDB Monitor, watch 'top', adjust buffer pool size, spread disk i/o. If the load is CPU-bound and you have many queries running simultaneously, you may experience 'thread thrashing'. Then you can try to set innodb_thread_concurrency lower than the default 8, even to 1. > > By 'geting bit', I mean for several minutes the db server basically > > stops, and our website stops serving pages. Does anyone have advice > > about what we can do to alleviate this? Instead of having three 150mb > > log files, would we be better off with 30 15mb log files? > > It shouldn't matter how many files you have. InnoDB sees them as one > striped file anyway. > > > Our log files are on the same raid array as the data, but would it > > really make that much difference to move them to a separate disk? > > It can if things are I/O bound, and they likely are. > > > Alternately, is there a way to trigger this action at night, so we can > > avoid it happening during the day? It shut us down for about five > > minutes today. > > You could increase the size of your logs. That'll increase recovery > time if there's ever a crash, but it should give InnoDB more breathing > room. > > Jeremy > -- > Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! > <[EMAIL PROTECTED]> | http://jeremy.zawodny.com/ > > MySQL 3.23.51: up 63 days, processed 1,335,360,828 queries (241/sec. avg) Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
