Received: from maia.hub.org (maia-3.hub.org [200.46.204.243])
	by mail.postgresql.org (Postfix) with ESMTP id DA2A4633439
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Fri, 27 Aug 2010 14:28:40 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia,
	port 10024) with ESMTP id 07640-02
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Fri, 27 Aug 2010 17:28:33 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from moutng.kundenserver.de (moutng.kundenserver.de
	[212.227.126.171])
	by mail.postgresql.org (Postfix) with ESMTP id 79F5C634669
	for <pgsql-performance@postgresql.org>;
	Fri, 27 Aug 2010 14:28:33 -0300 (ADT)
Received: from [192.168.1.6] (static-71-179-240-8.bltmmd.fios.verizon.net
	[71.179.240.8])
	by mrelayeu.kundenserver.de (node=mreu2) with ESMTP (Nemesis)
	id 0MM2cS-1OljKl1poy-008Ihp; Fri, 27 Aug 2010 19:28:32 +0200
Message-ID: <4C77F5BE.7070202@2ndquadrant.com>
Date: Fri, 27 Aug 2010 13:28:30 -0400
From: Greg Smith <greg@2ndquadrant.com>
User-Agent: Thunderbird 2.0.0.24 (X11/20100317)
MIME-Version: 1.0
To: Willy-Bas Loos <willybas@gmail.com>
CC: Pierre C <lists@peufeu.com>, acmmailing@tweakers.net,
	pgsql-performance@postgresql.org
Subject: Re: turn off caching for performance test
References: <AANLkTi=dBXkp2qMHZOR_gd9eD+cjwMqOwg-N6POGq+ji@mail.gmail.com>
	<op.vh128jb5eorkce@apollo13>
	<AANLkTimT19FjqVB+_kjevwbW9wG7FuV6O+nSrK0p6hYc@mail.gmail.com>
In-Reply-To: <AANLkTimT19FjqVB+_kjevwbW9wG7FuV6O+nSrK0p6hYc@mail.gmail.com>
Content-Type: multipart/alternative;
	boundary="------------070506050402060900090800"
X-Provags-ID: V02:K0:IAdIC2Tuy7G2qrhbm8nsWT6a1wnytXcyrzrXGj8elqo
	1H3HscMtlZ13fVpx9whssxA5c3tkkftIJF1jDeyl83cqLwcet9
	jK70RM0tng9OfCvlGSCEgdp9yvkfrkYIN/0sbq0O4kN1wntCK0
	OY7RWEVcCnWuyVVBK7zcgxV7dnQbnsFdBpaoPkXcXTZkpUJMTF
	liQP+TeXMM6b+otSELBwE8u6Nwbh1i2iUxW4mXsTWM=
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.899 tagged_above=-10 required=5
	tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_NONE=-0.0001
X-Spam-Level: 
X-Archive-Number: 201008/296
X-Sequence-Number: 40139

This is a multi-part message in MIME format.
--------------070506050402060900090800
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Willy-Bas Loos wrote:
> But that's only the OS cache. There's also the shared_buffers, which 
> are a postgres specific thing.
> I've found DISCARD in the manual 
> <http://www.postgresql.org/docs/8.3/interactive/sql-discard.html>, but 
> that only influences a single session, not the shared buffers.
>
> I reckon restarting the cluster should help, would it wipe out the 
> cache? (pg_ctlcluster 8.3 main restart)
> Or is there a more graceful way?

Stop the cluster; flush the OS cache; start the cluster again.  Now you 
have a clean cache to retest again.  No easier way that's reliable.  If 
you try to clear out the database by doing things like scanning large 
tables not involved in the query, you'll discover features in PostgreSQL 
will specifically defeat that from using more than a small portion of 
the cache.  Better to just do a full shutdown.

-- 
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com   www.2ndQuadrant.us


--------------070506050402060900090800
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Willy-Bas Loos wrote:
<blockquote
 cite="mid:AANLkTimT19FjqVB+_kjevwbW9wG7FuV6O+nSrK0p6hYc@mail.gmail.com"
 type="cite">But that's only the OS cache. There's also the
shared_buffers, which are a postgres specific thing.<br>
I've found DISCARD in the <a moz-do-not-send="true"
 href="http://www.postgresql.org/docs/8.3/interactive/sql-discard.html">manual</a>,
but that only influences a single session, not the shared buffers.<br>
  <br>
I reckon restarting the cluster should help, would it wipe out the
cache? (pg_ctlcluster 8.3 main restart)<br>
Or is there a more graceful way?<br>
</blockquote>
<br>
Stop the cluster; flush the OS cache; start the cluster again.&nbsp; Now you
have a clean cache to retest again.&nbsp; No easier way that's reliable.&nbsp; If
you try to clear out the database by doing things like scanning large
tables not involved in the query, you'll discover features in
PostgreSQL will specifically defeat that from using more than a small
portion of the cache.&nbsp; Better to just do a full shutdown.<br>
<br>
<pre class="moz-signature" cols="72">-- 
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
<a class="moz-txt-link-abbreviated" href="mailto:greg@2ndQuadrant.com">greg@2ndQuadrant.com</a>   <a class="moz-txt-link-abbreviated" href="http://www.2ndQuadrant.us">www.2ndQuadrant.us</a>
</pre>
</body>
</html>

--------------070506050402060900090800--