[MDEV-18385] Document use cases and locking behavior for OPTIMIZE TABLE with innodb_defragment=1 Created: 2019-01-26  Updated: 2023-05-22

Status: Open
Project: MariaDB Server
Component/s: Documentation, Locking, Storage Engine - InnoDB
Fix Version/s: None

Type: Task Priority: Major
Reporter: Nuno Assignee: Ralf Gebhardt
Resolution: Unresolved Votes: 2
Labels: innodb

Issue Links:
PartOf
Relates
relates to MDEV-5834 Merge Kakao Defragmentation implement... Closed
relates to MDEV-11336 Enable defragmentation on 10.2 when t... Closed
relates to MDEV-30544 Deprecate innodb_defragment and relat... Closed
relates to MDEV-30545 Remove innodb_defragment and related ... Closed
relates to MDEV-30635 OPTIMIZE TABLE documentation is not m... Closed

 Description   

Using MariaDB 10.3, I've just tested the Defragmentation for the first time.

I added to my.cnf:
innodb_defragment = 1
.

And then, after restart, I tried "OPTIMIZE TABLE xxxx" on a frequently used table (lots of INSERTs per second), to see how it goes.

I see that pretty much every INSERT query was getting stuck waiting for locks to be released.
.

This doesn't seem to be an improvement over the original OPTIMIZE which creates a new temp table and then swaps, and consequently the table file will be much shorter.

While the new OPTIMIZE using defragmentation locks the table and the file won't get shorter.
.
.

Is it intentional that OPTIMIZE using defragmentation locks the table?

MDEV-9155 is marked as Fixed/Closed since April 2017, so I assumed this would not lock anymore.
.

Thanks!

 Comments   
Comment by Marko Mäkelä [ 2019-01-28 ]

jplindst, can you please comment on this? You reviewed and applied the original contribution MDEV-5834. How is the page latching supposed to work? How often are index locks acquired and released?

Comment by Patryk Pomykalski [ 2019-03-06 ]

For now you can at least document that it takes the table data lock and is basically useless and instead use ALTER TABLE ENGINE=InnoDB, ALGORITHM=INPLACE.

Comment by Geoff Montee (Inactive) [ 2019-09-25 ]

The following documentation pages may need to be updated:

Comment by Julien Fritsch [ 2021-09-22 ]

jacob.moorman from our discussion, I decided to reassign Kenneth's ticket to you?

Comment by Marko Mäkelä [ 2023-05-22 ]

The defragmentation will acquire an exclusive index latch potentially for extended periods of time. That, as we know from MDEV-14637, is a performance disaster even if it was done for shorter periods of time. It is true that the normal online table rebuild that is done by OPTIMIZE TABLE when innodb_defragment=1 is not set may cause more writes. However, it should minimize the impact to concurrent DML operations.

Generated at Thu Feb 08 08:43:42 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.