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-proportion-tp5736998.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.