Re: [PERFORM] 8xIntel S3500 SSD in RAID10 on Dell H710p
On Wed, Dec 10, 2014 at 2:30 AM, Strahinja Kustudić strahin...@nordeus.com wrote: On Wed, Dec 10, 2014 at 4:55 AM, Mark Kirkwood mark.kirkw...@catalyst.net.nz wrote: That is interesting: I've done some testing on this type of card with 16 (slightly faster Hitachi) SSD attached. Setting WT and NORA should enable the so-called 'fastpath' mode for the card [1]. We saw performance improve markedly (300MB/s random write go to 1300MB/s). This *might* be related to the fact that 16 SSD can put out more IOPS than the card can actually handle - whereas your 8 S3500 is probably the perfect number (e.g 8*11000 = 88000 which the card can handle ok). [1] If you make the change while there are no outstanding background operations (array rebuild etc) in progress (see http://www.flagshiptech.com/eBay/Dell/poweredgeh310h710h810UsersGuide.pdf). I read that guide too, which is the reason why I tried with WT/NORA, but the document also states: NOTE: RAID 10, RAID 50, and RAID 60 virtual disks cannot use FastPath. Which is a little odd, since usually if you want performance with reliability, you go RAID10. Do you have any suggestions what I could try to tweak to get more performance? Definitely crank effective_io_concurrency. It will not help stock pgbench test since it doesn't involve bitmap heap scans but when it kicks in it's much faster. http://www.postgresql.org/message-id/CAHyXU0yiVvfQAnR9cyH=HWh1WbLRsioe=mzRJTHwtr=2azs...@mail.gmail.com As it pertains to random read performance, I think you'll find that you're getting pretty close to maxing out what the computer is basically capable of -- I highly doubt you'll be read bound on storage for any application; the classic techniques of optimizing queries, indexes and tables is where focus your energy. Sequential write will also be no problem. The only area where the s3500 falls short is random writes. If your random write i/o requirements are extreme, you've bought the wrong drive, I'd have shelled out for the S3700 (but it's never too late; you can stack one on and move high write activity tables to the s3700 driven tablespace). merlni -- 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] Re: [SQL] querying with index on jsonb slower than standard column. Why?
On 12/08/2014 01:39 PM, Tim Dudgeon wrote: On 08/12/2014 18:14, Adrian Klaver wrote: Recheck Cond: data - 'assay1_ic50'::text))::double precision 90::double precision) AND (((data - 'assay2_ic50'::text))::double precision 10::double precision)) which means we have to pull the JSONB value out of the tuple, search it to find the 'assay1_ic50' key, convert the associated value to text (which is not exactly cheap because *the value is stored as a numeric*), then reparse that text string into a float8, after which we can use float8gt. And then probably do an equivalent amount of work on the way to making the other comparison. So this says nothing much about the lossy-bitmap code, and a lot about how the JSONB code isn't very well optimized yet. In particular, the decision not to provide an operator that could extract a numeric field without conversion to text is looking pretty bad here. Yes, that bit seemed strange to me. As I understand the value is stored internally as numeric, but the only way to access it is as text and then cast back to numeric. I *think* this is the only way to do it presently? Yeah, I believe the core problem is that Postgres currently doesn't have any way to have variadic return times from a function which don't match variadic input types. Returning a value as an actual numeric from JSONB would require returning a numeric from a function whose input type is text or json. So a known issue but one which would require a lot of replumbing to fix. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.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] Re: [SQL] querying with index on jsonb slower than standard column. Why?
Josh Berkus j...@agliodbs.com writes: Yeah, I believe the core problem is that Postgres currently doesn't have any way to have variadic return times from a function which don't match variadic input types. Returning a value as an actual numeric from JSONB would require returning a numeric from a function whose input type is text or json. So a known issue but one which would require a lot of replumbing to fix. Well, it'd be easy to fix if we were willing to invent distinct operators depending on which type you wanted out (perhaps - for text output as today, add -# for numeric output, etc). Doesn't seem terribly nice from a usability standpoint though. The usability issue could be fixed by teaching the planner to fold a construct like (jsonb - 'foo')::numeric into (jsonb -# 'foo'). But I'm not sure how we do that except in a really ugly and ad-hoc fashion. 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] Re: [SQL] querying with index on jsonb slower than standard column. Why?
On Fri, Dec 12, 2014 at 6:44 PM, Tom Lane t...@sss.pgh.pa.us wrote: The usability issue could be fixed by teaching the planner to fold a construct like (jsonb - 'foo')::numeric into (jsonb -# 'foo'). But I'm not sure how we do that except in a really ugly and ad-hoc fashion. It would be doable if you could have polymorphism on return type, and teach the planner to interpret (jsonb - 'foo')::numeric as the operator with a numeric return type. That's a trickier business even, but it could be far more useful and generically helpful than -#. Tricky part is what to do when the cast is missing. -- 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] Re: [SQL] querying with index on jsonb slower than standard column. Why?
On 12/12/2014 04:44 PM, Tom Lane wrote: Josh Berkus j...@agliodbs.com writes: Yeah, I believe the core problem is that Postgres currently doesn't have any way to have variadic return times from a function which don't match variadic input types. Returning a value as an actual numeric from JSONB would require returning a numeric from a function whose input type is text or json. So a known issue but one which would require a lot of replumbing to fix. Well, it'd be easy to fix if we were willing to invent distinct operators depending on which type you wanted out (perhaps - for text output as today, add -# for numeric output, etc). That was my immediate reaction. Not sure about the operator name. I'd tentatively suggest -# (taking an int or text argument) and ## taking a text[] argument, both returning numeric, and erroring out if the value is a string, boolean, object or array. Doesn't seem terribly nice from a usability standpoint though. The usability issue could be fixed by teaching the planner to fold a construct like (jsonb - 'foo')::numeric into (jsonb -# 'foo'). But I'm not sure how we do that except in a really ugly and ad-hoc fashion. I would be inclined to add the operator and see how cumbersome people find it. I suspect in many cases it might be sufficient. 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] Re: [SQL] querying with index on jsonb slower than standard column. Why?
Andrew Dunstan and...@dunslane.net writes: On 12/12/2014 04:44 PM, Tom Lane wrote: Well, it'd be easy to fix if we were willing to invent distinct operators depending on which type you wanted out (perhaps - for text output as today, add -# for numeric output, etc). That was my immediate reaction. Not sure about the operator name. I'd tentatively suggest -# (taking an int or text argument) and ## taking a text[] argument, both returning numeric, and erroring out if the value is a string, boolean, object or array. The usability issue could be fixed by teaching the planner to fold a construct like (jsonb - 'foo')::numeric into (jsonb -# 'foo'). But I'm not sure how we do that except in a really ugly and ad-hoc fashion. I would be inclined to add the operator and see how cumbersome people find it. I suspect in many cases it might be sufficient. We can't just add the operator and worry about usability later; if we're thinking we might want to introduce such an automatic transformation, we have to be sure the new operator is defined in a way that allows the transformation to not change any semantics. What that means in this case is that if (jsonb - 'foo')::numeric would have succeeded, (jsonb -# 'foo') has to succeed; which means it'd better be willing to attempt conversion of string values to numeric, not just throw an error on sight. 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] Re: [SQL] querying with index on jsonb slower than standard column. Why?
On 12/12/2014 08:20 PM, Tom Lane wrote: We can't just add the operator and worry about usability later; if we're thinking we might want to introduce such an automatic transformation, we have to be sure the new operator is defined in a way that allows the transformation to not change any semantics. What that means in this case is that if (jsonb - 'foo')::numeric would have succeeded, (jsonb -# 'foo') has to succeed; which means it'd better be willing to attempt conversion of string values to numeric, not just throw an error on sight. Well, I'm not 100% convinced about the magic transformation being a good thing. Json numbers are distinct from strings, and part of the justification for this is to extract a numeric datum from jsonb exactly as stored, on performance grounds. So turning round now and making that turn a string into a number if possible seems to me to be going in the wrong direction. 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] Re: [SQL] querying with index on jsonb slower than standard column. Why?
On Sat, Dec 13, 2014 at 12:05 AM, Andrew Dunstan and...@dunslane.net wrote: On 12/12/2014 08:20 PM, Tom Lane wrote: We can't just add the operator and worry about usability later; if we're thinking we might want to introduce such an automatic transformation, we have to be sure the new operator is defined in a way that allows the transformation to not change any semantics. What that means in this case is that if (jsonb - 'foo')::numeric would have succeeded, (jsonb -# 'foo') has to succeed; which means it'd better be willing to attempt conversion of string values to numeric, not just throw an error on sight. Well, I'm not 100% convinced about the magic transformation being a good thing. Json numbers are distinct from strings, and part of the justification for this is to extract a numeric datum from jsonb exactly as stored, on performance grounds. So turning round now and making that turn a string into a number if possible seems to me to be going in the wrong direction. It's still better than doing the conversion every time. The niceness of that implementation aside, I don't see how it can be considered the wrong direction. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
