pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedIncreasing query time after updates
6+ messages / 4 participants
[nested] [flat]
* Increasing query time after updates
@ 2014-01-21 06:26 Katharina Koobs <katharina.koobs@uni-konstanz.de>
0 siblings, 2 replies; 6+ messages in thread
From: Katharina Koobs @ 2014-01-21 06:26 UTC (permalink / raw)
To: pgsql-performance; +Cc: 'Sebastian Vogt' <Sebastian.Vogt@uni-konstanz.de>
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.
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?
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Increasing query time after updates
@ 2014-01-21 08:06 Heikki Linnakangas <hlinnakangas@vmware.com>
parent: Katharina Koobs <katharina.koobs@uni-konstanz.de>
1 sibling, 1 reply; 6+ messages in thread
From: Heikki Linnakangas @ 2014-01-21 08:06 UTC (permalink / raw)
To: Katharina Koobs <katharina.koobs@uni-konstanz.de>; +Cc: pgsql-performance@postgresql.org, 'Sebastian Vogt' <Sebastian.Vogt@uni-konstanz.de>
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Increasing query time after updates
@ 2014-01-21 08:37 Katharina Koobs <katharina.koobs@uni-konstanz.de>
parent: Heikki Linnakangas <hlinnakangas@vmware.com>
0 siblings, 1 reply; 6+ messages in thread
From: Katharina Koobs @ 2014-01-21 08:37 UTC (permalink / raw)
To: pgsql-performance
Dear Heikki,
thank you for your valuable feedback. Regarding your questions: It
gradually slower every day. The database size is increasing only
slightly over time.
I will try your hint regarding CLUSTERING. The difference in effect of
VACUUM FULL in version 9.0 sounds very interesting. I will discuss the
update to version 9.0 with my colleague.
Any further idea or feedback is much appreciated.
Thank you so much & kind regards,
Katharina
-----Ursprüngliche Nachricht-----
Von: Heikki Linnakangas [mailto:hlinnakangas@vmware.com]
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
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Increasing query time after updates
@ 2014-01-21 08:45 Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
parent: Katharina Koobs <katharina.koobs@uni-konstanz.de>
0 siblings, 1 reply; 6+ messages in thread
From: Mark Kirkwood @ 2014-01-21 08:45 UTC (permalink / raw)
To: Katharina Koobs <katharina.koobs@uni-konstanz.de>; pgsql-performance
On 21/01/14 21:37, Katharina Koobs wrote:
> Dear Heikki,
> thank you for your valuable feedback. Regarding your questions: It
> gradually slower every day. The database size is increasing only
> slightly over time.
>
> I will try your hint regarding CLUSTERING. The difference in effect of
> VACUUM FULL in version 9.0 sounds very interesting. I will discuss the
> update to version 9.0 with my colleague.
>
> Any further idea or feedback is much appreciated.
>
>
Index bloat could be a factor too - performing a regular REINDEX on the
relevant tables could be worth a try.
Regards
Mark
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Increasing query time after updates
@ 2014-01-21 10:57 Tomas Vondra <tv@fuzzy.cz>
parent: Katharina Koobs <katharina.koobs@uni-konstanz.de>
1 sibling, 0 replies; 6+ messages in thread
From: Tomas Vondra @ 2014-01-21 10:57 UTC (permalink / raw)
To: Katharina Koobs <katharina.koobs@uni-konstanz.de>; +Cc: pgsql-performance@postgresql.org, "'Sebastian Vogt'" <sebastian.vogt@uni-konstanz.de>
Hi,
On 21 Leden 2014, 7:26, 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.
> 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?
The table/index bloat would be my first bet, but that should be fixed (or
at least improved) by the vacuum commands you've tested.
Sadly, the amount of info you provided is insufficient to determine the
cause - the best thing you can give us are explain plans of the query, one
when it's fast, one when it's slow.
If it's longer than a few lines, please post it to explain.depesz.com and
not here (the clients will reformat it, making it unreadable).
> 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
Any reason not to use higher value for effective_cache_size? You have 7GB
of RAM, 1GB of that is for shared buffers, so I'd say ~4GB would be a good
value here. Unlikely to be the cause of the issues you're seeing, though.
Tomas
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Increasing query time after updates
@ 2014-01-21 18:53 Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
parent: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
0 siblings, 0 replies; 6+ messages in thread
From: Mark Kirkwood @ 2014-01-21 18:53 UTC (permalink / raw)
To: Katharina Koobs <katharina.koobs@uni-konstanz.de>; pgsql-performance
On 21/01/14 21:45, Mark Kirkwood wrote:
> On 21/01/14 21:37, Katharina Koobs wrote:
>> Dear Heikki,
>> thank you for your valuable feedback. Regarding your questions: It
>> gradually slower every day. The database size is increasing only
>> slightly over time.
>>
>> I will try your hint regarding CLUSTERING. The difference in effect of
>> VACUUM FULL in version 9.0 sounds very interesting. I will discuss the
>> update to version 9.0 with my colleague.
>>
>> Any further idea or feedback is much appreciated.
>>
>>
>
> Index bloat could be a factor too - performing a regular REINDEX on the
> relevant tables could be worth a try.
>
Sorry - I missed that you had tried reindex already.
regards
Mark
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2014-01-21 18:53 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-01-21 06:26 Increasing query time after updates Katharina Koobs <katharina.koobs@uni-konstanz.de>
2014-01-21 08:06 ` Heikki Linnakangas <hlinnakangas@vmware.com>
2014-01-21 08:37 ` Katharina Koobs <katharina.koobs@uni-konstanz.de>
2014-01-21 08:45 ` Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2014-01-21 18:53 ` Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2014-01-21 10:57 ` Tomas Vondra <tv@fuzzy.cz>
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