Обсуждение: Vacuum Full - stops responding(?)
Hello, I have problem with VACUUM FULL on one table 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... After 10 hours nothing new appear... What is going on with this vacuum? VACUUM ANALYZE is OK 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 processes.. Why vacuum full stops responding? :(
Krowa Krowax <krowa333@gmail.com> wrote: > Why vacuum full stops responding? :( If you give it enough time (which is hard to predict and possibly more time than you want to allow), it will probably finish, eventually. It is, however, almost never needed or desirable to use VACUUM FULL. I recommend you just use VACUUM ANALYZE (possibly with VERBOSE). If the table is bloated enough to warrant trying to reclaim space, do you have room for an extra copy of it? If so, try using CLUSTER, followed by VACUUM ANALYZE, instead of VACUUM FULL. -Kevin
Krowa Krowax <krowa333@gmail.com> writes:
> 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.
That might be about the worst case of table bloat I've ever seen :-(
> After 10 hours nothing new appear... What is going on with this vacuum?
It's trying to repack your table in place. On the whole, I'd suggest
you cancel the vacuum and try a CLUSTER or some such instead.
After you get this cleaned up, think about enabling autovacuum ...
regards, tom lane
On Mon, Oct 19, 2009 at 4:38 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Krowa Krowax <krowa333@gmail.com> writes: >> 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. > > That might be about the worst case of table bloat I've ever seen :-( > >> After 10 hours nothing new appear... What is going on with this vacuum? > > It's trying to repack your table in place. On the whole, I'd suggest > you cancel the vacuum and try a CLUSTER or some such instead. Unless that table's already in (mostly) index order, even cluster is gonna be pretty painful. I'd suggest a dump and reload myself. > After you get this cleaned up, think about enabling autovacuum ... This. Very much this.
On Mon, Oct 19, 2009 at 7:37 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote: >> It's trying to repack your table in place. On the whole, I'd suggest >> you cancel the vacuum and try a CLUSTER or some such instead. > > Unless that table's already in (mostly) index order, even cluster is > gonna be pretty painful. > I'd suggest a dump and reload myself. The run-time of CLUSTER doesn't vary very much based on whether the data is already in index order or not. The number of passes only grows like log(n) of the size of your data and if you set maintenance_work_mem large enough (somewhere around 100MB-1GB) the constants are small enough that you're unlikely to even outgrow a single pass (plus a final merge though) That said one sort is still more than zero. So a dump/reload is always going to be competitive depending on how slow your data is to compare versus how slow it is to convert to text and back (and how much larger the text is than the native format) -- greg
Greg Stark <gsstark@mit.edu> writes:
> The run-time of CLUSTER doesn't vary very much based on whether the
> data is already in index order or not. The number of passes only grows
> like log(n) of the size of your data and if you set
> maintenance_work_mem large enough (somewhere around 100MB-1GB) the
> constants are small enough that you're unlikely to even outgrow a
> single pass (plus a final merge though)
Uh ... what? It's not based on the sort code, unless someone rewrote it
since I looked last. It's an index scan and will definitely depend on
the index ordering.
regards, tom lane
On Mon, Oct 19, 2009 at 8:56 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Greg Stark <gsstark@mit.edu> writes: >> The run-time of CLUSTER doesn't vary very much based on whether the >> data is already in index order or not. The number of passes only grows >> like log(n) of the size of your data and if you set >> maintenance_work_mem large enough (somewhere around 100MB-1GB) the >> constants are small enough that you're unlikely to even outgrow a >> single pass (plus a final merge though) > > Uh ... what? It's not based on the sort code, unless someone rewrote it > since I looked last. It's an index scan and will definitely depend on > the index ordering. Er, uh, of course. I wonder what I was thinking. Sorry -- greg
Greg Stark escribió: > On Mon, Oct 19, 2009 at 8:56 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > Greg Stark <gsstark@mit.edu> writes: > >> The run-time of CLUSTER doesn't vary very much based on whether the > >> data is already in index order or not. The number of passes only grows > >> like log(n) of the size of your data and if you set > >> maintenance_work_mem large enough (somewhere around 100MB-1GB) the > >> constants are small enough that you're unlikely to even outgrow a > >> single pass (plus a final merge though) > > > > Uh ... what? It's not based on the sort code, unless someone rewrote it > > since I looked last. It's an index scan and will definitely depend on > > the index ordering. > > Er, uh, of course. I wonder what I was thinking. Your patched version of course. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
On Tue, Oct 20, 2009 at 9:03 PM, Alvaro Herrera <alvherre@commandprompt.com> wrote: > Greg Stark escribió: >> On Mon, Oct 19, 2009 at 8:56 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> > Greg Stark <gsstark@mit.edu> writes: >> >> The run-time of CLUSTER doesn't vary very much based on whether the >> >> data is already in index order or not. The number of passes only grows >> >> like log(n) of the size of your data and if you set >> >> maintenance_work_mem large enough (somewhere around 100MB-1GB) the >> >> constants are small enough that you're unlikely to even outgrow a >> >> single pass (plus a final merge though) >> > >> > Uh ... what? It's not based on the sort code, unless someone rewrote it >> > since I looked last. It's an index scan and will definitely depend on >> > the index ordering. >> >> Er, uh, of course. I wonder what I was thinking. > > Your patched version of course. I would quite happily trade being right for a much faster cluster command. Also Pizza. I would gladly trade pizza for a faster cluster command.