Re: [PATCH] Speedup truncates of relation forks

On 6/11/19 9:34 AM, Jamison, Kirk wrote:
> Hi all,
>
> Attached is a patch to speed up the performance of truncates of relations.
>

Thanks for working on this!

>
> *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

You should post these functions in this thread for the archives ;)

>
> *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.
>

From a user POW, the main issue with relation truncation is that it can block
queries on standby server during truncation replay.

It could be interesting if you can test this case and give results of your path.
Maybe by performing read queries on standby server and counting wait_event with
pg_wait_sampling?

Regards,

--
Adrien