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 | [EMAIL PROTECTED] 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
Re: [PERFORM] Postgres 7.4 and vacuum_cost_delay.
Title: RE: [PERFORM] Postgres 7.4 and vacuum_cost_delay. 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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] 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 | [EMAIL PROTECTED] 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
Re: [PERFORM] Postgres 7.4 and vacuum_cost_delay.
show all and grep are your friend. From my laptop with 8.1: [EMAIL PROTECTED]: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. [EMAIL PROTECTED]: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 [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Postgres 7.4 and vacuum_cost_delay.
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... [EMAIL PROTECTED]: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 [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Postgres 7.4 and vacuum_cost_delay.
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/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
[PERFORM] Postgres 7.4 and vacuum_cost_delay.
Title: 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
