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]
