Re: performance degradation while doing repeatative updates - Mailing list pgsql-bugs

I made your tests to see it.

But I already read about many techniques to monitor and increase
performance on Postgres.

A good pratical is use "VACUUM" on your database daily.

The pratical sample.

 >> Starts
NOTICE:  CREATE TABLE will create implicit sequence 'degradation_id_seq'
for SERIAL column 'degradation.id'
NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index
'degradation_pkey' for table 'degradation'
turn 1: elapsed time: 5.481 Dif: 448.10%
turn 2: elapsed time: 5.589 Dif: 1.97%
turn 3: elapsed time: 5.949 Dif: 6.44%
turn 4: elapsed time: 5.892 Dif: -0.96%
    >> cut <<
turn 241: elapsed time: 18.214 Dif: -5.09%
turn 242: elapsed time: 20.232 Dif: 11.08%
turn 243: elapsed time: 18.865 Dif: -6.76%
turn 244: elapsed time: 21.083 Dif: 11.76%
turn 245: elapsed time: 30.123 Dif: 42.88%
turn 246: elapsed time: 20.804 Dif: -30.94%

 >> running "vacuum verbose degradation;" during requests (not
recomended, but...)

rtest=# vacuum verbose degradation;
NOTICE:  --Relation degradation--
NOTICE:  Pages 4395: Changed 19, reaped 4377, Empty 0, New 0; Tup 3000:
Vac 744000, Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 44, MaxLen 44;
Re-using: Free/Avail. Space 32788524/32788524; EndEmpty/Avail. Pages
0/4377. CPU 0.45s/0.42u sec.
NOTICE:  Index degradation_pkey: Pages 2033; Tuples 3000: Deleted
744000. CPU 1.89s/25.12u sec.
NOTICE:  Rel degradation: Pages: 4395 --> 18; Tuple(s) moved: 3000. CPU
2.19s/3.08u sec.
NOTICE:  Index degradation_pkey: Pages 2033; Tuples 3000: Deleted 3000.
CPU 0.92s/0.91u sec.
VACUUM

turn 247: elapsed time: 32.044 Dif: 54.03%
turn 248: elapsed time: 86.787 Dif: 170.84%
turn 249: elapsed time: 74.132 Dif: -14.58%
turn 250: elapsed time: 6.150 Dif: -91.70%
turn 251: elapsed time: 8.622 Dif: 40.20%
turn 252: elapsed time: 6.349 Dif: -26.36%
turn 253: elapsed time: 8.406 Dif: 32.40%
turn 254: elapsed time: 6.527 Dif: -22.35%
turn 255: elapsed time: 6.363 Dif: -2.51%
turn 256: elapsed time: 6.921 Dif: 8.77%

 >> long time
rtest=# vacuum verbose analyze degradation;
NOTICE:  --Relation degradation--
NOTICE:  Pages 3653: Changed 18, reaped 3636, Empty 0, New 0; Tup 3000:
Vac 618000, Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 44, MaxLen 44;
Re-using: Free/Avail. Space 27235632/27235632; EndEmpty/Avail. Pages
0/3636. CPU 0.36s/0.37u sec.
NOTICE:  Index degradation_pkey: Pages 2033; Tuples 3000: Deleted
618000. CPU 1.40s/16.87u sec.
NOTICE:  Rel degradation: Pages: 3653 --> 18; Tuple(s) moved: 3000. CPU
1.92s/2.77u sec.
NOTICE:  Index degradation_pkey: Pages 2033; Tuples 3000: Deleted 3000.
CPU 0.88s/1.09u sec.
NOTICE:  Analyzing...
VACUUM

turn 451: elapsed time: 17.343 Dif: 14.49%
turn 452: elapsed time: 17.461 Dif: 0.68%
turn 453: elapsed time: 15.798 Dif: -9.52%
turn 454: elapsed time: 18.578 Dif: 17.60%
turn 455: elapsed time: 57.360 Dif: 208.75%
turn 456: elapsed time: 7.224 Dif: -87.41%
turn 457: elapsed time: 6.933 Dif: -4.03%
turn 458: elapsed time: 6.631 Dif: -4.36%
turn 459: elapsed time: 6.537 Dif: -1.42%

More details about vacuum on http://techdocs.postgresql.org

I use one daily process to dump data and execute vacuum, and don't have
great problems.

I think... "It's not a BUG, it's a FEATURE" :)

* I use version 7.2.1, but have many recommends to upgrade to 7.2.2.
  Many changes to best performance (vacuum full ?) and resources to
monitoring...


Escuse my bad english :\

[]'s
Marco Antonio



Vladimir Dozen wrote:

> ehlo.
>
>   I'm doing initial design test for our web database, and
>   I found strange performance degradation while doing repeatative
>   updates of a table: each next cycle of update takes more time.
>   Average slowdown for a cycle is about 1%, i.e. after doing about
>   100 updates, update time becomes twice as large.
>
>   I run Postgres under Cygwin on PII-400/192M machine. I has no
>   chance to run it under a kind of unix system yet, but I guess the
>   problem is not related to OS used.
>
>   Version:
>   PostgreSQL 7.2.1 on i686-pc-cygwin, compiled by GCC 2.95.3-5
>
>   I've made a small example (using JDBC) to show it (see attachment).
>   The test creates 3000 records of (id,x,y) integers, and in
>   loop updates x and y values of each record.
>
>   Results are like following:
>
> turn:0 elapsed time:8.612
> turn:1 elapsed time:8.373
> turn:2 elapsed time:7.701
> turn:3 elapsed time:7.42
> turn:4 elapsed time:7.611
> turn:5 elapsed time:7.581
> turn:6 elapsed time:7.631
> turn:7 elapsed time:7.721
> turn:8 elapsed time:7.711
> turn:9 elapsed time:7.862
> turn:10 elapsed time:8.021
> turn:11 elapsed time:7.922
> turn:12 elapsed time:8.211
> turn:13 elapsed time:8.092
> turn:14 elapsed time:8.252
> turn:15 elapsed time:8.292
> turn:16 elapsed time:8.422
> turn:17 elapsed time:8.572
> turn:18 elapsed time:8.502
> turn:19 elapsed time:8.583
> turn:20 elapsed time:8.742
> turn:21 elapsed time:8.943
> turn:22 elapsed time:8.913
> turn:23 elapsed time:9.103
> turn:24 elapsed time:9.133
> turn:25 elapsed time:9.183
> turn:26 elapsed time:9.494
> turn:27 elapsed time:9.784
> turn:28 elapsed time:9.894
> turn:29 elapsed time:9.885
> turn:30 elapsed time:10.044
> turn:31 elapsed time:10.115
> turn:32 elapsed time:10.405
> turn:33 elapsed time:10.645
> turn:34 elapsed time:10.425
> turn:35 elapsed time:10.886
> turn:36 elapsed time:10.915
> turn:37 elapsed time:11.046
> turn:38 elapsed time:11.697
> turn:39 elapsed time:11.627
> turn:40 elapsed time:11.907
> turn:41 elapsed time:12.308
> turn:42 elapsed time:12.538
> turn:43 elapsed time:12.808
> turn:44 elapsed time:13.439
> turn:45 elapsed time:13.67
> turn:46 elapsed time:14.351
> turn:47 elapsed time:15.241
> turn:48 elapsed time:16.014
> turn:49 elapsed time:16.583
> turn:50 elapsed time:17.395
> ...
>
> dozen
>