DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:reply-to:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=CPlc91dCVEaem9BDyf6VNfeccp9rlex2n+4MgS+C0AUyR111d48mPDKiiQ1ZorSB5UiGLQkD6xeyWRBPIJV2xXxMpmNfZZqrKr0dCl1/gySGAoeVYbsxMk5VnIgHiqMei6r8sOJnt7UVhzaOLKDLcMX2OEIuRpWgDZ0eb2ZZ+H0=
Message-ID: <4a0cafe20505310617d803a0c@mail.gmail.com>
Date: Tue, 31 May 2005 08:17:31 -0500
From: Josh Close <narshe@gmail.com>
Reply-To: Josh Close <narshe@gmail.com>
To: POSTGRES-PERFORMANCE <pgsql-performance@postgresql.org>
Subject: Re: slow queries, possibly disk io
In-Reply-To: <429B90D1.9030402@wildcash.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
References: <4a0cafe205052613242e0d4d10@mail.gmail.com>
	<758d5e7f05052614232f6c9164@mail.gmail.com>
	<4a0cafe205052706043ef8f53f@mail.gmail.com>
	<4a0cafe205052706042d4e60e5@mail.gmail.com>
	<14584.1117204158@sss.pgh.pa.us> <429B90D1.9030402@wildcash.com>

I didn't see iostat as available to install, but I'm using dstat to see thi=
s.

The server has constant disk reads averaging around 50M and quite a
few in the 60M range. This is when selects are being done, which is
almost always. I would think if postgres is grabbing everything from
memory that this wouldn't happen. This is why I think there must be
some way to allocate more mem to postgres.

There is 2 gigs of mem in this server. Here are my current settings.

max_connections =3D 100
shared_buffers =3D 50000
sort_mem =3D 4096
vacuum_mem =3D 32768
effective_cache_size =3D 450000

Shared buffers is set to 10% of total mem. Effective cache size is 90% of m=
em.

Is there anything that can be done to have postgres grab more from
memory rather than disk?


On 5/30/05, Rudi Starcevic <tech@wildcash.com> wrote:
> Hi,
>=20
> I had some disk io issues recently with NFS, I found the command 'iostat
> -x 5' to be a great help when using Linux.
>=20
> For example here is the output when I do a 10GB file transfer onto hdc
> Device:    rrqm/s wrqm/s   r/s   w/s  rsec/s  wsec/s    rkB/s    wkB/s
> avgrq-sz avgqu-sz   await  svctm  %util
> hdc          0.00 875.95  0.00 29.66    0.00 7244.89     0.00  3622.44
> 244.27     3.07  103.52   1.78   5.27
>=20
> The last field show the disk is 5.27% busy.
>=20
> I have seen this value at 100%, adding more server brought it under 100%.
> It seems that if you hit 100% problems sort of cascade all over that
> place. For example Apache connections went right up and hit their max.
>=20
> I am not sure how accurate the % is but it has work pretty well for me.
>=20
> Perhaps use this command in another window with you run your SQL and see
> what it shows.
>=20
> HTH.
> Kind regards,
> Rudi.
>=20
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>=20


--=20
-Josh