Re: archive_command too slow.

On Wed, Nov 2, 2016 at 12:06 PM, Joao Junior <jcoj2006(at)gmail(dot)com> wrote:

> Hi friends,
>
> I am running 2 Linux machines, kernel 3.13.0-45-generic #74-Ubuntu SMP.
> Postgresql version 9.4 in both machine, in a Hot Standby cenario.
>
> Master-Slave using WAL files, not streaming replication.
>
> The archive_command from master is:
>
> archive_command = '/usr/bin/rsync -a -e "ssh" "%p"
> slave:/data2/postgres/standby/main/incoming/"%f"' #
>

How long does it take just to set up the ssh tunnel?

$ time ssh slave hostname

In my hands, this takes about 0.5, every time. If you need to archive 26
segments per minute, that much overhead is going to consume a substantial
fraction of your time budget.

How much network bandwidth do you have? If you scp a big chunk of files in
one command over to the slave (not into a production directory of it,of
course) how fast does that go?

$ time rsync datadir/pg_xlog/000000010000C9600000004? slave:/tmp/foo/

...

>
> It seems that archive_command is very slowly compared with the amount of
> WAL segments generated.
> Any suggestions??? Should I use another strategy to increase the
> archive_command process speed???
>

If network throughput is the problem, use compression, or get a faster
network.

If setting up the ssh tunnel is the problem, you could assess whether you
really need that security, or compile a custom postgresql with larger WAL
file sizes, or write a fancy archive_command which first archives the files
to a local directory, and then transfers them in chunks to the slave. Or
maybe use streaming rather than file shipping.

Cheers,

Jeff