MySQL Bugs: #58287: Skip unallocated InnoDB data pages

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:	None
Category:	MySQL Enterprise Backup	Severity:	S5 (Performance)
Version:	all, 3.5	OS:	Any
Assigned to:	Ingo Strüwing	CPU Architecture:	Any

Description:
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.

triage: setting tag to EBMRTBD (next release)

Isn't this what the --skip-unused-pages does?

http://dev.mysql.com/doc/mysql-enterprise-backup/3.9/en/backup-capacity-options.html#optio...

Right. And the mentioned feature is the fix for this "bug". The bug report has been forgotten to close. Sorry for that.