Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2009
    Posts
    2

    Question Unanswered: Vacuum Full - stops responding(?)

    Hello,

    I have problem with VACUUM FULL on one table (other vacuum's ends completely)

    Code:
    Table statistics
    Statistic 	              Value
    Sequential Scans 	        455
    Sequential Tuples Read 	  114059927
    Index Scans             	135554861
    Index Tuples Fetched     	152172886
    Tuples Inserted         	2281476
    Tuples Updated          	67287404
    Tuples Deleted          	2254901
    Heap Blocks Read         	449369371
    Heap Blocks Hit         	2969477465
    Index Blocks Read       	171509578
    Index Blocks Hit 	        2947370213
    Toast Blocks Read 	      7105675639
    Toast Blocks Hit        	151665604
    Toast Index Blocks Read 	59614577
    Toast Index Blocks Hit   	679416492
    Table Size              	397 MB
    Toast Table Size 	        157 GB
    Indexes Size            	177 MB
    Code:
    db1=# reindex table pg_toast.pg_toast_17929229; vacuum full verbose sch1.myobjects;
    REINDEX
    VACUUM
    INFO:  vacuuming "sch1.myobjects"
    INFO:  "myobjects": found 8359 removable, 265619 nonremovable row versions in 25899 pages
    DETAIL:  0 dead row versions cannot be removed yet.
    Nonremovable row versions range from 96 to 2032 bytes long.
    There were 286682 unused item pointers.
    Total free space (including removable row versions) is 81949164 bytes.
    5540 pages are or will become empty, including 1 at the end of the table.
    18640 pages containing 81678752 free bytes are potential move destinations.
    CPU 0.47s/0.06u sec elapsed 1.69 sec.
    INFO:  index "myobjects_pkey" now contains 265619 row versions in 1503 pages
    DETAIL:  8359 index row versions were removed.
    70 index pages have been deleted, 70 are currently reusable.
    CPU 0.05s/0.10u sec elapsed 1.55 sec.
    INFO:  index "mob" now contains 265619 row versions in 4855 pages
    DETAIL:  8359 index row versions were removed.
    358 index pages have been deleted, 358 are currently reusable.
    CPU 0.20s/0.12u sec elapsed 9.42 sec.
    INFO:  index "so_idt" now contains 265619 row versions in 1506 pages
    DETAIL:  8359 index row versions were removed.
    75 index pages have been deleted, 75 are currently reusable.
    CPU 0.07s/0.12u sec elapsed 1.44 sec.
    INFO:  index "so_obclass" now contains 265619 row versions in 1938 pages
    DETAIL:  8359 index row versions were removed.
    10 index pages have been deleted, 10 are currently reusable.
    CPU 0.04s/0.10u sec elapsed 3.74 sec.
    INFO:  index "so_obstring" now contains 265619 row versions in 3240 pages
    DETAIL:  8359 index row versions were removed.
    218 index pages have been deleted, 218 are currently reusable.
    CPU 0.09s/0.15u sec elapsed 5.03 sec.
    INFO:  "myobjects": moved 8244 row versions, truncated 25899 to 15998 pages
    DETAIL:  CPU 1.98s/2.05u sec elapsed 25.51 sec.
    INFO:  index "myobjects_pkey" now contains 265619 row versions in 1503 pages
    DETAIL:  8244 index row versions were removed.
    76 index pages have been deleted, 76 are currently reusable.
    CPU 0.04s/0.05u sec elapsed 0.09 sec.
    INFO:  index "mob" now contains 265619 row versions in 4855 pages
    DETAIL:  8244 index row versions were removed.
    397 index pages have been deleted, 397 are currently reusable.
    CPU 0.13s/0.05u sec elapsed 0.19 sec.
    INFO:  index "so_idt" now contains 265619 row versions in 1506 pages
    DETAIL:  8244 index row versions were removed.
    81 index pages have been deleted, 81 are currently reusable.
    CPU 0.04s/0.05u sec elapsed 0.09 sec.
    INFO:  index "so_obclass" now contains 265619 row versions in 1964 pages
    DETAIL:  8244 index row versions were removed.
    690 index pages have been deleted, 690 are currently reusable.
    CPU 0.07s/0.04u sec elapsed 0.12 sec.
    INFO:  index "so_obstring" now contains 265619 row versions in 3240 pages
    DETAIL:  8244 index row versions were removed.
    240 index pages have been deleted, 240 are currently reusable.
    CPU 0.08s/0.06u sec elapsed 0.14 sec.
    INFO:  vacuuming "pg_toast.pg_toast_17929229"
    INFO:  "pg_toast_17929229": found 6203 removable, 257509 nonremovable row versions in 20509016 pages
    DETAIL:  0 dead row versions cannot be removed yet.
    Nonremovable row versions range from 45 to 2030 bytes long.
    There were 87521183 unused item pointers.
    Total free space (including removable row versions) is 166910212580 bytes.
    20384082 pages are or will become empty, including 3 at the end of the table.
    20507826 pages containing 166910155528 free bytes are potential move destinations.
    CPU 412.14s/58.08u sec elapsed 3632.16 sec.
    INFO:  index "pg_toast_17929229_index" now contains 257509 row versions in 709 pages
    DETAIL:  0 index row versions were removed.
    0 index pages have been deleted, 0 are currently reusable.
    CPU 0.02s/0.15u sec elapsed 0.22 sec.
    ^^This is the last message...

    Code:
      PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
    25948 postgres  18   0 3543m 3.2g 1.9g D    2 20.6  20:09.29 postgres: postgres db1 [local] VACUUM
    After 10 hours nothing new appear... What is going on with this vacuum?

    Code:
      PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
    25948 postgres  17   0 3544m 3.2g 1.9g D    2 20.7  27:55.63 postgres: postgres db1 [local] VACUUM
    VACUUM ANALYZE is doing well

    Code:
    db1=# vacuum analyze verbose sch1.myobjects;
    (...)
    INFO:  vacuuming "pg_toast.pg_toast_17929229"
    INFO:  index "pg_toast_17929229_index" now contains 260391 row versions in 1279 pages
    DETAIL:  194099 index row versions were removed.
    0 index pages have been deleted, 0 are currently reusable.
    CPU 0.01s/0.16u sec elapsed 0.18 sec.
    INFO:  "pg_toast_17929229": removed 196567 row versions in 42846 pages
    DETAIL:  CPU 0.05s/0.32u sec elapsed 1.24 sec.
    INFO:  "pg_toast_17929229": found 196567 removable, 257570 nonremovable row versions in 20510280 pages
    DETAIL:  321 dead row versions cannot be removed yet.
    There were 87339448 unused item pointers.
    0 pages are entirely empty.
    CPU 266.24s/51.49u sec elapsed 1920.18 sec.
    INFO:  analyzing "sch1.myobjects"
    INFO:  "myobjects": scanned 3000 of 16620 pages, containing 48341 live rows and 1107 dead rows; 3000 rows in sample, 267809 estimated total rows
    db1=#
    There is no 'idle in transaction' postgres process..
    Why vacuum full stops responding?

    PostgreSQL 8.1.18
    Last edited by Krowax; 10-20-09 at 03:57.

  2. #2
    Join Date
    Oct 2009
    Posts
    3
    Hello,
    I presume you are using tools likes "top" or "htop", these tools don't update the "custom command line string" that use applications like Postgresql, you have to relaunch the tools to see the new process string.

  3. #3
    Join Date
    Oct 2009
    Posts
    2
    Quote Originally Posted by Kedare
    Hello,
    I presume you are using tools likes "top" or "htop", these tools don't update the "custom command line string" that use applications like Postgresql, you have to relaunch the tools to see the new process string.
    Hello, thanks for replay

    I've only pasted top command result. I use:
    Code:
    ps -ewo pid,start,pcpu,pmem,cmd | grep [p]ost
    for checking postgres processes. In that vacuum full there wasn't processes like 'idle in transaction'.

    Sorry for my english

  4. #4
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    No surprise here. That single table is 158GB. You are either running out of disk space or just not giving it enough time.

    You should look into partitioning that table.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •