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

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
server.

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