pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedVACUUM FULL does not works.......
10+ messages / 5 participants
[nested] [flat]
* VACUUM FULL does not works.......
@ 2006-12-06 17:07 asif ali <asif_icrossing@yahoo.com>
0 siblings, 3 replies; 10+ messages in thread
From: asif ali @ 2006-12-06 17:07 UTC (permalink / raw)
To: pgsql-performance
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: VACUUM FULL does not works.......
@ 2006-12-06 17:13 Jean-Max Reymond <jmreymond@gmail.com>
parent: asif ali <asif_icrossing@yahoo.com>
2 siblings, 1 reply; 10+ messages in thread
From: Jean-Max Reymond @ 2006-12-06 17:13 UTC (permalink / raw)
To: asif ali <asif_icrossing@yahoo.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: VACUUM FULL does not works.......
@ 2006-12-06 17:20 Scott Marlowe <smarlowe@g2switchworks.com>
parent: asif ali <asif_icrossing@yahoo.com>
2 siblings, 0 replies; 10+ messages in thread
From: Scott Marlowe @ 2006-12-06 17:20 UTC (permalink / raw)
To: asif ali <asif_icrossing@yahoo.com>; +Cc: pgsql-performance
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: VACUUM FULL does not works.......
@ 2006-12-06 17:47 Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
parent: asif ali <asif_icrossing@yahoo.com>
2 siblings, 0 replies; 10+ messages in thread
From: Rajesh Kumar Mallah @ 2006-12-06 17:47 UTC (permalink / raw)
To: asif ali <asif_icrossing@yahoo.com>; +Cc: pgsql-performance
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: VACUUM FULL does not works.......
@ 2006-12-06 18:37 asif ali <asif_icrossing@yahoo.com>
parent: Jean-Max Reymond <jmreymond@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: asif ali @ 2006-12-06 18:37 UTC (permalink / raw)
To: pgsql-performance
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: VACUUM FULL does not works.......
@ 2006-12-06 19:55 Tom Lane <tgl@sss.pgh.pa.us>
parent: asif ali <asif_icrossing@yahoo.com>
0 siblings, 1 reply; 10+ messages in thread
From: Tom Lane @ 2006-12-06 19:55 UTC (permalink / raw)
To: asif ali <asif_icrossing@yahoo.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: VACUUM FULL does not works.......
@ 2006-12-06 21:53 asif ali <asif_icrossing@yahoo.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 10+ messages in thread
From: asif ali @ 2006-12-06 21:53 UTC (permalink / raw)
To: pgsql-performance
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: VACUUM FULL does not works.......
@ 2006-12-06 22:13 Scott Marlowe <smarlowe@g2switchworks.com>
parent: asif ali <asif_icrossing@yahoo.com>
0 siblings, 1 reply; 10+ messages in thread
From: Scott Marlowe @ 2006-12-06 22:13 UTC (permalink / raw)
To: asif ali <asif_icrossing@yahoo.com>; +Cc: pgsql-performance
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...
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: VACUUM FULL does not works.......
@ 2006-12-07 00:19 asif ali <asif_icrossing@yahoo.com>
parent: Scott Marlowe <smarlowe@g2switchworks.com>
0 siblings, 1 reply; 10+ messages in thread
From: asif ali @ 2006-12-07 00:19 UTC (permalink / raw)
To: pgsql-performance
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: VACUUM FULL does not works.......
@ 2006-12-07 06:58 Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
parent: asif ali <asif_icrossing@yahoo.com>
0 siblings, 0 replies; 10+ messages in thread
From: Rajesh Kumar Mallah @ 2006-12-07 06:58 UTC (permalink / raw)
To: asif ali <asif_icrossing@yahoo.com>; +Cc: pgsql-performance
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.
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2006-12-07 06:58 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-12-06 17:07 VACUUM FULL does not works....... asif ali <asif_icrossing@yahoo.com>
2006-12-06 17:13 ` Jean-Max Reymond <jmreymond@gmail.com>
2006-12-06 18:37 ` asif ali <asif_icrossing@yahoo.com>
2006-12-06 19:55 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-12-06 21:53 ` asif ali <asif_icrossing@yahoo.com>
2006-12-06 22:13 ` Scott Marlowe <smarlowe@g2switchworks.com>
2006-12-07 00:19 ` asif ali <asif_icrossing@yahoo.com>
2006-12-07 06:58 ` Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
2006-12-06 17:20 ` Scott Marlowe <smarlowe@g2switchworks.com>
2006-12-06 17:47 ` Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox