Re: Table Bloat still there after the Vacuum
| От | akp geek |
|---|---|
| Тема | Re: Table Bloat still there after the Vacuum |
| Дата | |
| Msg-id | w2l2024a9fb1004260827x142e3aafg19cd2ed7acba96ec@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Table Bloat still there after the Vacuum (Bill Moran <wmoran@potentialtech.com>) |
| Ответы |
Re: Table Bloat still there after the Vacuum
(Tom Lane <tgl@sss.pgh.pa.us>)
|
| Список | pgsql-general |
What I did was, I issued the following command
$vacuumdb -d prodDB -t orders -f -z -v
Regards
"Orders": found 0 removable, 27164544 nonremovable row versions in 518971 pages
DETAIL: 27126176 dead row versions cannot be removed yet.
Nonremovable row versions range from 118 to 213 bytes long.
There were 10425 unused item pointers.
Total free space (including removable row versions) is 35613716 bytes.
0 pages are or will become empty, including 0 at the end of the table.
89274 pages containing 12011420 free bytes are potential move destinations.
CPU 15.53s/16.55u sec elapsed 62.78 sec.
INFO: index "idx_orders_id" now contains 27164544 row versions in 95569 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 3.18s/4.35u sec elapsed 20.52 sec.
INFO: "Orders": moved 6 row versions, truncated 518971 to 518971 pages
DETAIL: CPU 0.08s/0.08u sec elapsed 7.69 sec.
INFO: index " idx_orders_id" now contains 27164544 row versions in 95569 pages
DETAIL: 6 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 2.25s/2.78u sec elapsed 14.97 sec.
INFO: vacuuming "pg_toast.pg_toast_1059337"
INFO: "pg_toast_1059337": found 0 removable, 0 nonremovable row versions in 0 pages
DETAIL: 0 dead row versions cannot be removed yet.
Nonremovable row versions range from 0 to 0 bytes long.
There were 0 unused item pointers.
Total free space (including removable row versions) is 0 bytes.
0 pages are or will become empty, including 0 at the end of the table.
0 pages containing 0 free bytes are potential move destinations.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "pg_toast_1059337_index" now contains 0 row versions in 1 pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: analyzing "Orders"
Regards
On Mon, Apr 26, 2010 at 10:55 AM, Bill Moran <wmoran@potentialtech.com> wrote:
In response to akp geek <akpgeek@gmail.com>:VACUUM doesn't guarantee that it will clean all the bloat out, it makes
> Hi All -
>
> I have a table bloated with following details
> rows:29431 pages:516039 shouldbe:534 (966.4X) wasted size:4223016960 (3 GB)
> *
>
> I did a vacuum on the database and also I did vacuumdb
> full on the table. Still there is no change. Can you please suggest if there
> is any other operation that can be done to take care of the issue
some effort to debloat, but that's not its primary function.
VACUUM FULL will completely debloat a table, contingent on restrictions
below. Is that what you're running? I'm a little confused by your
comment "vacuumdb full on the table" which contradicts itself. Please
provide the exact commands that your ran, along with the output that
resulted.
Neither type of VACUUM can debloat rows that are still in use by
transactions. If the applications that connect to this database are
keeping transactions open for long periods, it will adversely affect
those commands' ability to clean up dead rows.
There is much more here:
http://www.postgresql.org/docs/8.4/static/routine-vacuuming.html
--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/
В списке pgsql-general по дате отправления: