Re: very slow largeobject transfers through JDBC

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