Just curious here, I emailed the list when I think it was
down about a problem I was having with Slony and replicating
a large database with large rows.

We are using DBMail for our Email store and I was replicating
it to another server with Slony.  The database is ~17G and
easily has records in the multi-meg size.

I started having problems where the CPU load was running around
5 consistently, and the database was almost 45G in size.  Since
everything was becoming unusable I stopped slony, and uninstalled
the nodes.  Once I did that, my process load went to <1 at almost
all times and my database shrunk back to a more normal 17G size.

Likewise on the slave database it dropped some in size, but a
VACUUM FULL was required to get it back to normal.  Also the
load on it dropped to almost nothing and more importantly the
RAM and Swap went way down on both servers.

So I'm wondering if this particular problem is what was
causing me issues with Slony, or if I have something else
wrong.  It seem to me that there must be some huge log space
that was taking up a lot of space, but when I uninstalled
the nodes that went away, so I can't look now.

This was a slow build up process.  Initially everything worked
pretty good, but after about a month of use, it was headed
downhill.

Right now I'm looking at a PITR solution to manage my backup
concerns and just stick with one DB server.  Any other thoughts
are appreciated.

--
David A. Niblett               | email: niblettda at gru.net
Network Administrator          | Phone: (352) 334-3400
Gainesville Regional Utilities | Web: http://www.gru.net/



-----Original Message-----
From: Jan Wieck [mailto:JanWieck at Yahoo.com] 
Sent: Sunday, November 20, 2005 10:52 AM
To: cbbrowne at ca.afilias.info
Cc: Slony-I Mailing List
Subject: Re: [Slony1-general] Re: Out of memory errors


On 11/19/2005 11:07 AM, cbbrowne at ca.afilias.info wrote:
>> I think I found the underlying cause. The buffer lines never shrink. 
>> They grow continuously to fit the largest string which has gone 
>> through them.  We use to see a gradual increase because the 
>> occassional long record would grow that buffer and eventually enough 
>> large records would be processed to exceed process virtual memory.  
>> The new problem is caused by adding a new table with lots of long 
>> lines which cause all the buffers to grow large and prevent even a 
>> single SYNC from being processed.
> 
> So it's a very strange sort of memory leak...
> 
>> I have a patch which frees the data string instead of resetting when 
>> it is above a certain size (I chose 32 KB).  There is a danger that a 
>> single fetch could be too big when lots of really huge rows come 
>> together.
> 
> 32K sounds reasonable; I'd be keen on seeing that patch.
> 
> The problem of the "single fetch" is one I have had in mind for a 
> while now.  I have created a would-be solution that *doesn't* work; I 
> think I'll have to try again.
> 
> My "doesn't work" solution is thus...
> 
> - When rows are inserted into sl_log_?, we count the width of the data 
> field (well, it's only approximate, but that's good enough for this
> purpose) and store it as sl_length
> 
> - The cursor that does the fetches is changed; we leave out the data 
> field, which means that the return set will remain compact, so it 
> would be pretty safe to arbitrarily do FETCH 1000 rather than the 
> present FETCH 100.
> 
> The processing is then different.  Within each FETCH, we loop thru, 
> looking at the length field.
> 
> We collect entries into a query set, one by one, until the aggregate 
> size breaks some barrier.  Let's say 10MB.  Then we pass that query 
> set to a set returning function which will pass back, in order, the 
> full data for the set of items requested.
> 
> Thus, if you have a lot of 11MB rows, those will be processed more or 
> less one by one.  That doesn't strike me as being likely to be 
> spectacularly inefficient; the extra transaction and query overhead 
> would be hidden nicely by the huge size of the row.
> 
> Unfortunately, the SRF (Set Returning Function) seems prone to do seq 
> scans on sl_log_1 *for each row!!!*  Performance winds up sucking 
> quite spectacularly :-(.
> 
> I have two thoughts on resolving the performance issue:
> 
> 1.  Rather than having the SRF querying for the rows individually, it 
> could simulate the 'outer' query, so it would try to pull all of them 
> at once.
> 
> 2.  We draw the sl_log_? entries for the given SYNC set into a temp 
> table, where an added sequence field, sl_seqno starts at 1 and goes up 
> to however many rows are associated with the SYNC.

You surely don't want to use temp tables (at all). Temp tables advance 
the OID counter and I don't want to see bug reports where slony is held 
accountable for OID wrapping.

I also think that forcibly getting every single however small log 
statement with a separate SELECT will be a really bad performance hit. 
Even with saved execution plans. What you probably want to do is to 
return the log data in a CASE block so that the field is only suppressed 
if the length actually exceeds your limit.

> 
> Application becomes dead simple; the "outer" query becomes "select 
> sl_seqno, sl_length from sl_log_temp", and all we need track, in the 
> inner loop, is  how sl_seqno progresses.  (Which obviously needs an 
> index.)

Don't forget that selecting, fetching and filling the log into the 
memory buffers is done in one thread, while applying them on the local 
DB is done in another. You need to coordinate those two, because it 
means that the helper thread does the malloc() and when it exceeds the 
limit it has to wait until the worker thread did the free().


Jan

-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck at Yahoo.com #
_______________________________________________
Slony1-general mailing list
Slony1-general at gborg.postgresql.org
http://gborg.postgresql.org/mailman/listinfo/slony1-general