On Sunday 20 November 2005 07:51, Jan Wieck wrote:
> 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.

There is another issue (at least in 7.3) with temp tables,  they cause index 
bloat in pg_attribute and pg_class, to the point of the only way to recover 
performance is to bring the DB up in single user mode and reindex evetryhing.  
My experience with that has been less than enjoyable and I find the best 
solution ends up being to drop and reimport the database.

>
> 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

-- 
Darcy Buskermolen
Wavefire Technologies Corp.

http://www.wavefire.com
ph: 250.717.0200
fx: 250.763.1759