pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedqueries are fast after dump->restore but slow again after some days dispite vacuum
8+ messages / 6 participants
[nested] [flat]
* queries are fast after dump->restore but slow again after some days dispite vacuum
@ 2012-07-19 11:33 Felix Scheicher <mandavi@web.de>
0 siblings, 1 reply; 8+ messages in thread
From: Felix Scheicher @ 2012-07-19 11:33 UTC (permalink / raw)
To: pgsql-performance
Hi, I am running a 9.1 server at Ubuntu. When I upgraded to the current version
I did a pg_dump followed by pg_restore and found that the db was much faster.
But slowed down again after two days. I did the dump-restore again and could now
compare the two (actually identical) databases. This is a query of the old one
directly after a VACUUM ANALYSE:
QUERY PLAN
--------------------------------------------------------------------------------
------------------------------------------------------------------------
WindowAgg (cost=2231.56..2232.17 rows=22 width=59) (actual
time=16748.382..16749.203 rows=340 loops=1)
-> Sort (cost=2231.56..2231.62 rows=22 width=59) (actual
time=16748.360..16748.575 rows=340 loops=1)
Sort Key: ba.bookid, (CASE WHEN (e.languageid = 123) THEN 1 WHEN
(e.languageid = 401) THEN 2 WHEN (e.languageid = 150) THEN 3 ELSE 4 END)
Sort Method: quicksort Memory: 60kB
-> Nested Loop (cost=0.00..2231.07 rows=22 width=59) (actual
time=0.125..16747.395 rows=340 loops=1)
-> Index Scan using authorid1 on book_author ba
(cost=0.00..73.94 rows=20 width=8) (actual time=0.034..11.453 rows=99 loops=1)
Index Cond: (authorid = 543)
-> Index Scan using foreign_key_bookid on editions e
(cost=0.00..107.76 rows=8 width=51) (actual time=90.741..169.031 rows=3
loops=99)
Index Cond: (bookid = ba.bookid)
Filter: mainname
Total runtime: 16752.146 ms
(11 Zeilen)
And here after dump-restore:
QUERY PLAN
--------------------------------------------------------------------------------
---------------------------------------------------------------------
WindowAgg (cost=2325.78..2326.41 rows=23 width=58) (actual time=18.583..19.387
rows=340 loops=1)
-> Sort (cost=2325.78..2325.84 rows=23 width=58) (actual
time=18.562..18.823 rows=340 loops=1)
Sort Key: ba.bookid, (CASE WHEN (e.languageid = 123) THEN 1 WHEN
(e.languageid = 401) THEN 2 WHEN (e.languageid = 150) THEN 3 ELSE 4 END)
Sort Method: quicksort Memory: 60kB
-> Nested Loop (cost=0.00..2325.26 rows=23 width=58) (actual
time=0.385..18.060 rows=340 loops=1)
-> Index Scan using authorid1 on book_author ba
(cost=0.00..73.29 rows=20 width=8) (actual time=0.045..0.541 rows=99 loops=1)
Index Cond: (authorid = 543)
-> Index Scan using foreign_key_bookid on editions e
(cost=0.00..112.49 rows=9 width=50) (actual time=0.056..0.168 rows=3 loops=99)
Index Cond: (bookid = ba.bookid)
Filter: mainname
Total runtime: 19.787 ms
(11 Zeilen)
server settings:
shared_buffers = 680MB
work_mem = 10MB
maintenance_work_mem = 64MB
checkpoint_segments = 32
checkpoint_completion_target = 0.9
effective_cache_size = 1500MB
No matter how much I vacuum or analyse the slow db, I don't get it faster.
I also checked for dead tuples - there are none.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: queries are fast after dump->restore but slow again after some days dispite vacuum
@ 2012-07-19 12:38 Andrew Dunstan <andrew@dunslane.net>
parent: Felix Scheicher <mandavi@web.de>
0 siblings, 1 reply; 8+ messages in thread
From: Andrew Dunstan @ 2012-07-19 12:38 UTC (permalink / raw)
To: Felix Scheicher <mandavi@web.de>; +Cc: pgsql-performance
On 07/19/2012 07:33 AM, Felix Scheicher wrote:
> Hi, I am running a 9.1 server at Ubuntu. When I upgraded to the current version
> I did a pg_dump followed by pg_restore and found that the db was much faster.
> But slowed down again after two days. I did the dump-restore again and could now
> compare the two (actually identical) databases. This is a query of the old one
> directly after a VACUUM ANALYSE:
...
>
> No matter how much I vacuum or analyse the slow db, I don't get it faster.
> I also checked for dead tuples - there are none.
Try running CLUSTER on the relevant tables and see if it makes a
difference. If it does you might want to look into using pg_reorg
periodically.
cheers
andrew
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: queries are fast after dump->restore but slow again after some days dispite vacuum
@ 2012-07-19 15:13 Felix Scheicher <mandavi@web.de>
parent: Andrew Dunstan <andrew@dunslane.net>
0 siblings, 3 replies; 8+ messages in thread
From: Felix Scheicher @ 2012-07-19 15:13 UTC (permalink / raw)
To: pgsql-performance
Andrew Dunstan <andrew <at> dunslane.net> writes:
> Try running CLUSTER on the relevant tables and see if it makes a
> difference. If it does you might want to look into using pg_reorg
> periodically.
That worked like a charm! Many thanks. But how comes, the queries are also fast
after a restore without the cluster?
regards,
Felix
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: queries are fast after dump->restore but slow again after some days dispite vacuum
@ 2012-07-19 15:48 Andrew Dunstan <andrew@dunslane.net>
parent: Felix Scheicher <mandavi@web.de>
2 siblings, 0 replies; 8+ messages in thread
From: Andrew Dunstan @ 2012-07-19 15:48 UTC (permalink / raw)
To: Felix Scheicher <mandavi@web.de>; +Cc: pgsql-performance
On 07/19/2012 11:13 AM, Felix Scheicher wrote:
> Andrew Dunstan <andrew <at> dunslane.net> writes:
>
>> Try running CLUSTER on the relevant tables and see if it makes a
>> difference. If it does you might want to look into using pg_reorg
>> periodically.
>
> That worked like a charm! Many thanks. But how comes, the queries are also fast
> after a restore without the cluster?
>
There is probably a lot of unused space in your table. CLUSTER rewrites
a fresh copy, as do restore and pg_reorg.
You might also want to try changing the settings on the table so it gets
much more aggressively auto-vacuumed, so that dead space is made
available much more quickly, and the table has less chance to get bloated.
cheers
andrew
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: queries are fast after dump->restore but slow again after some days dispite vacuum
@ 2012-07-19 16:12 Jeff Janes <jeff.janes@gmail.com>
parent: Felix Scheicher <mandavi@web.de>
2 siblings, 0 replies; 8+ messages in thread
From: Jeff Janes @ 2012-07-19 16:12 UTC (permalink / raw)
To: Felix Scheicher <mandavi@web.de>; +Cc: pgsql-performance
On Thu, Jul 19, 2012 at 8:13 AM, Felix Scheicher <mandavi@web.de> wrote:
> Andrew Dunstan <andrew <at> dunslane.net> writes:
>
>> Try running CLUSTER on the relevant tables and see if it makes a
>> difference. If it does you might want to look into using pg_reorg
>> periodically.
>
>
> That worked like a charm! Many thanks. But how comes, the queries are also fast
> after a restore without the cluster?
Probably fewer buffers needed to be touched.
Running "explain (analyze, buffers)" would give information on how
many buffers were touched.
Cheers,
Jeff
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: queries are fast after dump->restore but slow again after some days dispite vacuum
@ 2012-07-19 17:04 Scott Marlowe <scott.marlowe@gmail.com>
parent: Felix Scheicher <mandavi@web.de>
2 siblings, 1 reply; 8+ messages in thread
From: Scott Marlowe @ 2012-07-19 17:04 UTC (permalink / raw)
To: Felix Scheicher <mandavi@web.de>; +Cc: pgsql-performance
Are you running a lot of full table updates?
On Thu, Jul 19, 2012 at 9:13 AM, Felix Scheicher <mandavi@web.de> wrote:
> Andrew Dunstan <andrew <at> dunslane.net> writes:
>
>> Try running CLUSTER on the relevant tables and see if it makes a
>> difference. If it does you might want to look into using pg_reorg
>> periodically.
>
>
> That worked like a charm! Many thanks. But how comes, the queries are also fast
> after a restore without the cluster?
>
> regards,
> Felix
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
--
To understand recursion, one must first understand recursion.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: queries are fast after dump->restore but slow again after some days dispite vacuum
@ 2012-07-19 17:24 mandavi <mandavi@web.de>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: mandavi @ 2012-07-19 17:24 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: pgsql-performance
>>> Try running CLUSTER on the relevant tables and see if it makes a
>>> difference. If it does you might want to look into using pg_reorg
>>> periodically.
>>
>>
>> That worked like a charm! Many thanks. But how comes, the queries are also fast
>> after a restore without the cluster?
>>
2012/7/19 Scott Marlowe <scott.marlowe@gmail.com>:
> Are you running a lot of full table updates?
If you mean updates which are applied on every or almost every row of
the table - yes, it happens with two rather small tables of max. 10
000 rows. But they are both not touched by the query with this big
performance difference.
Regards,
Felix
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: queries are fast after dump->restore but slow again after some days dispite vacuum
@ 2012-07-20 07:10 Laszlo Nagy <gandalf@shopzeus.com>
parent: mandavi <mandavi@web.de>
0 siblings, 0 replies; 8+ messages in thread
From: Laszlo Nagy @ 2012-07-20 07:10 UTC (permalink / raw)
To: mandavi <mandavi@web.de>; +Cc: Scott Marlowe <scott.marlowe@gmail.com>; pgsql-performance
>> Are you running a lot of full table updates?
> If you mean updates which are applied on every or almost every row of
> the table - yes, it happens with two rather small tables of max. 10
> 000 rows. But they are both not touched by the query with this big
> performance difference.
I'm not an expert, but would it help to change fillfactor to about 45%?
I'm just guessing that full table updates with fillfactor=45% could
store the rows on the same page. Maybe I'm wrong.
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2012-07-20 07:10 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-07-19 11:33 queries are fast after dump->restore but slow again after some days dispite vacuum Felix Scheicher <mandavi@web.de>
2012-07-19 12:38 ` Andrew Dunstan <andrew@dunslane.net>
2012-07-19 15:13 ` Felix Scheicher <mandavi@web.de>
2012-07-19 15:48 ` Andrew Dunstan <andrew@dunslane.net>
2012-07-19 16:12 ` Jeff Janes <jeff.janes@gmail.com>
2012-07-19 17:04 ` Scott Marlowe <scott.marlowe@gmail.com>
2012-07-19 17:24 ` mandavi <mandavi@web.de>
2012-07-20 07:10 ` Laszlo Nagy <gandalf@shopzeus.com>
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