-----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/m3UIo1XmbAXRboRAnfHAKCVobTZGF9MlTjuAOkzIQESv1SDoQC fah67
hdCkn/4KtnlYk1mqcS1u8bY=
=/3Y4
-----END PGP SIGNATURE-----

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

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

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

-----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+ZCLs2RY0xnAC fZ7Mp
uBC031TFhO2NGOihfWPAQQ8=
=QCYi
-----END PGP SIGNATURE-----

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

>>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+ZCLs2RY0xnAC fZ7Mp
> uBC031TFhO2NGOihfWPAQQ8=
> =QCYi
> -----END PGP SIGNATURE-----
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>

Hello all,

I sent a similar post to a FreeBSD group, but thought I'd might try here too.

I am completing a box for PostgreSQL server on FreeBSD. Selecting a RAID controller I decided to go
with 3ware SE9650-16, following good opinions about 3ware controllers found on FreeBSD and
PostgreSQL groups.

However my dealer suggest me not to go with 3ware, and take Promise SuperTrak EX16350, instead. This
suggestion does not have any technical background and it comes generally from the fact of limited
availability of 16x 3ware controllers on the local market and immediate availability of Promise.

Is this technically a good idea to take Promise instead of 3ware or rather I definitely should
insist on 3ware and wait for it?

Thank you

Ireneusz Pluta


---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

On 3/20/07, Ireneusz Pluta <ipluta@wp.pl> wrote:
> Hello all,
>
> I sent a similar post to a FreeBSD group, but thought I'd might try here too.
>
> I am completing a box for PostgreSQL server on FreeBSD. Selecting a RAID controller I decided to go
> with 3ware SE9650-16, following good opinions about 3ware controllers found on FreeBSD and
> PostgreSQL groups.
>
> However my dealer suggest me not to go with 3ware, and take Promise SuperTrak EX16350, instead. This
> suggestion does not have any technical background and it comes generally from the fact of limited
> availability of 16x 3ware controllers on the local market and immediate availability of Promise.
>
> Is this technically a good idea to take Promise instead of 3ware or rather I definitely should
> insist on 3ware and wait for it?


Promise raid controllers are famous for being software based with all
the real work being done in the driver. Without doing the research
this may or may not be the case with this particular controller.
Another issue with cheap RAID controllers is the performance may not
be as good as software raid...in fact it may be worse. Look for
benchmarks on the web and be skeptical.

merlin

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

On Tue, Mar 20, 2007 at 10:18:45AM -0400, Merlin Moncure wrote:
> On 3/20/07, Ireneusz Pluta <ipluta@wp.pl> wrote:
> >Hello all,
> >
> >I sent a similar post to a FreeBSD group, but thought I'd might try here
> >too.
> >
> >I am completing a box for PostgreSQL server on FreeBSD. Selecting a RAID
> >controller I decided to go
> >with 3ware SE9650-16, following good opinions about 3ware controllers
> >found on FreeBSD and
> >PostgreSQL groups.
> >
> >However my dealer suggest me not to go with 3ware, and take Promise
> >SuperTrak EX16350, instead. This
> >suggestion does not have any technical background and it comes generally
> >from the fact of limited
> >availability of 16x 3ware controllers on the local market and immediate
> >availability of Promise.
> >
> >Is this technically a good idea to take Promise instead of 3ware or rather
> >I definitely should
> >insist on 3ware and wait for it?
>
>
> Promise raid controllers are famous for being software based with all
> the real work being done in the driver. Without doing the research
> this may or may not be the case with this particular controller.
> Another issue with cheap RAID controllers is the performance may not
> be as good as software raid...in fact it may be worse. Look for
> benchmarks on the web and be skeptical.

A Promise RAID is the only hardware RAID I've ever had eat an entire
array for me... Granted this was one of those "external array with SCSI
to the host", but it's certainly turned me away from Promise.. Probably
not related to the controller in question, just their general quality
level.

//Magnus


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

> Is this technically a good idea to take Promise instead of 3ware or
> rather I definitely should insist on 3ware and wait for it?

Use 3Ware they are proven to provide a decent raid controller for
SATA/PATA. Promise on the other hand... not so much.

Joshua D. Drake

>
> Thank you
>
> Ireneusz Pluta
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
> http://www.postgresql.org/about/donate
>


--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/


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

On 20-Mar-07, at 9:23 AM, Ireneusz Pluta wrote:

> Hello all,
>
> I sent a similar post to a FreeBSD group, but thought I'd might try
> here too.
>
> I am completing a box for PostgreSQL server on FreeBSD. Selecting a
> RAID controller I decided to go with 3ware SE9650-16, following
> good opinions about 3ware controllers found on FreeBSD and
> PostgreSQL groups.
>
> However my dealer suggest me not to go with 3ware, and take Promise
> SuperTrak EX16350, instead. This suggestion does not have any
> technical background and it comes generally from the fact of
> limited availability of 16x 3ware controllers on the local market
> and immediate availability of Promise.
>
> Is this technically a good idea to take Promise instead of 3ware or
> rather I definitely should insist on 3ware and wait for it?
>
The reality is that most dealers have no idea what is "good" for a
database application. It is likely that this card is better for him
somehow ( more margin, easier to get, etc.)

I'd stick with 3ware, areca, or lsi. And even then I'd check it when
I got it to make sure it lived up to it's reputation.
Dave
> Thank you
>
> Ireneusz Pluta
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
> http://www.postgresql.org/about/donate
>


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

On 20-Mar-07, at 1:53 PM, Benjamin Arai wrote:

> This is a little biased but I would stay away from areca only
> because they have fans on the card. At some point down the line
> that card is going to die. When it does there is really no telling
> what it will do to your data. I personally use 3Ware cards, they
> work well but I have had one die before (1/10).
>
Well, they are also the only one of the bunch that I am aware of that
will sell you 1G of cache. Plus if you use battery backup sooner or
later you have to replace the batteries. I use areca all the time
and I've never had a fan die, but I admit it is a point of failure.

Dave


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

http://archives.postgresql.org