Re: BUG #7494: WAL replay speed depends heavily on the shared_buffers size

Oh, here is more context about this issue:
http://www.postgresql.org/message-id/201208171307.16342.andres@2ndquadrant.com

-- Valentin

On Wed, Aug 28, 2013 at 1:28 AM, Valentine Gogichashvili
<valgog(at)gmail(dot)com>wrote:

> Hello Andreas,
>
> it is more then a year now, that I have not returned to that topic.
>
> As we still have a problem with very slow WAL replay in situations, when
> we create and drop a lot of temporary tables, I have made a small synthetic
> test.
> I have build the simplest steaming replication using PostgreSQL 9.3rc1.
>
> My goal was to see, if the recovery process will be able to keep up with
> this WAL flow.
>
> Running this sproc on the master:
>
> CREATE OR REPLACE FUNCTION public.f()
> RETURNS integer
> LANGUAGE plpgsql
> AS $function$
> begin
>
> CREATE TEMP TABLE temp_table_to_test_replication AS
> SELECT s.i as id from generate_series(1, 100) as s(i);
> DROP TABLE temp_table_to_test_replication;
> RETURN 1;
> end;
> $function$
>
> leads to writing of WAL files. Is it an expected behavior? Is it expected
> that WAL files are filled when the only thing, that sproc is supposed to
> do is to create and drop a temporary table. Are these catalog changes?
>
> (I was calling it with:
>
> seq 1000 | xargs -l -n 1 -P 5 -I x psql -tA -c "select f();" > /dev/null
>
> for that call pg_stat_replication.sent_location moved from 0/21891CB0 to
> 0/21DFEC74 or 5689284 bytes
> )
>
>
> WAL files are also written when executing a stored procedure, that was
> writing into an UNLOGGED table:
>
> CREATE OR REPLACE FUNCTION public.s()
> RETURNS integer
> LANGUAGE plpgsql
> AS $function$
> begin
> INSERT INTO unlogged_test_table SELECT pg_backend_pid() FROM
> generate_series(1,100) as s(i);
> DELETE FROM unlogged_test_table WHERE id = pg_backend_pid();
> RETURN pg_backend_pid();
> end;
> $function$
>
> (WAL position moved from 0/21E51894 to 0/21E5B58C for 40184 bytes)
>
> Also as I recreated the same table as a LOGGED one, the WAL diff generated
> becomes 11668768 that is what I would expect.
>
> WAL files are not being generated when calling really read-only sprocs
> like now() :)
>
> Regards,
>
> Valentine Gogichashvili
>