Обсуждение: Table Bloat still there after the Vacuum
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
Thanks for the help
Regards
In response to akp geek <akpgeek@gmail.com>: > 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 VACUUM doesn't guarantee that it will clean all the bloat out, it makes 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/
2010/4/26 akp geek <akpgeek@gmail.com>: > Hi All - > I have a table bloated with following details > rows:29431 pages:516039 shouldbe:534 (966.4X) wasted size:4223016960 (3 GB) > * I think this info come from check_postgres nagios script. As said in the doc, this info is not 100% sure : it depend on the last analyze *and* the type the columns *and* the distribution of data in those columns. I suggest you to check the pg_stats.avg_width relative to this table. And, vacuum verbose information is good for diagnostics... > 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 > Thanks for the help > > Regards -- Cédric Villemain
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/
2010/4/26 Cédric Villemain <cedric.villemain.debian@gmail.com>: > 2010/4/26 akp geek <akpgeek@gmail.com>: >> Hi All - >> I have a table bloated with following details >> rows:29431 pages:516039 shouldbe:534 (966.4X) wasted size:4223016960 (3 GB) >> * > > I think this info come from check_postgres nagios script. > As said in the doc, this info is not 100% sure : it depend on the last > analyze *and* the type the columns *and* the distribution of data in > those columns. > I suggest you to check the pg_stats.avg_width relative to this table. > > And, vacuum verbose information is good for diagnostics... and the verbose said there is actually a problem :-) check long running transaction, idle in connection, ... they prevent the vacuum things > >> 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 >> Thanks for the help >> >> Regards > > > > -- > Cédric Villemain > -- Cédric Villemain
akp geek <akpgeek@gmail.com> writes:
> * "Orders": found 0 removable, 27164544 nonremovable row versions in 518971
> pages*
> *DETAIL: 27126176 dead row versions cannot be removed yet.*
So there isn't anything vacuum can do right now. You've apparently got
an old open transaction, which is blocking vacuum from removing those
dead rows because the open transaction should still be able to see them
if it were to look. You need to close that transaction, and then do a
vacuum full (and probably a reindex after that).
regards, tom lane
pgsql-general-owner@postgresql.org wrote on 04/26/2010 03:43:03 PM:
> 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
>
> Thanks for the help
>
> Regards
Try a CLUSTER (http://www.postgresql.org/docs/8.4/static/sql-cluster.html) that will physically rewrite the table.
However note it will require an Access Exclusive lock on the table preventing any other activity on the table.
Chris Ellis
******************************************************************************
If you are not the intended recipient of this email please do not send it on
to others, open any attachments or file the email locally.
Please inform the sender of the error and then delete the original email.
For more information, please refer to http://www.shropshire.gov.uk/privacy.nsf
******************************************************************************
Help prevent the spread of swine flu. CATCH IT. BIN IT. KILL IT.
******************************************************************************
Thank you all for providing me important details. I will certainly follow them to fix the issue I have
Regards
Regards
On Mon, Apr 26, 2010 at 11:53 AM, <Chris.Ellis@shropshire.gov.uk> wrote:
pgsql-general-owner@postgresql.org wrote on 04/26/2010 03:43:03 PM:
> Hi All -Try a CLUSTER (http://www.postgresql.org/docs/8.4/static/sql-cluster.html) that will physically rewrite the table.>
> 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
>
> Thanks for the help
>
> Regards
However note it will require an Access Exclusive lock on the table preventing any other activity on the table.
Chris Ellis******************************************************************************
If you are not the intended recipient of this email please do not send it on
to others, open any attachments or file the email locally.
Please inform the sender of the error and then delete the original email.
For more information, please refer to http://www.shropshire.gov.uk/privacy.nsf
******************************************************************************
Help prevent the spread of swine flu. CATCH IT. BIN IT. KILL IT.
******************************************************************************