DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.co.uk;
	h=Received:X-Mailer:Date:From:Reply-To:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-ID;
	b=PXKXg124UoHteNJpoX2kM5Re3hYyT40vWjNFaLjECM+L/fr0HwDFQkYzaAVItGWMq0215VxN70f6V9XsRLuDRiX57c8sMPYBNTi07SaYcpwrKAztn75vyIXSaU/Hxz7v9EqapLTLhdoAVVMhu7uSHSZD2S/+mRzInUFja3Yrf1A=;
Date: Wed, 30 Jul 2008 14:57:47 +0000 (GMT)
From: Valentin Bogdanov <valiouk@yahoo.co.uk>
Reply-To: valiouk@yahoo.co.uk
Subject: Re: Database size Vs performance degradation
To: pgsql-performance@postgresql.org, Dave North <DNorth@signiant.com>
In-Reply-To: 
 <35FABCF85D99464FB00BC5123DC2A70A051D0FB8@s228130hz1ew09.apptix-01.savvis.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Message-ID: <276541.4189.qm@web25805.mail.ukl.yahoo.com>

I am guessing that you are using DELETE to remove the 75,000 unimportant.
Change your batch job to CREATE a new table consisting only of the 5,000 im=
portant. You can use "CREATE TABLE table_name AS select_statement" command.=
 Then drop the old table. After that you can use ALTER TABLE to change the =
name of the new table to that of the old one.=20

I am not an expert but if this is a viable solution for you then I think do=
ing it this way will rid you of your bloating problem.

Regards,
Val


--- On Wed, 30/7/08, Dave North <DNorth@signiant.com> wrote:

> From: Dave North <DNorth@signiant.com>
> Subject: [PERFORM] Database size Vs performance degradation
> To: pgsql-performance@postgresql.org
> Date: Wednesday, 30 July, 2008, 1:09 PM
> Morning folks,
> =09Long time listener, first time poster.  Having an
> interesting
> problem related to performance which I'll try and
> describe below and
> hopefully get some enlightenment.  First the environment:
>=20
>=20
> Postgres 8.1.8
> =09shared_buffers =3D 2000
> =09max_fsm_pages =3D 400000
> Redhat Enterprise 4
> Running on HP DL380 w/ 4GB RAM, dual 10K HDDs in RAID 0+1
> Also running on the server is a tomcat web server and other
> ancillaries
>=20
> Now, the problem.  We have an application that continually
> writes a
> bunch of data to a few tables which is then deleted by a
> batch job each
> night.  We're adding around 80,000 rows to one table
> per day and
> removing around 75,000 that are deemed to be
> "unimportant".  Now, the
> problem we see is that after a period of time, the database
> access
> becomes very 'slow' and the load avg on the machine
> gets up around 5.
> When this happens, the application using the DB basically
> grinds to a
> halt.  Checking the stats, the DB size is around 7.5GB; no
> tables or
> indexes look to be 'bloated' (we have been using
> psql since 7.3 with the
> classic index bloat problem) and the auto-vac has been
> running solidly.
>=20
> We had this problem around a month ago and again yesterday.
>  Because the
> application needs reasonably high availability, we
> couldn't full vacuum
> so what we did was a dump and load to another system.  What
> I found here
> was that after the load, the DB size was around 2.7GB - a
> decrease of
> 5GB.  Re-loading this back onto the main system, and the
> world is good.
>=20
> One observation I've made on the DB system is the disk
> I/O seems
> dreadfully slow...we're at around 75% I/O wait
> sometimes and the read
> rates seem quite slow (hdparm says around 2.2MB/sec -
> 20MB/sec for
> un-cached reads).  I've also observed that the OS cache
> seems to be
> using all of the remaining memory for it's cache
> (around 3GB) which
> seems probably the best it can do with the available
> memory.
>=20
> Now, clearly we need to examine the need for the
> application to write
> and remove so much data but my main question is:
>=20
> Why does the size of the database with so much
> "un-used" space seem to
> impact performance so much?  If (in this case) the extra
> 5GB of space is
> essentially "unallocated", does it factor into
> any of the caching or
> performance metrics that the DBMS uses?  And if so, would I
> be better
> having a higher shared_buffers rather than relying so much
> on OS cache?
>=20
> Yes, I know we need to upgrade to 8.3 but that's going
> to take some time
> :)
>=20
> Many thanks in advance.
>=20
> Dave
>=20
> ___
> Dave North
> dnorth@signiant.com
> Signiant - Making Media Move
> Visit Signiant at: www.signiant.com
> <http://www.signiant.com/> =20
>=20
>=20
> --=20
> Sent via pgsql-performance mailing list
> (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance=0A=0A=0A      ______=
____________________________________________________=0ANot happy with your =
email address?.=0AGet the one you really want - millions of new email addre=
sses available now at Yahoo! http://uk.docs.yahoo.com/ymail/new.html