Re: Re: InnoDB: Looong pause when log file is full?
Hi Heikki, Thank you for responding. (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. That's good to know---so that isn't it. 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. Respectfully, I don't think that's it. Both times I've seen the database do this it's been in periods of relatively low traffic. Until we switched to InnoDB tables, the only times the database was frozen up like this was when someone did an ill-advised select that took forever and gummed everything up. That's the main reason we switched to InnoDB tables---so a long select wouldn't hold up the db (by an update blocking, which would block subsequent selects). In this case, when the (inno-)db was 'pausing', all of the queries in the (growing) queue looked normal. And there was a lot of free ram. I didn't see anything useful in the InnoDB monitor, but then I'm new to reading it. Then all of a sudden all queries finished and there were no more in the queue and the problem didn't recur for five days. I thought it must be related to the log files, because when I looked at them it looked like one was just finished being written to. (Because its modification time was the previous minute, while the next (zeroth) log file was now the active one.) I know without a repeatable test case, there's not much you can do. If I get more information I'll let you know. It helps to hear that this behavior isn't normal, though. --Pete sql - 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
Re: InnoDB: Looong pause when log file is full?
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
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. 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) - 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
Re: Re: InnoDB: Looong pause when log file is full?
Thanks for your feedback (and your general untiring devotion to the cause...) On Thu, Aug 01, 2002 at 04:30:10PM -0700, Jeremy Zawodny wrote: 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. That's good to know. 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. Won't that only make for fewer long pauses, rather than schedule or shorten them? It won't fly with the powers that be if our system shuts down in the middle of the day and my response is Oh yeah, our server does that every three weeks and there's nothing we can do about it. Someone else suggested setting innodb_flush_log_at_trx_commit to 1, which should make for many frequent flushes rather than one monster occasional flush. Would that not take care of it? (I set it to 0 because the manual suggested that as a big performance improvement at the expense of a small measure of recoverability in the unlikely event of a crash.) [Is there a way to set that variable with 3.23.51 without shutting down the server? I couldn't find a way.] Thanks, --Pete sql,query - 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
