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 >>>>>>>>>> >>>>>>>>>
