Обсуждение: performance problem with version 7.0.3

Hello there

Running postgreSQL 7.0.3 on a SuSE linux server (1GHz, PIII, 384 MB RAM) I
have problems with the performance. There are three to six postmaster
processes running. One with ODBC connector to a NT server , the others use
JDBC. Each month, we're having this situation. The customer encounters
bad/slow performance. A list that normaly takes 20 seconds to display takes
the up to 3 minutes.

I did a

vacuum verbose analyze zylinder on the main table for this list and got the
following output :

mondadori=# vacuum verbose analyze zylinder;
NOTICE:  --Relation zylinder--
NOTICE:  Pages 12275: Changed 2, reaped 12264, Empty 0, New 0; Tup 1609:
Vac 143979, Keep/VTL 4/4, Crash 0, UnUsed 0, MinLen 557, MaxLen 703;
Re-using: Free/Avail. Space 98833500/98830092; EndEmpty/Avail. Pages
0/12263. CPU 0.47s/0.45u sec.
NOTICE:  Index z_a_nr_idx: Pages 555; Tuples 1609: Deleted 143979. CPU
0.05s/1.03u sec.
NOTICE:  Index zylinder_pkey: Pages 830; Tuples 1609: Deleted 143979. CPU
0.06s/0.94u sec.
NOTICE:  Rel zylinder: Pages: 12275 --> 134; Tuple(s) moved: 700. CPU
1.14s/0.37u sec.
NOTICE:  Index z_a_nr_idx: Pages 555; Tuples 1609: Deleted 700. CPU
0.04s/0.00u sec.
NOTICE:  Index zylinder_pkey: Pages 830; Tuples 1609: Deleted 700. CPU
0.06s/0.01u sec.
VACUUM
mondadori=# vacuum verbose analyze zylinder;
NOTICE:  --Relation zylinder--
NOTICE:  Pages 134: Changed 0, reaped 3, Empty 0, New 0; Tup 1609: Vac 0,
Keep/VTL 4/0, Crash 0, UnUsed 7, MinLen 557, MaxLen 703; Re-using:
Free/Avail. Space 4348/0; EndEmpty/Avail. Pages 0/0. CPU 0.01s/0.22u sec.
NOTICE:  Index z_a_nr_idx: Pages 555; Tuples 1609: Deleted 0. CPU
0.02s/0.00u sec.
NOTICE:  Index zylinder_pkey: Pages 830; Tuples 1609: Deleted 0. CPU
0.02s/0.03u sec.
VACUUM
mondadori=# vacuum verbose analyze zylinder;
NOTICE:  --Relation zylinder--
NOTICE:  Pages 134: Changed 0, reaped 7, Empty 0, New 0; Tup 1605: Vac 4,
Keep/VTL 0/0, Crash 0, UnUsed 7, MinLen 557, MaxLen 703; Re-using:
Free/Avail. Space 8564/4216; EndEmpty/Avail. Pages 0/4. CPU 0.01s/0.23u
sec.
NOTICE:  Index z_a_nr_idx: Pages 555; Tuples 1605: Deleted 4. CPU
0.03s/0.00u sec.
NOTICE:  Index zylinder_pkey: Pages 830; Tuples 1605: Deleted 4. CPU
0.04s/0.00u sec.
NOTICE:  Rel zylinder: Pages: 134 --> 134; Tuple(s) moved: 4. CPU
0.00s/0.00u sec.
NOTICE:  Index z_a_nr_idx: Pages 555; Tuples 1605: Deleted 4. CPU
0.02s/0.01u sec.
NOTICE:  Index zylinder_pkey: Pages 830; Tuples 1605: Deleted 4. CPU
0.04s/0.00u sec.
VACUUM

Please help me in analyzing this output. After vacuum/analyze, the
performance is ok for another month.

Thanks in advance ... jr

__________________________________________________

PFISTER + PARTNER, SYSTEM - ENGINEERING AG
Juerg Rietmann
Grundstrasse 22a
6343 Rotkreuz
Switzerland

internet       :  www.pup.ch
phone          : +4141 790 4040
fax       : +4141 790 2545
mobile         : +4179 211 0315
__________________________________________________

juerg.rietmann@pup.ch writes:
> Please help me in analyzing this output. After vacuum/analyze, the
> performance is ok for another month.

So, vacuum on a regular basis.  Most people do it at least once a day.

            regards, tom lane

juerg.rietmann@pup.ch wrote in message news:<OFCE7D518C.69014E0E-ONC1256B4F.004AE587@pup.ch>...
> Hello there
>
> > Please help me in analyzing this output. After vacuum/analyze, the
> performance is ok for another month.
>

Are you regularily doing a "vacuum analyze"? This is a critical step
to maintaining the database as PostgreSQL doesn't re-use
deleted/updated tuples. In order to keep the db queries fast within
PostgreSQL, you'll need to regularily perform a "vacuum analyze". The
frequency will depend on how often the database has
updated/deleted/inserted tuples.

HTH,
-Tony