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