On Wed, Nov 2, 2016 at 12:06 PM, Joao Junior <jcoj2006@gmail.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.