Обсуждение: help me to explain database behaviour after vacuum.
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
> ПОДРОБНО: 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 ===================== > > >
Ivan <Ivan-Sun1@mail.ru> writes:
> As you can see, disk usage stop grows for several hours
> (3-5) after vacuum, then start again.
You don't have max_fsm_pages set large enough. Note the output at the
end of the VACUUM printout:
> INFO: free space map: 67 relations, 20713 pages stored; 29872 total pages needed
> ��������: Allocated FSM size: 1000 relations + 20000 pages = 186 kB shared memory.
30000 instead of 20000 would be enough at the moment, but I'd suggest
going to 100000 to give yourself some breathing room.
regards, tom lane