Re: Overload after some minutes, please help!

Hi all,

for further investigation we seperated the sub-SELECT from the DELETE
statement and it looks like the SELECT is usually finished in some 100
milliseconds but after some minutes it suddenly takes some minutes.

Peter

2006/10/20, Peter Bauer <peter.m.bauer@gmail.com>:
> Hi all,
>
> we have a theory for the root of all evil which causes a reproducable
> deadlock which is not detected by Postgre:
>
> The DELETE statement contains a select which waits for a sharelock
> (according to pg_locks and pg_stat_activity) on rows locked by the
> UPDATE statement. The UPDATE itself waits to get a lock for some rows
> which are exclusively locked by the DELETE statement (got from its
> sub-SELECT).
>
> What do you think about this theory?
>
> thx,
> Peter
>
> 2006/10/19, Peter Bauer <peter.m.bauer@gmail.com>:
> > thank you very much, we will test it
> >
> > br,
> > Peter
> >
> > 2006/10/19, Jim C. Nasby <jim@nasby.net>:
> > > On Thu, Oct 19, 2006 at 01:57:56PM +0200, Peter Bauer wrote:
> > > In the update statement, don't wrap the ID values in quotes. At best
> > > it's extra work; at worse it will fool the planner into not using the
> > > index.
> > >
> > > > shared_buffers = 1000           # min 16 or max_connections*2, 8KB each
> > >
> > > This is *way* too small for what you're trying to do. Try a minimum of
> > > 10% of memory, and 50% of memory may be a better idea.
> > >
> > > > #temp_buffers = 1000            # min 100, 8KB each
> > > > #max_prepared_transactions = 5      # can be 0 or more
> > > > # note: increasing max_prepared_transactions costs ~600 bytes of shared memory
> > > > # per transaction slot, plus lock space (see max_locks_per_transaction).
> > > > work_mem = 20480                # min 64, size in KB,
> > >
> > > Making that active might help a lot, but beware of running the machine
> > > out of memory...
> > >
> > > > #max_fsm_pages = 20000          # min max_fsm_relations*16, 6 bytes each
> > >
> > > Probably needs to get increased.
> > >
> > > > #bgwriter_delay = 200           # 10-10000 milliseconds between rounds
> > > > #bgwriter_lru_percent = 1.0     # 0-100% of LRU buffers scanned/round
> > > > #bgwriter_lru_maxpages = 5      # 0-1000 buffers max written/round
> > > > #bgwriter_all_percent = 0.333       # 0-100% of all buffers scanned/round
> > > > #bgwriter_all_maxpages = 5      # 0-1000 buffers max written/round
> > >
> > > The background writer might need to be tuned more aggressively.
> > >
> > > > #checkpoint_warning = 30        # in seconds, 0 is off
> > >
> > > I'd set that closer to 300 to make sure you're not checkpointing a lot,
> > > though keep in mind that will impact failover time.
> > >
> > > > effective_cache_size = 44800        # typically 8KB each
> > >
> > > The machine only has 1/2G of memory?
> > >
> > > > #autovacuum_naptime = 60        # time between autovacuum runs, in secs
> > >
> > > I'd drop that to 30.
> > >
> > > > #autovacuum_vacuum_threshold = 1000 # min # of tuple updates before
> > > >                     # vacuum
> > > > #autovacuum_analyze_threshold = 500 # min # of tuple updates before
> > > >                     # analyze
> > > > #autovacuum_vacuum_scale_factor = 0.4   # fraction of rel size before
> > > >                     # vacuum
> > > > #autovacuum_analyze_scale_factor = 0.2  # fraction of rel size before
> > > >                     # analyze
> > >
> > > I'd cut the above 4 in half.
> > >
> > > --
> > > Jim Nasby                                            jim@nasby.net
> > > EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)
> > >
> >
>