Defragment InnoDB table on MariaDB 10.0

We ported defragmentation feature of Facebook MySQL 5.6 to MariaDB 10.0.
(This feature only support XtraDB of MariaDB 10.0, InnoDB not yet support.)

Facebook patch

Timer support

https://github.com/facebook/mysql-5.6/commit/c75a413edeb96eb99bf11d7269bdfea06f96d6b6

Defragmentation feature

https://github.com/facebook/mysql-5.6/commit/a2d3a747426735c3a5f5feca8af8607f8acdc5a6
https://github.com/facebook/mysql-5.6/commit/def96c83fead34107d4122ec77364010d0edfa00
https://github.com/facebook/mysql-5.6/commit/9c67c5db5de056672df27e0cc995af652ad353da
https://github.com/facebook/mysql-5.6/commit/921a81b707673bc5bec324e17eb597b12e2232a6
https://github.com/facebook/mysql-5.6/commit/aa519bd44c16d8081d1ace194b383c1983794dd7
https://github.com/facebook/mysql-5.6/commit/fea7d13ca457ea7bbb38a2b36ae418c57c110139
https://github.com/facebook/mysql-5.6/commit/09b29d305cf380ecb241f163e1fbd68749688572
https://github.com/facebook/mysql-5.6/commit/9284abb38d6f968582dbd83a67d137ce7a7bd9c7
https://github.com/facebook/mysql-5.6/commit/dbd623df3a6b25fd8eb0216d469444d111e1de3c
https://github.com/facebook/mysql-5.6/commit/aed55dc4385d0a7c92eaca4d8db2f12c916dc4ab
https://github.com/facebook/mysql-5.6/commit/aad5c82ff8a5d40c84df5018365230da90e892a8

You can reference more detailed information about this feature from above facebook github sites.

Expectations

1) Increasing page fill factor and scan efficiency
2) Make free pages and recycling it without allocating new spaces

System variables (All system variables are DYNAMIC and GLOBAL)

• innodb_defragment (ON | OFF), DEFAULT : ONControl whether using innodb defragmentation feature or not.Changing innodb_defragment=OFF will pause any ongoing defragmentation task. And paused defragmentation task will be proceeded when innodb_defragment is set as ON.ALTER TABLE .. DEFRAGMENT .. statement will fail when innodb_defragment=OFF.

• innodb_defragment_fill_factor (0.7 ~ 1.0), DEFAULT : 0.9
• innodb_defragment_fill_factor_n_recs (1 ~ 100), DEFAULT : 20Both system variables control how many rows would be stored in one page (It's controlling fill factor of data page).Also controlling how much space will be reserved for future usage.
  reserved_space = min(page_size * (1 - innodb_defragment_fill_factor), average_record_size * innodb_defragment_fill_factor_n_recs);
  In other words, you can control fill factor of pages with both row count and percentage of page size.

• innodb_defragment_frequency (1 ~ 1000), DEFAULT : 40 (Facebook default is 100, But it's too high on commodity server)Control how fast defragmentation thread processing merge task. Innodb defragment thread will do merge this many times in a second.If innodb_defragment_frequency=100 and innodb_defragment_n_pages=7 then server have to process 700 disk reads (without considering innodb buffer pool).You should increase this system variable(it's dynamic so you can change it whenever you want) upto 1000 when data is stored in fast ssd. 

• innodb_defragment_n_pages (2 ~ 32), DEFAULT : 7Defragment thread will merge all records from this many contigous pages to target page.

• innodb_defragment_stats_accuracy (0 ~ ), DEFAULT : 0
  This patch introduce some columns(n_page_split, n_pages_freed, n_leaf_pages_defrag, n_leaf_pages_reserved) on mysql.innodb_index_stats table.
  This informations will be used for defragmentation efficiency and making decision whether defragment is needed or not.
  And this columns' value is not exact value. it's approximate.
  innodb_defragment_stats_accuracy control how often this statistics will be refreshed.

• n_leaf_pages_defrag
  n_leaf_pages column of mysql.innodb_index_stats table is refreshed when over 10% of rows are changed.
  But n_leaf_pages_defrag is updated when other defragmentation statistics are updated (So it's more accurate than n_leaf_pages column).

• n_pages_freed
  How many pages are freed by last defragmentation task. It's only updated defragmentation task is done.

• n_page_split
  The number of page split is stored based on innodb_defragment_stats_accuracy system variable.
  And this value will be reset to 0 after defragmentation.

• n_leaf_pages_reserved
  This column store how many free pages are there.
  You can make decision whether defragmentation is need or not with this column and n_leaf_pages.

Status variables

• Innodb_defragment_count
  The number of btr_defragment_n_pages call for defragmentation.

• Innodb_defragment_failures
  The number of btr_defragment_n_pages could not make free page.

• Innodb_defragment_compression_failures
  The number of compression fail caused by defragmentation.

Defragmentation efficienty = (Innodb_defragment_count - Innodb_defragment_failures) * 100 / Innodb_defragment_count

Defragmentation syntax

-- // Defragment whole table (basic)
ALTER TABLE tb_t10 DEFRAGMENT;

-- // Defragment single index (Primary key name is 'PRIMARY')
ALTER TABLE tb_t10 DEFRAGMENT INDEX PRIMARY;
ALTER TABLE tb_t10 DEFRAGMENT INDEX ix_secondary_index;

-- // Run defragment as async commit mode (ASYNC_COMMIT)
ALTER TABLE tb_t10 DEFRAGMENT ASYNC_COMMIT;
ALTER TABLE tb_t10 DEFRAGMENT INDEX ix_secondary_index ASYNC_COMMIT;

Defragmentation task does not need any long time exclusive lock. so it could be used on online-serviced MariaDB (if there's free system resource like disk or cpu)

Download

https://github.com/kakao/mariadb-10.0/commit/3c22ca303dbe7168a52c55e0a7362e6a6982c3f4