Exporting tables is inefficient when backup contains a large (and unrelated) change buffer

[Davi Arnaut]

XtraBackup is quite inefficient at exporting tables from a backup when it contains a large change buffer (aka insert buffer) that is mostly composed of changes to tables other than the tables being exported.

The problem arises from the need to merge change buffer entries when exporting tables. In order to accomplish this, XtraBackup relies on a normal InnoDB shutdown that merges pending change buffer entries. The problem is that this background merging does random dives into the change buffer index (see ibuf_merge_pages), making the process rather inefficient if most of the entries in the change buffer are for tables that are not even part of the backup.

An easy solution is to simply discard all change buffer entries for an inexistent (deleted) tablespace once the first entry for such tablespace is found. Ideally and if possible, make export akin to FLUSH TABLES FOR EXPORT and do a fast shutdown.

[Davi Arnaut (davi)]

--- a/storage/innobase/buf/buf0rea.cc
+++ b/storage/innobase/buf/buf0rea.cc
@@ -810,11 +810,9 @@ buf_read_ibuf_merge_pages(
                if (UNIV_UNLIKELY(err == DB_TABLESPACE_DELETED)) {
 tablespace_deleted:
                        /* We have deleted or are deleting the single-table
- tablespace: remove the entries for that page */
+ tablespace: remove all entries for the tablespace */

- ibuf_merge_or_delete_for_page(NULL, space_ids[i],
- page_nos[i],
- zip_size, FALSE);
+ ibuf_delete_for_discarded_space(space_ids[i]);
                }
        }

[Davi Arnaut (davi)]

In a test scenario with a ibuf size 21645, export time went from 1m44 to 0m44.
In a production database with a much larger ibuf, export time went from 45m to 5m.

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PXB-695