Running OPTIMIZE. Jesus.

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?

To take full advantage of Flickr, you should use a JavaScript-enabled browser and
install the latest version of the Macromedia Flash Player.

Comments

dathan 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 )

jspaw 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 )

dathan 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?

[?]

	jspaw's photostream
		0

	WebOps Visualizations (Pool)
		71 items

Additional Information

Some rights reserved Anyone can see this photo

Taken on January 6, 2009

3 people call this photo a favorite

Viewed 1,045 times

Add to your map

Yay location removed

...

(Some formats are OK.)

Or, remove it from the map.

Go to the map

You	Sign in \| Create Your Free Account
Explore	Places \| Last 7 Days \| This Month \| Popular Tags \| The Commons \| Creative Commons \| Search
Help	Community Guidelines \| The Help Forum \| FAQ \| Sitemap \| Help by Email

Running OPTIMIZE. Jesus.

Comments

dathan says:

jspaw says:

dathan says:

Would you like to comment?

jspaw's photostream

WebOps Visualizations (Pool)

Tags

Additional Information