RE: [PATCH] Speedup truncates of relation forks

On Tuesday, June 11, 2019 7:23 PM (GMT+9), Adrien Nayrat wrote:

> > Attached is a patch to speed up the performance of truncates of relations.
>
> Thanks for working on this!

Thank you also for taking a look at my thread.

> > 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 ;)
This is noted. Pasting it below:

create or replace function create_tables(numtabs int)
returns void as $$
declare query_string text;
begin
for i in 1..numtabs loop
query_string := 'create table tab_' || i::text || ' (a int);';
execute query_string;
end loop;
end;
$$ language plpgsql;

create or replace function delfrom_tables(numtabs int)
returns void as $$
declare query_string text;
begin
for i in 1..numtabs loop
query_string := 'delete from tab_' || i::text;
execute query_string;
end loop;
end;
$$ language plpgsql;

create or replace function insert_tables(numtabs int)
returns void as $$
declare query_string text;
begin
for i in 1..numtabs loop
query_string := 'insert into tab_' || i::text || ' VALUES (5);' ;
execute query_string;
end loop;
end;
$$ language plpgsql;

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

Thanks for the suggestion. I tried using the extension pg_wait_sampling,
But I wasn't sure that I could replicate the problem of blocked queries on standby server.
Could you advise?
Here's what I did for now, similar to my previous test with hot standby setup,
but with additional read queries of wait events on standby server.

128MB shared_buffers
SELECT create_tables(10000);
SELECT insert_tables(10000);
SELECT delfrom_tables(10000);

[Before VACUUM]
Standby: SELECT the following view from pg_stat_waitaccum

wait_event_type | wait_event | calls | microsec
-----------------+-----------------+-------+----------
Client | ClientRead | 2 | 20887759
IO | DataFileRead | 175 | 2788
IO | RelationMapRead | 4 | 26
IO | SLRURead | 2 | 38

Primary: Execute VACUUM (induces relation truncates)

[After VACUUM]
Standby:
wait_event_type | wait_event | calls | microsec
-----------------+-----------------+-------+----------
Client | ClientRead | 7 | 77662067
IO | DataFileRead | 284 | 4523
IO | RelationMapRead | 10 | 51
IO | SLRURead | 3 | 57

Regards,
Kirk Jamison