Re: [PERFORM] queries are fast after dump-restore but slow again after some days dispite vacuum
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. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] 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. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] queries are fast after dump-restore but slow again after some days dispite vacuum
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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] queries are fast after dump-restore but slow again after some days dispite vacuum
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
Re: [PERFORM] queries are fast after dump-restore but slow again after some days dispite vacuum
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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] queries are fast after dump-restore but slow again after some days dispite vacuum
On Thu, Jul 19, 2012 at 8:13 AM, Felix Scheicher mand...@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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] queries are fast after dump-restore but slow again after some days dispite vacuum
Are you running a lot of full table updates? On Thu, Jul 19, 2012 at 9:13 AM, Felix Scheicher mand...@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. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] queries are fast after dump-restore but slow again after some days dispite vacuum
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.marl...@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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
