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

Issue

I've been doing some research around the performance of read-only versus read-write database transactions. The MySQL server is remote across a slow VPN link so it's easy for me to see differences between the transaction types. This is with connection pooling which I know is working based on comparing 1st versus 2nd JDBC calls.

When I configure the Spring AOP to use a read-only transaction on my DAO call, the calls are 30-40% slower compared to read-write:

<!-- slower -->
<tx:method name="find*" read-only="true" propagation="REQUIRED" />
...
// slower
@Transaction(readOnly = true)

Versus:

<!-- faster -->
<tx:method name="find*" read-only="false" propagation="REQUIRED" />
...
// faster
@Transaction

Looking at tcpdump, it seems like the read-only transaction is doing more back and forth talking to MySQL. Here's the read-only dump versus read-write.

1. Can anyone explain why the read-only calls are taking longer? Is this expected?

2. Is there anything I'm doing wrong or anything that I can do to improve their speed aside from improving the network? Just found this awesome post with some good performance recommendations. Any other comments?

Thanks much.

Solution

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

Answered By - Gray