Skip site navigation (1) Skip section navigation (2)

Re: 2GB or not 2GB

From: Magnus Hagander <magnus(at)hagander(dot)net>
To: "Joshua D(dot) Drake" <jd(at)commandprompt(dot)com>
Cc: josh(at)agliodbs(dot)com, pgsql-performance(at)postgresql(dot)org
Subject: Re: 2GB or not 2GB
Date: 2008-05-29 19:50:48
Message-ID: 20080529215048.11314145@mha-laptop.hagander.net (view raw or flat)
Thread:
Lists: pgsql-performance
Joshua D. Drake wrote:
> 
> 
> On Wed, 2008-05-28 at 16:59 -0700, Josh Berkus wrote:
> > Folks,
> 
> > shared_buffers:  according to witnesses, Greg Smith presented at
> > East that based on PostgreSQL's buffer algorithms, buffers above
> > 2GB would not really receive significant use.  However, Jignesh
> > Shah has tested that on workloads with large numbers of
> > connections, allocating up to 10GB improves performance. 
> 
> I have seen multiple production systems where upping the buffers up to
> 6-8GB helps. What I don't know, and what I am guessing Greg is
> referring to is if it helps as much as say upping to 2GB. E.g; the
> scale of performance increase goes down while the actual performance
> goes up (like adding more CPUs).

That could be it. I'm one of the people who recall *something* about
it, but I don't remember any specifics :-)


> > sort_mem: My tests with 8.2 and DBT3 seemed to show that, due to 
> > limitations of our tape sort algorithm, allocating over 2GB for a
> > single sort had no benefit.  However, Magnus and others have
> > claimed otherwise. Has this improved in 8.3?
> 
> I have never see work_mem (there is no sort_mem Josh) do any good
> above 1GB. Of course, I would never willingly use that much work_mem
> unless there was a really good reason that involved a guarantee of
> not calling me at 3:00am.

I have. Not as a system-wide setting, but for a single batch job doing
*large* queries. Don't recall exactly, but it wasn't necessarily for
sort - might have been for hash. I've seen it make a *big* difference.

maintenance_work_mem, however, I didn't see much difference upping it
past 1Gb or so.


//Magnus

In response to

pgsql-performance by date

Next:From: Jignesh K. ShahDate: 2008-05-29 22:08:10
Subject: ProcArrayLock (The Saga continues)
Previous:From: Tom LaneDate: 2008-05-29 17:59:11
Subject: Re: Adding "LIMIT 1" kills performance.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group