From: Jay Newman <jaynewman001@gmail.com>
To: pgsql-admin@postgresql.org
Subject: pg_basebackup over slowed connection , slowed out of proportion
Date: Mon, 17 Dec 2012 20:01:24 -0800 (PST)
Message-ID: <1355803284214-5736998.post@n5.nabble.com> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
I have set up a PG9.2 database slightly over 110 GB in size. In addition to
the master server, I have 2 servers running as active standby nodes.
When I run pg_basebackup to set up the standby servers, it completes in
about 60 minutes - which is okay for my needs. However, I also need to set
up another standby server on the far end of a 100 MB link. As a dry run, I
have changed the interface settings on one of the 2 local standby servers so
that it runs at 100 MB, full duplex rather than 1 GB.
My expectation was that *if* the backup was primarily limited by network
speed, that it would take about 10 times as long - 600 minutes, or 10 hours.
Instead, the pg_basebackup was completing about 2 percent per hour, and hit
45 percent after running 24 hours. That puts the total backup time to over
50 hours, which is more than 50 times the original time, or 5 times more
than I would expect from having slowed the connection down.
To validate that the network connection was behaving itself, I ran a "dd"
before and after to an NFS mount; the results are reasonably close to the 10
x factor.
512000000 bytes (512 MB) copied, 7.10688 s, 72.0 MB/s
512000000 bytes (512 MB) copied, 87.2156 s, 5.9 MB/s
My first thought was that something else had changed, so I flipped the
network speed back to 1 GB and the backup once again ran within 60 minutes.
Can anyone please shed light on expected behavior for this process? Maybe
one or more parameters need to be adjusted in postgresql.conf to accomodate
this kind of network where there are both fast and slow replication
partners.
Below are the lines from the config - minus the lines which are just
comments.
listen_addresses = '*'
port = 5434
max_connections = 300
shared_buffers = 400MB
wal_level = hot_standby
synchronous_commit = local
checkpoint_segments = 200
archive_mode = on
archive_command = 'rsync -a %p /mnt/wal/db/%f'
max_wal_senders = 3
wal_keep_segments = 10000 # (Set high deliberately for the sake of
replication servers that might get behind) synchronous_standby_names =
'partner,rpartner'
hot_standby = on
log_destination = 'syslog'
logging_collector = on
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 0
syslog_facility = 'LOCAL4'
syslog_ident = 'postgres'
log_timezone = 'US/Pacific'
bytea_output = 'escape'
datestyle = 'iso, mdy'
timezone = 'US/Pacific'
lc_messages = 'en_US.UTF-8'
lc_monetary = 'en_US.UTF-8'
lc_numeric = 'en_US.UTF-8'
lc_time = 'en_US.UTF-8'
default_text_search_config = 'pg_catalog.english'
--
View this message in context: http://postgresql.1045698.n5.nabble.com/pg-basebackup-over-slowed-connection-slowed-out-of-proportio...
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: jaynewman001@gmail.com
Subject: Re: pg_basebackup over slowed connection , slowed out of proportion
In-Reply-To: <1355803284214-5736998.post@n5.nabble.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox