WL#11348: Defaults: Increase Slave's Multi-Threaded Event Applier Buffer
Affects: Server-8.0
—
Status: Complete
EXECUTIVE SUMMARY ================= This worklog changes the default of slave_pending_jobs_size_max from 16M to 128M. USER STORIES ============ The default value for the variable being discussed here is: https://dev.mysql.com/doc/refman/8.0/en/replication-options-slave.html#option_mysqld_slave-pending-jobs-size-max It's not uncommon for users to have some large transactions mixed in with more "normal" smaller ones. If these large transactions are => slave_pending_jobs_size_max in size then they completely clog up the MTS applier pipeline and the slave will begin lagging. For example, see: https://forums.mysql.com/read.php?26,660062,660074#msg-660074 The current default of 16M is unnecessarily small given today's memory sizes and our other defaults. Given all of this, we should increase the default size from 16M to 128M.
FUNCTIONAL REQUIREMENTS ======================= FR1: The output of '@@global.slave_pending_jobs_size_max' is 128M by default. NON-FUNCTIONAL REQUIREMENTS =========================== NFR1: These default changes must not cause performance regression in SysQA perf tests.
1. the default of slave_pending_jobs_size_max is changed from 16M to 128M for avoiding some big transactions clog up the MTS applier pipeline and keeping high concurrency of slave workers, and it should be bigger than the default of max_allowed_packet. 2. Cross-version Replication Impact - No risk for cross-version replication. 3. Upgrade and Default values - No risk for upgrades on default configs (MTS is disabled). - Risk for upgrades for people with MTS enabled on default configs (memory usage may be a bit higher) - No risk for upgrades with people with MTS enabled and slave_max_pending_jobs_size set 4. Cross-platform Impact - No cross-platform impact 5. Resource Usage Side-effects - The max memory used by all applier worker queues combined together may grow up to 128 MB (Previous default was 12MB). NOTE: docs can adjust the manual if needed and eventually the user can calculate the resources it needs to deploy the MySQL server (taking into account slave_max_pending_jobs_size buffer size). 6. For the QA portion, we should have the standard SysQA tests run as has been done for all other 5.7 and 8.0 defaults change worklogs, to check for any unexpected performance regressions or other added instability.
Copyright (c) 2000, 2024, Oracle Corporation and/or its affiliates. All rights reserved.