On May 19, 2010, at 5:00 AM, Jan Wieck wrote:

>
>
> Notice also that there is a 1000+ bump in the log_actionsequence for  
> the
> two operations done by the last transaction. This suggests that  
> somehow
> that sequence is configured for cacheing, which is a bad thing in  
> general.
>

The db is quite write heavy - so it is feasible that it bumped by 1000  
in that window. (We generate around 5500 wal segments a day on the  
origin to give you an idea)

Yeah, I'm still completely baffled - I saved sl_log from yesterday's  
hiccup - would there be anything else of use to capture when it occurs  
again?  But I think the smoking gun was that log_actionseq being  
bassackwards.  If it were something with caching or ordering of  
triggers we'd be seeing that a whole lot more often I'd wager.  I also  
find it paticularly interesting that it is almost always (as far as I  
can remember anyway) this one paticular table.

to give a bit more details - we have a few tables here, lets call them  
"sourcereport", "events", and "summary".  All three are replicated.

An insert is done on the sourcereport table (which fires slon logger)
This fires a trigger to determine if the contents of that insert are  
worthy of getting an entry in events. If so, we insert into events.  
(which fires a slon logger).
After that trigger we fire the summary trigger which updates the  
summary table based on the new sourcereport row by doing delete from  
summary where id = XXX;
insert into summary (id, blah) select XXX, blah from sourcereport;

this happens 20-30 times a day, m-f

--
Jeff Trout <jeff at jefftrout.com>
http://www.stuarthamm.net/
http://www.dellsmartexitin.com/