pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPerformance autovaccum
5+ messages / 5 participants
[nested] [flat]
* Performance autovaccum
@ 2013-07-08 16:14 Jeison Bedoya <jeisonb@audifarma.com.co>
2013-07-08 22:21 ` Re: Performance autovaccum Michael Paquier <michael.paquier@gmail.com>
2013-07-09 22:14 ` Re: Performance autovaccum Josh Berkus <josh@agliodbs.com>
0 siblings, 2 replies; 5+ messages in thread
From: Jeison Bedoya @ 2013-07-08 16:14 UTC (permalink / raw)
To: pgsql-performance
Hi, i have a postgresql 9.2.2, but i don´t use autovaccum but i want to
begin to use it. some recommendation about the optimal configuration?
or some link to explain it.
Thanks
--
Atentamente,
JEISON BEDOYA DELGADO
Adm. Servidores y Comunicaciones
AUDIFARMA S.A.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Performance autovaccum
2013-07-08 16:14 Performance autovaccum Jeison Bedoya <jeisonb@audifarma.com.co>
@ 2013-07-08 22:21 ` Michael Paquier <michael.paquier@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: Michael Paquier @ 2013-07-08 22:21 UTC (permalink / raw)
To: Jeison Bedoya <jeisonb@audifarma.com.co>; +Cc: pgsql-performance
On Tue, Jul 9, 2013 at 1:14 AM, Jeison Bedoya <jeisonb@audifarma.com.co> wrote:
> Hi, i have a postgresql 9.2.2,
You should update to 9.2.4. There are major security fixes in this subrelease.
> but i don´t use autovaccum but i want to
> begin to use it. some recommendation about the optimal configuration? or
> some link to explain it.
Perhaps that?
http://www.postgresql.org/docs/9.2/static/routine-vacuuming.html#AUTOVACUUM
Atentamente,
--
Michael
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Performance autovaccum
2013-07-08 16:14 Performance autovaccum Jeison Bedoya <jeisonb@audifarma.com.co>
@ 2013-07-09 22:14 ` Josh Berkus <josh@agliodbs.com>
2013-07-10 00:41 ` Re: Performance autovaccum Joshua D. Drake <jd@commandprompt.com>
1 sibling, 1 reply; 5+ messages in thread
From: Josh Berkus @ 2013-07-09 22:14 UTC (permalink / raw)
To: pgsql-performance
On 07/08/2013 09:14 AM, Jeison Bedoya wrote:
> Hi, i have a postgresql 9.2.2, but i don´t use autovaccum but i want to
> begin to use it. some recommendation about the optimal configuration?
> or some link to explain it.
Initial configuration:
autovacuum = on
There, you're done. You only do something else if the default
configuraiton is proven not to work for you.
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Performance autovaccum
2013-07-08 16:14 Performance autovaccum Jeison Bedoya <jeisonb@audifarma.com.co>
2013-07-09 22:14 ` Re: Performance autovaccum Josh Berkus <josh@agliodbs.com>
@ 2013-07-10 00:41 ` Joshua D. Drake <jd@commandprompt.com>
2013-07-10 13:12 ` Re: Performance autovaccum Rick Otten <rotten@manta.com>
0 siblings, 1 reply; 5+ messages in thread
From: Joshua D. Drake @ 2013-07-10 00:41 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-performance
On 07/09/2013 03:14 PM, Josh Berkus wrote:
>
> On 07/08/2013 09:14 AM, Jeison Bedoya wrote:
>> Hi, i have a postgresql 9.2.2, but i don´t use autovaccum but i want to
>> begin to use it. some recommendation about the optimal configuration?
>> or some link to explain it.
>
> Initial configuration:
>
> autovacuum = on
>
> There, you're done. You only do something else if the default
> configuraiton is proven not to work for you.
>
Well, and a restart of PostgreSQL. It should also be noted that
autovacuum by default is on. You can check to see if it is currently
running for you by issuing the following command from psql:
show autovacuum;
Other than that JoshB is correct. The default settings for autovacuum
work for the 95% of users out there.
JD
--
Command Prompt, Inc. - http://www.commandprompt.com/ 509-416-6579
PostgreSQL Support, Training, Professional Services and Development
High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc
For my dreams of your image that blossoms
a rose in the deeps of my heart. - W.B. Yeats
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Performance autovaccum
2013-07-08 16:14 Performance autovaccum Jeison Bedoya <jeisonb@audifarma.com.co>
2013-07-09 22:14 ` Re: Performance autovaccum Josh Berkus <josh@agliodbs.com>
2013-07-10 00:41 ` Re: Performance autovaccum Joshua D. Drake <jd@commandprompt.com>
@ 2013-07-10 13:12 ` Rick Otten <rotten@manta.com>
0 siblings, 0 replies; 5+ messages in thread
From: Rick Otten @ 2013-07-10 13:12 UTC (permalink / raw)
To: pgsql-performance
In our use case, the default autovacuum settings did not work, I guess we are in the 5% group of users. The default settings were too aggressive when it ran against some of our larger tables (example: 100M rows by 250 columns) in our front end OLTP database causing severe performance degradation. We had to throttle it back (and haven't had problems since).
You should definitely run autovacuum. If you are only able to experiment with it in production, I recommend taking it slow at first, and gradually making it more aggressive after you have a good handle on the impact (and observe it running on the larger critical tables in your data inventory without impact). You can start with the defaults, they aren't too bad. In our case - a backend for a high performance, highly available website with a set of webservers that are very sensitive to even slight query time changes, the default settings simply consumed too much overhead. I think in the end all we had to change was the autovacuum_vacuum_cost_delay to make enough difference to keep our site up and running. You should review the tuning options though.
The problem is if you kill the autovacuum process because you suspect it is causing issues during a crisis, the autovacuumer will just start it back up again a few minutes later. If you disable it permanently your query performance will likely slowly degrade. You need to find somewhere in between.
Oh - one last note: If you have a partitioned table (partitioned by date), autovacuum will not run against the older partitions (because they are no longer changing). If you've had autovacuum off for a while, you may need to go back and manually vacuum analyze the older partitions to clean them up after you get autovacuum running. (ditto for other old tables that are no longer changing)
-----Original Message-----
From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Joshua D. Drake
Sent: Tuesday, July 09, 2013 8:42 PM
To: Josh Berkus
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Performance autovaccum
On 07/09/2013 03:14 PM, Josh Berkus wrote:
>
> On 07/08/2013 09:14 AM, Jeison Bedoya wrote:
>> Hi, i have a postgresql 9.2.2, but i don´t use autovaccum but i want
>> to begin to use it. some recommendation about the optimal configuration?
>> or some link to explain it.
>
> Initial configuration:
>
> autovacuum = on
>
> There, you're done. You only do something else if the default
> configuraiton is proven not to work for you.
>
Well, and a restart of PostgreSQL. It should also be noted that autovacuum by default is on. You can check to see if it is currently running for you by issuing the following command from psql:
show autovacuum;
Other than that JoshB is correct. The default settings for autovacuum work for the 95% of users out there.
JD
--
Command Prompt, Inc. - http://www.commandprompt.com/ 509-416-6579 PostgreSQL Support, Training, Professional Services and Development High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc For my dreams of your image that blossoms
a rose in the deeps of my heart. - W.B. Yeats
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2013-07-10 13:12 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-07-08 16:14 Performance autovaccum Jeison Bedoya <jeisonb@audifarma.com.co>
2013-07-08 22:21 ` Michael Paquier <michael.paquier@gmail.com>
2013-07-09 22:14 ` Josh Berkus <josh@agliodbs.com>
2013-07-10 00:41 ` Joshua D. Drake <jd@commandprompt.com>
2013-07-10 13:12 ` Rick Otten <rotten@manta.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