Re: Really slow shutdown with Innodb, db not accessible?
Nick, - Original Message - From: "Nick Arnett" <[EMAIL PROTECTED]> To: "Heikki Tuuri" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Wednesday, July 30, 2003 1:24 AM Subject: RE: Really slow shutdown with Innodb, db not accessible? > FYI, as I looked at the code that led up to this, I have realized that MySQL > was doing a huge rollback, which ended up taking about five hours. It was > rolling back about 2 million INSERTs, I think. The rollback really was not > necessary, so I've changed the appropriate code so that it's no longer a > transaction. > > The culprit was some table locking that improved performance quite a bit > when the tables were MyISAM. Gotta go look for more of those lurking in the > corners, I guess. I forgot it could also be a huge rollback. If you can drop the whole table, then section 6.1 of ibman.html explains how to get rid of a runaway rollback. > -- > Nick Arnett > Phone/fax: (408) 904-7198 > [EMAIL PROTECTED] Regards, Heikki > > -Original Message- > > From: Heikki Tuuri [mailto:[EMAIL PROTECTED] > > Sent: Tuesday, July 29, 2003 12:58 PM > > To: [EMAIL PROTECTED] > > Subject: Re: Really slow shutdown with Innodb, db not accessible? > > Importance: High > > > > > > Nick, > > > > - Original Message - > > From: ""Nick Arnett"" <[EMAIL PROTECTED]> > > Newsgroups: mailing.database.mysql > > Sent: Tuesday, July 29, 2003 8:54 PM > > Subject: Really slow shutdown with Innodb, db not accessible? > > > > > > > For the last four hours or so, I've been waiting for MySQL > > (4.0.12 on W2K) > > > to complete a shutdown. The fast shutdown flag is not set > > > (innodb_fast_shutdown=0), so I assume it is doing a purge and > > merge... but > > > in the meantime, I don't have any access to the server -- clients simply > > > can't connect. This is a real problem, since it renders the database > > > useless for a long period of time. My Innodb table is about 15 GB and > > > probably has about 10 million records in various tables. > > > > > > When the darn thing finally shuts down, I'll restart with fast shutdown > > on, > > > but I'm wondering how foolish it would be to kill the process, > > given that > > > Innodb should then do a crash repair. Would the crash repair > > take longer > > > than what it's doing now? Would the server be inaccessible as > > it is now? > > > > crash recovery is usually much faster than purge and merge. > > > > Killing the mysqld process is a legal (and the fastest :)) way of shutting > > down InnoDB. > > > > Why did you set fast_shutdown=0? > > > > By the way, I am not sure the setting really affects the variable value at > > all, since in versions < 4.0.15 there was a bug that it was specified as a > > NO_ARG parameter. > > > > > Besides enabling fast shutdown, what else will help avoid this kind of > > thing > > > in the future? > > > > > > Thanks for any info... > > > > > > -- > > > Nick Arnett > > > > Regards, > > > > Heikki > > > > > Phone/fax: (408) 904-7198 > > > [EMAIL PROTECTED] > > > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Really slow shutdown with Innodb, db not accessible?
FYI, as I looked at the code that led up to this, I have realized that MySQL was doing a huge rollback, which ended up taking about five hours. It was rolling back about 2 million INSERTs, I think. The rollback really was not necessary, so I've changed the appropriate code so that it's no longer a transaction. The culprit was some table locking that improved performance quite a bit when the tables were MyISAM. Gotta go look for more of those lurking in the corners, I guess. -- Nick Arnett Phone/fax: (408) 904-7198 [EMAIL PROTECTED] > -Original Message- > From: Heikki Tuuri [mailto:[EMAIL PROTECTED] > Sent: Tuesday, July 29, 2003 12:58 PM > To: [EMAIL PROTECTED] > Subject: Re: Really slow shutdown with Innodb, db not accessible? > Importance: High > > > Nick, > > - Original Message - > From: ""Nick Arnett"" <[EMAIL PROTECTED]> > Newsgroups: mailing.database.mysql > Sent: Tuesday, July 29, 2003 8:54 PM > Subject: Really slow shutdown with Innodb, db not accessible? > > > > For the last four hours or so, I've been waiting for MySQL > (4.0.12 on W2K) > > to complete a shutdown. The fast shutdown flag is not set > > (innodb_fast_shutdown=0), so I assume it is doing a purge and > merge... but > > in the meantime, I don't have any access to the server -- clients simply > > can't connect. This is a real problem, since it renders the database > > useless for a long period of time. My Innodb table is about 15 GB and > > probably has about 10 million records in various tables. > > > > When the darn thing finally shuts down, I'll restart with fast shutdown > on, > > but I'm wondering how foolish it would be to kill the process, > given that > > Innodb should then do a crash repair. Would the crash repair > take longer > > than what it's doing now? Would the server be inaccessible as > it is now? > > crash recovery is usually much faster than purge and merge. > > Killing the mysqld process is a legal (and the fastest :)) way of shutting > down InnoDB. > > Why did you set fast_shutdown=0? > > By the way, I am not sure the setting really affects the variable value at > all, since in versions < 4.0.15 there was a bug that it was specified as a > NO_ARG parameter. > > > Besides enabling fast shutdown, what else will help avoid this kind of > thing > > in the future? > > > > Thanks for any info... > > > > -- > > Nick Arnett > > Regards, > > Heikki > > > Phone/fax: (408) 904-7198 > > [EMAIL PROTECTED] > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Really slow shutdown with Innodb, db not accessible?
> -Original Message- > From: Heikki Tuuri [mailto:[EMAIL PROTECTED] > Sent: Tuesday, July 29, 2003 12:58 PM > crash recovery is usually much faster than purge and merge. > > Killing the mysqld process is a legal (and the fastest :)) way of shutting > down InnoDB. That's good to hear. W2K tells me I don't have permission to kill the process, despite having all admin privileges, so I'll look into that now. Soon, this database will move to Linux or BSD, I hope. > Why did you set fast_shutdown=0? I'm asking myself the same question... ;-) I really don't remember. The last time I changed the config was when I started using Innodb, four or five months ago. Don't know what the heck I was thinking. > By the way, I am not sure the setting really affects the variable value at > all, since in versions < 4.0.15 there was a bug that it was specified as a > NO_ARG parameter. I noticed some of your other messages about that. It's probably time for me to update. Thanks very much. I really appreciate the speed with which you respond (not just to my messages, I read the list regularly). Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Really slow shutdown with Innodb, db not accessible?
Nick, - Original Message - From: ""Nick Arnett"" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Tuesday, July 29, 2003 8:54 PM Subject: Really slow shutdown with Innodb, db not accessible? > For the last four hours or so, I've been waiting for MySQL (4.0.12 on W2K) > to complete a shutdown. The fast shutdown flag is not set > (innodb_fast_shutdown=0), so I assume it is doing a purge and merge... but > in the meantime, I don't have any access to the server -- clients simply > can't connect. This is a real problem, since it renders the database > useless for a long period of time. My Innodb table is about 15 GB and > probably has about 10 million records in various tables. > > When the darn thing finally shuts down, I'll restart with fast shutdown on, > but I'm wondering how foolish it would be to kill the process, given that > Innodb should then do a crash repair. Would the crash repair take longer > than what it's doing now? Would the server be inaccessible as it is now? crash recovery is usually much faster than purge and merge. Killing the mysqld process is a legal (and the fastest :)) way of shutting down InnoDB. Why did you set fast_shutdown=0? By the way, I am not sure the setting really affects the variable value at all, since in versions < 4.0.15 there was a bug that it was specified as a NO_ARG parameter. > Besides enabling fast shutdown, what else will help avoid this kind of thing > in the future? > > Thanks for any info... > > -- > Nick Arnett Regards, Heikki > Phone/fax: (408) 904-7198 > [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
