Subject: Re: Postgres becoming slow, only full vacuum fixes it
Mime-Version: 1.0 (Apple Message framework v1278)
Content-Type: text/plain; charset=iso-8859-1
From: Kiriakos Tsourapas <ktsour@gmail.com>
In-Reply-To: <50605035.8020107@ulb.ac.be>
Date: Mon, 24 Sep 2012 16:51:44 +0300
Cc: pgsql-performance@postgresql.org
Content-Transfer-Encoding: quoted-printable
Message-Id: <3A53BCFC-561D-4C75-96D8-5DB3DCC4DC93@gmail.com>
References: <D15ECA12-8D6E-4E53-A294-5E175564CA61@gmail.com>
	<71DC95CE-16EF-4D53-9971-C573DCD30622@gmail.com>
	<50605035.8020107@ulb.ac.be>
To: Julien Cigar <jcigar@ulb.ac.be>

Hi,

Thank you for your response.
Please find below my answers/comments.


On Sep 24, 2012, at 15:21, Julien Cigar wrote:

> Hello,
>=20
> 1) upgrade your PostgreSQL installation, there have been numerous =
bugfixes releases since 8.4.2
Not possible right now. It will have to be the last solution.
> 2) you'll have to show us an explain analyze of the slow queries. If I =
take a look at those you provided everything run i less than 1ms.
Will do so in a couple of days that it will get slow again.
> 3) with 200 records you'll always have a seqscan
Does it really matter? I mean, with 200 records any query should be =
ultra fast. Right ?
> 4) how much memory do you have ? shared_buffers =3D 256MB and =
effective_cache_size =3D 512MB looks OK only if you have between 1 and =
2GB of RAM
I have included the server specs and the results of top commands, =
showing that we have 8GB ram and how much memory is used/cached/swapped. =
Personally I don't quite understand the linux memory, but I have posted =
them hoping you may see something I don't.
> 5) synchronous_commit =3D off should only be used if you have a =
battery-backed write cache.
I agree with the comments that have followed my post. I have changed it, =
knowing there is a small risk, but hoping it will help our performance.
> 6) autovacuum_naptime should be changed only if autovacuum is =
constantly running (so if you have dozen of databases in your cluster)
As I said, changing the autovacuum values have not changed the problem. =
So, you may as well consider that we have the default values for =
autovacuuming... the problem existed with the default values too.
> 7) are you sure the problem isn't related to Bucardo ?
Not at all sure... I have no idea. Can you suggest of a way to figure it =
out ?


Thank you=