Hello hackers, This is an attempt to resurrect the thread [1] to throttle WAL inserts before the point of commit.
Background: Transactions on commit, wait for replication and make sure WAL is flushed up to commit lsn on standby, when synchronous_commit is on. While commit is a mandatory sync/wait point, waiting for replication at some periodic intervals en route may be desirable/efficient to act as good citizen. Consider for example, a setup where primary and standby can write at 20GB/sec, while network between them can only transfer at 2GB/sec. Now if CTAS is run in such a setup for a large table, it can generate WAL very aggressively on primary, but can't be transferred at that rate to standby. Hence, there would be pending WAL build-up on primary. This exhibits two main things: - Fairness: new write transactions (even if single tuple I/U/D), and even read transactions (setting hint bits) would exhibit latency for amount of time equivalent to the pending WAL to be shipped and flushed to standby. - Primary needs to have space to hold that much WAL, since till the WAL is not shipped to standby, it can't be recycled, if replication slots are in use. Proposed solution (patch attached): - Global (backend local) variable wal_bytes_written to track the amount of wal written by the backend since the start of transaction or the last time SyncReplWaitForLSN() was called for this transaction. - Whenever we find wal_bytes_written exceeds the new wait_for_replication_threshold GUC, we set the control flag XlogThrottlePending (similar in spirit to LogMemoryContextPending), which is then handled at ProcessInterrupts() time. This is the mechanism proposed in [2]. Doing it this way avoids issues such as holding locks inside a critical section. - To do the wait itself, we rely on SyncRepWaitForLSN(), with the cached value of the WAL flush point. [1] https://www.postgresql.org/message-id/flat/CAHg%2BQDcO_zhgBCMn5SosvhuuCoJ1vKmLjnVuqUEOd4S73B1urw%40mail.gmail.com [2] https://www.postgresql.org/message-id/20220105174643.lozdd3radxv4tlmx%40alap3.anarazel.de Regards, Shirisha Broadcom Inc. -- This electronic communication and the information and any files transmitted with it, or attached to it, are confidential and are intended solely for the use of the individual or entity to whom it is addressed and may contain information that is confidential, legally privileged, protected by privacy laws, or otherwise restricted from disclosure to anyone else. If you are not the intended recipient or the person responsible for delivering the e-mail to the intended recipient, you are hereby notified that any use, copying, distributing, dissemination, forwarding, printing, or copying of this e-mail is strictly prohibited. If you received this e-mail in error, please return the e-mail to the sender, delete it from your computer, and destroy any printed copy of it.
v1-0001-WAL-throttling-mechanism-for-synchronous-replicat.patch
Description: Binary data
