Re: help me to explain database behaviour after vacuum.

> ПОДРОБНО: 414193 dead row versions cannot be removed yet.

Increase max_fsm_pages in postgresql.conf.

Ivan wrote:
> 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 =====================
>
>
>