Postgres Replaying WAL slowly - Mailing list pgsql-performance

Hello Everyone ...

We have 6 PG 9.1.12 installation, one master (Ubuntu 10.04), one slony slave(Ubuntu 10.04), and four streaming replica (2 on Ubuntu 10.04 and 2 on RHEL 6.5 (Santiago) which lies on different datacenter). All Ubuntu is on same datacenter. Master send wal archive to slony slave.

This is the infrastructure description :

200Mbit link between data centers, esx 5.5 on hp blade chassis. proliant gen 7 blades. postgres servers dedicated to esx hosts (no other vms on those esx hosts). 3par disk backends with 4 and 8 Gbit fiber channel connections. 10Gbit ethernet virtual connects on the hp chassis. cisco fabric and network switches.

All postgres installed from Ubuntu/RHEL package.

Everything works fine until on Thursday we have high load on master, and after that every streaming replica lag further behind the master. Even on night and weekend where all server load is low. But the slony slave is OK at all.

We thought it was due to network, so we decide to copy wal files to local of a streaming server, and replaying wal from local. After PG restart, it replays wal on a good speed about 3 seconds per wal file, but as the time goes the speed decreasing. We had 30 seconds per wal file. The worst we get is 3 minutes to replay 1 wal file.

The rate of wal produced from master is normal like usual. And also on Thursday we had wal files on pg_xlog on streaming replica server, but no other wal files.

This is the configuration :

SELECT name, current_setting(name)

  FROM pg_settings

  WHERE source NOT IN ('default', 'override');

              name              |                  current_setting

--------------------------------+---------------------------------------------------

 application_name               | psql

 archive_command                | /var/lib/postgresql/scripts/wal_archive "%p" "%f"

 archive_mode                   | on

 checkpoint_completion_target   | 0.7

 checkpoint_segments            | 30

 client_encoding                | UTF8

 DateStyle                      | ISO, MDY

 default_text_search_config     | pg_catalog.english

 effective_cache_size           | 125GB

 effective_io_concurrency       | 3

 external_pid_file              | /var/run/postgresql/9.1-main.pid

 hot_standby                    | on

 hot_standby_feedback           | on

 lc_messages                    | en_US.UTF-8

 lc_monetary                    | en_US.UTF-8

 lc_numeric                     | en_US.UTF-8

 lc_time                        | en_US.UTF-8

 listen_addresses               | *

 log_checkpoints                | on

 log_connections                | on

 log_destination                | csvlog

 log_directory                  | pg_log

 log_disconnections             | on

 log_filename                   | postgresql-%a.log

 log_line_prefix                | %t

 log_lock_waits                 | on

 log_rotation_age               | 1d

 log_rotation_size              | 0

 log_temp_files                 | 100kB

 log_timezone                   | localtime

 log_truncate_on_rotation       | on

 logging_collector              | on

 maintenance_work_mem           | 1GB

 max_connections                | 750

 max_locks_per_transaction      | 900

 max_pred_locks_per_transaction | 900

 max_stack_depth                | 2MB

 max_wal_senders                | 6

 port                           | 5432

 shared_buffers                 | 8GB

 ssl                            | on

 temp_buffers                   | 64MB

 TimeZone                       | America/Chicago

 unix_socket_directory          | /var/run/postgresql

 wal_keep_segments              | 50

 wal_level                      | hot_standby

 work_mem                       | 256MB

(47 rows)

Thanks for any help

Soni Maula Harriz