Thread: Postgres performance slowly gets worse over a month
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
On 08 Aug 2002 14:06:57 -0400 "Robert M. Meyer" <rmeyer@installs.com> 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... > [snip] > Any other suggestions? We're looking to do something soon before the > load gets out of hand. With all due respect, the depth of your problem indicates to me you need a database administrator. Someone who understands the database and the hardware and performance/tuning principles. (in case you think I'm hinting about something, I'm not available for work right now -- you really do need to hire/contract a DBA sort) > 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. First guess: analyze your queries. It's gotten me through a number of problems that sound just like this. As the data set grows, a poorly formed query will chew more and more I/O time. -- Tim Ellis Senior Database Architect Gamet, Inc.
"Robert M. Meyer" <rmeyer@installs.com> writes:
> 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...
Given that you've increased the FSM size *and* are doing a full vacuum
every night (I hope with analyze option as well!), it seems unlikely
that your tables are physically growing --- I suspect there may be an
index growth problem instead. But you should try to check that.
Try doing
select relkind,sum(relpages) from pg_class group by relkind;
every so often (preferably just after a vacuum run) to see how the space
totals change over time.
> 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
IIRC, that only rebuilds indexes on the system tables. You mentioned
that you'd rebuilt user indexes too --- how did you go about that
exactly?
> 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.
The load must be all disk I/O then. Do you have adequate RAM in this
thing? What have you set shared_buffers to?
More generally, what's your typical query mix? Have you checked for
inefficient plans on the most common queries?
regards, tom lane
On Thu, Aug 08, 2002 at 02:06:57PM -0400, Robert M. Meyer wrote:
> 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?
What you are talking about there is reindexing the system tables.
But "recreating the indexes" as a solution to file growth and
performance problems is not this. Instead, what you do is drop the
index and recreate it. At least, that's what I have gathered from
the "reindex" scripts that were sent to the -general list a little
while ago.
> Any other suggestions?
Yes. As someone else suggested, it sounds like you need a full-time
DBA, at least for a little while.
A
--
----
Andrew Sullivan 87 Mowat Avenue
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M6K 3E3
+1 416 646 3304 x110
Robert, Sounds to me that you have some kind of fragmentation causing the slow degredation of performance. A dump/restore, which is basically a very drastic defragmentation, is fixing it. It happens slowly and there seems not to be a single big cause. More like the sum of many little things. You also report that there are a lot of deleted tuples (rows). Don't know a quick and easy fix. Maybe its caused how your application is designed. Inserting deleting many rows. Probably frequent updates. Some rows using lots of space and some very little. Anyway something is causing very inefficient space use. So I would agree with the other respondens that you need a DBA to look at your database. Especially the design itself. Regards, Nikolaus Dilger "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
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"