On Thursday 23 August 2007 13:59, David Rees wrote:
> On 8/17/07, Dan Falconer <lists_slony1-general at avsupport.com> wrote:
> > On Wednesday 15 August 2007 16:33, David Rees wrote:
> > > Why are you running such an old version of Pg? You should be running
> > > 8.0.13 at least...
> >
> > Good point... I'm not a DBA, but I play one at my job.  ;)
> >
> :-) Get that beast upgraded to the latest maintenance release if
> possible. Many have reported that going to the latest major release is
> a good idea as well.

I kinda saw that one coming.  Hopefully I can convince all the other 
cheiftains that it's a good idea.   The good news is I can then finally test 
how it works to switch master while upgrading.  Woot.
>
> > The master server has a very worrisome load, running between 5-20.  I
> > took a snapshot of it:
> >         load average: 13.18, 7.23, 4.33
>
> The load average is fairly high for a dual-processor machine, but your
> vmstat output looks very benign with the system sitting at 50% idle
> and low IO wait times.
>
> > There's also a fetch that's been running for a very long time (there was
> > a poorly timed vacuum of the whole database that took 2 days while this
> > was running; this has been formatted for your screen)::::
>
> What is that process doing according to postgres? What locks are being
> held and are any processes being blocked by that long running query?

Well, according to Postgres, it's a fetch being run by the slave server 
("FETCH 100 FROM LOG").  I couldn't really tell if there were any locks being 
held by it or what was being blocked (queries to help facilitate that would 
be cool).  The logs had a combined total of 20+ million records (5M in 
sl_log_1, 15M in sl_log_2)... it was 4 days behind, so I killed replication 
completely: dropped the replication schema on both servers, re-init, 
create/subscribe set.
>
> > The slons aren't run with any special settings.  They're started using
> > the "slon_start" bash script included with Slony (some items removed to
> > protect the innocent):::
> >
> > /usr/local/pgsql/bin/slon -s 5000 -d2 replication host=******* dbname=pl
> > user=postgres port=5432
>
> You might want to try bumping up the maximum sync group size from the
> default of 6 to something like 50-100 to see if that helps.
>
> What about postgresql tuning itself? What do the performance related
> postgresql.conf settings look like?
>
> -Dave

Any help with regards to changing options using the slon_start script would be 
great... I can't find anything in the configuration file that indicates such 
settings.

As far as performance options in postgresql.conf:::

shared_buffers = 12800
work_mem = 32768
maintenance_work_mem = 102400
max_stack_depth = 4096
max_fsm_pages = 165000
max_fsm_relations = 10000
effective_cache_size = 768000
random_page_cost = 2


I've got all the stats turned on as well, if any of that information can be 
helpful.  The "performance tuning" was all done by myself and another 
developer: we were both pretty much guessing at what we were doing, mostly 
going for maximization of memory usage.




-- 
Best Regards,


Dan Falconer
"Head Geek", Avsupport, Inc. / Partslogistics.com
http://www.partslogistics.com