Table Bloat still there after the Vacuum

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
*
*
*
*
*
* "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:

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

On Mon, Apr 26, 2010 at 11:53 AM, <Chris.Ellis@shropshire.gov.uk> wrote: