Why do spring/hibernate read-only database transactions run slower than read-write?

by

Why do spring/hibernate read-only database transactions run slower than read-write?

<tldr> The short answer to question #1 was that hibernate starts off a @Transaction(readOnly = true) session with a set session.transaction.read.only synchronous JDBC call and ends with a set session.transaction.read.write call. These calls are not sent when doing read-write calls which is why read-only calls were slower. See below for my remedy for this. </tldr>

Ok this has been an interesting ride. Lot for me to learn and share. Some of the below should have been obvious but hopefully my ignorance and what I’ve learned will be helpful to others.

The longer answer to question #2 involves the following details of the steps that I took to try and improve our remote database performance:

  1. First thing that we did was switch our database VPN from TCP to UDP after reading this OpenVPN optimization page. Sigh. I should have known about this. I also added the following settings to the OpenVPN client and server configs. Read-only transaction overhead dropped from 480ms to 141ms but was still more than read-write’s 100ms. Big win.

    ; Got these from:
; https://community.openvpn.net/openvpn/wiki/Gigabit_Networks_Linux
proto udp
tun-mtu 6000
fragment 0
mssfix 0

  2. In looking closely at the tcpdump output (tcpdump ... -X for the win), I noticed that there were a lot of unnecessary auto-commit and read-only/read-write JDBC calls being made. Upgrading to a newer version of the awesome HikariCP connection pool library we use helped with this. In version 2.4.1 they added some intelligence which reduced some of these calls. Read-only transaction overhead down to 120ms. Read-write still at 100ms. Nice.

  3. Brett Wooldridge, the author of HikariCP pointed me to MySQL driver settings that might help. Thanks much dude. Adding the following settings to our MySQL JDBC URL tells the driver to use the software state of the connection and not ask the server for the status.

    jdbc:mysql://.../database?useLocalSessionState=true&useLocalTransactionState=true

    These settings caused more of the synchronous JDBC commands to be removed. Read-only transaction overhead dropped to 60ms and now is the same as read-write. Woo hoo.

    Edit/WARNING: we actually rolled back adding useLocalTransactionState=true after bugs were found where the driver was not sending transaction information. Not sure if the bugs were in the driver, hibernate, or our code.

  4. But in looking more at the tcpdump output, I still saw read-only/read-write transaction settings being sent. My last fix was to write a custom read-only detecting pool that gives out connections from a special pool if it sees the first call to the connection is connection.setReadOnly(true).

Using this custom pool dropped the transaction overhead for both read-only and read-write connections to 20ms. I think it basically removed the last of the JDBC transaction overhead calls. Here’s the source of the two classes that I wrote from my home page write up of all this. The code is relatively brittle and relies on Hibernate doing a connection.setReadOnly(true) first thing but it seems to be working well and I documented it in the XML and code carefully.

So basic @Transaction overhead went from 480ms to 20ms over a couple days of work. 100 “real life” hibernate calls to a dao.find(...) method started at 55 seconds and ended at 4.5 seconds. Pretty kick ass. Wish it was always this easy to get a 10x speed improvement.

Hope my experience helps others.

Leave a Comment