More precisely: when fetching 10k rows, JDBC driver just does a large bunch of socket reads. With lobs, it's ping-pong: one read, one write per lob...
On Mon, Sep 3, 2018 at 6:54 PM Mate Varga <m...@matevarga.net> wrote: > So I have detailed profiling results now. Basically it takes very long > that for each blob, the JDBC driver reads from the socket then it creates > the byte array on the Java side. Then it reads the next blob, etc. I guess > this takes many network roundtrips. > > On Mon, Sep 3, 2018 at 5:58 PM Dave Cramer <p...@fastcrypt.com> wrote: > >> >> On Mon, 3 Sep 2018 at 10:48, Mate Varga <m...@matevarga.net> wrote: >> >>> That's 1690 msec (1.69 seconds, and that is how long it takes to fetch >>> 20k (small-ish) rows without LOBs (LOBs are a few lines below on the >>> screenshot) >>> >> >> that sound high as well! >> >> Something isn't adding up.. >> >> >> Dave Cramer >> >> da...@postgresintl.com >> www.postgresintl.com >> >> >> >>> >>> On Mon, Sep 3, 2018 at 4:40 PM Dave Cramer <p...@fastcrypt.com> wrote: >>> >>>> the one you have highlighted ~1.69ms >>>> >>>> Dave Cramer >>>> >>>> da...@postgresintl.com >>>> www.postgresintl.com >>>> >>>> >>>> On Mon, 3 Sep 2018 at 10:38, Mate Varga <m...@matevarga.net> wrote: >>>> >>>>> Which frame do you refer to? >>>>> >>>>> On Mon, Sep 3, 2018 at 3:57 PM Dave Cramer <p...@fastcrypt.com> wrote: >>>>> >>>>>> Not sure why reading from a socket is taking 1ms ? >>>>>> >>>>>> Dave Cramer >>>>>> >>>>>> da...@postgresintl.com >>>>>> www.postgresintl.com >>>>>> >>>>>> >>>>>> On Mon, 3 Sep 2018 at 09:39, Mate Varga <m...@matevarga.net> wrote: >>>>>> >>>>>>> Hi, >>>>>>> >>>>>>> https://imgur.com/a/ovsJPRv -- I've uploaded the profiling info (as >>>>>>> an image, sorry). It seems this is a JDBC-level problem. I understand >>>>>>> that >>>>>>> the absolute timing is not meaningful at all because you don't know how >>>>>>> large the resultset is, but I can tell that this is only a few thousands >>>>>>> rows + few thousand largeobjects, each largeobject is around 1 kByte. >>>>>>> (Yes >>>>>>> I know this is not a proper use of LOBs -- it's a legacy db structure >>>>>>> that's hard to change.) >>>>>>> >>>>>>> Thanks. >>>>>>> Mate >>>>>>> >>>>>>> On Mon, Sep 3, 2018 at 11:52 AM Mate Varga <m...@matevarga.net> wrote: >>>>>>> >>>>>>>> Hey, >>>>>>>> >>>>>>>> we'll try to test this with pure JDBC versus hibernate. Thanks! >>>>>>>> >>>>>>>> >>>>>>>> On Mon, Sep 3, 2018 at 11:48 AM Dave Cramer <p...@fastcrypt.com> >>>>>>>> wrote: >>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>>> On Mon, 3 Sep 2018 at 03:55, Mate Varga <m...@matevarga.net> wrote: >>>>>>>>> >>>>>>>>>> Basically there's a class with a byte[] field, the class is >>>>>>>>>> mapped to table T and the byte field is annotated with @Lob so it >>>>>>>>>> goes to >>>>>>>>>> the pg_largeobject table. >>>>>>>>>> >>>>>>>>> >>>>>>>>> Ah, so hibernate is in the mix. I wonder if that is causing some >>>>>>>>> challenges ? >>>>>>>>> >>>>>>>>> >>>>>>>>>> The DB is on separate host but relatively close to the app, and I >>>>>>>>>> can reproduce the problem locally as well. One interesting bit is >>>>>>>>>> that >>>>>>>>>> turning of SSL between the app and PSQL speeds up things by at least >>>>>>>>>> 50%. >>>>>>>>>> >>>>>>>>>> Ah, one addition -- the binary objects are encrypted, so their >>>>>>>>>> entropy is very high. >>>>>>>>>> >>>>>>>>>> Any chance you could write a simple non-hibernate test code to >>>>>>>>> time the code ? >>>>>>>>> >>>>>>>>> Dave Cramer >>>>>>>>> >>>>>>>>> dave.cra...@crunchydata.ca >>>>>>>>> www.crunchydata.ca >>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>>>> Mate >>>>>>>>>> >>>>>>>>>> On Sun, Sep 2, 2018 at 12:55 AM Dave Cramer <p...@fastcrypt.com> >>>>>>>>>> wrote: >>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> On Fri, 31 Aug 2018 at 10:15, Mate Varga <m...@matevarga.net> >>>>>>>>>>> wrote: >>>>>>>>>>> >>>>>>>>>>>> I see -- we could try that, though we're mostly using an ORM >>>>>>>>>>>> (Hibernate) to do this. Thanks! >>>>>>>>>>>> >>>>>>>>>>>> On Fri, Aug 31, 2018 at 3:57 PM Dmitry Igrishin < >>>>>>>>>>>> dmit...@gmail.com> wrote: >>>>>>>>>>>> >>>>>>>>>>>>> пт, 31 авг. 2018 г. в 16:35, Mate Varga <m...@matevarga.net>: >>>>>>>>>>>>> > >>>>>>>>>>>>> > Hi, >>>>>>>>>>>>> > >>>>>>>>>>>>> > we're fetching binary data from pg_largeobject table. The >>>>>>>>>>>>> data is not very large, but we ended up storing it there. If I'm >>>>>>>>>>>>> copying >>>>>>>>>>>>> the data to a file from the psql console, then it takes X time >>>>>>>>>>>>> (e.g. a >>>>>>>>>>>>> second), fetching it through the JDBC driver takes at least 10x >>>>>>>>>>>>> more. We >>>>>>>>>>>>> don't see this difference between JDBC and 'native' performance >>>>>>>>>>>>> for >>>>>>>>>>>>> anything except largeobjects (and bytea columns, for the record). >>>>>>>>>>>>> > >>>>>>>>>>>>> > Does anyone have any advice about whether this can be tuned >>>>>>>>>>>>> or what the cause is? >>>>>>>>>>>>> I don't know what a reason of that, but I think it's >>>>>>>>>>>>> reasonable and >>>>>>>>>>>>> quite simple to call lo_import()/lo_export() via JNI. >>>>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>> Can't imagine that's any faster. The driver simply implements >>>>>>>>>>> the protocol >>>>>>>>>>> >>>>>>>>>>> Do you have any code to share ? Any other information ? >>>>>>>>>>> >>>>>>>>>>> Is the JDBC connection significantly further away network wise ? >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> Dave Cramer >>>>>>>>>>> >>>>>>>>>>> da...@postgresintl.com >>>>>>>>>>> www.postgresintl.com >>>>>>>>>>> >>>>>>>>>>
