Bug #58287 Skip unallocated InnoDB data pages
Submitted: 18 Nov 2010 13:13 Modified: 3 Mar 2014 11:01
Reporter: Marko Mäkelä Email Updates:
Status: Closed Impact on me:
Category:MySQL Enterprise Backup Severity:S5 (Performance)
Version:all, 3.5 OS:Any
Assigned to: Ingo Strüwing CPU Architecture:Any

[18 Nov 2010 13:13] Marko Mäkelä
Currently, MySQL Enterprise Backup (formerly known as InnoDB Hot Backup or ibbackup) copies all data pages, including those that are marked free in the allocation bitmap pages. There is no need to read or copy these garbage pages. 

Five to six years ago, before Oracle acquired Innobase Oy and Sun/MySQL, I wrote an embedded backup solution for a company that skipped free pages. It was a 'cold backup', with a custom patch to the server that would suspend all InnoDB master thread activity during the backup process, but I do not see why this would not work in the context of hot backup (full or incremental).

How to repeat:
Use innodb_file_per_table=0. Create and populate lots of tables, and drop them. You should have a large system tablespace full of garbage pages. Watch the backup crawl.

Suggested fix:
When copying the data files, save the most recent bitmap page in memory and consult it to skip all pages that are marked free.

The bitmap page is every page_size pages. For uncompressed tablespaces, that would be on pages 0, 16384, 32768, 49152, 65536, and so on. For a compressed tablespace with a 2K page size, the bitmap would be on pages 0, 2048, 4096, and so on. The bitmap page always describes the following page_size pages.
[11 Jan 2011 21:20] Omer Barnir
triage: setting tag to EBMRTBD (next release)
[28 Feb 2014 15:52] Daniël van Eeden
Isn't this what the --skip-unused-pages does?

[3 Mar 2014 11:01] Ingo Strüwing
Right. And the mentioned feature is the fix for this "bug". The bug report has been forgotten to close. Sorry for that.