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/
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