Re: BUG #7494: WAL replay speed depends heavily on the shared_buffers size - Mailing list pgsql-bugs

From Valentine Gogichashvili
Subject Re: BUG #7494: WAL replay speed depends heavily on the shared_buffers size
Date
Msg-id CAP93muVWvK43JyMDYJ_ah6dTqxmADfhxM_xYfd7PKqBxNwQACg@mail.gmail.com
Whole thread Raw
In response to BUG #7494: WAL replay speed depends heavily on the shared_buffers size  (Valentine Gogichashvili <valgog@gmail.com>)
Responses Re: BUG #7494: WAL replay speed depends heavily on the shared_buffers size
List pgsql-bugs
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@gmail.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
>

pgsql-bugs by date:

Previous
From: Valentine Gogichashvili
Date:
Subject: Re: BUG #8404: JDBC block hot standby replication?
Next
From: Heikki Linnakangas
Date:
Subject: Re: BUG #8404: JDBC block hot standby replication?