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

Question

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.

Pulitzer

this is really weird. BTW, #1614639 – Lara

Interesting read @Leo, thanks. – Pulitzer

Answer 1

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

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.

Pulitzer

nice work. You might also investigate the MySQL driver properties alwaysSendSetIsolation and readOnlyPropagatesToServer. Either one or both may be able to eliminate the need for your special pool wrapper. – Scaffold

for further performance improvements you might want to investigate cacheResultSetMetadata, cacheServerConfiguration, and tcpRcvBuf. – Scaffold

Thanks @brettw. I've looked at the readOnlyPropagatesToServer and I don't think it will help. The issues is that hibernate always sets and then resets the read-only status on the Connection. I don't think the driver can ignore them blindly but my code has that luxury. I'll check out the rest. – Pulitzer

Thought you might find this interesting. Old article, but basically staying it is better not to use the Transactional annotation at all for read-only operations, or to additionally use propagation=Propagation.SUPPORTS. I'm curious as to how that affects the setReadOnly()/setAutoCommit() behaviors at the Hibernate level. – Scaffold

Thanks for that @Scaffold but it doesn't apply here. I need the @Transaction because that's what gives me my hibernate session. I'm not doing straight JDBC. I also use readOnly = true to ensure that I'm not writing inappropriately. – Pulitzer

@Gray, thanks for this writeup. With regards to point 3 being rolled-back, do you have pointers to which bugs you ran into or with what version? I see that bugs.mysql.com/bug.php?id=75209 is fixed as of Connector/J 5.1.40. – Superfamily

Yeah I need to do more testing @DelGurth. We definitely had problems with 5.1.40 that forces us to remove that setting in production ASAP. I've not had the time to do more testing of it however. – Pulitzer