Message-ID: <52DE2A97.60002@vmware.com>
Date: Tue, 21 Jan 2014 10:06:47 +0200
From: Heikki Linnakangas <hlinnakangas@vmware.com>
Organization: VMware
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:17.0) Gecko/20131103 Icedove/17.0.10
MIME-Version: 1.0
To: Katharina Koobs <katharina.koobs@uni-konstanz.de>
CC: pgsql-performance@postgresql.org,
 'Sebastian Vogt' <Sebastian.Vogt@uni-konstanz.de>
Subject: Re: Increasing query time after updates
References: <000001cf1671$c5e023b0$51a06b10$@uni-konstanz.de>
In-Reply-To: <000001cf1671$c5e023b0$51a06b10$@uni-konstanz.de>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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 
minutes to one hour after three weeks? The former would suggest some 
kind of bloating or fragmentation, while the latter would suggest a 
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 
column, but get gradually shuffled by the updates. Exporting and 
importing would load the data back in order. However, a blow to that 
theory is that a pg_dump + reload will load the tuples in roughly the 
same physical order, but perhaps you used something else for the 
export+import.

You could try running CLUSTER on any large tables. Since version 9.0, 
VACUUM FULL does more or less the same as CLUSTER, ie. rewrites the 
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 = 16GB
>
> shared_buffers = 1024MB
> temp_buffers = 32MB
> work_mem = 8MB
> checkpoint_segments = 20
> effective_cache_size = 512MB
> max_locks_per_transaction = 256

With 7GB of RAM, you might want to raise effective_cache_size to 
something like 4GB. It doesn't allocate anything, but tells PostgreSQL 
how much memory it can expect the operating system to use as buffer 
cache, which can influence query plans. I doubt it makes any difference 
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 
community after July 2014. You'll have to upgrade pretty soon anyway, so 
you might as well upgrade now and see if it helps.

- Heikki


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