pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Jeff Trout <jeff@jefftrout.com>
To: Marinos J. Yannikos <mjy@geizhals.at>
Cc: pgsql-performance@postgresql.org
Cc: josh@agliodbs.com
Subject: Re: optimization ideas for frequent, large(ish) updates
Date: Sun, 15 Feb 2004 12:20:38 -0500
Message-ID: <460CEC02-5FDB-11D8-8142-000D9366F0C4@jefftrout.com> (raw)
In-Reply-To: <402ED348.7000800@geizhals.at>
References: <402C213A.4030105@geizhals.at>
	<200402122228.41680.josh@agliodbs.com>
	<402ED348.7000800@geizhals.at>


On Feb 14, 2004, at 9:02 PM, Marinos J. Yannikos wrote:

> Josh Berkus wrote:
>
>> 800MB for sort mem?   Are you sure you typed that correctly?   You 
>> must be counting on not having a lot of concurrent queries.  It sure 
>> will speed up index updating, though!
>
> 800MB is correct, yes... There are usually only 10-30 postgres 
> processes  active (imagine 5-10 people working on the web front-end 
> while cron jobs access the db occasionally). Very few queries can use 
> such large amounts of memory for sorting, but they do exist.
>

Remember that it is going to allocate 800MB per sort.  It is not "you 
can allocate up to 800MB, so if you need 1 meg, use one meg".  Some 
queries may end up having a few sort steps.

In terms of sort mem it is best to set a system default to a nice good 
value for most queries.  and then in your reporting queries or other 
ones set sort_mem for that session (set sort_mem = 800000) then only 
that session will use the looney sort_mem

It would be interesting to know if your machine is swapping.
--
Jeff Trout <jeff@jefftrout.com>
http://www.jefftrout.com/
http://www.stuarthamm.net/




view thread (10+ messages)  latest in thread

Message-ID: <460CEC02-5FDB-11D8-8142-000D9366F0C4@jefftrout.com>
Permalink:  ../460CEC02-5FDB-11D8-8142-000D9366F0C4@jefftrout.com/
Also on:    postgresql.org/message-id/460CEC02-5FDB-11D8-8142-000D9366F0C4@jefftrout.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: jeff@jefftrout.com, mjy@geizhals.at, josh@agliodbs.com
  Subject: Re: optimization ideas for frequent, large(ish) updates
  In-Reply-To: <460CEC02-5FDB-11D8-8142-000D9366F0C4@jefftrout.com>
  References: <402C213A.4030105@geizhals.at> <200402122228.41680.josh@agliodbs.com> <402ED348.7000800@geizhals.at> <460CEC02-5FDB-11D8-8142-000D9366F0C4@jefftrout.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox