0

I try to defragment table using innodb-defragment=1 https://mariadb.com/kb/en/library/defragmenting-innodb-tablespaces/

But it was a bad experience : - locks on table - no a good defragmentation result (always data free on table)

So I use percona-tools https://www.percona.com/doc/percona-toolkit/LATEST/pt-online-schema-change.html

And get a good result no lock et no more data free on table. (But need space on disk to duplicate table)

But what's wrong with the first method ?

UPDATE :

enter image description here

  • That .png only brings up more questions -- I can think of 3 reasons for the huge (6.9G Data_free): Was that file_per_table or not? Did you recently delete most of the rows? Does the table have lots of Partitions? – Rick James Jan 8 at 16:06
  • (My Answer goes into details, but here is the straight answer.) That method does not relase space to the OS; it only defrags. I suggest it is mostly useless. And, assuming it was after a massive delete, I suggest a better way. – Rick James Jan 8 at 16:28
3

Myth-busters.

Defragmentation is a concept that mostly does not apply to InnoDB. It is rarely worth the effort to use OPTIMIZE TABLE or other techniques to defrag.

There are several ways in which InnoDB fragments and/or has "free" space:

  • The rows in a block not filling up the block.
  • BTree blocks gravitate to 69% full. If you will insert new rows at random spots, this is sort of the limit of the intra-block waste.
  • The blocks in an 'extent' not consuming the entire extent.
  • Undo/redo records temporarily consuming space.
  • Data and Indexes are each BTrees, with various 'free' space.

SHOW TABLE STATUS shows only part of the "free" space. Furthermore, it will usually indicate 4/5/6/7MB as free for non-small tables because of reserving space in an 'extent' for future action.

Why defrag?

  • Defragmentation provides only a tiny benefit to performance; so this is rarely a valid reason.
  • If you are almost out of disk space, defragging may keep you out of trouble briefly. But if the defrag action requires copying the table/tablespace, then you may not have room to do it. Catch-22. Well, OK, MariaDB's feature avoids the copy. But will it shrink the size of the tablespace file, thereby giving space back to the OS?
  • InnoDB already does some defragmentation -- by combining adjacent somewhat-empty blocks automatically (under certain circumstances).

(Looking at TABLE STATUS provided)

It shows 0.9GB "data" but a huge 6.9GB "free". (I assume that STATUS was taken after an OPTIMIZE?) This can exist in multiple situations:

  • innodb_file_per_table = OFF, in which case, the "free" is how much free space is in ibdata1, and has nothing to do with the table in question. (You say this was not your case.)
  • The table has a thousand PARTITIONs. Each partition always shows some "free" space; it could add up to 6.9GB. (One reason why PARTITIONing may not be wise to pursue.)
  • You recently DELETEd most of the table's rows. This was probably a very slow process. It would have been faster to create a new table, copy over the rows to keep, then rename to put the table in place. And, guess what, this approach would have given you 'optimize' for free.

The defrag feature in MariaDB, according to the manual:

Note that tablespace files (including ibdata1) will not shrink as the result of defragmentation, but one will get better memory utilization in the InnoDB buffer pool as there are fewer data pages in use.

So...

  • It does not take extra disk space while defragmenting. This can be very important if you are too low on disk space.
  • It fails to give the space back to the OS, even for file_per_table.
  • It apparently does not prevent accesses to the table during the defragmenting. (Or at worst does it in quick bursts.)
  • It's an interesting response but it deals with 3Go of free space on a 6Go table (data+ index). But in fact the optimize table method doesn't shrink the file. But it depends of table structure and usage and indexes, in some particulars cases I think that it good to defragmente some time... – Mr_Thorynque Jan 3 at 9:28
  • @Mr_Thorynque - Which file? If you are talking about ibdata1, true. It is very difficult to release free space from that file to the OS. That's way innodb_file_per_table was invented. If the table(s) are PARTITIONed, there is another sloppiness in the "free" space for it. – Rick James Jan 3 at 15:40
  • it is configured as innodb_file_per_table – Mr_Thorynque Jan 4 at 13:55
  • @Mr_Thorynque - Please provide SHOW TABLE STATUS for that table. – Rick James Jan 4 at 22:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.