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]
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? Besides enabling fast shutdown, what else will help avoid this kind of thing in the future? Thanks for any info... -- Nick Arnett 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]
