Message-ID: <50606ABD.7090609@ulb.ac.be>
Date: Mon, 24 Sep 2012 16:14:21 +0200
From: Julien Cigar <jcigar@ulb.ac.be>
Organization: Belgian Biodiversity Platform
User-Agent: Mozilla/5.0 (X11; FreeBSD i386;
	rv:12.0) Gecko/20120503 Thunderbird/12.0
MIME-Version: 1.0
To: Kiriakos Tsourapas <ktsour@gmail.com>
CC: pgsql-performance@postgresql.org
Subject: Re: Postgres becoming slow, only full vacuum fixes it
References: <D15ECA12-8D6E-4E53-A294-5E175564CA61@gmail.com>
	<71DC95CE-16EF-4D53-9971-C573DCD30622@gmail.com>
	<50605035.8020107@ulb.ac.be>
	<3A53BCFC-561D-4C75-96D8-5DB3DCC4DC93@gmail.com>
In-Reply-To: <3A53BCFC-561D-4C75-96D8-5DB3DCC4DC93@gmail.com>
Content-Type: multipart/mixed; boundary="------------020805070400090003090705"

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

On 09/24/2012 15:51, Kiriakos Tsourapas wrote:
> Hi,
>
> Thank you for your response.
> Please find below my answers/comments.
>
>
> On Sep 24, 2012, at 15:21, Julien Cigar wrote:
>
>> Hello,
>>
>> 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 ?

right..!

>> 4) how much memory do you have ? shared_buffers = 256MB and effective_cache_size = 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.

with 8GB of RAM I would start with shared_buffers to 1GB and 
effective_cache_size to 4GB. I would also change the default work_mem to 
32MB and maintenance_work_mem to 512MB

>> 5) synchronous_commit = 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 ?

Unfortunately I never used Bucardo, but be sure that it's not a problem 
with your network (and that you understand all the challenges involved 
in multi-master replication)

>
>
> Thank you


-- 
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.


--------------020805070400090003090705
Content-Type: text/x-vcard; charset=utf-8;
 name="jcigar.vcf"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
 filename="jcigar.vcf"

begin:vcard
fn:Julien Cigar
n:Cigar;Julien
org;quoted-printable:Belgian Biodiversity Platform;ULB (Universit=C3=A9 Libre de Bruxelles)
adr:;;;Brussels;;;Belgium
email;internet:jcigar@ulb.ac.be
tel;work:+32(0)26505752
x-mozilla-html:FALSE
url:http://www.biodiversity.be
version:2.1
end:vcard


--------------020805070400090003090705--