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

 > ПОДРОБНО:  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 =====================
>
>
>