On Thu, Aug 19, 2010 at 12:06 AM, Samuel Gendler <sgend...@ideasculptor.com>wrote:
> Incidentally, if I set values on the connection before querying, is there > an easy way to get things back to default values or will my code need to > know the prior value and explicitly set it back? Something like > > <get connection from pool> > set work_mem = '512MB' > query > set value = 'default' > <return connection to pool> > > or maybe > > <get connection from pool> > BEGIN; > set work_mem='512MB' > select query > ROLLBACK; > <return connection to pool> > > I guess I'm getting the hang of this whole postgres thing because those were both wild guesses and both of them appear to work. set work_mem=default sets it to the value in the config file, and setting within a transaction and rolling back also restores the original value. > > On Wed, Aug 18, 2010 at 11:52 PM, Scott Marlowe > <scott.marl...@gmail.com>wrote: > >> Exactly, it's about the concurrency. I have a server with 128G ram >> but it runs dozens of queries at a time for hundreds of clients a >> second. The chance that something big for work_mem might jump up and >> bite me are pretty good there. Even so, at 16Meg it's not really big >> for that machine, and I might test cranking it up. Note that large >> work_mem can cause the kernel to flush its cache, which means going to >> disk for everybody's data, and all the queries are slow instead of >> one. Keep an eye on how high work_mem affects your kernel cache. >> >> On Thu, Aug 19, 2010 at 12:38 AM, Samuel Gendler >> <sgend...@ideasculptor.com> wrote: >> > Yeah, although with 48GB of available memory and not that much >> concurrency, >> > I'm not sure it matters that much. But point taken, I'll see about >> modifying >> > the app such that work_mem gets set on a per-query basis. >> > >> > On Wed, Aug 18, 2010 at 11:24 PM, Scott Marlowe < >> scott.marl...@gmail.com> >> > wrote: >> >> >> >> On Wed, Aug 18, 2010 at 11:45 PM, Samuel Gendler >> >> <sgend...@ideasculptor.com> wrote: >> >> > Answered my own question. Cranking work_mem up to 350MB revealed >> that >> >> > the in-memory sort requires more memory than the disk sort. >> >> >> >> Note that unless you run VERY few client connections, it's usually >> >> better to leave work_mem somewhere in the 1 to 32Meg range and have >> >> the connection or user or database that needs 350Meg be set there. >> >> >> >> I.e. >> >> >> >> <connect> >> >> set work_mem='512MB'; >> >> <execute query >> >> >> >> OR >> >> >> >> alter user memoryhog set work_mem='512MB'; >> >> >> >> OR >> >> >> >> alter database memhogdb set work_mem='512MB'; >> > >> > >> >> >> >> -- >> To understand recursion, one must first understand recursion. >> > >
