Re: Postgres performance slowly gets worse over a month

Robert,

    I got your email but I am currently at the Mayo clinic with my
wife, so I can't give you much help.  If you download:

    ftp://china.maxbaud.net/pub/PostgreSQL/fixtable.pl

This is my perl script which will do a live/hot reindex of your tables.
You can run the command and it will give you a really short description of
options.   It should not let you run the script unless yo give it a group
of options that actually make sense.  I usually run it something like:

    fixtable.pl -t foo -I bar

This will recreate ALL indexes (-I) on table foo (-t foo) in the bar
database.  I use this thing more often than I would like because I get
as much as 1million records changed on a daily basis in one of my tables
and the indexes make a big difference in performance (I assume because of
disk fragmentation on the large file size).

- brian


On 8 Aug 2002, Robert M. Meyer wrote:

>
> Back on July 23, I posted on our performance problem.  At that time, I
> got several suggestions about what to do to try to fix it.  Well, it's
> happening again...
>
> To recap, we have a web based application that utilizes a postgres
> backend for handling orders and scheduling.  Gradually, the average load
> on the system climbs until we dump the database, drop it and reload it
> from the dumps.  This occurs slowly (weeks, rather than hours) and will
> eventually get so bad that no work can get done.  Another point is that
> the progression of performance appears to be geometric, rather than
> linear.  I also notice that the size of the data directory starts at
> about 4.5 Gig and climbs throughout this process.  It's currently at
> 6.3Gig.
>
> We do a full vacuum every night.  We have adjusted max_fsm_pages to
> 1000000 and max_fsm_relations to 10000 (we were seeing deleted tuples in
> the 50K range on some of our tables).  We are using ADODB 2.12 in PHP on
> Apache 1.3.26 to access the database.  We've tried rebuilding all of the
> indexes and that didn't help.  We're going to try it again because we
> wound up corrupting our indexes in the system tables while trying to
> drop and recreate a table that got partially created before a system
> crash.  We suspect that we may have had other problems at the time.  We
> have a contractor that did the reindex for us so I'm not sure of the
> process at this point.  I suspect that it's something like;
> 1. shut down postgres
> 2. run 'postgres -O -P' to start a single user instantiation of the
> engine
> 3. type 'reindex' to get it to do it
> 4. exit postgres and restart the DB with pg_ctl
>
> Does that sound about right?
>
> Any other suggestions?  We're looking to do something soon before the
> load gets out of hand.  We have done full DB dumps and restores to fix
> this in the past and it takes 4-5 hours in the middle of the night since
> we can't take the system down during the day 'cuz we have about 1100
> people sitting on their hands when we do it.  One of the suggestions was
> to use 'pgmonitor' to keep watch over what's happening but transaction
> go through too quickly and nothing seems to tie the system up for any
> period of time.  We also notice that while the load keeps increasing,
> the actual CPU time is very small.  We'll see loads above 3 with each
> CPU (we have two) sitting with 80%+ idle time.
>
> As a recap, this is happening on a Compaq Proliant 3500 system with a
> five disk raid5 in hardware.
>
> --
> Robert M. Meyer
> Sr. Network Administrator
> DigiVision Satellite Services
> 14 Lafayette Sq, Ste 410
> Buffalo, NY 14203-1904
> (716)332-1451
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>

Wm. Brian McCane                    | Life is full of doors that won't open
Search http://recall.maxbaud.net/   | when you knock, equally spaced amid those
Usenet http://freenews.maxbaud.net/ | that open when you don't want them to.
Auction http://www.sellit-here.com/ | - Roger Zelazny "Blood of Amber"