Re: innodb rollbacks
Jeremy, - Original Message - From: Jeremy Zawodny [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, May 30, 2002 8:43 PM Subject: Re: innodb rollbacks On Thu, May 30, 2002 at 12:16:28PM +0300, Heikki Tuuri wrote: However, if there are lots of updates, and the user has a dangling uncommitted consistent read to the database for a long time, then the size of the undo logs may become significant also in InnoDB. I have to consider adding the size info to the InnoDB Monitor. Heikki, That reminds me of an InnoDB wish-list item I have. I like the level of detail provided in the InnoDB monitor output. However, I'd really like to be available via MySQL rather than just in the logs. That will make it a lot easier to collect the data remotely and write apps that can monitor and make use of the data. Have you given any thought to that? (I have no idea what the effort would be like, but it's can't hurt to ask...) it will take me 5 hours effective work time to add something like SHOW INNODB STATUS; I have to add it soon (June 2002), because the upcoming BMC Patrol Knowledge Module for InnoDB needs these stats to draw nice graphs. Thanks, Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.51: up 0 days, processed 18,745,524 queries (295/sec. avg) Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, hot backup, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com - 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 rollbacks
Walt, Jeremy, the undo logs (= rollback segments) in the tablespace generally take much less space than the inserted rows. Thus it is best just to look with SHOW TABLE STATUS what is the available free space. I think in Oracle rollback segments use a lot more space than in InnoDB. However, if there are lots of updates, and the user has a dangling uncommitted consistent read to the database for a long time, then the size of the undo logs may become significant also in InnoDB. I have to consider adding the size info to the InnoDB Monitor. 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 - Original Message - From: Jeremy Zawodny [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Thursday, May 30, 2002 6:31 AM Subject: Re: innodb rollbacks On Tue, May 21, 2002 at 10:41:51AM -0400, walt wrote: Thanks for your reply Jeremy! I'm an Oracle person, so I tend to think of things being in multiple files/tablespaces. Right. Do you know if there is a way to view the rollback stats? I checked the InnoDB monitor (details in the InnoDB docs here http://www.innodb.com/ibman.html) but don't see anything. Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.51: up 0 days, processed 3,071,354 queries (252/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 - 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 rollbacks
On Thu, May 30, 2002 at 12:16:28PM +0300, Heikki Tuuri wrote: However, if there are lots of updates, and the user has a dangling uncommitted consistent read to the database for a long time, then the size of the undo logs may become significant also in InnoDB. I have to consider adding the size info to the InnoDB Monitor. Heikki, That reminds me of an InnoDB wish-list item I have. I like the level of detail provided in the InnoDB monitor output. However, I'd really like to be available via MySQL rather than just in the logs. That will make it a lot easier to collect the data remotely and write apps that can monitor and make use of the data. Have you given any thought to that? (I have no idea what the effort would be like, but it's can't hurt to ask...) Thanks, Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.51: up 0 days, processed 18,745,524 queries (295/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: innodb rollbacks
On Thu, May 30, 2002 at 10:43:58AM -0700, Jeremy Zawodny wrote: : Heikki, : : That reminds me of an InnoDB wish-list item I have. I like the level : of detail provided in the InnoDB monitor output. However, I'd really : like to be available via MySQL rather than just in the logs. That : will make it a lot easier to collect the data remotely and write apps : that can monitor and make use of the data. : : Have you given any thought to that? (I have no idea what the effort : would be like, but it's can't hurt to ask...) Just for the record, we second that notion. It's a pain in the ass using the InnoDB table monitor simply because it dumps so much output into the log files. There are times when we just want to grab the status at a particular moment, like how one would do SHOW TABLE STATUS or SHOW PROCESSLIST. * Philip Molter * Texas.net Internet * http://www.texas.net/ * [EMAIL PROTECTED] - 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 rollbacks
On Thu, May 30, 2002 at 02:30:07PM -0500, Philip Molter wrote: On Thu, May 30, 2002 at 10:43:58AM -0700, Jeremy Zawodny wrote: : Heikki, : : That reminds me of an InnoDB wish-list item I have. I like the level : of detail provided in the InnoDB monitor output. However, I'd really : like to be available via MySQL rather than just in the logs. That : will make it a lot easier to collect the data remotely and write apps : that can monitor and make use of the data. : : Have you given any thought to that? (I have no idea what the effort : would be like, but it's can't hurt to ask...) Just for the record, we second that notion. It's a pain in the ass using the InnoDB table monitor simply because it dumps so much output into the log files. There are times when we just want to grab the status at a particular moment, like how one would do SHOW TABLE STATUS or SHOW PROCESSLIST. Right... Something along the lines of SHOW INNODB STATUS would be excellent. Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.51: up 0 days, processed 21,365,291 queries (302/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: innodb rollbacks
On Tue, May 21, 2002 at 10:41:51AM -0400, walt wrote: Thanks for your reply Jeremy! I'm an Oracle person, so I tend to think of things being in multiple files/tablespaces. Right. Do you know if there is a way to view the rollback stats? I checked the InnoDB monitor (details in the InnoDB docs here http://www.innodb.com/ibman.html) but don't see anything. Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.51: up 0 days, processed 3,071,354 queries (252/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: innodb rollbacks
On Mon, May 20, 2002 at 12:25:26PM -0400, walt wrote: Does anyone know where rollbacks are stored for innodb tables? I assume memory since I keep getting a table full error when trying to drop an index. sql,query I'm not exactly sure what you're asking, but the rollback information needs to be stored on disk. Otherwise it cannot recover form a crash. Anyway, I suspect that your problem is that your tablespace is full. Can you try added another file and see if that makes the problem go away? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ - 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 rollbacks
On Tuesday 21 May 2002 12:24 am, Jeremy Zawodny wrote: On Mon, May 20, 2002 at 12:25:26PM -0400, walt wrote: Does anyone know where rollbacks are stored for innodb tables? I assume memory since I keep getting a table full error when trying to drop an index. sql,query I'm not exactly sure what you're asking, but the rollback information needs to be stored on disk. Otherwise it cannot recover form a crash. Anyway, I suspect that your problem is that your tablespace is full. Can you try added another file and see if that makes the problem go away? Jeremy Thanks for your reply Jeremy! I'm an Oracle person, so I tend to think of things being in multiple files/tablespaces. Do you know if there is a way to view the rollback stats? I've got 6GB of datafiles and only 1 table loaded with 46 rows, 14 indexes, and approx 1.6million records. I'm trying now to load all the data again in a seperate table to see what it takes to fill up the datafiles. Thanks again! -- Walter Anthony System Administrator National Electronic Attachment Atlanta, Georgia 1-800-782-5150 ext. 1608 If it's not broketweak it - 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
innodb rollbacks
Does anyone know where rollbacks are stored for innodb tables? I assume memory since I keep getting a table full error when trying to drop an index. sql,query Thanks for your help! -- Walter Anthony System Administrator National Electronic Attachment Atlanta, Georgia 1-800-782-5150 ext. 1608 If it's not broketweak it - 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
