From: Kiriakos Tsourapas <ktsour@gmail.com>
To: Julien Cigar <jcigar@ulb.ac.be>
Cc: pgsql-performance@postgresql.org
Subject: Re: Postgres becoming slow, only full vacuum fixes it
Date: Mon, 24 Sep 2012 16:51:44 +0300
Message-ID: <3A53BCFC-561D-4C75-96D8-5DB3DCC4DC93@gmail.com> (raw)
In-Reply-To: <50605035.8020107@ulb.ac.be>
References: <D15ECA12-8D6E-4E53-A294-5E175564CA61@gmail.com>
<71DC95CE-16EF-4D53-9971-C573DCD30622@gmail.com>
<50605035.8020107@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,
>
> 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 = 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.
> 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 ?
Thank you
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: ktsour@gmail.com, jcigar@ulb.ac.be
Subject: Re: Postgres becoming slow, only full vacuum fixes it
In-Reply-To: <3A53BCFC-561D-4C75-96D8-5DB3DCC4DC93@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox