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@xxxxxxx