Hi,
I have a simple database table with 170MB overhead.
Is this something I need to worry about? When I run optimize on it, it tells me that innodb doesn't support optimize and so recreates the table but still has 170MB overhead.
Is this something I can comfortably ignore?
cheers!
Is this something I can comfortably ignore?
It depends. If everything fit in RAM, it's ok.
Which engine do You use ? mysql>show create table table_name; InnoDB supports optimize table.
Mysql 5.1 innodb supports optimize: http://dev.mysql.com/doc/refman/5.1/en/optimize-table.html
Mysql 5.0 innodb supports optimize: http://dev.mysql.com/doc/refman/5.0/en/optimize-table.html
MySQL 4.1 innodb supports optimize : http://dev.mysql.com/doc/refman/4.1/en/optimize-table.html
There's a lot of garbage advice surrounding when to optimize tables.
If by 'overhead' you mean free space, keep in mind that InnoDB naturally leaves pages on 93% full (15/16) to leave gaps for later updates.
When you optimize tables, it also does not actually recreate them in an optimal way. It recreates the table definition and then copies the data into it row-by-row. Primary key indexes are not fragmented this way - but secondary keys may be (since they may be inserted out of order causing pagesplits/fragmentation from day one).
Baron writes a good post on this here: http://www.xaprb.com/blog/2010/02/07/how-often-should-you-use-optimize-table/