Menu ▾ ▴

#469 Performance Issues due to Packet Size

JDBC
closed
John Eberhard
None
5
2023-01-12
2022-10-24
Balázs Gunics
No

Hello,

We have multiple customers reporting performance issues with the recent versions of the JTOpen400 JDBC driver when used from Talend. Issue seems to be independent from Java (happens with both 8 and 11) , we had both Linux / Windows complaints.

We tried to investigate as much as possible and come to the following conclusion:
Without changing any code from our side just the JDBC driver we can see a performance issue.
The different JDBC drivers have no difference when examined via JFR.

The performance difference is 20 seconds for small amount of data.

As it seems that we're simply waiting on the network we also collected network packets.

After analyzing these packets it seems that the older drivers send 65k packets while the new one does 1k. This results in the total packets between the client-server increase from 101/40 packets to 1534/1028 packets.

Drivers used in the test:
Latest JTOpen (11.0)
jt400_V5R2

Is there a JDBC parameter/property we should configure to force the old behavior?

Regards,
Balázs

1 Attachments
image_2022-10-24_155107055.png

Related

Bugs: #469

Discussion

  • Balázs Gunics

    Balázs Gunics - 2022-10-24

    I wasn't able to add multiple attachments easily, but here's the new version:

     
    Thumbnail
    image_2022-10-24_155836431.png

  • John Eberhard

    John Eberhard - 2022-10-24

    Try setting the block size property

    "block size" Specifies the block size (in kilobytes) to retrieve from the system and cache on the client. This property has no effect unless the "block criteria" property is non-zero. Larger block sizes reduce the frequency of communication to the system, and therefore may increase performance.

    https://jt400.sourceforge.net/doc/com/ibm/as400/access/doc-files/JDBCProperties.html

     
    👍
    1

  • John Eberhard

    John Eberhard - 2022-10-24
    • status: open --> pending
    • assigned_to: John Eberhard
     

  • Balázs Gunics

    Balázs Gunics - 2022-10-25

    Hello John,

    Thanks for the quick response. We've asked our customer to validate this but still waiting for feedback.
    Will keep you posted.

     

  • Balázs Gunics

    Balázs Gunics - 2022-12-05

    Hello John,

    Block size does affect the Read Performance. By setting it to 512 we can see it's faster. (I was able to go up to 1000row/sec from 500row/sec)
    However the write performance is still not good. Meanwhile I got access to a system.
    We're using Prepared Statements.
    I tried with a 10/100 batch size.

    Here's my setting:
    block size=512;send buffer size=512;toolbox trace=datastream;

    By enabling the toolbox trace I can see that we're sending data record by record and waiting for a response from the server.
    In fact it seems that there's a lot of null values. I created my tables with type VARCHAR(128) but the drier behaves like CHAR(128)

    Attached a snippet of the output.

    Regards,
    Balázs

     
    AS400 logs.txt

  • Balázs Gunics

    Balázs Gunics - 2023-01-09

    Hello John,

    Happy new year!
    Is there any other files / information we should provide?

    Regards,
    Balázs

     

  • John Eberhard

    John Eberhard - 2023-01-10

    Can you provide traces of running with the old driver and the new driver so that I can compare them? Can you also use toolbox trace=all?

     

  • Balázs Gunics

    Balázs Gunics - 2023-01-12

    Hello John,

    Please find the log files attached.
    The old driver generates a lot smaller logs (13mb vs 26mb)

    advParam=block size\=512;send buffer size\=512;toolbox trace\=all;data compression\=true

     
    jt400_logs.zip

  • John Eberhard

    John Eberhard - 2023-01-12

    It looks like the application is requesting that auto generated keys are to be returned by the insert statement. The JTOpen 5.4 driver did not always correctly return generated keys. If generated keys are not retrieved by the application, can the prepare of the insert statement be changed to not request generated keys?

    In the JTOpen 5.4 driver the inserts are batched. In the recent drivers, the inserts cannot be batched because the return of auto generated keys was requested. 

    Details: 

Tracepoint in JTOpen 5.4:  Begin batching via server-side with 100 rows..
Tracepoint in JTOpen 11.0: Begin batching via client-side multiple executes..

Details:
Tracepoint in JTOpen 5.4:  Prepared STMT0001*, SQL Statement -->[INSERT INTO BGUNICS_TEST (newColumn,newColumn1,newColumn2,newColumn3,newColumn4) VALUES (?,?,?,?,?)].
Tracepoint in JTOpen 11.0:  Prepared STMT0001*, SQL Statement -->[SELECT *SQLGENCOLUMNS FROM NEW TABLE(INSERT INTO BGUNICS_TEST (newColumn,newColumn1,newColumn2,newColumn3,newColumn4) VALUES (?,?,?,?,?))].

    The *SQLGENCOLUMNS is how the generated keys are returned.

     

  • Balázs Gunics

    Balázs Gunics - 2023-01-12

    Hello John,

    Yes removing the return generated keys increases the performance greatly.
    pstmt_<%=cid %> = conn_<%=cid%>.prepareStatement(insert_<%=cid%>, java.sql.Statement.RETURN_GENERATED_KEYS);

    Thank you for your help!

     

  • John Eberhard

    John Eberhard - 2023-01-12
    • status: pending --> closed
     

Log in to post a comment.

MongoDB Logo MongoDB