[PERFORM] Query optimization problem
I have a query: SELECT d1.ID, d2.ID FROM DocPrimary d1 JOIN DocPrimary d2 ON d2.BasedOn=d1.ID WHERE (d1.ID=234409763) or (d2.ID=234409763) i think what QO(Query Optimizer) can make it faster (now it seq scan and on million records works 7 sec) SELECT d1.ID, d2.ID FROM DocPrimary d1 JOIN DocPrimary d2 ON d2.BasedOn=d1.ID WHERE (d2.BasedOn=234409763) or (d2.ID=234409763) -- Slow Query -- test=# EXPLAIN (ANALYZE on, VERBOSE on, COSTS on, BUFFERS off )SELECT d1.ID, d2.ID test-# FROM DocPrimary d1 test-# JOIN DocPrimary d2 ON d2.BasedOn=d1.ID test-# WHERE (d1.ID=234409763) or (d2.ID=234409763); QUERY PLAN Hash Join (cost=58.15..132.35 rows=2 width=8) (actual time=0.007..0.007 rows=0 loops=1) Output: d1.id, d2.id Hash Cond: (d2.basedon = d1.id) Join Filter: ((d1.id = 234409763) OR (d2.id = 234409763)) - Seq Scan on public.docprimary d2 (cost=0.00..31.40 rows=2140 width=8) (actual time=0.002..0.002 rows=0 loops=1) Output: d2.id, d2.basedon - Hash (cost=31.40..31.40 rows=2140 width=4) (never executed) Output: d1.id - Seq Scan on public.docprimary d1 (cost=0.00..31.40 rows=2140 width=4) (never executed) Output: d1.id -- Fast Query -- test=# EXPLAIN (ANALYZE on, VERBOSE on, COSTS on, BUFFERS off )SELECT d1.ID, d2.ID test-# FROM DocPrimary d1 test-# JOIN DocPrimary d2 ON d2.BasedOn=d1.ID test-# WHERE (d2.BasedOn=234409763) or (d2.ID=234409763); QUERY PLAN - Nested Loop (cost=8.60..58.67 rows=12 width=8) (actual time=0.026..0.026 rows=0 loops=1) Output: d1.id, d2.id - Bitmap Heap Scan on public.docprimary d2 (cost=8.60..19.31 rows=12 width=8) (actual time=0.023..0.023 rows=0 loops=1) Output: d2.id, d2.basedon Recheck Cond: ((d2.basedon = 234409763) OR (d2.id = 234409763)) - BitmapOr (cost=8.60..8.60 rows=12 width=0) (actual time=0.018..0.018 rows=0 loops=1) - Bitmap Index Scan on basedon_idx (cost=0.00..4.33 rows=11 width=0) (actual time=0.008..0.008 rows=0 loops=1) Index Cond: (d2.basedon = 234409763) - Bitmap Index Scan on id_pk (cost=0.00..4.26 rows=1 width=0) (actual time=0.003..0.003 rows=0 loops=1) Index Cond: (d2.id = 234409763) - Index Scan using id_pk on public.docprimary d1 (cost=0.00..3.27 rows=1 width=4) (never executed) Output: d1.id, d1.basedon Index Cond: (d1.id = d2.basedon) PGver: PostgreSQL 9.0b x86 OS: Win7 x64 - Create table query: - CREATE TABLE docprimary ( id integer NOT NULL, basedon integer, CONSTRAINT id_pk PRIMARY KEY (id) ); CREATE INDEX basedon_idx ON docprimary USING btree (basedon); -- 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] performance on new linux box
On Wed, Jul 14, 2010 at 6:57 PM, Scott Carey sc...@richrelevance.comwrote: But none of this explains why a 4-disk raid 10 is slower than a 1 disk system. If there is no write-back caching on the RAID, it should still be similar to the one disk setup. Unless that one-disk setup turned off fsync() or was configured with synchronous_commit off. Even low end laptop drives don't lie these days about a cache flush or sync() -- OS's/file systems can, and some SSD's do. If loss of a transaction during a power failure is OK, then just turn synchronous_commit off and get the performance back. The discussion about transaction rate being limited by the disks is related to that, and its not necessary _IF_ its ok to lose a transaction if the power fails. For most applications, losing a transaction or two in a power failure is fine. Obviously, its not with financial transactions or other such work. On Jul 8, 2010, at 2:42 PM, Craig James wrote: On 7/8/10 2:18 PM, timothy.noo...@emc.com wrote: How does the linux machine know that there is a BBU installed and to change its behavior or change the behavior of Postgres? I am experiencing performance issues, not with searching but more with IO. It doesn't. It trusts the disk controller. Linux says, Flush your cache and the controller says, OK, it's flushed. In the case of a BBU controller, the controller can say that almost instantly because it's got the data in a battery-backed memory that will survive even if the power goes out. In the case of a non-BBU controller (RAID or non-RAID), the controller has to actually wait for the head to move to the right spot, then wait for the disk to spin around to the right sector, then write the data. Only then can it say, OK, it's flushed. So to Linux, it just appears to be a disk that's exceptionally fast at flushing its buffers. Craig -Original Message- From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Craig James Sent: Thursday, July 08, 2010 4:02 PM To: pgsql-performance@postgresql.org Subject: Re: [PERFORM] performance on new linux box On 7/8/10 12:47 PM, Ryan Wexler wrote: On Thu, Jul 8, 2010 at 12:46 PM, Kevin Grittner kevin.gritt...@wicourts.govmailto:kevin.gritt...@wicourts.gov wrote: Ryan Wexlerr...@iridiumsuite.commailto:r...@iridiumsuite.com wrote: One thing I don't understand is why BBU will result in a huge performance gain. I thought BBU was all about power failures? Well, it makes it safe for the controller to consider the write complete as soon as it hits the RAM cache, rather than waiting for persistence to the disk itself. It can then schedule the writes in a manner which is efficient based on the physical medium. Something like this was probably happening on your non-server machines, but without BBU it was not actually safe. Server class machines tend to be more conservative about not losing your data, but without a RAID controller with BBU cache, that slows writes down to the speed of the rotating disks. -Kevin Thanks for the explanations that makes things clearer. It still amazes me that it would account for a 5x change in IO. It's not exactly a 5x change in I/O, rather it's a 5x change in *transactions*. Without a BBU Postgres has to wait for each transaction to by physically written to the disk, which at 7200 RPM (or 10K or 15K) means a few hundred per second. Most of the time Postgres is just sitting there waiting for the disk to say, OK, I did it. With BBU, once the RAID card has the data, it's virtually guaranteed it will get to the disk even if the power fails, so the RAID controller says, OK, I did it even though the data is still in the controller's cache and not actually on the disk. It means there's no tight relationship between the disk's rotational speed and your transaction rate. Craig -- Sent via pgsql-performance mailing list ( pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance Something was clearly wrong with my former raid card. Frankly, I am not sure if it was configuration or simply hardware failure. The server is hosted so I only had so much access. But the card was swapped out with a new one and now performance is quite good. I am just trying to tune the new card now. thanks for all the input
Re: [PERFORM] performance on new linux box
On Jul 14, 2010, at 6:57 PM, Scott Carey wrote: But none of this explains why a 4-disk raid 10 is slower than a 1 disk system. If there is no write-back caching on the RAID, it should still be similar to the one disk setup. Many raid controllers are smart enough to always turn off write caching on the drives, and also disable the feature on their own buffer without a BBU. Add a BBU, and the cache on the controller starts getting used, but *not* the cache on the drives. Take away the controller, and most OS's by default enable the write cache on the drive. You can turn it off if you want, but if you know how to do that, then you're probably also the same kind of person that would have purchased a raid card with a BBU. -- 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] Query optimization problem
Hello Zotov, Somehow the equivalence d2.basedon=d1.id is not used in the slow query, probably because the equivalence constant value would be used inside a not-base expression (the OR). You can see that the equivalence values *are* used by changing the or to an and and compare both queries. The only thing you can do to guarantee the planner has all information to in cases like this it explicitly name the equivalence inside OR expressions, e.g. SELECT d1.ID, d2.ID FROM DocPrimary d1 JOIN DocPrimary d2 ON d2.BasedOn=d1.ID WHERE (d1.ID=234409763 and d2.basedon=234409763) or (d2.ID=234409763) ; regards, Yeb Havinga PS: the analyze time of the slow query showed 0.007ms? Zotov wrote: I have a query: SELECT d1.ID, d2.ID FROM DocPrimary d1 JOIN DocPrimary d2 ON d2.BasedOn=d1.ID WHERE (d1.ID=234409763) or (d2.ID=234409763) i think what QO(Query Optimizer) can make it faster (now it seq scan and on million records works 7 sec) SELECT d1.ID, d2.ID FROM DocPrimary d1 JOIN DocPrimary d2 ON d2.BasedOn=d1.ID WHERE (d2.BasedOn=234409763) or (d2.ID=234409763) -- Slow Query -- test=# EXPLAIN (ANALYZE on, VERBOSE on, COSTS on, BUFFERS off )SELECT d1.ID, d2.ID test-# FROM DocPrimary d1 test-# JOIN DocPrimary d2 ON d2.BasedOn=d1.ID test-# WHERE (d1.ID=234409763) or (d2.ID=234409763); QUERY PLAN Hash Join (cost=58.15..132.35 rows=2 width=8) (actual time=0.007..0.007 rows=0 loops=1) Output: d1.id, d2.id Hash Cond: (d2.basedon = d1.id) Join Filter: ((d1.id = 234409763) OR (d2.id = 234409763)) - Seq Scan on public.docprimary d2 (cost=0.00..31.40 rows=2140 width=8) (actual time=0.002..0.002 rows=0 loops=1) Output: d2.id, d2.basedon - Hash (cost=31.40..31.40 rows=2140 width=4) (never executed) Output: d1.id - Seq Scan on public.docprimary d1 (cost=0.00..31.40 rows=2140 width=4) (never executed) Output: d1.id -- Fast Query -- test=# EXPLAIN (ANALYZE on, VERBOSE on, COSTS on, BUFFERS off )SELECT d1.ID, d2.ID test-# FROM DocPrimary d1 test-# JOIN DocPrimary d2 ON d2.BasedOn=d1.ID test-# WHERE (d2.BasedOn=234409763) or (d2.ID=234409763); QUERY PLAN - Nested Loop (cost=8.60..58.67 rows=12 width=8) (actual time=0.026..0.026 rows=0 loops=1) Output: d1.id, d2.id - Bitmap Heap Scan on public.docprimary d2 (cost=8.60..19.31 rows=12 width=8) (actual time=0.023..0.023 rows=0 loops=1) Output: d2.id, d2.basedon Recheck Cond: ((d2.basedon = 234409763) OR (d2.id = 234409763)) - BitmapOr (cost=8.60..8.60 rows=12 width=0) (actual time=0.018..0.018 rows=0 loops=1) - Bitmap Index Scan on basedon_idx (cost=0.00..4.33 rows=11 width=0) (actual time=0.008..0.008 rows=0 loops=1) Index Cond: (d2.basedon = 234409763) - Bitmap Index Scan on id_pk (cost=0.00..4.26 rows=1 width=0) (actual time=0.003..0.003 rows=0 loops=1) Index Cond: (d2.id = 234409763) - Index Scan using id_pk on public.docprimary d1 (cost=0.00..3.27 rows=1 width=4) (never executed) Output: d1.id, d1.basedon Index Cond: (d1.id = d2.basedon) PGver: PostgreSQL 9.0b x86 OS: Win7 x64 - Create table query: - CREATE TABLE docprimary ( id integer NOT NULL, basedon integer, CONSTRAINT id_pk PRIMARY KEY (id) ); CREATE INDEX basedon_idx ON docprimary USING btree (basedon); -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Identical query slower on 8.4 vs 8.3
I have two servers with equal specs, one of them running 8.3.7 and the new server running 8.4.4. The only tweak I have made from the default install (from Ubuntu repositories) is increasing shared_buffers to 768MB. Both servers are running 64-bit, but are different releases of Ubuntu. This is the query I am running: SELECT DISTINCT test.tid, testresult.trscore, testresult.trpossiblescore, testresult.trstart, testresult.trfinish, testresult.trscorebreakdown, testresult.fk_sid, testresult.fk_tid, test.tname, qr.qrscore, qr.qrtotalscore, testresult.trid, qr.qrid FROM testresult, test, questionresult qr WHERE test.tid = testresult.fk_tid AND qr.fk_trid = testresult.trid ORDER BY test.tid; Results when running on the v8.3.7 server Total query runtime: 32185 ms. 700536 rows retrieved. Results when running on the v8.4.4 server Total query runtime: 164227 ms. 700536 rows retrieved. Results when running on a different v8.4.4 server with slightly faster hardware and shared_buffers at 1024MB (this one has a few more rows of data due to this being the server that is currently live, so it has more recent data) Total query runtime: 157931 ms. 700556 rows retrieved. Anyone have any ideas on where I should start looking to figure this out? I didn't perform any special steps when moving to v8.4, I just did a pg_dump from the 8.3 server and restored it on the new 8.4 servers. Maybe that is where I made a mistake. Thanks! Patrick
Re: [PERFORM] Identical query slower on 8.4 vs 8.3
On 15 July 2010 15:41, Patrick Donlin pdon...@oaisd.org wrote: I have two servers with equal specs, one of them running 8.3.7 and the new server running 8.4.4. The only tweak I have made from the default install (from Ubuntu repositories) is increasing shared_buffers to 768MB. Both servers are running 64-bit, but are different releases of Ubuntu. This is the query I am running: SELECT DISTINCT test.tid, testresult.trscore, testresult.trpossiblescore, testresult.trstart, testresult.trfinish, testresult.trscorebreakdown, testresult.fk_sid, testresult.fk_tid, test.tname, qr.qrscore, qr.qrtotalscore, testresult.trid, qr.qrid FROM testresult, test, questionresult qr WHERE test.tid = testresult.fk_tid AND qr.fk_trid = testresult.trid ORDER BY test.tid; Results when running on the v8.3.7 server Total query runtime: 32185 ms. 700536 rows retrieved. Results when running on the v8.4.4 server Total query runtime: 164227 ms. 700536 rows retrieved. Results when running on a different v8.4.4 server with slightly faster hardware and shared_buffers at 1024MB (this one has a few more rows of data due to this being the server that is currently live, so it has more recent data) Total query runtime: 157931 ms. 700556 rows retrieved. Anyone have any ideas on where I should start looking to figure this out? I didn't perform any special steps when moving to v8.4, I just did a pg_dump from the 8.3 server and restored it on the new 8.4 servers. Maybe that is where I made a mistake. Thanks! Patrick First thing to check is did you do a VACUUM ANALYZE on the database? Thom -- 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] Identical query slower on 8.4 vs 8.3
Patrick Donlin pdon...@oaisd.org wrote: Anyone have any ideas on where I should start looking to figure this out? You're going to want to run EXPLAIN ANALYZE for the slow query on both servers. If you want the rest of us to be able to contribute ideas, we'll need a little more information -- please read this page: http://wiki.postgresql.org/wiki/SlowQueryQuestions I didn't perform any special steps when moving to v8.4, I just did a pg_dump from the 8.3 server and restored it on the new 8.4 servers. A database VACUUM ANALYZE by a superuser is a good idea; otherwise that's fine technique. -Kevin -- 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] Identical query slower on 8.4 vs 8.3
I'll read over that wiki entry, but for now here is the EXPLAIN ANALYZE output assuming I did it correctly. I have run vacuumdb --full --analyze, it actually runs as a nightly cron job. 8.4.4 Sever: Unique (cost=202950.82..227521.59 rows=702022 width=86) (actual time=21273.371..22429.511 rows=700536 loops=1) - Sort (cost=202950.82..204705.87 rows=702022 width=86) (actual time=21273.368..22015.948 rows=700536 loops=1) Sort Key: test.tid, testresult.trscore, testresult.trpossiblescore, testresult.trstart, testresult.trfinish, testresult.trscorebreakdown, testresult.fk_sid, test.tname, qr.qrscore, qr.qrtotalscore, testresult.trid, qr.qrid Sort Method: external merge Disk: 71768kB - Hash Join (cost=2300.82..34001.42 rows=702022 width=86) (actual time=64.388..1177.468 rows=700536 loops=1) Hash Cond: (qr.fk_trid = testresult.trid) - Seq Scan on questionresult qr (cost=0.00..12182.22 rows=702022 width=16) (actual time=0.090..275.518 rows=702022 loops=1) - Hash (cost=1552.97..1552.97 rows=29668 width=74) (actual time=63.042..63.042 rows=29515 loops=1) - Hash Join (cost=3.35..1552.97 rows=29668 width=74) (actual time=0.227..39.111 rows=29515 loops=1) Hash Cond: (testresult.fk_tid = test.tid) - Seq Scan on testresult (cost=0.00..1141.68 rows=29668 width=53) (actual time=0.019..15.622 rows=29668 loops=1) - Hash (cost=2.60..2.60 rows=60 width=21) (actual time=0.088..0.088 rows=60 loops=1) - Seq Scan on test (cost=0.00..2.60 rows=60 width=21) (actual time=0.015..0.044 rows=60 loops=1) Total runtime: 22528.820 ms 8.3.7 Server: Unique (cost=202950.82..227521.59 rows=702022 width=86) (actual time=22157.714..23343.461 rows=700536 loops=1) - Sort (cost=202950.82..204705.87 rows=702022 width=86) (actual time=22157.706..22942.018 rows=700536 loops=1) Sort Key: test.tid, testresult.trscore, testresult.trpossiblescore, testresult.trstart, testresult.trfinish, testresult.trscorebreakdown, testresult.fk_sid, test.tname, qr.qrscore, qr.qrtotalscore, testresult.trid, qr.qrid Sort Method: external merge Disk: 75864kB - Hash Join (cost=2300.82..34001.42 rows=702022 width=86) (actual time=72.842..1276.634 rows=700536 loops=1) Hash Cond: (qr.fk_trid = testresult.trid) - Seq Scan on questionresult qr (cost=0.00..12182.22 rows=702022 width=16) (actual time=0.112..229.987 rows=702022 loops=1) - Hash (cost=1552.97..1552.97 rows=29668 width=74) (actual time=71.421..71.421 rows=29515 loops=1) - Hash Join (cost=3.35..1552.97 rows=29668 width=74) (actual time=0.398..44.524 rows=29515 loops=1) Hash Cond: (testresult.fk_tid = test.tid) - Seq Scan on testresult (cost=0.00..1141.68 rows=29668 width=53) (actual time=0.117..20.890 rows=29668 loops=1) - Hash (cost=2.60..2.60 rows=60 width=21) (actual time=0.112..0.112 rows=60 loops=1) - Seq Scan on test (cost=0.00..2.60 rows=60 width=21) (actual time=0.035..0.069 rows=60 loops=1) Total runtime: 23462.639 ms Thanks for the quick responses and being patient with me not providing enough information. -Patrick - Original Message - From: Kevin Grittner kevin.gritt...@wicourts.gov To: Patrick Donlin pdon...@oaisd.org, pgsql-performance@postgresql.org Sent: Thursday, July 15, 2010 10:55:19 AM GMT -05:00 US/Canada Eastern Subject: Re: [PERFORM] Identical query slower on 8.4 vs 8.3 Patrick Donlin pdon...@oaisd.org wrote: Anyone have any ideas on where I should start looking to figure this out? You're going to want to run EXPLAIN ANALYZE for the slow query on both servers. If you want the rest of us to be able to contribute ideas, we'll need a little more information -- please read this page: http://wiki.postgresql.org/wiki/SlowQueryQuestions I didn't perform any special steps when moving to v8.4, I just did a pg_dump from the 8.3 server and restored it on the new 8.4 servers. A database VACUUM ANALYZE by a superuser is a good idea; otherwise that's fine technique. -Kevin
Re: [PERFORM] Identical query slower on 8.4 vs 8.3
Excerpts from Patrick Donlin's message of jue jul 15 11:12:53 -0400 2010: I'll read over that wiki entry, but for now here is the EXPLAIN ANALYZE output assuming I did it correctly. I have run vacuumdb --full --analyze, it actually runs as a nightly cron job. These plans seem identical (though the fact that the leading whitespace was trimmed means it's untrustworthy -- please in the future send them as text attachments instead so that your mailer doesn't interfere with formatting). The 8.4 plan is even a full second faster, according to the total runtime line. The slowness could've been caused by caching effects ... -- 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] Identical query slower on 8.4 vs 8.3
FULL is usually bad. Stick to vacuum analyze and drop the full. Do you have indexes on: test.tid, testresult.fk_tid, questionresult.fk_trid and testresult.trid -Andy On 7/15/2010 10:12 AM, Patrick Donlin wrote: I'll read over that wiki entry, but for now here is the EXPLAIN ANALYZE output assuming I did it correctly. I have run vacuumdb --full --analyze, it actually runs as a nightly cron job. 8.4.4 Sever: Unique (cost=202950.82..227521.59 rows=702022 width=86) (actual time=21273.371..22429.511 rows=700536 loops=1) - Sort (cost=202950.82..204705.87 rows=702022 width=86) (actual time=21273.368..22015.948 rows=700536 loops=1) Sort Key: test.tid, testresult.trscore, testresult.trpossiblescore, testresult.trstart, testresult.trfinish, testresult.trscorebreakdown, testresult.fk_sid, test.tname, qr.qrscore, qr.qrtotalscore, testresult.trid, qr.qrid Sort Method: external merge Disk: 71768kB - Hash Join (cost=2300.82..34001.42 rows=702022 width=86) (actual time=64.388..1177.468 rows=700536 loops=1) Hash Cond: (qr.fk_trid = testresult.trid) - Seq Scan on questionresult qr (cost=0.00..12182.22 rows=702022 width=16) (actual time=0.090..275.518 rows=702022 loops=1) - Hash (cost=1552.97..1552.97 rows=29668 width=74) (actual time=63.042..63.042 rows=29515 loops=1) - Hash Join (cost=3.35..1552.97 rows=29668 width=74) (actual time=0.227..39.111 rows=29515 loops=1) Hash Cond: (testresult.fk_tid = test.tid) - Seq Scan on testresult (cost=0.00..1141.68 rows=29668 width=53) (actual time=0.019..15.622 rows=29668 loops=1) - Hash (cost=2.60..2.60 rows=60 width=21) (actual time=0.088..0.088 rows=60 loops=1) - Seq Scan on test (cost=0.00..2.60 rows=60 width=21) (actual time=0.015..0.044 rows=60 loops=1) Total runtime: 22528.820 ms 8.3.7 Server: Unique (cost=202950.82..227521.59 rows=702022 width=86) (actual time=22157.714..23343.461 rows=700536 loops=1) - Sort (cost=202950.82..204705.87 rows=702022 width=86) (actual time=22157.706..22942.018 rows=700536 loops=1) Sort Key: test.tid, testresult.trscore, testresult.trpossiblescore, testresult.trstart, testresult.trfinish, testresult.trscorebreakdown, testresult.fk_sid, test.tname, qr.qrscore, qr.qrtotalscore, testresult.trid, qr.qrid Sort Method: external merge Disk: 75864kB - Hash Join (cost=2300.82..34001.42 rows=702022 width=86) (actual time=72.842..1276.634 rows=700536 loops=1) Hash Cond: (qr.fk_trid = testresult.trid) - Seq Scan on questionresult qr (cost=0.00..12182.22 rows=702022 width=16) (actual time=0.112..229.987 rows=702022 loops=1) - Hash (cost=1552.97..1552.97 rows=29668 width=74) (actual time=71.421..71.421 rows=29515 loops=1) - Hash Join (cost=3.35..1552.97 rows=29668 width=74) (actual time=0.398..44.524 rows=29515 loops=1) Hash Cond: (testresult.fk_tid = test.tid) - Seq Scan on testresult (cost=0.00..1141.68 rows=29668 width=53) (actual time=0.117..20.890 rows=29668 loops=1) - Hash (cost=2.60..2.60 rows=60 width=21) (actual time=0.112..0.112 rows=60 loops=1) - Seq Scan on test (cost=0.00..2.60 rows=60 width=21) (actual time=0.035..0.069 rows=60 loops=1) Total runtime: 23462.639 ms Thanks for the quick responses and being patient with me not providing enough information. -Patrick - Original Message - From: Kevin Grittner kevin.gritt...@wicourts.gov To: Patrick Donlin pdon...@oaisd.org, pgsql-performance@postgresql.org Sent: Thursday, July 15, 2010 10:55:19 AM GMT -05:00 US/Canada Eastern Subject: Re: [PERFORM] Identical query slower on 8.4 vs 8.3 Patrick Donlin pdon...@oaisd.org wrote: Anyone have any ideas on where I should start looking to figure this out? You're going to want to run EXPLAIN ANALYZE for the slow query on both servers. If you want the rest of us to be able to contribute ideas, we'll need a little more information -- please read this page: http://wiki.postgresql.org/wiki/SlowQueryQuestions I didn't perform any special steps when moving to v8.4, I just did a pg_dump from the 8.3 server and restored it on the new 8.4 servers. A database VACUUM ANALYZE by a superuser is a good idea; otherwise that's fine technique. -Kevin -- 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] Identical query slower on 8.4 vs 8.3
On Thu, Jul 15, 2010 at 9:41 AM, Patrick Donlin pdon...@oaisd.org wrote: I have two servers with equal specs, one of them running 8.3.7 and the new server running 8.4.4. The only tweak I have made from the default install (from Ubuntu repositories) is increasing shared_buffers to 768MB. Both servers are running 64-bit, but are different releases of Ubuntu. ^^^ Right there. *different releases*. I've seen fairly significant differences in identical hardware with even minor O/S point releases. After you run a full vacuum and then reindex and then vacuum analyze (probably not entirely necessary) if there is still a difference I'd point at the O/S. -- Jon -- 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] Identical query slower on 8.4 vs 8.3
Patrick Donlin pdon...@oaisd.org wrote: I have run vacuumdb --full --analyze, it actually runs as a nightly cron job. That's usually not wise -- VACUUM FULL can cause index bloat, and is not normally necessary. If you have autovacuum turned on and run a database vacuum each night, you can probably avoid ever running VACUUM FULL. A long-running transaction or mass deletes might still make aggressive cleanup necessary on occasion, but you should consider using CLUSTER instead of VACUUM FULL. So, you should probably change your crontab job to vacuum --all --analyze. Also, after a bulk load of a database like this you might consider a one-time VACUUM FREEZE of the database. Without that there will come a time when autovacuum will need to rewrite all rows from the bulk load which haven't subsequently been modified, in order to prevent transaction ID wraparound problems. -Kevin -- 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] Identical query slower on 8.4 vs 8.3
On Thu, Jul 15, 2010 at 11:12 AM, Patrick Donlin pdon...@oaisd.org wrote: I'll read over that wiki entry, but for now here is the EXPLAIN ANALYZE output assuming I did it correctly. I have run vacuumdb --full --analyze, it actually runs as a nightly cron job. 8.4.4 Sever: Unique (cost=202950.82..227521.59 rows=702022 width=86) (actual time=21273.371..22429.511 rows=700536 loops=1) - Sort (cost=202950.82..204705.87 rows=702022 width=86) (actual time=21273.368..22015.948 rows=700536 loops=1) Sort Key: test.tid, testresult.trscore, testresult.trpossiblescore, testresult.trstart, testresult.trfinish, testresult.trscorebreakdown, testresult.fk_sid, test.tname, qr.qrscore, qr.qrtotalscore, testresult.trid, qr.qrid Sort Method: external merge Disk: 71768kB - Hash Join (cost=2300.82..34001.42 rows=702022 width=86) (actual time=64.388..1177.468 rows=700536 loops=1) Hash Cond: (qr.fk_trid = testresult.trid) - Seq Scan on questionresult qr (cost=0.00..12182.22 rows=702022 width=16) (actual time=0.090..275.518 rows=702022 loops=1) - Hash (cost=1552.97..1552.97 rows=29668 width=74) (actual time=63.042..63.042 rows=29515 loops=1) - Hash Join (cost=3.35..1552.97 rows=29668 width=74) (actual time=0.227..39.111 rows=29515 loops=1) Hash Cond: (testresult.fk_tid = test.tid) - Seq Scan on testresult (cost=0.00..1141.68 rows=29668 width=53) (actual time=0.019..15.622 rows=29668 loops=1) - Hash (cost=2.60..2.60 rows=60 width=21) (actual time=0.088..0.088 rows=60 loops=1) - Seq Scan on test (cost=0.00..2.60 rows=60 width=21) (actual time=0.015..0.044 rows=60 loops=1) Total runtime: 22528.820 ms 8.3.7 Server: Unique (cost=202950.82..227521.59 rows=702022 width=86) (actual time=22157.714..23343.461 rows=700536 loops=1) - Sort (cost=202950.82..204705.87 rows=702022 width=86) (actual time=22157.706..22942.018 rows=700536 loops=1) Sort Key: test.tid, testresult.trscore, testresult.trpossiblescore, testresult.trstart, testresult.trfinish, testresult.trscorebreakdown, testresult.fk_sid, test.tname, qr.qrscore, qr.qrtotalscore, testresult.trid, qr.qrid Sort Method: external merge Disk: 75864kB - Hash Join (cost=2300.82..34001.42 rows=702022 width=86) (actual time=72.842..1276.634 rows=700536 loops=1) Hash Cond: (qr.fk_trid = testresult.trid) - Seq Scan on questionresult qr (cost=0.00..12182.22 rows=702022 width=16) (actual time=0.112..229.987 rows=702022 loops=1) - Hash (cost=1552.97..1552.97 rows=29668 width=74) (actual time=71.421..71.421 rows=29515 loops=1) - Hash Join (cost=3.35..1552.97 rows=29668 width=74) (actual time=0.398..44.524 rows=29515 loops=1) Hash Cond: (testresult.fk_tid = test.tid) - Seq Scan on testresult (cost=0.00..1141.68 rows=29668 width=53) (actual time=0.117..20.890 rows=29668 loops=1) - Hash (cost=2.60..2.60 rows=60 width=21) (actual time=0.112..0.112 rows=60 loops=1) - Seq Scan on test (cost=0.00..2.60 rows=60 width=21) (actual time=0.035..0.069 rows=60 loops=1) Total runtime: 23462.639 ms your plans are identical as is the runtime basically. this means you might want to consider the following possibilities: *) operator error :-) *) cache effects *) environmental factors on the server at the time *) network/client issues I say network issues because if your explain analyze (which actually does run the entire query) is significantly faster than the full query, then we have to consider that the formatting and transfer of the data back to the client (even if it's on the same box) becomes suspicious. If you've eliminated other possibilities, try running other big, trivially planned, mucho result returning queries (like select * from table) on both servers and comparing times. merlin -- 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] Identical query slower on 8.4 vs 8.3
On Thu, 2010-07-15 at 10:41 -0400, Patrick Donlin wrote: Results when running on the v8.3.7 server Total query runtime: 32185 ms. 700536 rows retrieved. Results when running on the v8.4.4 server Total query runtime: 164227 ms. 700536 rows retrieved. Anyone have any ideas on where I should start looking to figure this out? I didn't perform any special steps when moving to v8.4, I just did a pg_dump from the 8.3 server and restored it on the new 8.4 servers. Maybe that is where I made a mistake. Three immediate things come to mind: 1. One had relations in file or shared buffer cache, the other didn't 2. One is running ext4 versus ext3 and when you end up spilling to disk when you over run work_mem, the ext4 machine is faster, but without knowing which machine is which it is a bit tough to diagnose. 3. You didn't run ANALYZE on one of the machines Sincerely, Joshua D. Drake Thanks! Patrick -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Question of using COPY on a table with triggers
First of all, a little background. We have a table which is used as a trigger table for entering and processing data for a network monitoring system. Essentially, we insert a set of columns into a table, and each row fires a trigger function which calls a very large stored procedure which aggregates data, etc. At that point, the row is deleted from the temp table. Currently, records are transferred from the data collector as a series of multi-row inserts. Before going through the exercise of recoding, and given the fact that each of this inserts fires of a trigger, will I see any noticeable performance? The table definition follows: CREATE TABLE tbltmptests ( tmptestsysid bigserial NOT NULL, testhash character varying(32), testtime timestamp with time zone, statusid integer, replytxt text, replyval real, groupid integer, CONSTRAINT tbltmptests_pkey PRIMARY KEY (tmptestsysid) ) WITH ( OIDS=FALSE ); ALTER TABLE tbltmptests OWNER TO postgres; -- Trigger: tbltmptests_tr on tbltmptests -- DROP TRIGGER tbltmptests_tr ON tbltmptests; CREATE TRIGGER tbltmptests_tr AFTER INSERT ON tbltmptests FOR EACH ROW EXECUTE PROCEDURE fn_testtrigger(); Another question - is there anything special we need to do to handle the primary constraint field? Now, on a related note and looking forward to the streaming replication of v9, will this work with it, since we have multiple tables being update by a trigger function?
Re: [PERFORM] Identical query slower on 8.4 vs 8.3
Thanks everyone for the input so far, Merlin's comment about the network gave me one of those duh moments since I have been running these queries remotely using pgadmin. I will experiment with this more tomorrow/Monday along with the other suggestions that have been posted to hopefully narrow it down. Running the query from my webserver yielded much better times, but from a quick look it seems my 8.4 server is still a bit slower. I will share more details as I dig into it more tomorrow or Monday. -Patrick - Original Message - From: Merlin Moncure mmonc...@gmail.com To: Patrick Donlin pdon...@oaisd.org Cc: Kevin Grittner kevin.gritt...@wicourts.gov, pgsql-performance@postgresql.org Sent: Thursday, July 15, 2010 12:04:13 PM GMT -05:00 US/Canada Eastern Subject: Re: [PERFORM] Identical query slower on 8.4 vs 8.3 your plans are identical as is the runtime basically. this means you might want to consider the following possibilities: *) operator error :-) *) cache effects *) environmental factors on the server at the time *) network/client issues I say network issues because if your explain analyze (which actually does run the entire query) is significantly faster than the full query, then we have to consider that the formatting and transfer of the data back to the client (even if it's on the same box) becomes suspicious. If you've eliminated other possibilities, try running other big, trivially planned, mucho result returning queries (like select * from table) on both servers and comparing times. merlin
Re: [PERFORM] performance on new linux box
On Jul 15, 2010, at 12:40 PM, Ryan Wexler wrote: On Wed, Jul 14, 2010 at 7:50 PM, Ben Chobot be...@silentmedia.com wrote: On Jul 14, 2010, at 6:57 PM, Scott Carey wrote: But none of this explains why a 4-disk raid 10 is slower than a 1 disk system. If there is no write-back caching on the RAID, it should still be similar to the one disk setup. Many raid controllers are smart enough to always turn off write caching on the drives, and also disable the feature on their own buffer without a BBU. Add a BBU, and the cache on the controller starts getting used, but *not* the cache on the drives. Take away the controller, and most OS's by default enable the write cache on the drive. You can turn it off if you want, but if you know how to do that, then you're probably also the same kind of person that would have purchased a raid card with a BBU. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance Ben I don't quite follow your message. Could you spell it out a little clearer for me? thanks -ryan Most (all?) hard drives have cache built into them. Many raid cards have cache built into them. When the power dies, all the data in any cache is lost, which is why it's dangerous to use it for write caching. For that reason, you can attach a BBU to a raid card which keeps the cache alive until the power is restored (hopefully). But no hard drive I am aware of lets you attach a battery, so using a hard drive's cache for write caching will always be dangerous. That's why many raid cards will always disable write caching on the hard drives themselves, and only enable write caching using their own memory when a BBU is installed. Does that make more sense?
Re: [PERFORM] performance on new linux box
On Jul 15, 2010, at 9:30 AM, Scott Carey wrote: Many raid controllers are smart enough to always turn off write caching on the drives, and also disable the feature on their own buffer without a BBU. Add a BBU, and the cache on the controller starts getting used, but *not* the cache on the drives. This does not make sense. Write caching on all hard drives in the last decade are safe because they support a write cache flush command properly. If the card is smart it would issue the drive's write cache flush command to fulfill an fsync() or barrier request with no BBU. You're missing the point. If the power dies suddenly, there's no time to flush any cache anywhere. That's the entire point of the BBU - it keeps the RAM powered up on the raid card. It doesn't keep the disks spinning long enough to flush caches. -- 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] performance on new linux box
On Jul 14, 2010, at 7:50 PM, Ben Chobot wrote: On Jul 14, 2010, at 6:57 PM, Scott Carey wrote: But none of this explains why a 4-disk raid 10 is slower than a 1 disk system. If there is no write-back caching on the RAID, it should still be similar to the one disk setup. Many raid controllers are smart enough to always turn off write caching on the drives, and also disable the feature on their own buffer without a BBU. Add a BBU, and the cache on the controller starts getting used, but *not* the cache on the drives. This does not make sense. Write caching on all hard drives in the last decade are safe because they support a write cache flush command properly. If the card is smart it would issue the drive's write cache flush command to fulfill an fsync() or barrier request with no BBU. Take away the controller, and most OS's by default enable the write cache on the drive. You can turn it off if you want, but if you know how to do that, then you're probably also the same kind of person that would have purchased a raid card with a BBU. Sure, or you can use an OS/File System combination that respects fsync() which will call the drive's write cache flush. There are some issues with certain file systems and barriers for file system metadata, but for the WAL log, we're only dalking about fdatasync() equivalency, which most file systems do just fine even with a drive's write cache on. -- 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] Question of using COPY on a table with triggers
Essentially, we insert a set of columns into a table, and each row fires a trigger function which calls a very large stored procedure For inserting lots of rows, COPY is much faster than INSERT because it parses data (a lot) faster and is more data-stream-friendly. However the actual inserting into the tbale and trigger-calling has to be done for both. If the trigger is a very large stored procedure it is very likely that executing it will take a lot more time than parsing executing the INSERT. So, using COPY instead of INSERT will not gain you anything. -- 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] Question of using COPY on a table with triggers
That is what I thought. The trigger calls a 3000 row stored procedure which does all of the calculations to aggregate data into 3 separate tables and then insert the raw data point into a 4th table. -Original Message- From: Pierre C [mailto:li...@peufeu.com] Sent: Thursday, July 15, 2010 4:47 PM To: Benjamin Krajmalnik; pgsql-performance@postgresql.org Subject: Re: [PERFORM] Question of using COPY on a table with triggers Essentially, we insert a set of columns into a table, and each row fires a trigger function which calls a very large stored procedure For inserting lots of rows, COPY is much faster than INSERT because it parses data (a lot) faster and is more data-stream-friendly. However the actual inserting into the tbale and trigger-calling has to be done for both. If the trigger is a very large stored procedure it is very likely that executing it will take a lot more time than parsing executing the INSERT. So, using COPY instead of INSERT will not gain you anything. -- 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] Question of using COPY on a table with triggers
Benjamin Krajmalnik k...@servoyant.com writes: That is what I thought. The trigger calls a 3000 row stored procedure which does all of the calculations to aggregate data into 3 separate tables and then insert the raw data point into a 4th table. Youch. Seems like you might want to rethink the idea of doing those calculations incrementally for each added row. Wouldn't it be better to add all the new data and then do the aggregation once? regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
