Re: [PERFORM] How filesystems matter with PostgreSQL

2010-06-05 Thread Ron Mayer
Jon Schewe wrote: > OK, so if I want the 15 minute speed, I need to give up safety (OK in > this case as this is just research testing), or see if I can tune > postgres better. Depending on your app, one more possibility would be to see if you can re-factor the application so it can do multiple w

Re: [PERFORM] How filesystems matter with PostgreSQL

2010-06-05 Thread Scott Marlowe
On Sat, Jun 5, 2010 at 6:07 PM, Jon Schewe wrote: > On 06/05/2010 07:02 PM, Scott Marlowe wrote: >> On Sat, Jun 5, 2010 at 5:58 PM, Jon Schewe wrote: >> >>> On 06/05/2010 06:54 PM, Scott Marlowe wrote: >>> On Sat, Jun 5, 2010 at 5:03 PM, Jon Schewe wrote: > On 06/05/2010 05:52

Re: [PERFORM] How filesystems matter with PostgreSQL

2010-06-05 Thread Jon Schewe
On 06/05/2010 07:02 PM, Scott Marlowe wrote: > On Sat, Jun 5, 2010 at 5:58 PM, Jon Schewe wrote: > >> On 06/05/2010 06:54 PM, Scott Marlowe wrote: >> >>> On Sat, Jun 5, 2010 at 5:03 PM, Jon Schewe wrote: >>> >>> On 06/05/2010 05:52 PM, Greg Smith wrote:

Re: [PERFORM] How filesystems matter with PostgreSQL

2010-06-05 Thread Scott Marlowe
On Sat, Jun 5, 2010 at 5:58 PM, Jon Schewe wrote: > On 06/05/2010 06:54 PM, Scott Marlowe wrote: >> On Sat, Jun 5, 2010 at 5:03 PM, Jon Schewe wrote: >> >>> >>> On 06/05/2010 05:52 PM, Greg Smith wrote: >>> Jon Schewe wrote: >>   If that's the case, what you've measured is which fil

Re: [PERFORM] How filesystems matter with PostgreSQL

2010-06-05 Thread Jon Schewe
On 06/05/2010 06:54 PM, Scott Marlowe wrote: > On Sat, Jun 5, 2010 at 5:03 PM, Jon Schewe wrote: > >> >> On 06/05/2010 05:52 PM, Greg Smith wrote: >> >>> Jon Schewe wrote: >>> > If that's the case, what you've measured is which filesystems are > safe because they default t

Re: [PERFORM] How filesystems matter with PostgreSQL

2010-06-05 Thread Scott Marlowe
On Sat, Jun 5, 2010 at 5:03 PM, Jon Schewe wrote: > > > On 06/05/2010 05:52 PM, Greg Smith wrote: >> Jon Schewe wrote:   If that's the case, what you've measured is which filesystems are safe because they default to flushing drive cache (the ones that take around 15 minutes) and whi

Re: [PERFORM] How filesystems matter with PostgreSQL

2010-06-05 Thread Jon Schewe
On 06/05/2010 05:52 PM, Greg Smith wrote: > Jon Schewe wrote: >>> If that's the case, what you've measured is which filesystems are >>> safe because they default to flushing drive cache (the ones that take >>> around 15 minutes) and which do not (the ones that take >=around 2 >>> hours). You c

Re: [PERFORM] How filesystems matter with PostgreSQL

2010-06-05 Thread Greg Smith
Jon Schewe wrote: If that's the case, what you've measured is which filesystems are safe because they default to flushing drive cache (the ones that take around 15 minutes) and which do not (the ones that take >=around 2 hours). You can't make ext3 flush the cache correctly no matter what you

Re: [PERFORM] Weird XFS WAL problem

2010-06-05 Thread Greg Smith
Kevin Grittner wrote: I don't know at the protocol level; I just know that write barriers do *something* which causes our controllers to wait for actual disk platter persistence, while fsync does not It's in the docs now: http://www.postgresql.org/docs/9.0/static/wal-reliability.html FLUSH

Re: [PERFORM] How filesystems matter with PostgreSQL

2010-06-05 Thread Jon Schewe
On 06/05/2010 05:36 PM, Greg Smith wrote: > Jon Schewe wrote: >> The tests were all done on an opensuse 11.2 64-bit machine, >> on the same hard drive (just ran mkfs between each test) on the same >> input with the same code base. > > So no controller card, just the motherboard and a single hard dr

Re: [PERFORM] How filesystems matter with PostgreSQL

2010-06-05 Thread Greg Smith
Jon Schewe wrote: The tests were all done on an opensuse 11.2 64-bit machine, on the same hard drive (just ran mkfs between each test) on the same input with the same code base. So no controller card, just the motherboard and a single hard drive? If that's the case, what you've measured is wh

Re: [PERFORM] slow query

2010-06-05 Thread Scott Marlowe
On Sat, Jun 5, 2010 at 8:02 AM, Anj Adu wrote: > Thanks..I'll try this. Should I also rebuild the contrib modules..or > just the core postgres database? That's really up to you. If you use a contrib module in particular, I'd definitely rebuild that one. It's pretty easy anyway. -- Sent via pg

Re: [PERFORM] slow query

2010-06-05 Thread Anj Adu
Thanks..I'll try this. Should I also rebuild the contrib modules..or just the core postgres database? On Sat, Jun 5, 2010 at 2:38 AM, Scott Marlowe wrote: > On Fri, Jun 4, 2010 at 12:21 PM, Anj Adu wrote: >> The behaviour is different in postgres 8.1.9 (much faster)  (the table >> has 9 million

Re: [PERFORM] slow query

2010-06-05 Thread hubert depesz lubaczewski
On Thu, Jun 03, 2010 at 06:45:30PM -0700, Anj Adu wrote: > http://explain.depesz.com/s/kHa can you please show us \d dev4_act_dy_fact_2010_05_t3 ? depesz -- Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/ jid/gtalk: dep...@depesz.com / aim:depeszhdl / skype:depesz_h

Re: [PERFORM] slow query

2010-06-05 Thread Scott Marlowe
On Fri, Jun 4, 2010 at 12:21 PM, Anj Adu wrote: > The behaviour is different in postgres 8.1.9 (much faster)  (the table > has 9 million rows instead of 25 million..but the query comes back > very fast (8 seconds).. > > Wonder if this is very specific to 8.4.0 You should really be running 8.4.4.