performance problem with version 7.0.3

From: juerg(dot)rietmann(at)pup(dot)ch
To: pgsql-admin(at)postgresql(dot)org
Subject: performance problem with version 7.0.3
Date: 2002-01-28 13:46:54
Message-ID: OFCE7D518C.69014E0E-ONC1256B4F.004AE587@pup.ch
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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
__________________________________________________

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Marc Mitchell 2002-01-28 16:42:45 PRIMARY KEY Indexes.
Previous Message Pablo Prieto 2002-01-28 12:19:38 Re: OT: Decent GUI-based reportwriter for Linux?