Обсуждение: queries are fast after dump->restore but slow again after some days dispite vacuum

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.

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

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

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

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

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.

>>> 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

>> 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.