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

Re: Caching (was Re: choosing the right platform)

From: "scott(dot)marlowe" <scott(dot)marlowe(at)ihs(dot)com>
To: Ron Mayer <ron(at)intervideo(dot)com>
Cc: Josh Berkus <josh(at)agliodbs(dot)com>,Matthew Nuzum <cobalt(at)bearfruit(dot)org>,"'Pgsql-Performance'" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Caching (was Re: choosing the right platform)
Date: 2003-04-11 15:14:00
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
On Thu, 10 Apr 2003, Ron Mayer wrote:

> Short summary...
>   I think sort_mem matters quite a bit (20-40%) on
>   my data-warehousing applications.
>   Am I doing something wrong to need so much sort_mem?

No.  In fact, it's not uncommon for certain queries to need WAY more sort 
memory than most queries.  The mistake that gets made is setting sort_mem 
to something like 32 meg for every sort.  There are many "sorts" on my 
machine that are coming from well ordered data, and don't really need to 
be done in memory to be reasonably fast.  Those can run fine with 8 meg 
sort_mem.  For things with less well ordered in the database, or where the 
data set is really big (100s of megs of data being sorted) it often helps 
to just grab a 100 meg sort_mem for the session.  

If sort_mem is too big, the OS will likely wind up swapping it or shared 
memory out and thrashing at the worst, or just surrendering all spare 
memory to sort_mem, thus flushing all fs cache.  For a lot of apps, it's 
all about the sweet spot of memory to each subsystem, and sort_mem can go 
from nibbling memory to eating it like Nibbler from Futurama in seconds if 
you set it just a little too high and have the right parallel load on your 

So, as long as you aren't starving your server of resources, setting 
sort_mem higher is fine.

In response to

pgsql-performance by date

Next:From: Josh BerkusDate: 2003-04-11 16:15:15
Subject: Re: Caching (was Re: choosing the right platform)
Previous:From: Josh BerkusDate: 2003-04-11 00:13:19
Subject: Re: Help analyzing 7.2.4 EXPLAIN

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