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

Bug #1366065 reported by Davi Arnaut on 2014-09-05
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Percona XtraBackup moved to https://jira.percona.com/projects/PXB
Fix Released
Medium
Alexey Kopytov
2.2
Fix Released
Medium
Alexey Kopytov
2.3
Fix Released
Medium
Alexey Kopytov

Bug Description

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.

Related branches

Davi Arnaut (davi) wrote :

--- 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) wrote :

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.

tags: added: contribution

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

Davi Arnaut (davi) on 2018-03-17
description: updated
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers