pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedVacuum full is slow
5+ messages / 4 participants
[nested] [flat]
* Vacuum full is slow
@ 2007-03-19 11:02 Ruben Rubio <ruben@rentalia.com>
0 siblings, 2 replies; 5+ messages in thread
From: Ruben Rubio @ 2007-03-19 11:02 UTC (permalink / raw)
To: pgsql-performance
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Hi,
Vacuum full is very slow for me . I dont know how to speed it up. It
takes between 60 and 90 minutes.
I have set up autovacuum but I also run vacuum full once per week.
The slowest parts in the vacuum full output are :
INFO: "a": moved 14076 row versions, truncated 6013 to 1005 pages
DETAIL: CPU 3.51s/2.16u sec elapsed 1156.00 sec.
INFO: "b": moved 22174 row versions, truncated 1285 to 933 pages
DETAIL: CPU 3.77s/1.52u sec elapsed 443.79 sec.
INFO: "c": moved 36897 row versions, truncated 2824 to 1988 pages
DETAIL: CPU 3.26s/1.45u sec elapsed 676.18 sec.
How can I speed it up?
Postgres version 8.1.3
Thanks in advance
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2.2 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iD8DBQFF/m3UIo1XmbAXRboRAnfHAKCVobTZGF9MlTjuAOkzIQESv1SDoQCfah67
hdCkn/4KtnlYk1mqcS1u8bY=
=/3Y4
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Vacuum full is slow
@ 2007-03-19 11:23 Heikki Linnakangas <heikki@enterprisedb.com>
parent: Ruben Rubio <ruben@rentalia.com>
1 sibling, 1 reply; 5+ messages in thread
From: Heikki Linnakangas @ 2007-03-19 11:23 UTC (permalink / raw)
To: Ruben Rubio <ruben@rentalia.com>; +Cc: pgsql-performance
Ruben Rubio wrote:
> Vacuum full is very slow for me . I dont know how to speed it up. It
> takes between 60 and 90 minutes.
>
> I have set up autovacuum but I also run vacuum full once per week.
Do you really need to run vacuum full? I don't know you're workload, but
usually you're better off just not running it.
One alternative is to run CLUSTER instead of VACUUM FULL. It's usually
faster, but beware that it's not safe if you're concurrently running
serializable transactions that access the table. pg_dump in particular
is a problem. In a maintenance window with no other activity, however,
it's ok.
> The slowest parts in the vacuum full output are :
>
> INFO: "a": moved 14076 row versions, truncated 6013 to 1005 pages
> DETAIL: CPU 3.51s/2.16u sec elapsed 1156.00 sec.
>
> INFO: "b": moved 22174 row versions, truncated 1285 to 933 pages
> DETAIL: CPU 3.77s/1.52u sec elapsed 443.79 sec.
>
> INFO: "c": moved 36897 row versions, truncated 2824 to 1988 pages
> DETAIL: CPU 3.26s/1.45u sec elapsed 676.18 sec.
>
> How can I speed it up?
You don't have vacuum_cost_delay set, do you? How long does normal
vacuum run?
The manual suggests dropping all indexes before running vacuum full, and
recreating them afterwards. That's worth trying.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Vacuum full is slow
@ 2007-03-19 14:20 Ruben Rubio <ruben@rentalia.com>
parent: Heikki Linnakangas <heikki@enterprisedb.com>
0 siblings, 1 reply; 5+ messages in thread
From: Ruben Rubio @ 2007-03-19 14:20 UTC (permalink / raw)
To: Heikki Linnakangas <heikki@enterprisedb.com>; +Cc: pgsql-performance
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
>
> You don't have vacuum_cost_delay set, do you? How long does normal
> vacuum run?
vacuum_cost_delay = 100
No idea how long will take normal vacuum. I ll try tonight when there is
not too much load.
>
> The manual suggests dropping all indexes before running vacuum full, and
> recreating them afterwards. That's worth trying.
>
I ll try that also. Is there any way to do it? Do i have to delete /
create each one manually?
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2.2 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iD8DBQFF/pxLIo1XmbAXRboRAjR1AJ9V4kBDCd++HSmUm8+ZCLs2RY0xnACfZ7Mp
uBC031TFhO2NGOihfWPAQQ8=
=QCYi
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Vacuum full is slow
@ 2007-03-19 14:37 Shoaib Mir <shoaibmir@gmail.com>
parent: Ruben Rubio <ruben@rentalia.com>
0 siblings, 0 replies; 5+ messages in thread
From: Shoaib Mir @ 2007-03-19 14:37 UTC (permalink / raw)
To: Ruben Rubio <ruben@rentalia.com>; +Cc: Heikki Linnakangas <heikki@enterprisedb.com>; pgsql-performance
>>vacuum_cost_delay = 100
>>No idea how long will take normal vacuum. I ll try tonight when there is
>>not too much load.
That can really take the VACUUM a long time to complete, but you might want
to have it there as it will be good for performance by setting it a little
high in a high OLTP environment.
I will recommend setting it to 0 first and then you can start moving it high
as per your needs...
--
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)
On 3/19/07, Ruben Rubio <ruben@rentalia.com> wrote:
>
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> >
> > You don't have vacuum_cost_delay set, do you? How long does normal
> > vacuum run?
>
> vacuum_cost_delay = 100
> No idea how long will take normal vacuum. I ll try tonight when there is
> not too much load.
>
> >
> > The manual suggests dropping all indexes before running vacuum full, and
> > recreating them afterwards. That's worth trying.
> >
>
> I ll try that also. Is there any way to do it? Do i have to delete /
> create each one manually?
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.2.2 (GNU/Linux)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
>
> iD8DBQFF/pxLIo1XmbAXRboRAjR1AJ9V4kBDCd++HSmUm8+ZCLs2RY0xnACfZ7Mp
> uBC031TFhO2NGOihfWPAQQ8=
> =QCYi
> -----END PGP SIGNATURE-----
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Vacuum full is slow
@ 2007-03-23 16:32 Scott Marlowe <smarlowe@g2switchworks.com>
parent: Ruben Rubio <ruben@rentalia.com>
1 sibling, 0 replies; 5+ messages in thread
From: Scott Marlowe @ 2007-03-23 16:32 UTC (permalink / raw)
To: Ruben Rubio <ruben@rentalia.com>; +Cc: pgsql-performance
On Mon, 2007-03-19 at 06:02, Ruben Rubio wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Hi,
>
> Vacuum full is very slow for me . I dont know how to speed it up. It
> takes between 60 and 90 minutes.
>
> I have set up autovacuum but I also run vacuum full once per week.
Note two things.
1: you need to update your pgsql version. 8.1.3 is a bit old.
2: You shouldn't normally need to run vacuum full. Vacuum full is
there to get you out of problems created when regular vacuum falls
behind. It contributes to index bloat as well. If routine vacuuming
isn't working, regular vacuum full is not the answer (well, 99% of the
time it's not). Fixing routing vacuuming is the answer.
If you don't have an actual problem with routine vacuuming, you would be
better off writing a monitoring script to keep track of bloat in tables
and send you an email than running vacuum full all the time.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2007-03-23 16:32 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-03-19 11:02 Vacuum full is slow Ruben Rubio <ruben@rentalia.com>
2007-03-19 11:23 ` Heikki Linnakangas <heikki@enterprisedb.com>
2007-03-19 14:20 ` Ruben Rubio <ruben@rentalia.com>
2007-03-19 14:37 ` Shoaib Mir <shoaibmir@gmail.com>
2007-03-23 16:32 ` Scott Marlowe <smarlowe@g2switchworks.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