Slony1 PostgreSQL Replication - Long running TX causing out of memory errors...

Author

Long running TX causing out of memory errors...

Philip Warner

2005-09-13, 8:24 pm

It looks like my "out of memory" errors may have been caused by long
running txs. At least I have one at the moment that seems to be; there
is a single xid in sl_log_1 that has 45000 rows. The slon process dies
every time it tries to process it.

Is there a way around this? Is the slon process reading the whole tx
into memory?

Any help would bre appreciated...

FWIW, the counts by XID from sl_log_1 are:

log_origin | log_xid | count
------------+---------+-------
3 | 4795427 | 45228
3 | 4795451 | 6
3 | 4795452 | 6
3 | 4795481 | 6
3 | 4795501 | 6
3 | 4795505 | 6
.....etc for 8000 more rows.

The large single xid has about 1.5GB of data and was caused by doing an
"insert...select..." statement.

Can slon be hacked to avoid buffering so much in memory?

Christopher Browne

2005-09-14, 11:23 am

Philip Warner <pjw- Ig6Zz+cC40D0CCvOHzKK
cA@public.gmane.org> writes:
> It looks like my "out of memory" errors may have been caused by long
> running txs. At least I have one at the moment that seems to be; there
> is a single xid in sl_log_1 that has 45000 rows. The slon process dies
> every time it tries to process it.
>
> Is there a way around this? Is the slon process reading the whole tx
> into memory?
>
> Any help would bre appreciated...
>
> FWIW, the counts by XID from sl_log_1 are:
>
> log_origin | log_xid | count
> ------------+---------+-------
> 3 | 4795427 | 45228
> 3 | 4795451 | 6
> 3 | 4795452 | 6
> 3 | 4795481 | 6
> 3 | 4795501 | 6
> 3 | 4795505 | 6
> ....etc for 8000 more rows.
>
> The large single xid has about 1.5GB of data and was caused by doing an
> "insert...select..." statement.
>
> Can slon be hacked to avoid buffering so much in memory?

I don't see any easy "slon hack" here; I'm not so sure that the
problem is actually within slon; it may be that the big transaction is
causing the backend to fall over, in which case trying to "fix" slon
would be a futile exercise...

The approach that comes to mind is more of a "data hack"; to chop out
that XID and apply it via "hacked" means.

Something like...

select log_actionseq,
case
when log_cmdtype = 'I' then 'insert into ' || c.relname || ' '
when log_cmdtype = 'U' then 'update only ' || c.relname || ' set '
when log_cmdtype = 'D' then 'delete from only ' || c.relname || ' where '
end || log_cmddata as query
into keep_4795427_queries

from _someschema.sl_log_1, _someschema.sl_table t, pg_class c
where
log_xid = '4795427' and
t.tab_id = log_tableid and
c.oid = t.tab_reloid;

Then you can dump the table keep_4795427_queries
, which will consist
of the series of action IDs and the insert/delete/update queries.

It's a loose sort of "log shipping log."

Copy that to stdout, sort it (to get it in actionid order), and you
have something you can apply in as many pieces as you wish.

Delete from sl_log_1 where log_xid = '4795427' and you're done :-).

I daresay this update is Not Entirely Safe; there is *some* risk that
other updates in the same SYNC could interfere due to the fact that
they are being applied in a somewhat different order. In effect,
those updates are taking place AFTER the big 1.5GB set of updates.

On the other hand, reality is that this 1.5GB set of updates probably
took a considerable period of time to apply; it probably was mostly
"in effect" before any of the other updates that arrived during that
SYNC. So while not "safe," there's some reason to expect it not to be
"totally disagreeable."
--
select 'cbbrowne' || '@' || 'ca.afilias.info';
<http://dev6.int.libertyrms.com/>
Christopher Browne
(416) 673-4124 (land)

Philip Warner

2005-09-15, 3:24 am

Christopher Browne wrote:

>
>
>I don't see any easy "slon hack" here; I'm not so sure that the
>problem is actually within slon; it may be that the big transaction is
>causing the backend to fall over, in which case trying to "fix" slon
>would be a futile exercise...
>
>
It's the slon worker process that dies by exhausting its memory. It
seems to want to read the entire xid's worth of log entries into memory
while applying them in background, and since reading is a lot faster
than writing that translates to > 1.5GB memory for the one process. I've
logged a bug on this, since it seems reasonable to vbe able to ask slon
to either not get too far ahead of itself or set a limit on the total
buffer size it uses.

>The approach that comes to mind is more of a "data hack"; to chop out
>that XID and apply it via "hacked" means.
>
>
Thanks for this.

Jim Archer

2005-09-15, 3:24 am

--On Thursday, September 15, 2005 11:41 AM +1000 Philip Warner
<pjw- Ig6Zz+cC40D0CCvOHzKK
cA@public.gmane.org> wrote:

> It's the slon worker process that dies by exhausting its memory. It
> seems to want to read the entire xid's worth of log entries into memory
> while applying them in background, and since reading is a lot faster

What happens to the data on the origin when this happens? Is there any
corruption? How would we recover from this? Do we have to rebuild the
nodes or just restart the slon?

Philip Warner

2005-09-15, 3:24 am

Jim Archer wrote:

>
>
>
> What happens to the data on the origin when this happens? Is there
> any corruption? How would we recover from this? Do we have to
> rebuild the nodes or just restart the slon?

Origin seems fine; everything just rolls back & aborts.

No corruption that I have seen.

Recovery is impossible without changes to slon (or deleting the big log
entry, but that's got its own problems).

Restarting slon does not help (except in some cases where is was
restoring a group of events, and can get through some of them each time,
apparently).

I have only taken a basic look at the slon code, but it seems the basic
process is:

thread type A: read all pending logs into memory (from all source nodes?)
thread type B: process in-memory logs to (single?) target DB.

(not sure if it does all source DBs at one time or in sequence).

What probably needs to happen is have a pair of threshold: 'empty'
threshold and 'full' threshold; when the type A thread reaches the
'full' threshold (in terms of current loaded log queue length), it
pauses (maybe finishes source TX, not sure). When type B thread reaches
'empty' threshold, it wakes up thread 'A'. So, in my case, these might
be set to empty=50 and full=100. Alternatively, the same basic approach
could be applied to buffer sizes rather than queue length: when the
total allocated (used?) buffer size exceeds the 'full' amount, thread A
pauses, when it drops below the 'empty' amount, thread A starts.

Jim Archer

2005-09-15, 3:24 am

--On Thursday, September 15, 2005 1:29 PM +1000 Philip Warner
<pjw- Ig6Zz+cC40D0CCvOHzKK
cA@public.gmane.org> wrote:

> Origin seems fine; everything just rolls back & aborts.
>
> No corruption that I have seen.
>
> Recovery is impossible without changes to slon (or deleting the big log
> entry, but that's got its own problems).
>
> Restarting slon does not help (except in some cases where is was
> restoring a group of events, and can get through some of them each time,
> apparently).

Hi Philip and thanks for the reply. I have been doing testing and have had
a cluster running for several days now, but not with any really big
transactions. My use does occasionally have very big transactions, bigger
than the 8,000 records I think you said you tried. I guess I'll be doing
some more testing before installing this on my production machines.

Thanks very much for the heads up!

Jim

Philip Warner

2005-09-15, 3:24 am

Jim Archer wrote:

> -I have been doing testing and have had a cluster running for several
> days now, but not with any really big transactions. My use does
> occasionally have very big transactions, bigger than the 8,000 records
> I think you said you tried. I guess I'll be doing some more testing
> before installing this on my production machines.
> Thanks very much for the heads up!

No problem; it's not the number of records, its the total size of the
records (eg. we can have a single update that has more than 20MB of
data). I think we've been blowing the process address space limit of
500MB because of the unctrolled reads the slon process does.

Sponsored Links