WL#10476: Defaults: Enable Hash Scans In RBR
Affects: Server-8.0
—
Status: Complete
Rationale ---------------- This change adds HASH_SCAN as an optional algorithm, which offers the best possible option when applying Row Based Replication (RBR) events on tables that have no viable index for uniquely identifying rows. With the HASH_SCAN algorithm we can still try and avoid doing a full-table scan for each event in such cases and instead search for the applicable row(s) using the generated hash table/map. There is no clear reason NOT to enable this. Server Changes: ---------------- slave-rows-search-algorithms = 'INDEX_SCAN,HASH_SCAN' : Enables the use of a hash table to avoid repeated table scans when no Primary Key or Primary Key Equivalent (non-null unique secondary index) exists on a table User Override ---------------- slave-rows-search-algorithms = 'TABLE_SCAN,INDEX_SCAN' : Disables potential use of the HASH_SCAN method Upgrade Story ---------------- There are no special upgrade considerations. Acceptance Criteria ---------------- 1. This setting should not cause any instability/failures in our automated tests. 2. This setting should not cause any performance regressions on average for all SysQA perf tests. 3. This setting should not cause any noticeable increase in memory usage.
FUNCTIONAL REQUIREMENTS: ======================= FR1: All MTR tests in rpl suite shall pass with slave-rows-search-algorithms = 'INDEX_SCAN,HASH_SCAN', GTID_MODE=ON. FR2: All MTR tests in rpl suite shall pass with slave-rows-search-algorithms = 'INDEX_SCAN,HASH_SCAN', GTID_MODE=OFF. FR3: All MTR tests in rpl_gtid and rpl_nogtid suite shall pass with slave-rows- search-algorithms = 'INDEX_SCAN,HASH_SCAN'. FR4: All MTR tests related to replication (having rpl_init.inc/master_slave.inc) in suites other than rpl suites shall pass with slave-rows-search-algorithms = 'INDEX_SCAN,HASH_SCAN' for both GTID-MODE ON and OFF. FR5: All MTR tests in Group_replication suite must pass with slave-rows-search- algorithms = 'INDEX_SCAN,HASH_SCAN'. FR6: All MTR tests in rpl suites must pass for slave-rows-search-algorithms = 'TABLE_SCAN,INDEX_SCAN'. NON-FUNCTIONAL REQUIREMENTS: =========================== NF1: This default setting should not cause any performance regressions on average for all SysQA perf tests. NF2. This default setting should not cause any noticeable increase in memory usage.
Copyright (c) 2000, 2024, Oracle Corporation and/or its affiliates. All rights reserved.