Running OPTIMIZE. Jesus.

    Newer Older

    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?

    kellan, jtyost2, and R.B. Boyer added this photo to their favorites.

    1. dathan 75 months ago | reply

      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.

    2. jspaw 75 months ago | reply

      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.

    3. dathan 75 months ago | reply

      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.

    keyboard shortcuts: previous photo next photo L view in light box F favorite < scroll film strip left > scroll film strip right ? show all shortcuts