help me to explain database behaviour after vacuum. - Mailing list pgsql-admin

Hello,

OS: Windows 2003 Server SP1
DB: Postgresql-8.0.3

I have a database for caching html pages. Main table consists of
fields for URL (varchar(8192)), page body (text) and a few others.
Pages has various expiration time (up to 3 days). Every 30 minutes
a special program deletes expired pages from database. Every night
at 2.00 I execute
vacuumdb --all --verbose

In a few days after system's start time total amount of data is
considered to be a constant. But database's folder size is growing
all the time. Plain dump of database's data
pg_dump.exe --format=p --data-only
produces approximately 2.5 Gb after database has grown
enough (e.g. 23.08.2005 at 18.00 dump file size was 2.5 Gb
though disk usage is about 15 Gb).

So we start to collect some statistics:
 - folder size (every hour)
 - vacuum output info
I attached these data below (i include data only for toast table
of the main table from vacuum statistics, because just that table
grows all the time).

As you can see, disk usage stop grows for several hours
(3-5) after vacuum, then start again. Vacuum marks as free every time
similar amount of data pages (min - 138537 pages (1 Gb), max - 351290
pages (2.7 Gb)) but total page count (and count of unused item pointers)
grows every time.

Please help or we get out of free space soon! :)
Thank you.

===================== Statistics =====================

*************************************
The size of the database directory:
*************************************
Time(DD.MM.YYYY)        Size in bytes
-------------------------------------
15.08.2005 16:49:09     2 761 358 541
15.08.2005 16:49:11     2 761 398 504
15.08.2005 16:49:15     2 761 512 195
15.08.2005 17:00:00     2 783 858 974
15.08.2005 18:00:00     2 898 590 925
15.08.2005 19:00:00     2 967 843 102
15.08.2005 20:00:00     3 028 939 038
15.08.2005 21:00:00     3 088 331 038
15.08.2005 22:00:00     3 148 214 558
15.08.2005 23:00:00     3 209 656 552
16.08.2005 0:00:00      3 272 855 838
16.08.2005 1:00:00      3 324 154 142
16.08.2005 2:00:00      3 362 918 686
16.08.2005 3:00:01      3 388 768 144
16.08.2005 4:00:03      3 390 935 326
16.08.2005 5:00:00      3 390 959 902
16.08.2005 6:00:00      3 390 992 670
16.08.2005 7:00:00      3 391 041 822
16.08.2005 8:00:00      3 410 899 230
16.08.2005 9:00:00      3 476 058 398
16.08.2005 10:00:00     3 580 170 526
16.08.2005 11:00:00     3 711 915 267
16.08.2005 12:00:00     3 854 955 779
16.08.2005 13:00:00     3 994 969 239
16.08.2005 14:00:00     4 127 413 507
16.08.2005 15:00:00     4 249 934 056
16.08.2005 16:00:00     4 367 544 606
16.08.2005 17:00:00     4 485 635 277
16.08.2005 18:00:00     4 591 491 304
16.08.2005 19:00:00     4 668 420 382
16.08.2005 20:00:00     4 725 649 694
16.08.2005 21:00:00     4 784 050 462
16.08.2005 22:00:00     4 840 304 926
16.08.2005 23:00:00     4 903 784 734
17.08.2005 0:00:00      4 966 207 774
17.08.2005 1:00:00      5 016 482 078
17.08.2005 2:00:00      5 053 919 518
17.08.2005 3:00:01      5 078 798 622
17.08.2005 4:00:00      5 078 839 582
17.08.2005 5:00:00      5 078 872 350
17.08.2005 6:00:00      5 078 905 118
17.08.2005 7:00:00      5 078 970 654
17.08.2005 8:00:00      5 105 701 150
17.08.2005 9:00:00      5 165 412 638
17.08.2005 10:00:00     5 269 942 558
17.08.2005 11:00:00     5 393 424 562
17.08.2005 12:00:00     5 542 617 266
17.08.2005 13:00:00     5 687 980 318
17.08.2005 14:00:00     5 814 309 150
17.08.2005 15:00:00     5 943 408 872
17.08.2005 16:00:00     6 062 631 036
17.08.2005 17:00:00     6 180 951 299
17.08.2005 18:00:00     6 285 152 542
17.08.2005 19:00:00     6 358 349 059
17.08.2005 20:00:00     6 415 397 150
17.08.2005 21:00:00     6 474 010 910
17.08.2005 22:00:00     6 527 351 016
17.08.2005 23:00:00     6 591 877 406
18.08.2005 0:00:00      6 653 939 998
18.08.2005 1:00:00      6 707 187 998
18.08.2005 2:00:00      6 743 626 014
18.08.2005 3:00:01      6 773 243 792
18.08.2005 4:00:03      6 787 567 902
18.08.2005 5:00:00      6 787 600 670
18.08.2005 6:00:00      6 787 633 438
18.08.2005 7:00:00      6 787 674 398
18.08.2005 8:00:00      6 787 485 982
18.08.2005 9:00:00      6 849 777 950
18.08.2005 10:00:00     6 952 531 203
18.08.2005 11:00:00     7 078 965 534
18.08.2005 12:00:00     7 222 718 750
18.08.2005 13:00:00     7 360 431 255
18.08.2005 14:00:00     7 494 335 693
18.08.2005 15:00:00     7 620 137 246
18.08.2005 16:00:00     7 742 042 398
18.08.2005 17:00:00     7 734 596 902
18.08.2005 18:00:01     7 837 103 398
18.08.2005 19:00:00     7 909 217 574
18.08.2005 20:00:00     7 967 298 854
18.08.2005 21:00:00     8 024 020 262
18.08.2005 22:00:00     8 085 591 334
18.08.2005 23:00:00     8 149 701 926
19.08.2005 0:00:00      8 209 069 350
19.08.2005 1:00:00      8 257 991 974
19.08.2005 2:00:00      8 295 896 358
19.08.2005 3:00:01      8 322 858 931
19.08.2005 4:00:03      8 327 632 166
19.08.2005 5:00:00      8 327 632 166
19.08.2005 6:00:00      8 327 632 166
19.08.2005 7:00:00      8 327 632 166
19.08.2005 8:00:00      8 341 853 478
19.08.2005 9:00:00      8 404 931 878
19.08.2005 10:00:00     8 500 811 046
19.08.2005 11:00:00     8 628 040 998
19.08.2005 12:00:00     8 771 040 550
19.08.2005 13:00:00     8 914 261 286
19.08.2005 14:00:00     9 047 971 110
19.08.2005 15:00:00     9 176 192 294
19.08.2005 16:00:00     9 298 171 174
19.08.2005 17:00:00     9 407 673 638
19.08.2005 18:00:00     9 497 015 590
19.08.2005 19:00:00     9 563 813 158
19.08.2005 20:00:00     9 618 298 150
19.08.2005 21:00:00     9 669 817 638
19.08.2005 22:00:00     9 723 082 022
19.08.2005 23:00:00     9 782 490 406
20.08.2005 0:00:00      9 840 604 454
20.08.2005 1:00:00      9 891 337 510
20.08.2005 2:00:00      9 929 618 726
20.08.2005 3:00:01      9 958 113 203
20.08.2005 4:00:03      9 962 788 134
20.08.2005 5:00:00      9 962 788 134
20.08.2005 6:00:00      9 962 788 134
20.08.2005 7:00:00      9 962 788 134
20.08.2005 8:00:00      9 962 788 134
20.08.2005 9:00:00      9 962 788 134
20.08.2005 10:00:00     9 962 788 134
20.08.2005 11:00:00     9 962 788 134
20.08.2005 12:00:00     9 962 788 134
20.08.2005 13:00:00     9 962 788 134
20.08.2005 14:00:00     10 010 768 678
20.08.2005 15:00:00     10 097 849 638
20.08.2005 16:00:00     10 173 076 774
20.08.2005 17:00:00     10 242 929 958
20.08.2005 18:00:00     10 302 453 030
20.08.2005 19:00:00     10 357 110 054
20.08.2005 20:00:00     10 411 308 326
20.08.2005 21:00:00     10 464 023 846
20.08.2005 22:00:00     10 520 909 094
20.08.2005 23:00:00     10 576 508 198
21.08.2005 0:00:00      10 630 386 982
21.08.2005 1:00:00      10 674 984 230
21.08.2005 2:00:00      10 712 667 430
21.08.2005 3:00:01      10 736 424 230
21.08.2005 4:00:00      10 736 424 230
21.08.2005 5:00:00      10 736 424 230
21.08.2005 6:00:00      10 736 424 230
21.08.2005 7:00:00      10 736 432 422
21.08.2005 8:00:00      10 736 448 806
21.08.2005 9:00:00      10 749 408 550
21.08.2005 10:00:00     10 782 324 006
21.08.2005 11:00:00     10 825 913 638
21.08.2005 12:00:00     10 882 389 286
21.08.2005 13:00:00     10 945 885 478
21.08.2005 14:00:00     11 011 093 798
21.08.2005 15:00:00     11 073 066 278
21.08.2005 16:00:00     11 139 323 174
21.08.2005 17:00:00     11 202 426 150
21.08.2005 18:00:00     11 262 301 478
21.08.2005 19:00:00     11 322 127 654
21.08.2005 20:00:00     11 382 773 030
21.08.2005 21:00:00     11 440 510 246
21.08.2005 22:00:00     11 503 580 454
21.08.2005 23:00:00     11 563 529 510
22.08.2005 0:00:00      11 623 658 790
22.08.2005 1:00:00      11 675 137 318
22.08.2005 2:00:00      11 713 426 726
22.08.2005 3:00:01      11 736 945 958
22.08.2005 4:00:00      11 736 945 958
22.08.2005 5:00:00      11 736 945 958
22.08.2005 6:00:00      11 736 945 958
22.08.2005 7:00:00      11 736 945 958
22.08.2005 8:00:00      11 765 658 918
22.08.2005 9:00:00      11 827 123 494
22.08.2005 10:00:00     11 929 720 102
22.08.2005 11:00:00     12 065 535 270
22.08.2005 12:00:00     12 215 465 254
22.08.2005 13:00:00     12 357 211 430
22.08.2005 14:00:00     12 495 353 126
22.08.2005 15:00:00     12 636 116 262
22.08.2005 16:00:00     12 770 825 510
22.08.2005 17:00:00     12 902 249 766
22.08.2005 18:00:00     13 016 814 886
22.08.2005 19:00:00     13 102 200 102
22.08.2005 20:00:00     13 166 875 942
22.08.2005 21:00:00     13 229 528 358
22.08.2005 22:00:00     13 292 918 054
22.08.2005 23:00:00     13 362 304 294
23.08.2005 0:00:01      13 428 765 990
23.08.2005 1:00:01      13 482 284 326
23.08.2005 2:00:01      13 521 917 222
23.08.2005 3:00:02      13 550 362 547
23.08.2005 4:00:04      13 556 757 798
23.08.2005 5:00:00      13 556 757 798
23.08.2005 6:00:00      13 556 765 990
23.08.2005 7:00:00      13 556 774 182
23.08.2005 8:00:00      13 580 645 670
23.08.2005 9:00:00      13 647 107 366
23.08.2005 10:00:00     13 755 954 470
23.08.2005 11:00:00     13 895 087 398
23.08.2005 12:00:00     14 050 546 982
23.08.2005 13:00:01     14 204 359 974
23.08.2005 14:00:01     14 351 455 526
23.08.2005 15:00:00     14 493 046 054
23.08.2005 16:00:00     14 630 573 350
23.08.2005 17:00:00     14 760 629 542
23.08.2005 18:00:02     14 871 303 462
--------------------------------------

***************************************************************
Vacuum statistics for toast table for main table (table format)
***************************************************************
    1             2            3             4            5             6              7           8
-------------------------------------------------------------------------------------------------------
16.08.2005     837 973      228 320       837 973      328 195       374 895        430 886           0
17.08.2005     474 101      140 757       474 101      741 548       581 056      1 214 387     414 193
18.08.2005   1 319 785      351 290     1 319 785      308 290       786 679      1 632 176           0
19.08.2005     887 997      246 268       887 997      298 207       989 534      2 895 485           0
20.08.2005     859 652      238 901       859 652      298 042     1 188 914      3 732 153           0
21.08.2005     477 584      138 537       477 584      264 791     1 284 506      4 542 028           0
22.08.2005     559 767      155 696       559 767      254 133     1 406 651      4 968 456           0
23.08.2005     863 309      237 461       863 309      331 154     1 627 496      5 477 686           0
-------------------------------------------------------------------------------------------------------
1 - date
2 - removed
3 - removed in pages
4 - removable
5 - nonremovable
6 - found in pages
7 - unused item pointers
8 - dead row versions cannot be removed yet


*************************************************************
Vacuum statistics for toast table for main table (raw format)
*************************************************************
==================== BEGIN ====================
16.08.2005
02:00
===============================================
INFO:  vacuuming "pg_toast.pg_toast_17241"
INFO:  index "pg_toast_17241_index" now contains 330163 row versions in 12366 pages
ПОДРОБНО:  837973 index row versions were removed.
8347 index pages have been deleted, 6511 are currently reusable.
CPU 1.37s/2.45u sec elapsed 162.65 sec.
INFO:  "pg_toast_17241": removed 837973 row versions in 228320 pages
ПОДРОБНО:  CPU 31.42s/27.01u sec elapsed 3567.21 sec.
INFO:  "pg_toast_17241": found 837973 removable, 328195 nonremovable row versions in 374895 pages
ПОДРОБНО:  0 dead row versions cannot be removed yet.
There were 430886 unused item pointers.
0 pages are entirely empty.
CPU 44.76s/34.42u sec elapsed 3976.12 sec.
INFO:  free space map: 67 relations, 20694 pages stored; 28992 total pages needed
ПОДРОБНО:  Allocated FSM size: 1000 relations + 20000 pages = 186 kB shared memory.
===============================================
16.08.2005
03:07
===================== END =====================
==================== BEGIN ====================
17.08.2005
02:00
===============================================
INFO:  vacuuming "pg_toast.pg_toast_17241"
INFO:  index "pg_toast_17241_index" now contains 743897 row versions in 12366 pages
ПОДРОБНО:  474101 index row versions were removed.
6786 index pages have been deleted, 4969 are currently reusable.
CPU 1.34s/1.70u sec elapsed 113.76 sec.
INFO:  "pg_toast_17241": removed 474101 row versions in 140757 pages
ПОДРОБНО:  CPU 24.06s/17.28u sec elapsed 2329.00 sec.
INFO:  "pg_toast_17241": found 474101 removable, 741548 nonremovable row versions in 581056 pages
ПОДРОБНО:  414193 dead row versions cannot be removed yet.
There were 1214387 unused item pointers.
0 pages are entirely empty.
CPU 45.78s/26.03u sec elapsed 2801.12 sec.
INFO:  free space map: 67 relations, 20309 pages stored; 28000 total pages needed
ПОДРОБНО:  Allocated FSM size: 1000 relations + 20000 pages = 186 kB shared memory.
===============================================
17.08.2005
02:48
===================== END =====================
==================== BEGIN ====================
18.08.2005
02:00
===============================================
INFO:  vacuuming "pg_toast.pg_toast_17241"
INFO:  index "pg_toast_17241_index" now contains 310879 row versions in 12366 pages
ПОДРОБНО:  1319785 index row versions were removed.
7738 index pages have been deleted, 3406 are currently reusable.
CPU 2.18s/3.62u sec elapsed 244.62 sec.
INFO:  "pg_toast_17241": removed 1319785 row versions in 351290 pages
ПОДРОБНО:  CPU 53.96s/41.67u sec elapsed 5454.12 sec.
INFO:  "pg_toast_17241": found 1319785 removable, 308290 nonremovable row versions in 786679 pages
ПОДРОБНО:  0 dead row versions cannot be removed yet.
There were 1632176 unused item pointers.
0 pages are entirely empty.
CPU 78.17s/53.62u sec elapsed 6071.56 sec.
INFO:  free space map: 67 relations, 19854 pages stored; 27216 total pages needed
ПОДРОБНО:  Allocated FSM size: 1000 relations + 20000 pages = 186 kB shared memory.
===============================================
18.08.2005
03:43
===================== END =====================
==================== BEGIN ====================
19.08.2005
02:00
===============================================
INFO:  vacuuming "pg_toast.pg_toast_17241"
INFO:  index "pg_toast_17241_index" now contains 300292 row versions in 13171 pages
ПОДРОБНО:  887997 index row versions were removed.
8686 index pages have been deleted, 5204 are currently reusable.
CPU 1.60s/2.90u sec elapsed 206.60 sec.
INFO:  "pg_toast_17241": removed 887997 row versions in 246268 pages
ПОДРОБНО:  CPU 34.37s/30.01u sec elapsed 3813.34 sec.
INFO:  "pg_toast_17241": found 887997 removable, 298207 nonremovable row versions in 989534 pages
ПОДРОБНО:  0 dead row versions cannot be removed yet.
There were 2895485 unused item pointers.
0 pages are entirely empty.
CPU 53.46s/41.75u sec elapsed 4358.46 sec.
INFO:  free space map: 67 relations, 20436 pages stored; 28832 total pages needed
ПОДРОБНО:  Allocated FSM size: 1000 relations + 20000 pages = 186 kB shared memory.
===============================================
19.08.2005
03:14
===================== END =====================
==================== BEGIN ====================
20.08.2005
02:00
===============================================
INFO:  vacuuming "pg_toast.pg_toast_17241"
INFO:  index "pg_toast_17241_index" now contains 301323 row versions in 13173 pages
ПОДРОБНО:  859652 index row versions were removed.
8752 index pages have been deleted, 5415 are currently reusable.
CPU 2.07s/2.76u sec elapsed 230.03 sec.
INFO:  "pg_toast_17241": removed 859652 row versions in 238901 pages
ПОДРОБНО:  CPU 35.10s/26.53u sec elapsed 3741.36 sec.
INFO:  "pg_toast_17241": found 859652 removable, 298042 nonremovable row versions in 1188914 pages
ПОДРОБНО:  0 dead row versions cannot be removed yet.
There were 3732153 unused item pointers.
0 pages are entirely empty.
CPU 64.65s/39.84u sec elapsed 4439.73 sec.
INFO:  free space map: 67 relations, 20498 pages stored; 29040 total pages needed
ПОДРОБНО:  Allocated FSM size: 1000 relations + 20000 pages = 186 kB shared memory.
===============================================
20.08.2005
03:15
===================== END =====================
==================== BEGIN ====================
21.08.2005
02:00
===============================================
INFO:  vacuuming "pg_toast.pg_toast_17241"
INFO:  index "pg_toast_17241_index" now contains 267369 row versions in 13173 pages
ПОДРОБНО:  477584 index row versions were removed.
10173 index pages have been deleted, 7073 are currently reusable.
CPU 1.82s/2.31u sec elapsed 201.31 sec.
INFO:  "pg_toast_17241": removed 477584 row versions in 138537 pages
ПОДРОБНО:  CPU 19.65s/16.62u sec elapsed 2147.40 sec.
INFO:  "pg_toast_17241": found 477584 removable, 264791 nonremovable row versions in 1284506 pages
ПОДРОБНО:  0 dead row versions cannot be removed yet.
There were 4542028 unused item pointers.
0 pages are entirely empty.
CPU 44.89s/30.09u sec elapsed 2792.78 sec.
INFO:  free space map: 67 relations, 20873 pages stored; 30368 total pages needed
ПОДРОБНО:  Allocated FSM size: 1000 relations + 20000 pages = 186 kB shared memory.
===============================================
21.08.2005
02:48
===================== END =====================
==================== BEGIN ====================
22.08.2005
02:00
===============================================
INFO:  vacuuming "pg_toast.pg_toast_17241"
INFO:  index "pg_toast_17241_index" now contains 257084 row versions in 13173 pages
ПОДРОБНО:  559767 index row versions were removed.
10255 index pages have been deleted, 8086 are currently reusable.
CPU 1.10s/1.85u sec elapsed 153.71 sec.
INFO:  "pg_toast_17241": removed 559767 row versions in 155696 pages
ПОДРОБНО:  CPU 23.60s/27.73u sec elapsed 2325.21 sec.
INFO:  "pg_toast_17241": found 559767 removable, 254133 nonremovable row versions in 1406651 pages
ПОДРОБНО:  0 dead row versions cannot be removed yet.
There were 4968456 unused item pointers.
0 pages are entirely empty.
CPU 49.43s/41.12u sec elapsed 2938.20 sec.
INFO:  free space map: 67 relations, 21083 pages stored; 31072 total pages needed
ПОДРОБНО:  Allocated FSM size: 1000 relations + 20000 pages = 186 kB shared memory.
===============================================
22.08.2005
02:50
===================== END =====================
==================== BEGIN ====================
23.08.2005
02:00
===============================================
INFO:  vacuuming "pg_toast.pg_toast_17241"
INFO:  index "pg_toast_17241_index" now contains 334642 row versions in 13173 pages
ПОДРОБНО:  863309 index row versions were removed.
8748 index pages have been deleted, 6674 are currently reusable.
CPU 1.89s/2.51u sec elapsed 190.32 sec.
INFO:  "pg_toast_17241": removed 863309 row versions in 237461 pages
ПОДРОБНО:  CPU 35.35s/27.46u sec elapsed 3679.19 sec.
INFO:  "pg_toast_17241": found 863309 removable, 331154 nonremovable row versions in 1627496 pages
ПОДРОБНО:  0 dead row versions cannot be removed yet.
There were 5477686 unused item pointers.
0 pages are entirely empty.
CPU 69.73s/42.57u sec elapsed 4392.54 sec.
INFO:  free space map: 67 relations, 20713 pages stored; 29872 total pages needed
ПОДРОБНО:  Allocated FSM size: 1000 relations + 20000 pages = 186 kB shared memory.
===============================================
23.08.2005
03:15
===================== END =====================



--
Best regards,
 Ivan                          mailto:Ivan-Sun1@mail.ru