Thread: VACUUM FULL does not works.......

Hi,
I have a "product" table having 350 records. It takes approx 1.8 seconds to get all records from this table. I copies this table to a "product_temp" table and run the same query to select all records; and it took 10ms(much faster).
I did "VACUUM FULL" on "product" table but It did not work.

I checked the file size of these two tables. 
"product" table's file size is "32mb" and
"product_temp" table's file size is "72k".

So, it seems that "VACUUM FULL" is not doing anything.
Please suggest.

asif ali
icrossing inc.

---

Have a burning question? Go to Yahoo! Answers and get answers from real people who know.

2006/12/6, asif ali <asif_icrossing@yahoo.com>:
> Hi,
>  I have a "product" table having 350 records. It takes approx 1.8 seconds to
> get all records from this table. I copies this table to a "product_temp"
> table and run the same query to select all records; and it took 10ms(much
> faster).
>  I did "VACUUM FULL" on "product" table but It did not work.
>
>  I checked the file size of these two tables.
>  "product" table's file size is "32mb" and
>  "product_temp" table's file size is "72k".
>
>  So, it seems that "VACUUM FULL" is not doing anything.
>  Please suggest.

try VACUUM FULL VERBOSE and report the result.

--
Jean-Max Reymond
CKR Solutions Open Source
Nice France
http://www.ckr-solutions.com

On Wed, 2006-12-06 at 11:07, asif ali wrote:
> Hi,
> I have a "product" table having 350 records. It takes approx 1.8
> seconds to get all records from this table. I copies this table to a
> "product_temp" table and run the same query to select all records; and
> it took 10ms(much faster).
> I did "VACUUM FULL" on "product" table but It did not work.
>
> I checked the file size of these two tables.
> "product" table's file size is "32mb" and
> "product_temp" table's file size is "72k".
>
> So, it seems that "VACUUM FULL" is not doing anything.
> Please suggest.

More than likely you've got a long running transaction that the vacuum
can't vacuum around.

On 12/6/06, asif ali <asif_icrossing@yahoo.com> wrote:
> Hi,
>  I have a "product" table having 350 records. It takes approx 1.8 seconds to
> get all records from this table. I copies this table to a "product_temp"
> table and run the same query to select all records; and it took 10ms(much
> faster).
>  I did "VACUUM FULL" on "product" table but It did not work.
>
>  I checked the file size of these two tables.
>  "product" table's file size is "32mb" and
>  "product_temp" table's file size is "72k".
>
>  So, it seems that "VACUUM FULL" is not doing anything.
>  Please suggest.

It is desirable that PostgreSQL version be reported in problem descriptions.

Older versions of pgsql had problem of index bloat. It is interesting to
find out why VACUUM FULL does not work in your case(wait for the experts) ,
 but most probably CLUSTERING the table on primary key is going to
solve the query performance problem (temporarily)

>
>  asif ali
>  icrossing inc.
>
>  ________________________________
> Have a burning question? Go to Yahoo! Answers and get answers from real
> people who know.

Thanks for the prompt reply...
Here is the output of "VACUUM FULL VERBOSE"
The postgres version is "8.0.3".

Thanks
asif ali
icrossing inc

INFO:  vacuuming "public.product_table"
INFO:  "product_table": found 0 removable, 139178 nonremovable row versions in 4305 pages
DETAIL:  138859 dead row versions cannot be removed yet.
Nonremovable row versions range from 152 to 273 bytes long.
There were 26916 unused item pointers.
Total free space (including removable row versions) is 4507788 bytes.
249 pages are or will become empty, including 0 at the end of the table.
746 pages containing 4286656 free bytes are potential move destinations.
CPU 0.04s/0.06u sec elapsed 0.15 sec.
INFO:  index "product_table_client_name_unique" now contains 139178 row versions in 3916 pages
DETAIL:  0 index row versions were removed.
2539 index pages have been deleted, 2055 are currently reusable.
CPU 0.08s/0.02u sec elapsed 0.76 sec.
INFO:  index "product_table_cpc_agent_id_unique" now contains 139178 row versions in 1980 pages
DETAIL:  0 index row versions were removed.
1162 index pages have been deleted, 950 are currently reusable.
CPU 0.04s/0.02u sec elapsed 0.49 sec.
INFO:  index "product_table_pk" now contains 139178 row versions in 3472 pages
DETAIL:  0 index row versions were removed.
2260 index pages have been deleted, 1870 are currently reusable.
CPU 0.08s/0.02u sec elapsed 0.53 sec.
INFO:  "product_table": moved 18631 row versions, truncated 4305 to 4299 pages
DETAIL:  CPU 0.18s/1.14u sec elapsed 2.38 sec.
INFO:  index "product_table_client_name_unique" now contains 157728 row versions in 3916 pages
DETAIL:  81 index row versions were removed.
2407 index pages have been deleted, 1923 are currently reusable.
CPU 0.04s/0.01u sec elapsed 0.17 sec.
INFO:  index "product_table_cpc_agent_id_unique" now contains 157728 row versions in 1980 pages
DETAIL:  81 index row versions were removed.
1100 index pages have been deleted, 888 are currently reusable.
CPU 0.03s/0.01u sec elapsed 0.16 sec.
INFO:  index "product_table_pk" now contains 157728 row versions in 3472 pages
DETAIL:  81 index row versions were removed.
2150 index pages have been deleted, 1760 are currently reusable.
CPU 0.05s/0.01u sec elapsed 0.30 sec.
INFO:  vacuuming "pg_toast.pg_toast_11891545"
INFO:  "pg_toast_11891545": 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_11891545_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.

Query returned successfully with no result in 5201 ms.

Jean-Max Reymond <jmreymond@gmail.com> wrote:

2006/12/6, asif ali :
> Hi,
> I have a "product" table having 350 records. It takes approx 1.8 seconds to
> get all records from this table. I copies this table to a "product_temp"
> table and run the same query to select all records; and it took 10ms(much
> faster).
> I did "VACUUM FULL" on "product" table but It did not work.
>
> I checked the file size of these two tables.
> "product" table's file size is "32mb" and
> "product_temp" table's file size is "72k".
>
> So, it seems that "VACUUM FULL" is not doing anything.
> Please suggest.

try VACUUM FULL VERBOSE and report the result.

--
Jean-Max Reymond
CKR Solutions Open Source
Nice France
http://www.ckr-solutions.com

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

---

Want to start your own business? Learn how on Yahoo! Small Business.

asif ali <asif_icrossing@yahoo.com> writes:
>  INFO:  vacuuming "public.product_table"
>  INFO:  "product_table": found 0 removable, 139178 nonremovable row versions in 4305 pages
>  DETAIL:  138859 dead row versions cannot be removed yet.

So Scott's guess was correct: you've got a whole lot of dead rows in
there that will eventually be removable, but not while there's still
an open transaction that might be able to "see" them.  Find your open
transaction and get rid of it (pg_stat_activity might help).

            regards, tom lane

Thanks Everybody for helping me out.
I checked "pg_stat_activity"/pg_locks, but do not see any activity on the table.
How to find a old running transaction...
I saw this link, but it did not help..
http://archives.postgresql.org/pgsql-hackers/2005-02/msg00760.php

Thanks

asif ali
icrossing inc

Tom Lane <tgl@sss.pgh.pa.us> wrote:

asif ali writes:
> INFO: vacuuming "public.product_table"
> INFO: "product_table": found 0 removable, 139178 nonremovable row versions in 4305 pages
> DETAIL: 138859 dead row versions cannot be removed yet.

So Scott's guess was correct: you've got a whole lot of dead rows in
there that will eventually be removable, but not while there's still
an open transaction that might be able to "see" them. Find your open
transaction and get rid of it (pg_stat_activity might help).

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

---

Check out the all-new Yahoo! Mail beta - Fire up a more powerful email and get things done faster.

On Wed, 2006-12-06 at 15:53, asif ali wrote:
> Thanks Everybody for helping me out.
> I checked "pg_stat_activity"/pg_locks, but do not see any activity on
> the table.
> How to find a old running transaction...
> I saw this link, but it did not help..
> http://archives.postgresql.org/pgsql-hackers/2005-02/msg00760.php

Sometimes just using top or ps will show you.

on linux you can run top and then hit c for show command line and look
for ones that are IDLE

Or, try ps:

ps axw|grep postgres

On my machine normally:

 2408 ?        S      0:00 /usr/local/pgsql/bin/postmaster -p 5432 -D /home/postgres/data
 2615 ?        S      0:00 postgres: stats buffer process
 2616 ?        S      0:00 postgres: stats collector process
 2857 ?        S      0:00 postgres: writer process
 2858 ?        S      0:00 postgres: stats buffer process
 2859 ?        S      0:00 postgres: stats collector process

But with an idle transaction:

 2408 ?        S      0:00 /usr/local/pgsql/bin/postmaster -p 5432 -D /home/postgres/data
 2615 ?        S      0:00 postgres: stats buffer process
 2616 ?        S      0:00 postgres: stats collector process
 2857 ?        S      0:00 postgres: writer process
 2858 ?        S      0:00 postgres: stats buffer process
 2859 ?        S      0:00 postgres: stats collector process
 8679 ?        S      0:00 postgres: smarlowe test [local] idle in transaction

Thar she blows!

Also, you can restart the database and vacuum it then too. Of course,
don't do that during regular business hours...

Thanks Scott,
It worked!!!
We killed an old idle running transaction, now everything is fine..

Thanks Again
asif ali
icrossing inc

Scott Marlowe <smarlowe@g2switchworks.com> wrote:

On Wed, 2006-12-06 at 15:53, asif ali wrote:
> Thanks Everybody for helping me out.
> I checked "pg_stat_activity"/pg_locks, but do not see any activity on
> the table.
> How to find a old running transaction...
> I saw this link, but it did not help..
> http://archives.postgresql.org/pgsql-hackers/2005-02/msg00760.php

Sometimes just using top or ps will show you.

on linux you can run top and then hit c for show command line and look
for ones that are IDLE

Or, try ps:

ps axw|grep postgres

On my machine normally:

2408 ? S 0:00 /usr/local/pgsql/bin/postmaster -p 5432 -D /home/postgres/data
2615 ? S 0:00 postgres: stats buffer process
2616 ? S 0:00 postgres: stats collector process
2857 ? S 0:00 postgres: writer process
2858 ? S 0:00 postgres: stats buffer process
2859 ? S 0:00 postgres: stats collector process

But with an idle transaction:

2408 ? S 0:00 /usr/local/pgsql/bin/postmaster -p 5432 -D /home/postgres/data
2615 ? S 0:00 postgres: stats buffer process
2616 ? S 0:00 postgres: stats collector process
2857 ? S 0:00 postgres: writer process
2858 ? S 0:00 postgres: stats buffer process
2859 ? S 0:00 postgres: stats collector process
8679 ? S 0:00 postgres: smarlowe test [local] idle in transaction

Thar she blows!

Also, you can restart the database and vacuum it then too. Of course,
don't do that during regular business hours...

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

---

Have a burning question? Go to Yahoo! Answers and get answers from real people who know.

We have a view in our database.


CREATE view public.hogs AS
SELECT pg_stat_activity.procpid, pg_stat_activity.usename,
pg_stat_activity.current_query
   FROM ONLY pg_stat_activity;

Select current_query from public.hogs helps us to spot errant queries
at times.


regds
mallah.




On 12/7/06, asif ali <asif_icrossing@yahoo.com> wrote:
> Thanks Scott,
>  It worked!!!
>  We killed an old idle running transaction, now everything is fine..
>
>  Thanks Again
>  asif ali
>  icrossing inc
>
>
> Scott Marlowe <smarlowe@g2switchworks.com> wrote:
>  On Wed, 2006-12-06 at 15:53, asif ali wrote:
> > Thanks Everybody for helping me out.
> > I checked "pg_stat_activity"/pg_locks, but do not see any activity on
> > the table.
> > How to find a old running transaction...
> > I saw this link, but it did not help..
> >
> http://archives.postgresql.org/pgsql-hackers/2005-02/msg00760.php
>
> Sometimes just using top or ps will show you.
>
> on linux you can run top and then hit c for show command line and look
> for ones that are IDLE
>
> Or, try ps:
>
> ps axw|grep postgres
>
> On my machine normally:
>
>  2408 ? S 0:00 /usr/local/pgsql/bin/postmaster -p 5432 -D
> /home/postgres/data
>  2615 ? S 0:00 postgres: stats buffer process
>  2616 ? S 0:00 postgres: stats collector process
>  2857 ? S 0:00 postgres: writer process
>  2858 ? S 0:00 postgres: stats buffer process
>  2859 ? S 0:00 postgres: stats collector process
>
> But with an idle transaction:
>
>  2408 ? S 0:00 /usr/local/pgsql/bin/postmaster -p 5432 -D
> /home/postgres/data
>  2615 ? S 0:00 postgres: stats buffer process
>  2616 ? S 0:00 postgres: stats collector process
>  2857 ? S 0:00 postgres: writer process
>  2858 ? S 0:00 postgres: stats buffer process
>  2859 ? S 0:00 postgres: stats collector process
>  8679 ? S 0:00 postgres: smarlowe test [local] idle in transaction
>
> Thar she blows!
>
> Also, you can restart the database and vacuum it then too. Of course,
> don't do that during regular business hours...
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
>
>
>  ________________________________
> Have a burning question? Go to Yahoo! Answers and get answers from real
> people who know.
>
>