performance degradation while doing repeatative updates
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
Attachments:
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:
Show quoted text
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-5I'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