Message-ID: <51ADD54F.3030702@saparev.com>
Date: Tue, 04 Jun 2013 14:53:51 +0300
From: Kouber Saparev <kouber@saparev.com>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:17.0) Gecko/20130329 Thunderbird/17.0.5
MIME-Version: 1.0
To: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Subject: Big UPDATE breaking replication
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

Hello,

We are using the 9.1 built-in streaming replication.

Recently our slave nodes fell behind because of an UPDATE statement. It
took about 3 minutes to execute, but it affected half a million records,
hence the replication broke with the "requested WAL segment ... has
already been removed" series of error messages.

The WAL settings we have are:

max_wal_senders = 6
wal_keep_segments = 60
max_standby_archive_delay = 300s


I guess increasing the wal_keep_segments value would prevent it from
happening in the future, but increase it with how much? What value would
be high enough?

Also we noticed some strange error message appearing shortly before and
after this same statement: "LOG:  out of file descriptors: Too many open
files; release and retry".

Could it be related somehow and what does it mean exactly?

Here's an excerpt from the master DB log:


May 30 12:23:09 DB1 postgres[28201]: [13-1] user=www,db=xxx LOG:  out of
file descriptors: Too many open files; release and retry
May 30 12:23:09 DB1 postgres[28201]: [13-2] user=www,db=xxx CONTEXT:
writing block 0 of relation base/2819385/2820788
May 30 12:23:09 DB1 postgres[28201]: [13-3] user=www,db=xxx STATEMENT:
UPDATE
May 30 12:23:09 DB1 postgres[28201]: [13-4] ^I              message
May 30 12:23:09 DB1 postgres[28201]: [13-5] ^I            SET
May 30 12:23:09 DB1 postgres[28201]: [13-6] ^I
sender_has_deleted=TRUE,
May 30 12:23:09 DB1 postgres[28201]: [13-7] ^I
receiver_has_deleted=TRUE
May 30 12:23:09 DB1 postgres[28201]: [13-8] ^I            WHERE
from_profile_sid=870

...

May 30 12:39:47 DB1 postgres[9053]: [2-1] user=postgres,db=[unknown]
FATAL:  requested WAL segment 00000001000002DE000000BD has already been
removed


Regards,
--
Kouber Saparev


-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin