Thread: Postgres 7.4 and vacuum_cost_delay.
Hi everyone.
I've got a quick and stupid question: Does Postgres 7.4 (7.x) support vacuum_cost_delay?
For all my googles and documentation reading I've determined it's not supported, only because I can't find a 7.x doc or forum post claiming otherwise.
Upgrading to 8.x is out of the question, but I still need to employ something to auto-vacuum a large and active database (possibly more than once a day) in a manner that wouldn't affect load at the wrong time.
If I could combine pg_autovacuum with vacuum_cost_delay I could potentially have a solution. (barring some performance testing)
The only problem with pg_autovacuum is the need for pg_statio, which itself will reduce performance at all times.
Any suggestions?
Thanks!
- Chris
show all and grep are your friend. From my laptop with 8.1: decibel@phonebook.1[16:36]~:4%psql -tc 'show all' | grep vacuum_cost_delay|tr -s ' ' autovacuum_vacuum_cost_delay | -1 | Vacuum cost delay in milliseconds, for autovacuum. vacuum_cost_delay | 0 | Vacuum cost delay in milliseconds. decibel@phonebook.1[16:37]~:5% I don't have a 7.4 copy around, but you can just check it yourself. On Mon, May 01, 2006 at 02:40:41PM -0400, Chris Mckenzie wrote: > Hi everyone. > > I've got a quick and stupid question: Does Postgres 7.4 (7.x) support > vacuum_cost_delay? > > For all my googles and documentation reading I've determined it's not > supported, only because I can't find a 7.x doc or forum post claiming > otherwise. > > Upgrading to 8.x is out of the question, but I still need to employ > something to auto-vacuum a large and active database (possibly more than > once a day) in a manner that wouldn't affect load at the wrong time. > > If I could combine pg_autovacuum with vacuum_cost_delay I could potentially > have a solution. (barring some performance testing) > > The only problem with pg_autovacuum is the need for pg_statio, which itself > will reduce performance at all times. > > Any suggestions? > > Thanks! > > - Chris > > > -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Thanks.
My first check was of course a grep/search of the postgres.conf, next it was a complete source grep for vacuum_cost_delay.
I've come to the conclusion I need to simply start tracking all transactions and determining a cost/performance for the larger and frequently updated tables without the benefit and penalty of pg_statio.
- Chris
-----Original Message-----
From: Jim C. Nasby [mailto:jnasby@pervasive.com]
Sent: Tuesday, May 02, 2006 5:39 PM
To: Chris Mckenzie
Cc: 'pgsql-performance@postgresql.org'
Subject: Re: [PERFORM] Postgres 7.4 and vacuum_cost_delay.
show all and grep are your friend. From my laptop with 8.1:
decibel@phonebook.1[16:36]~:4%psql -tc 'show all' | grep vacuum_cost_delay|tr -s ' ' autovacuum_vacuum_cost_delay | -1 | Vacuum cost delay in milliseconds, for autovacuum. vacuum_cost_delay | 0 | Vacuum cost delay in milliseconds. decibel@phonebook.1[16:37]~:5%
I don't have a 7.4 copy around, but you can just check it yourself.
On Mon, May 01, 2006 at 02:40:41PM -0400, Chris Mckenzie wrote:
> Hi everyone.
>
> I've got a quick and stupid question: Does Postgres 7.4 (7.x) support
> vacuum_cost_delay?
>
> For all my googles and documentation reading I've determined it's not
> supported, only because I can't find a 7.x doc or forum post claiming
> otherwise.
>
> Upgrading to 8.x is out of the question, but I still need to employ
> something to auto-vacuum a large and active database (possibly more
> than once a day) in a manner that wouldn't affect load at the wrong
> time.
>
> If I could combine pg_autovacuum with vacuum_cost_delay I could
> potentially have a solution. (barring some performance testing)
>
> The only problem with pg_autovacuum is the need for pg_statio, which
> itself will reduce performance at all times.
>
> Any suggestions?
>
> Thanks!
>
> - Chris
>
>
>
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
On Tue, May 02, 2006 at 05:47:15PM -0400, Chris Mckenzie wrote:
> Thanks.
>
> My first check was of course a grep/search of the postgres.conf, next it was
> a complete source grep for vacuum_cost_delay.
It's there in head...
decibel@phonebook.1[17:52]~/pgsql/HEAD/src:4%grep -ri vacuum_cost_delay *|wc -l
8
> I've come to the conclusion I need to simply start tracking all transactions
> and determining a cost/performance for the larger and frequently updated
> tables without the benefit and penalty of pg_statio.
Huh? pg_statio shouldn't present a large penalty AFAIK...
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
On Mon, May 01, 2006 at 02:40:41PM -0400, Chris Mckenzie wrote: > I've got a quick and stupid question: Does Postgres 7.4 (7.x) support > vacuum_cost_delay? No, it does not; it was introduced in 8.0. /* Steinar */ -- Homepage: http://www.sesse.net/
On Tue, May 02, 2006 at 05:47:15PM -0400, Chris Mckenzie wrote:
> I've come to the conclusion I need to simply start tracking all transactions
> and determining a cost/performance for the larger and frequently updated
> tables without the benefit and penalty of pg_statio.
I'll bet it won't help you. If you can't get off 7.4 on a busy
machine, you're going to get hosed by I/O sometimes no matter what.
My suggestion is to write a bunch of rule-of-thumb rules for your
cron jobs, and start planning your upgrade.
Jan back-patched the vacuum stuff to 7.4 for us (Afilias), and we
tried playing with it; but it didn't really make the difference we'd
hoped.
The reason for this is that 7.4 also doesn't have the bg_writer. So
you're still faced with I/O storms, no matter what you do. If I were
in your shoes, I wouldn't waste a lot of time on trying to emulate
the new features in 7.4.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
In the future this spectacle of the middle classes shocking the avant-
garde will probably become the textbook definition of Postmodernism.
--Brad Holland
Hey, thanks for the advice.
Sticking with 7.4 isn't my call. There's a lot wrapped up in common usage of Postgres 7.4 and I could never rally everyone into moving forward. (at least not this year)
I've yet to prove (due to my current lack of statistical evidence) that our usage of 7.4 results in frequent vacuums impacting access. (it get more difficult to speculate when considering a large slony cluster) I'm hoping to gather some times and numbers on an internal dogfood of our product shortly.
Any advice on tracking vacuum performance and impact? I was thinking of just system timing the vacuumdb calls and turning on verbose for per-table/index stats. Do you think that's enough info?
Once I vacuum I won't be able to re-test any fragmentation that the vacuum cleaned up, so its all or nothing for this test.
Thanks again.
- Chris
-----Original Message-----
From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Andrew Sullivan
Sent: Thursday, May 04, 2006 8:28 AM
To: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Postgres 7.4 and vacuum_cost_delay.
On Tue, May 02, 2006 at 05:47:15PM -0400, Chris Mckenzie wrote:
> I've come to the conclusion I need to simply start tracking all
> transactions and determining a cost/performance for the larger and
> frequently updated tables without the benefit and penalty of
> pg_statio.
I'll bet it won't help you. If you can't get off 7.4 on a busy machine, you're going to get hosed by I/O sometimes no matter what.
My suggestion is to write a bunch of rule-of-thumb rules for your cron jobs, and start planning your upgrade.
Jan back-patched the vacuum stuff to 7.4 for us (Afilias), and we tried playing with it; but it didn't really make the difference we'd hoped.
The reason for this is that 7.4 also doesn't have the bg_writer. So you're still faced with I/O storms, no matter what you do. If I were in your shoes, I wouldn't waste a lot of time on trying to emulate the new features in 7.4.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
In the future this spectacle of the middle classes shocking the avant- garde will probably become the textbook definition of Postmodernism.
--Brad Holland
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster