Bug #105406 MySQLbackup component doesn't have purge interface
Submitted: 2 Nov 2021 6:14 Modified: 2 Nov 2021 7:29
Reporter: Rahul Malik Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Enterprise Backup Severity:S3 (Non-critical)
Version:8.0.27 OS:Any
Assigned to: CPU Architecture:Any
Tags: Contribution, page-tracking mysqlbackup_component

[2 Nov 2021 6:14] Rahul Malik
Description:
MySQL backup component has not exposed the purge UDF to purge the old pages not required to user.

It can be useful to reclaim the disk space when. 
1) user choose to restart page tracking feature.
2) User is sure he would not required list of pages older than some LSN. 

So it would be useful to  provide purge interface to remove old files from #ib_archive to reclaim the disk space.

How to repeat:
select mysqlbackup_page_track_set(1);

create table t1(i int);

select mysqlbackup_page_track_set(0);

select mysqlbackup_page_track_set(1);

once page-tracking file is restarted old files will not be used and are candidate for purge.

Suggested fix:
expose the purge interface . PFA patch to add new interface in MySQL
[2 Nov 2021 6:15] Rahul Malik
patch to add purge interface

Attachment: mysqlbackup_purge_interface.patch (application/octet-stream, text), 7.12 KiB.

[2 Nov 2021 6:38] MySQL Verification Team
Hello Rahul,

Thank you for the report and contribution.

regards,
Umesh
[2 Nov 2021 6:39] MySQL Verification Team
Hi Rahul,

Thank you very much for your patch contribution, we appreciate it!

Please note that in order for us to continue the process of reviewing your contribution to MySQL, please send us a signed copy of the Oracle Contributor Agreement (OCA) as outlined in http://www.oracle.com/technetwork/community/oca-486395.html

Signing an OCA needs to be done only once and it's valid for all other Oracle governed Open Source projects as well.

Getting a signed/approved OCA on file will help us facilitate your contribution - this one, and others in the future.  

Please let me know, if you have any questions.

Thank you for your interest in MySQL.

regards,
Umesh
[2 Nov 2021 7:29] Rahul Malik
Thank Umesh, 

OCA http://www.oracle.com/technetwork/community/oca-486395.html link is not working.
[2 Nov 2021 10:12] MySQL Verification Team
Thank you, Rahul.
Surely link is broken, I'll notify concern team and get back to you. Thank you!

regards,
Umesh
[2 Nov 2021 14:53] MySQL Verification Team
Hi Rahul,

This is the updated/correct link https://oca.opensource.oracle.com/
Please do revert if you still face any issues. Thank you!

regards,
Umesh
[11 Nov 2021 10:10] Rahul Malik
patch for myslbackup purge interface

Attachment: mysqlbackup_purge_interface.patch (application/octet-stream, text), 7.12 KiB.

[11 Nov 2021 10:12] Rahul Malik
I have changed my email id to official email id on which OCA is signed by my employer. Can you please check if anything is required from my end.
[11 Nov 2021 10:33] MySQL Verification Team
Sure, I'll notify concern people on this and let you know if anything further needed. Thank you.
[15 Nov 2021 12:43] MySQL Verification Team
Hi Rahul,

Our community team manager confirmed that your @percona.com account is covered by Percona company OCA, so you can contribute without any issues. Thank you!

regards,
Umesh
[18 Nov 2021 14:38] Ingo Strüwing
Posted by developer:
 
For the documentation:

We do have a section "Incremental Backup Using Page Tracking" in the reference manual. This needs to be extended by something like this:

When you activate page tracking with SELECT mysqlbackup_page_track_set(true); a file is created under the server's datadir, which collects information about changed pages. This file keeps growing until page tracking is deactivated with SELECT mysqlbackup_page_track_set(false);. If the server is stopped and restarted a new file is opened, but the old file(s) is not purged. As long as the page tracking is not deactivated explicitly, the file(s) grow.

To get rid of old page-track data, that is not needed any more, the following procedure is recommended: Before every *full* backup, do:

SELECT mysqlbackup_page_track_set(false);
SELECT mysqlbackup_page_track_purge_up_to(9223372036854775807);
SELECT mysqlbackup_page_track_set(true);

The big number is the highest possible LSN. It causes all page tracking files to be purged.
[10 Jan 2022 16:09] Daniel So
Posted by developer:
 
Added the following entry to the MEB 8.0.28 changelog: 

"Old page tracking files on the server created by the mysqlbackup component can now be purged by a new loadable function, mysqlbackup_page_track_purge_up_to(). See Incremental Backup Using Page Tracking for details. Thanks to Rahul Malik for contributing to this feature."

The manual has also been updated with a description for the new feature.