From: "Katharina Koobs" <katharina.koobs@uni-konstanz.de>
To: <pgsql-performance@postgresql.org>
References: <000001cf1671$c5e023b0$51a06b10$@uni-konstanz.de>
 <52DE2A97.60002@vmware.com>
In-Reply-To: <52DE2A97.60002@vmware.com>
Subject: Re: Increasing query time after updates
Date: Tue, 21 Jan 2014 09:37:56 +0100
Message-ID: <001701cf1684$151fbf50$3f5f3df0$@uni-konstanz.de>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Thread-Index: AQHOuUSMH3XS7EuTcBvwn1XOHYbScAIcvug7mn6vIPA=
Content-Language: de
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Dear Heikki,
thank you for your valuable feedback. Regarding your questions: It=20
gradually slower every day. The database size is increasing only=20
slightly over time.

I will try your hint regarding CLUSTERING. The difference in effect of=20
VACUUM FULL in version 9.0 sounds very interesting. I will discuss the=20
update to version 9.0 with my colleague.

Any further idea or feedback is much appreciated.

Thank you so much & kind regards,
Katharina


-----Urspr=FCngliche Nachricht-----
Von: Heikki Linnakangas [mailto:hlinnakangas@vmware.com]=20
Gesendet: Dienstag, 21. Januar 2014 09:07
An: Katharina Koobs
Cc: pgsql-performance@postgresql.org; 'Sebastian Vogt'
Betreff: Re: [PERFORM] Increasing query time after updates

On 01/21/2014 08:26 AM, Katharina Koobs wrote:
> Hi,
>
> We have a PostgreSQL DB, version 8.4 on a Suse Linux system.
> Every night a script runs with several updates and inserts. The query time
> at day increases after
> approximately 3 weeks from a few minutes to about an hour.

Does it get gradually slower every day, or suddenly jump from few=20
minutes to one hour after three weeks? The former would suggest some=20
kind of bloating or fragmentation, while the latter would suggest a=20
change in a query plan (possibly still caused by bloating).

Does the database size change over time?

> After export, drop and import the DB the query time is again at a few
> minutes.
>
> We have tested vacuum full, vacuum analyze and reindex and get no
> improvement.
>
> Has anyone an idea why the queries are getting slower and slower?

One theory is that the tables are initially more or less ordered by one=20
column, but get gradually shuffled by the updates. Exporting and=20
importing would load the data back in order. However, a blow to that=20
theory is that a pg_dump + reload will load the tuples in roughly the=20
same physical order, but perhaps you used something else for the=20
export+import.

You could try running CLUSTER on any large tables. Since version 9.0,=20
VACUUM FULL does more or less the same as CLUSTER, ie. rewrites the=20
whole table, but in 8.4 it's different.

> Thank you so much for your help!
>
>
> The DB configuration:
>
> Virtual server, 7GB RAM, DB size =3D 16GB
>
> shared_buffers =3D 1024MB
> temp_buffers =3D 32MB
> work_mem =3D 8MB
> checkpoint_segments =3D 20
> effective_cache_size =3D 512MB
> max_locks_per_transaction =3D 256

With 7GB of RAM, you might want to raise effective_cache_size to=20
something like 4GB. It doesn't allocate anything, but tells PostgreSQL=20
how much memory it can expect the operating system to use as buffer=20
cache, which can influence query plans. I doubt it makes any difference=20
for the problem you're seeing, but just as general advice..

8.4 is quite old by now, and will no longer be supported by the=20
community after July 2014. You'll have to upgrade pretty soon anyway, so=20
you might as well upgrade now and see if it helps.

- Heikki


--=20
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance