On Tue, Jun 11, 2019 at 07:34:35AM +0000, Jamison, Kirk wrote:
Hi all,
Attached is a patch to speed up the performance of truncates of relations.
This is also my first time to contribute my own patch,
and I'd gladly appreciate your feedback and advice.
Thanks for the patch. Please add it to the commitfest app, so that we
don't forget about it: https://commitfest.postgresql.org/23/
A. Summary
Whenever we truncate relations, it scans the shared buffers thrice
(one per fork) which can be time-consuming. This patch improves
the performance of relation truncates by initially marking the
pages-to-be-truncated of relation forks, then simultaneously
truncating them, resulting to an improved performance in VACUUM,
autovacuum operations and their recovery performance.
OK, so essentially the whole point is to scan the buffers only once, for
all forks at the same time (instead of three times).
B. Patch Details
The following functions were modified:
1. FreeSpaceMapTruncateRel() and visibilitymap_truncate()
a. CURRENT HEAD: These functions truncate the FSM pages and unused VM
pages.
b. PATCH: Both functions only mark the pages to truncate and return a
block number.
- We used to call smgrtruncate() in these functions, but these are now
moved inside the RelationTruncate() and smgr_redo().
- The tentative renaming of the functions are:
MarkFreeSpaceMapTruncateRel() and visibilitymap_mark_truncate(). Feel free to
suggest better names.
2. RelationTruncate()
a. HEAD: Truncate FSM and VM first, then write WAL, and lastly truncate
main fork.
b. PATCH: Now we mark FSM and VM pages first, write WAL, mark MAIN fork
pages, then truncate all forks (MAIN, FSM, VM) simultaneously.
3. smgr_redo()
a. HEAD: Truncate main fork and the relation during XLOG replay, create
fake rel cache for FSM and VM, truncate FSM, truncate VM, then free fake rel
cache.
b. PATCH: Mark main fork dirty buffers, create fake rel cache, mark fsm
and vm buffers, truncate marked pages of relation forks simultaneously,
truncate relation during XLOG replay, then free fake rel cache.
4. smgrtruncate(), DropRelFileNodeBuffers()
- input arguments are changed to array of forknum and block numbers, int
nforks (size of forkNum array)
- truncates the pages of relation forks simultaneously
5. smgrdounlinkfork()
I modified the function because it calls DropRelFileNodeBuffers. However, this
is a dead code that can be removed.
I did not remove it for now because that's not for me but the community to
decide.
You really don't need to extract the changes like this - such changes
are generally obvious from the diff.
You only need to explain things that are not obvious from the code
itself, e.g. non-trivial design decisions, etc.
C. Performance Test
I setup a synchronous streaming replication between a master-standby.
In postgresql.conf:
autovacuum = off
wal_level = replica
max_wal_senders = 5
wal_keep_segments = 16
max_locks_per_transaction = 10000
#shared_buffers = 8GB
#shared_buffers = 24GB
Objective: Measure VACUUM execution time; varying shared_buffers size.
1. Create table (ex. 10,000 tables). Insert data to tables.
2. DELETE FROM TABLE (ex. all rows of 10,000 tables)
3. psql -c "\timing on" (measures total execution of SQL queries)
4. VACUUM (whole db)
If you want to test with large number of relations,
you may use the stored functions I used here:
http://bit.ly/reltruncates
D. Results
HEAD results
1) 128MB shared_buffers　=　48.885 seconds
2) 8GB shared_buffers = 5 min 30.695 s
3) 24GB shared_buffers = 14 min 13.598 s
PATCH results
1) 128MB shared_buffers = 42.736 s
2) 8GB shared_buffers = 2 min 26.464 s
3) 24GB shared_buffers = 5 min 35.848 s
The performance significantly improved compared to HEAD,
especially for large shared buffers.
Right, that seems nice. And it matches the expected 1:3 speedup, at
least for the larger shared_buffers cases.
Years ago I've implemented an optimization for many DROP TABLE commands
in a single transaction - instead of scanning buffers for each relation,
the code now accumulates a small number of relations into an array, and
then does a bsearch for each buffer.
Would something like that be applicable/useful here? That is, if we do
multiple TRUNCATE commands in a single transaction, can we optimize it
like this?
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
