You aren't signed in     Sign In    Help

Running OPTIMIZE. Jesus.

Running OPTIMIZE. Jesus. by jspaw.
Seriously. Gained back FORTY GB.

Why isn't there a 'show innodb fragmentation' command for MySQL?

Does anyone know if XtraDB or some other goodies can help with this? 

Comments

view profile

dathan  Pro User  says:

Nope, you just to run optimize from time to time. If you use 5.1 with the new file_format option that zlib compresses at the expense of 3% cpu hit (which the db's will not notice) you can keep data small basically a 2-3 times reduction in disk space consumption.


The problem is you can't backup your server with ibbackup since there is no 5.1 ibbackup for the new file format.
Posted 6 months ago. ( permalink )

view profile

jspaw  Pro User  says:

Bah! Annoying. :)

It's frustrating to only infer how much fragmentation the box has from indirect evidence. The only way you can really know how much fragmentation you have is to run an optimize, and I want to know whether or not to take the time and even run the optimize in the first place.
Posted 6 months ago. ( permalink )

view profile

dathan  Pro User  says:

Hmm thinking about this more there may be away.

Take the avg row size, multiply that by the number of rows * 1.7 (innodb overhead) + free space that show table status shows
Take the size of your datafile
that should give you an estimate on how fragmented the datafile is.

Also take into account that the data is stored in pages, so there is a bit of overhead on that as well.

For instance its common for 100 rows to exist in a page INNODB tries to keep the pages sequential at the expense of fragmentation.
Posted 6 months ago. ( permalink )

Would you like to comment?

Sign up for a free account, or sign in (if you're already a member).

[?]
view photos Uploaded on January 6, 2009
by jspaw

jspaw's photostream

WebOps Visualizations (Pool)

71
items

Tags

Additional Information

AttributionNoncommercial Some rights reserved Anyone can see this photo

Add to your map