Re: very slow largeobject transfers through JDBC

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)

On Mon, Sep 3, 2018 at 4:40 PM Dave Cramer <pg(at)fastcrypt(dot)com> wrote:

> the one you have highlighted ~1.69ms
>
> Dave Cramer
>
> davec(at)postgresintl(dot)com
> www.postgresintl.com
>
>
> On Mon, 3 Sep 2018 at 10:38, Mate Varga <m(at)matevarga(dot)net> wrote:
>
>> Which frame do you refer to?
>>
>> On Mon, Sep 3, 2018 at 3:57 PM Dave Cramer <pg(at)fastcrypt(dot)com> wrote:
>>
>>> Not sure why reading from a socket is taking 1ms ?
>>>
>>> Dave Cramer
>>>
>>> davec(at)postgresintl(dot)com
>>> www.postgresintl.com
>>>
>>>
>>> On Mon, 3 Sep 2018 at 09:39, Mate Varga <m(at)matevarga(dot)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(at)matevarga(dot)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 <pg(at)fastcrypt(dot)com> wrote:
>>>>>
>>>>>>
>>>>>>
>>>>>> On Mon, 3 Sep 2018 at 03:55, Mate Varga <m(at)matevarga(dot)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(dot)cramer(at)crunchydata(dot)ca
>>>>>> www.crunchydata.ca
>>>>>>
>>>>>>
>>>>>>
>>>>>>> Mate
>>>>>>>
>>>>>>> On Sun, Sep 2, 2018 at 12:55 AM Dave Cramer <pg(at)fastcrypt(dot)com>
>>>>>>> wrote:
>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> On Fri, 31 Aug 2018 at 10:15, Mate Varga <m(at)matevarga(dot)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 <dmitigr(at)gmail(dot)com>
>>>>>>>>> wrote:
>>>>>>>>>
>>>>>>>>>> пт, 31 авг. 2018 г. в 16:35, Mate Varga <m(at)matevarga(dot)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
>>>>>>>>
>>>>>>>> davec(at)postgresintl(dot)com
>>>>>>>> www.postgresintl.com
>>>>>>>>
>>>>>>>