DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type:content-transfer-encoding;
	b=M10iqqDuS++Gu47oQDt7mIooD7nG93o2vIUvlB3IW1aLOkYWsI6f8WeaTN+NPc7+Xe
	ioHLmCxsRE+gw6NDWmA0vXkPR2oO9eoGEOyQ9OmTeCZRDuPSkmthrhMS7B3ZcLh3I684
	3A6x39a5OKt49eoH9IURjwwD460miqPc2FCwE=
MIME-Version: 1.0
In-Reply-To: <4B87D16B.2E1C.0042.0@homedecorators.com>
References: <8a547c841002252212t382cfeb4pde3e668a9d3f2f10@mail.gmail.com>
	<1267166541.2094.52.camel@hp-laptop2.gunduz.org>
	<8a547c841002261052r3bff71f2v6759859abce72710@mail.gmail.com>
	<4B87D16B.2E1C.0042.0@homedecorators.com>
Date: Fri, 26 Feb 2010 15:24:42 -0800
Message-ID: <8a547c841002261524y138eb983oa4fb4d691c85ca0c@mail.gmail.com>
Subject: Re: bgwriter, checkpoints, curious (seeing delays)
From: Tory M Blue <tmblue@gmail.com>
To: Jorge Montero <jorge_montero@homedecorators.com>
Cc: pgsql-performance@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

On Fri, Feb 26, 2010 at 11:49 AM, Jorge Montero
<jorge_montero@homedecorators.com> wrote:
>
>
>>>> Tory M Blue <tmblue@gmail.com> 02/26/10 12:52 PM >>>
>>>
>>> This is too much. Since you have 300 connections, you will probably swa=
p
>>> because of this setting, since each connection may use this much
>>> work_mem. The rule of the thumb is to set this to a lower general value
>>> (say, 1-2 MB), and set it per-query when needed.

>
> 1-2MB is good enough for many families of queries, but it's hard to say w=
hat the right default should be for you. The right number can be estimated =
by running explain analyze on your most common queries, with parameters tha=
t are representative to regular use, and see how much memory they actually =
claim to use. In my case, for example, most of my queries do just fine with=
 10 MB, while the reporting queries that accumulate quite a bit of deta req=
uest up to 60MB.
>
> If your average query needs 100 MB, it'd still mean that 40 connections t=
ake 4 gigs worth of work memory, which might be better spent caching the da=
tabase.

Ya my boxes are pretty well stacked, but a question. How does one get
the memory usage of a query. You state to look at explain analyze but
this gives timing and costs, but is one of the numbers memory or do I
have to take values and do some math?

---------------------------------------------------------------------------=
-----------------------------------------------
 Function Scan on listings_search  (cost=3D0.00..260.00 rows=3D1000
width=3D108) (actual time=3D904.374..904.383 rows=3D10 loops=3D1)
 Total runtime: 904.411 ms

Thanks
Tory

Also don't think this 5 second thing is the DB.. Sure is not checkpoints.