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]
