Re: [PERFORM] Bad prepare performance
Hi Martin, please CC the mailing-list,
then others can repply ;)
Cédric Villemain (13:59 2008-03-31):
> Le Monday 31 March 2008, Martin Kjeldsen a écrit :
> > I've done the same query on a 8.2.5 database. The first one is prepared
> > first and the other is executed directly.
> >
> > I understand why the there is such a great difference between the two ways
> > of executing the query (postgres has no way of knowing that $1 will be
> > quite big and that the result is not too big).
> >
> > I could just avoid using prepare statements, but this is done
automatically
> > with Perl's DBD::Pg. I know how to avoid using prepare statements (avoid
> > having placeholders in the statement), but that is not the prettiest of
> > work arounds.
>
> Did you saw this option :
>
> $sth = $dbh->prepare("SELECT id FROM mytable WHERE val = ?",
>{ pg_server_prepare => 0 });
>
> Then, *this* query will not be prepared by the server.
This works very well. Thanks!
Still I regard this as a work around and the optimal solution would be to
allow the prepare statement to be prepared with an max(guid) is close to $1
hint or something like that.
I heard something about delayed prepare, where the prepared statements is
prepared on first use, this would solve my problem. Is this something being
work on right now?
Best regards
Martin Kjeldsen
signature.asc
Description: This is a digitally signed message part.
[PERFORM] check performance parameters
Hi there, I have an application accessing a postgres database, and I need to estimate the following parameters: - read / write ratio - reads/second on typical load / peak load - writes/second on typical load / peak load Is there any available tool to achieve that ? TIA, Sabin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Cursors and different settings for default_statistics_target
Hi, the following statement retrieves 16358 rows in a cursor by fetching 1024 rows in bulks on a 8.2.4 server: DECLARE curs_285058224 CURSOR FOR SELECT objid, attrid, aggrid, lineid, objval FROM atobjval WHERE objid IN (281479288456304,281479288456359,281479288456360,281479288456384,2814792 88456385,281479288456403,281479288456404,281479288456406,281479288456408 ,281479288456432,281479288456433,281479288456434,281479288456438,2814792 88456442,281479288456468,281479288456499,281479288456546,281479288456547 ,281479288456590,281479288456636,281479288456638,281479288456722,2814792 88457111,281479288457125,281479288457126,281479288457143,281479288457229 ,281479288457230,281479288457477,281479288457478,281479288457546,2814792 88457559,281479288457676,281479288457686,281479288457792,281479288457808 ,281479288457809,281479288457852,281479288457853,281479288457902,2814792 88457961,281479288457962,281479288458005,281479288458097,281479288458116 ,281479288458155,281479288458156,281479288458183,281479288458516,2814792 88458523,281479288458576,281479288458577,281479288458624,281479288458716 ,281479288458721,281479288458735,281479288458736,281479288458737,2814792 88458758,281479288458786,281479288458788,281479288458789,281479288458794 ,281479288458806,281479288458914,281479288458957,281479288458958,2814792 88459029,281479288459126,281479288459127,281479288459135,281479288459259 ,281479288459260,281479288459261,281479288459262,281479288459321,2814792 88459425,281479288459426,281479288459427,281479288459428,281479288459447 ,281479288459450,281479288459453,281479288459457,281479288459462,2814792 88459607,281479288459608,281479288459635,281479288459636,281479288459732 ,281479288459767,281479288459954,281479288459974,281479288459975,2814792 88459976,281479288459977,281479288460034,281479288460060,281479288460070 ,281479288460073,281479288460088,281479288460162,281479288460163,2814792 88460167,281479288460170,281479288460173,281479288460176,281479288460179 ,281479288460182,281479288460185,281479288460188,281479288460217,2814792 88460290,281479288460292,281479288460318,281479288460325,281479288460332 ,281479288460337,281479288460339,281479288460377,281479288460378,2814792 88460394,281479288460412,281479288460457,281479288460565,281479288460566 ,281479288460567,281479288460608,281479288460609,281479288460683,2814792 88460684,281479288461021,281479288461024,281479288461059,281479288461091 ,281479288461281,281479288461367,281479288461368,281479288461369,2814792 88461377,281479288461429,281479288461477,281479288461483,281479288461484 ,281479288461485,281479288461493,281479288461494,281479288461502,2814792 88461522,281479288461570,281479288461578,281479288461654,281479288461655 ,281479288461690,281479288461711,281479288461712,281479288461747,2814792 88461776,281479288461777,281479288461838,281479288461839,281479288461878 ,281479288461889,281479288462036,281479288462083,281479288462090,2814792 88462096,281479288462104,281479288462129,281479288462136,281479288462276 ,281479288462277,281479288462366,281479288462367,281479288462448,2814792 88462450,281479288462502,281479288462817,281479288462967,281479288462968 ,281479288462969,281479288463200,281479288463246,281479288463247,2814792 88463248,281479288463255,281479288463437,281479288463441,281479288463462 ,281479288463482,281479288463642,281479288463645,281479288463782,2814792 88463790,281479288463802,281479288463809,281479288463819,281479288463843 ,281479288463859,281479288463967,281479288463968,281479288463969,2814792 88465253,281479288465396,281479288465397,281479288465417,281479288465429 ,281479288465436,281479288467191,285774255752169,285774255752181,2857742 55752183,285774255752188,285774255752189,285774255752198,285774255753788 ,285774255753789,285774255753790,285774255753793,285774255753794,2857742 55753808,285774255753809,285774255753811,285774255753812,285774255753828 ,285774255753893,285774255753993,285774255754091,285774255754106,2857742 55754110,285774255754160,285774255755169,285774255755179,285774255755184 ,285774255755187,285774255755205,285774255755252,285774255755254,2857742 55755271,285774255755481,285774255755494,285774255755514,285774255755534 ,285774255755571,285774255755597,285774255755616,285774255755622,2857742 55755632,285774255755696,285774255755717,285774255755729,285774255755747 ,285774255755759,285774255755787,285774255755791,285774255755798,2857742 55755802,285774255757269,285774255757270,285774255757286,285774255757287 ,285774255757518,285774255757687,285774255757797,285774255761019,2857742 55761021,285774255761069,285774255761070,285774255764181,285774255764182 ,285774255764196,285774255764204,285774255764276,285774255764290,2857742 55764301,285774255764312,285774255764333,285774255764334,285774255764335 ,285774255764367,285774255764369,285774255764371,285774255764382,2857742 55764394,285774255764418,285774255764420,285774255764430,285774255764486 ,285774255764490,285774255764498,285774255764616,285774255764683,2857742 55764787,285774255764802,28577425
Re: [PERFORM] check performance parameters
On Tue, 1 Apr 2008, Sabin Coanda wrote: I have an application accessing a postgres database, and I need to estimate the following parameters: - read / write ratio - reads/second on typical load / peak load - writes/second on typical load / peak load Is there any available tool to achieve that ? Assuming you have a vaguely unix/linux-ish type system, then use iostat. Matthew -- Reality is that which, when you stop believing in it, doesn't go away. -- Philip K. Dick -- 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] Cursors and different settings for default_statistics_target
"Hell, Robert" <[EMAIL PROTECTED]> writes: > When we use 20 as default_statistics_target the retrieval of the data > takes 7.5 seconds - with 25 as default_statistics_target (with restart > and analyze) it takes 0.6 seconds. > The query plan is identical in both situations (row estimation differs a > little bit) - the query is always fast when it is executed without a > cursor. A cursor doesn't necessarily use the same plan as a straight query does. Try "EXPLAIN DECLARE curs_285058224 CURSOR FOR ..." and see if you aren't getting different plans in these two cases. 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
[PERFORM] Insert time
Hi everyone, I am running a test with 1 thread calling a stored procedure in an endless loop. The stored procedure inserts 1000 records in a table that does not have indexes or constraints. In the log file I see that the time to execute the procedure sometimes it jumps from 100 ms to 700 ms. The auto-vacuum is turned off. Can anyone give me some details about this? Thanks a lot, 17221%2008-04-01 09:22:53 ESTLOG: statement: select * from testinsert(11001,1000) 17221%2008-04-01 09:22:53 ESTLOG: duration: 111.654 ms 17223%2008-04-01 09:22:53 ESTLOG: statement: select * from testinsert(11001,1000) 17223%2008-04-01 09:22:54 ESTLOG: duration: 710.426 ms __ Ask a question on any topic and get answers from real people. Go to Yahoo! Answers and share what you know at http://ca.answers.yahoo.com -- 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] Cursors and different settings for default_statistics_target
That's it - I found a more simple statement which has the same problem (0.02 seconds vs. 6 seconds): With cursor (6 seconds): appcooelakdb2=> explain DECLARE curs_1 CURSOR FOR SELECT DISTINCT t2.objid FROM atobjval t2 WHERE t2.aggrid = 0 AND t2.attrid = 281479288455385 ORDER BY t2.objid; QUERY PLAN -- Unique (cost=0.00..1404823.63 rows=538 width=8) -> Index Scan using atobjvalix on atobjval t2 (cost=0.00..1404751.32 rows=28925 width=8) Index Cond: ((attrid = 281479288455385::bigint) AND (aggrid = 0)) Without cursor (0.02 seconds) appcooelakdb2=> explain SELECT DISTINCT t2.objid FROM atobjval t2 WHERE t2.aggrid = 0 AND t2.attrid = 281479288455385 ORDER BY t2.objid; QUERY PLAN -- Unique (cost=151717.85..151862.48 rows=538 width=8) -> Sort (cost=151717.85..151790.17 rows=28925 width=8) Sort Key: objid -> Bitmap Heap Scan on atobjval t2 (cost=1692.40..149574.51 rows=28925 width=8) Recheck Cond: (attrid = 281479288455385::bigint) Filter: (aggrid = 0) -> Bitmap Index Scan on ind_atobjval (cost=0.00..1685.16 rows=59402 width=0) Index Cond: (attrid = 281479288455385::bigint) What's the difference between plan calculation for cursors and straight queries? Kind regards, Robert -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Dienstag, 01. April 2008 17:30 To: Hell, Robert Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Cursors and different settings for default_statistics_target "Hell, Robert" <[EMAIL PROTECTED]> writes: > When we use 20 as default_statistics_target the retrieval of the data > takes 7.5 seconds - with 25 as default_statistics_target (with restart > and analyze) it takes 0.6 seconds. > The query plan is identical in both situations (row estimation differs a > little bit) - the query is always fast when it is executed without a > cursor. A cursor doesn't necessarily use the same plan as a straight query does. Try "EXPLAIN DECLARE curs_285058224 CURSOR FOR ..." and see if you aren't getting different plans in these two cases. 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
Re: [PERFORM] Cursors and different settings for default_statistics_target
"Hell, Robert" <[EMAIL PROTECTED]> writes: > That's it - I found a more simple statement which has the same problem > (0.02 seconds vs. 6 seconds): This isn't necessarily the very same problem --- what are the plans for your original case with the two different stats settings? > What's the difference between plan calculation for cursors and straight > queries? The planner is set up to favor fast-start plans a little bit when planning a cursor, on the theory that you are probably more interested in getting some of the rows sooner than you are in the total runtime, and that you might not ever intend to fetch all the rows anyway. In the example you give here, it likes the indexscan/unique plan because of the zero startup cost, even though the total cost is (correctly) estimated as much higher. (Looking at this example, I wonder if the fast-start bias isn't a bit too strong...) It's not immediately apparent to me though how that would affect your original query. 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
Re: [PERFORM] Cursors and different settings for default_statistics_target
Here are the query plans for the original query - looks very similar (to
me):
EXPLAIN SELECT objid, attrid, aggrid, lineid, objval FROM atobjval WHERE
objid IN (281479288456304,,285774255837674) ORDER BY
objid, attrid, aggrid, lineid;
QUERY PLAN
--
Sort (cost=116851.38..117196.22 rows=137935 width=32)
Sort Key: objid, attrid, aggrid, lineid
-> Bitmap Heap Scan on atobjval (cost=4947.40..105076.13
rows=137935 width=32)
Recheck Cond: (objid = ANY ('{281479288456304,,285774255837674}'::bigint[]))
-> Bitmap Index Scan on atobjvalix (cost=0.00..4912.92
rows=137935 width=0)
Index Cond: (objid = ANY ('{281479288456304,,285774255837674}'::bigint[]))
explain DECLARE curs_285058224 CURSOR FOR SELECT objid, attrid, aggrid,
lineid, objval FROM atobjval WHERE objid IN (281479288456304,,285774255837674) ORDER BY objid, attrid, aggrid, lineid;
QUERY PLAN
--
Index Scan using atobjvalix on atobjval (cost=0.00..1041413.49
rows=137935 width=32)
Filter: (objid = ANY ('{281479288456304,,285774255837674}'::bigint[]))
That's CURSOR_OPT_FAST_PLAN and isn't it? Our application reads the full
results of most cursors.
Regards,
Robert
-Original Message-
From: Tom Lane [mailto:[EMAIL PROTECTED]
Sent: Dienstag, 01. April 2008 18:17
To: Hell, Robert
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Cursors and different settings for
default_statistics_target
"Hell, Robert" <[EMAIL PROTECTED]> writes:
> That's it - I found a more simple statement which has the same problem
> (0.02 seconds vs. 6 seconds):
This isn't necessarily the very same problem --- what are the plans for
your original case with the two different stats settings?
> What's the difference between plan calculation for cursors and
straight
> queries?
The planner is set up to favor fast-start plans a little bit when
planning a cursor, on the theory that you are probably more interested
in getting some of the rows sooner than you are in the total runtime,
and that you might not ever intend to fetch all the rows anyway.
In the example you give here, it likes the indexscan/unique plan because
of the zero startup cost, even though the total cost is (correctly)
estimated as much higher. (Looking at this example, I wonder if the
fast-start bias isn't a bit too strong...)
It's not immediately apparent to me though how that would affect
your original query.
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
Re: [PERFORM] Cursors and different settings for default_statistics_target
"Hell, Robert" <[EMAIL PROTECTED]> writes: > That's CURSOR_OPT_FAST_PLAN and isn't it? Our application reads the full > results of most cursors. Just out of curiosity, why use a cursor at all then? But anyway, you might want to consider running a custom build with a higher setting for tuple_fraction for OPT_FAST_PLAN (look into planner.c). I've occasionally thought about exposing that as a GUC parameter, but never gotten motivated to do it. 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
Re: [PERFORM] Cursors and different settings for default_statistics_target
On Tue, Apr 01, 2008 at 12:42:03PM -0400, Tom Lane wrote: >> That's CURSOR_OPT_FAST_PLAN and isn't it? Our application reads the full >> results of most cursors. > Just out of curiosity, why use a cursor at all then? This isn't the same scenario as the OP, but I've used a cursor in cases where I cannot keep all of the dataset in memory at the client at once, but I _can_ coerce it down to a more manageable size as it comes in. I don't know if a cursor is the only way to do this (short of making a custom function inside Postgres of some sort), but it seems to be the simplest way in libpqxx, at least. /* Steinar */ -- Homepage: http://www.sesse.net/ -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Too many commands in a transaction
Hi Iam getting the below error when iam running my program. ERROR: cannot have more than 2^32-1 commands in a transaction SQL state: 54000 If iam not wrong this error ocuurs when there are too many statements executing in one single transaction. But this error is occuring in a function that iam least expecting it to occur in. The function it occurs in is as follows: BEGIN resultindex := 1; -- Go through summedprobs, find where rnum falls, set resultindex FOR i IN REVERSE (array_upper(summedprobs,1)-1)..1 LOOP IF rnum >= summedprobs[i] AND rnum <= summedprobs[i+1] THEN resultindex := i; END IF; END LOOP; RETURN (dobs[resultindex]); EXCEPTION WHEN program_limit_exceeded THEN RAISE NOTICE 'Exception in GETRES'; RETURN (dobs[resultindex]); END; Is is beacuse of the REVERSE command? or because the program is executiung many select and update statements? Catching the exception isnt helping here either.Can anyone explain me why this error occurs and what i can do to resolve it? Thanks Sam -- 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] Cursors and different settings for default_statistics_target
Looks much better when using 0.0 for tuple_fraction in case of a cursor instead
of 0.1.
But why are the first 15 fetches (15360 rows) processed in 0.5 seconds and the
last fetch (998 rows) takes 7 seconds.
Are we just unlucky that the last fetch takes that long?
EXPLAIN SELECT objid, attrid, aggrid, lineid, objval FROM atobjval WHERE objid
IN (281479288456304,,285774255837674) ORDER BY objid, attrid,
aggrid, lineid;
QUERY PLAN
--
Sort (cost=116851.38..117196.22 rows=137935 width=32)
Sort Key: objid, attrid, aggrid, lineid
-> Bitmap Heap Scan on atobjval (cost=4947.40..105076.13 rows=137935
width=32)
Recheck Cond: (objid = ANY ('{281479288456304,,285774255837674}'::bigint[]))
-> Bitmap Index Scan on atobjvalix (cost=0.00..4912.92 rows=137935
width=0)
Index Cond: (objid = ANY ('{281479288456304,,285774255837674}'::bigint[]))
explain DECLARE curs_285058224 CURSOR FOR SELECT objid, attrid, aggrid, lineid,
objval FROM atobjval WHERE objid IN (281479288456304,,285774255837674) ORDER BY objid, attrid, aggrid, lineid;
QUERY PLAN
--
Index Scan using atobjvalix on atobjval (cost=0.00..1041413.49 rows=137935
width=32)
Filter: (objid = ANY ('{281479288456304,,285774255837674}'::bigint[]))
Regards,
Robert
-Ursprüngliche Nachricht-
Von: Tom Lane [mailto:[EMAIL PROTECTED]
Gesendet: Dienstag, 01. April 2008 18:42
An: Hell, Robert
Cc: pgsql-performance@postgresql.org
Betreff: Re: [PERFORM] Cursors and different settings for
default_statistics_target
"Hell, Robert" <[EMAIL PROTECTED]> writes:
> That's CURSOR_OPT_FAST_PLAN and isn't it? Our application reads the full
> results of most cursors.
Just out of curiosity, why use a cursor at all then? But anyway, you
might want to consider running a custom build with a higher setting for
tuple_fraction for OPT_FAST_PLAN (look into planner.c). I've
occasionally thought about exposing that as a GUC parameter, but
never gotten motivated to do it.
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
Re: [PERFORM] Cursors and different settings for default_statistics_target
"Hell, Robert" <[EMAIL PROTECTED]> writes: > But why are the first 15 fetches (15360 rows) processed in 0.5 seconds and > the last fetch (998 rows) takes 7 seconds. > Are we just unlucky that the last fetch takes that long? Well, the indexscan plan is going to scan through all the rows in objid order and return whichever of them happen to match the IN list. So I think you're just saying that your IN list isn't uniformly dense through the whole set of objids. If the real story is that you tend to select only objids within a narrow range, adding explicit "AND objid >= x AND objid <= y" constraints (where you compute x and y on the fly from the set of objids you're asking for) would reduce the overhead of the indexscan. 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
[PERFORM] SSDs
Tried harder to find info on the write cycles: found som CFs that claim 2million cycles, and found the Mtron SSDs which claim to have very advanced wear levelling and a suitably long lifetime as a result even with an assumption that the underlying flash can do 100k writes only. The 'consumer' MTrons are not shabby on the face of it and not too expensive, and the pro models even faster. But ... the spec pdf shows really hight performance for average access, stream read *and* write, random read ... and absolutely pants performance for random write. Like 130/s, for .5k and 4k writes. Its so pants it looks like a misprint and it doesn't seem to square with the review on tomshardware: http://www.tomshardware.com/2007/11/21/mtron_ssd_32_gb/page7.html Even there, the database IO rate does seem lower than you might hope, and this *might* be because the random reads are very very fast and the random writes ... aren't. Which is a shame, because that's exactly the bit I'd hope was fast. So, more work to do somewhere. -- 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] SSDs
My colleague has tested a single Mtron Mobo's and a set of 4. He also mentioned the write performance was pretty bad compared to a Western Digital Raptor. He had a solution for that however, just plug the SSD in a raid-controller with decent cache performance (his favorites are the Areca controllers) and the "bad" write performance is masked by the controller's cache. It wood probably be really nice if you'd get tuned controllers for ssd's so they use less cache for reads and more for writes. Best regards, Arjen On 2-4-2008 8:16, James Mansion wrote: Tried harder to find info on the write cycles: found som CFs that claim 2million cycles, and found the Mtron SSDs which claim to have very advanced wear levelling and a suitably long lifetime as a result even with an assumption that the underlying flash can do 100k writes only. The 'consumer' MTrons are not shabby on the face of it and not too expensive, and the pro models even faster. But ... the spec pdf shows really hight performance for average access, stream read *and* write, random read ... and absolutely pants performance for random write. Like 130/s, for .5k and 4k writes. Its so pants it looks like a misprint and it doesn't seem to square with the review on tomshardware: http://www.tomshardware.com/2007/11/21/mtron_ssd_32_gb/page7.html Even there, the database IO rate does seem lower than you might hope, and this *might* be because the random reads are very very fast and the random writes ... aren't. Which is a shame, because that's exactly the bit I'd hope was fast. So, more work to do somewhere. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
