Currently, my MariaDB database server is running out of space, hence I am trying to delete old records (using multiple threads with DELETE FROM tbl WHERE index > ? and index < ?).

My understanding is that InnoDB will reuse the deleted rows (which are marked for reuse) for future newly inserted records - so effectively, the storage space growth should be slowed down.

However, it seems that my space is still increasing. Hence, a theory I have is that the purging process is slow - resulting in the deleted rows not being "marked for reuse" quickly.

My current History List Length value is 13836102 and still growing.

I have already configured innodb_purge_threads=32 and innodb_max_purge_lag=100000 - and it feels like that the purging process is still having a hard time to catch up.

Question :

1) When the History List Length gets too big (millions and beyond), does it really meant that I'm not effectively reusing the deleted rows, and consuming more OS disk space for newly inserted records?

2) Is the large History List Length value a concern? (does it eventually consume more-than-needed space, affects IOPS, etc?).

  • Please provide SHOW CREATE TABLE. Let's see the entire transaction, through the COMMIT. – Rick James Dec 16 '19 at 16:34

Your Answer

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

Browse other questions tagged or ask your own question.