performance problem with version 7.0.3

Поиск
Список
Период
Сортировка
От juerg.rietmann@pup.ch
Тема performance problem with version 7.0.3
Дата
Msg-id OFCE7D518C.69014E0E-ONC1256B4F.004AE587@pup.ch
обсуждение исходный текст
Ответы Re: performance problem with version 7.0.3  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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
__________________________________________________


В списке pgsql-admin по дате отправления:

Предыдущее
От: Bradley Kieser
Дата:
Сообщение: Re: OT: Decent GUI-based reportwriter for Linux?
Следующее
От: "Marc Mitchell"
Дата:
Сообщение: PRIMARY KEY Indexes.