Re: innodb and fragmentation
Jon Hancock [EMAIL PROTECTED] writes: When you issue this null ALTER TABLE, is the entire table locked during the build? The table is read-only during the build. i.e. Is the only way to defragment to effectively take the table offline during the rebuild? Well. Not quite offline, but almost. Is there a method to estimate time to do this rebuild? I usually go with rows * 1/5000 seconds. But it varies with hardware and table complexity. My tables are not very complex. thanks, Jon - Original Message - From: Per Andreas Buer [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, September 19, 2003 6:17 PM Subject: Re: innodb and fragmentation Hello Heikki, Heikki Tuuri [EMAIL PROTECTED] writes: I think a 'null' alter table operation: ALTER TABLE innodbtable TYPE=INNODB; does the defragmentation with just one build of the table. And I think it also preserves FOREIGN KEY constraints. Please test it! It did the job just fine. Thanks. -- Per Andreas Buer -- 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] -- Per Andreas Buer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb and fragmentation
When you issue this null ALTER TABLE, is the entire table locked during the build? i.e. Is the only way to defragment to effectively take the table offline during the rebuild? Is there a method to estimate time to do this rebuild? thanks, Jon - Original Message - From: Per Andreas Buer [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, September 19, 2003 6:17 PM Subject: Re: innodb and fragmentation Hello Heikki, Heikki Tuuri [EMAIL PROTECTED] writes: I think a 'null' alter table operation: ALTER TABLE innodbtable TYPE=INNODB; does the defragmentation with just one build of the table. And I think it also preserves FOREIGN KEY constraints. Please test it! It did the job just fine. Thanks. -- Per Andreas Buer -- 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: innodb and fragmentation
Hello Heikki, Heikki Tuuri [EMAIL PROTECTED] writes: I think a 'null' alter table operation: ALTER TABLE innodbtable TYPE=INNODB; does the defragmentation with just one build of the table. And I think it also preserves FOREIGN KEY constraints. Please test it! It did the job just fine. Thanks. -- Per Andreas Buer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re: innodb and fragmentation
Per Andreas Buer [EMAIL PROTECTED] wrote: Hello Heikki, Heikki Tuuri writes: I think a 'null' alter table operation: ALTER TABLE innodbtable TYPE=INNODB; does the defragmentation with just one build of the table. And I think it also preserves FOREIGN KEY constraints. Please test it! It did the job just fine. Thanks. and it seems to be what I asked for as well, thanks! Franky -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb and fragmentation
this looks great. will this work with version 4.04 on solaris? as a side comment, anyone know when 4.1 will get out of the alpha stage? very much looking forward to upgrading, but only when its relatively stable. jeff Per Andreas Buer wrote: Hello Heikki, Heikki Tuuri [EMAIL PROTECTED] writes: I think a 'null' alter table operation: ALTER TABLE innodbtable TYPE=INNODB; does the defragmentation with just one build of the table. And I think it also preserves FOREIGN KEY constraints. Please test it! It did the job just fine. Thanks. -- Per Andreas Buer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Jeff Mathis, Ph.D. 505-955-1434 The Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb and fragmentation
At 12:40 PM -0600 9/19/03, Jeff Mathis wrote: this looks great. will this work with version 4.04 on solaris? as a side comment, anyone know when 4.1 will get out of the alpha stage? very much looking forward to upgrading, but only when its relatively stable. 4.0 went beta in 4.0.3, gamma in 4.0.6, stable in 4.0.12. 3.23 went beta in 3.23.20, gamma in 3.23.28, and stable in 3.23.31. 3.22: 3.22.4 / 3.22.14 / 3.22.17 So, it varies. It'll happen faster if people try the alphas and pound hard on them. :-) jeff Per Andreas Buer wrote: Hello Heikki, Heikki Tuuri [EMAIL PROTECTED] writes: I think a 'null' alter table operation: ALTER TABLE innodbtable TYPE=INNODB; does the defragmentation with just one build of the table. And I think it also preserves FOREIGN KEY constraints. Please test it! It did the job just fine. Thanks. -- Per Andreas Buer -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb and fragmentation
Paul DuBois wrote: At 12:40 PM -0600 9/19/03, Jeff Mathis wrote: this looks great. will this work with version 4.04 on solaris? as a side comment, anyone know when 4.1 will get out of the alpha stage? very much looking forward to upgrading, but only when its relatively stable. 4.0 went beta in 4.0.3, gamma in 4.0.6, stable in 4.0.12. 3.23 went beta in 3.23.20, gamma in 3.23.28, and stable in 3.23.31. 3.22: 3.22.4 / 3.22.14 / 3.22.17 So, it varies. It'll happen faster if people try the alphas and pound hard on them. :-) I wish we had the luxury. But, I may try and force the issue anyway. set up another instance. jeff -- Jeff Mathis, Ph.D. 505-955-1434 The Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb and fragmentation
Jeff, - Original Message - From: Jeff Mathis [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Saturday, August 23, 2003 12:20 AM Subject: Re: innodb and fragmentation does shutting down the database server and restarting it have the same effect? no. We were talking about table fragmentation in the tablespace, that is, in the ibdata files. But restarting mysqld has one nice side-effect: MySQL will calculate new index cardinality estimates for every InnoDB table it uses after the restart. The effect is the same as running ANALYZE TABLE (in MySQL versions 4.0.13, I think) on all InnoDB tables. That can help in optimization of some joins if your tables have changed a lot during the lifetime of the mysqld process. jeff Regards, Heikki Heikki Tuuri wrote: Per, I think a 'null' alter table operation: ALTER TABLE innodbtable TYPE=INNODB; does the defragmentation with just one build of the table. And I think it also preserves FOREIGN KEY constraints. Please test it! Best regards, Heikki Innobase Oy http://www.innodb.com InnoDB - transactions, foreign keys, and a hot backup tool for MySQL .. Subject: innodb and fragmentation From: Per Andreas Buer Date: Fri, 22 Aug 2003 09:39:58 +0200 Hi, We have an InnoDB database which is get quite fragmented. We defragment it about once a month, converting the table from innodb to myisam and back. After a defragmentation our database performance is more or less doubled. IO-strain is reduced with 50%. Would it be possible to have alter table foo no-op og alter table foo reindex or similar - so we could do this with only one conversion - not two? Are there any plans to implement index clustering or similar technology to battle this? (Would clustering help?) -- Per Andreas Buer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Jeff Mathis, Ph.D. 505-955-1434 The Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6 http://www.predict.com Santa Fe, NM 87505 -- 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: innodb and fragmentation
Per, I think a 'null' alter table operation: ALTER TABLE innodbtable TYPE=INNODB; does the defragmentation with just one build of the table. And I think it also preserves FOREIGN KEY constraints. Please test it! Best regards, Heikki Innobase Oy http://www.innodb.com InnoDB - transactions, foreign keys, and a hot backup tool for MySQL .. Subject: innodb and fragmentation From: Per Andreas Buer Date: Fri, 22 Aug 2003 09:39:58 +0200 Hi, We have an InnoDB database which is get quite fragmented. We defragment it about once a month, converting the table from innodb to myisam and back. After a defragmentation our database performance is more or less doubled. IO-strain is reduced with 50%. Would it be possible to have alter table foo no-op og alter table foo reindex or similar - so we could do this with only one conversion - not two? Are there any plans to implement index clustering or similar technology to battle this? (Would clustering help?) -- Per Andreas Buer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb and fragmentation
does shutting down the database server and restarting it have the same effect? jeff Heikki Tuuri wrote: Per, I think a 'null' alter table operation: ALTER TABLE innodbtable TYPE=INNODB; does the defragmentation with just one build of the table. And I think it also preserves FOREIGN KEY constraints. Please test it! Best regards, Heikki Innobase Oy http://www.innodb.com InnoDB - transactions, foreign keys, and a hot backup tool for MySQL .. Subject: innodb and fragmentation From: Per Andreas Buer Date: Fri, 22 Aug 2003 09:39:58 +0200 Hi, We have an InnoDB database which is get quite fragmented. We defragment it about once a month, converting the table from innodb to myisam and back. After a defragmentation our database performance is more or less doubled. IO-strain is reduced with 50%. Would it be possible to have alter table foo no-op og alter table foo reindex or similar - so we could do this with only one conversion - not two? Are there any plans to implement index clustering or similar technology to battle this? (Would clustering help?) -- Per Andreas Buer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Jeff Mathis, Ph.D. 505-955-1434 The Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDb and fragmentation
Sorry, - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, July 08, 2003 12:39 AM Subject: Re: InnoDb and fragmentation Mike, ... So how do I defrag the InnoDb file space so I can get it back up to speed? The simplest method is ALTER TABLE ... TYPE=MYISAM; ALTER TABLE ... TYPE=INNODB; I forgot to mention that the above method removes your FOREIGN KEY constraints in the table! If you have them, best to use mysqldump, and remember to put that SET FOREIGN_KEY_CHECKS=0 at the start of the dump files if you do not import the tables in the right order. ... Mike Best regards, Heikki Tuuri Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Transactions, foreign keys, and a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDb and fragmentation
Mike, - Original Message - From: mos [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Monday, July 07, 2003 5:54 PM Subject: InnoDb and fragmentation Heikki, Do InnoDb tables suffer from internal table fragmentation. (Is there any way to measure it?) I only mention this because of a problem I have with another database system (not MySQL). Some of its tables are hopelessly fragmented (800,000 file fragments for one table alone! Did I set a new record here?g). Since InnoDb creates its own file space, it is immune from OS fragmentation which is great (provided I defrag the drive before allocating InnoDb space), but what about internal fragmentation. If I continuously add/delete small temporary Innodb tables/rows, then add more rows to a table, delete rows etc., the table rows are no longer going to be contiguous. since InnoDB first allocates 32 pages individually to an index, and after that complete 1 MB 'extents', there should practically never be inter-table fragmentation. I mean, other tables never affect how fragmented a table is. Inside a single index, the worst possible fragmentation is that every page is only 50 % full and the pages are completely scattered in those 1 MB extents. Then a table can reserve many more extents than required, and table scans are slow because pages are not contiguous. InnoDB always tries to allocate an adjacent page in a B-tree page split, but often that is not possible. So how do I defrag the InnoDb file space so I can get it back up to speed? The simplest method is ALTER TABLE ... TYPE=MYISAM; ALTER TABLE ... TYPE=INNODB; The only way I can think of is to unload all the databases and then reload then back in which can take a bit of time. Am I worried about nothing? Or should this type of maintenance be done on a regular basis? TIA My feeling is that no defragmentation is normally needed unless you tend to run out of disk space. I'm using Win2k with NTFS volumes. Mike Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Transactions, foreign keys, and a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
