At work, we had been putting up with pretty sucky database performance. We are a distributed development group connecting to RDS/AWS over a VPN for our database services. It's remote which is supposed to be slow so I never really dove into it. However, in writing some lightweight JSON controller to returning results this week, testing showed that it was taking ~500ms which was just too slow and caused me to do a deep dive into the problem. Hopefully these experiences will help others.
We use hibernate as our ORM and spring to help us with hibernate session and transaction handling. Our DAO methods look something like:
@Transactional(readOnly = true) public User getUserByName(String name) { ... }
The @Transaction Java annotation tells spring (and hibernate) that the method should be surrounded by a read-only database transaction. This transaction ensures consistent reads across the entire method call and ensures that some code isn't performance a write operation unexpectedly.
However, I noticed that the transaction overhead for read-only connections was noticably slower than with read-write. When I started, read-only overhead was 480ms while read-write was 336ms. This is why I wrote this StackOverflow question entitled Why do spring/hibernate read-only database transactions run slower than read-write.
The short answer to the question was that hibernate starts off a @Transaction(readOnly = true) marked 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.
The rest of the page provides more details on the performance improvements made to our database calls.
The first thing I did was to take the VPN out of the loop to see how much penalty we were paying for it. Going straight to the RDS only took 141ms versus 480ms so the penalty was crazy large. I asked the Google and came up with this great page about optimizing OpenVPN connections. It recommended that I switch the VPN from TCP to UDP and to add the following settings to the client and server configs:
; Got these from: https://community.openvpn.net/openvpn/wiki/Gigabit_Networks_Linux tun-mtu 6000 fragment 0 mssfix 0
Ok. I should have know about the TCP to UDP at least. With TCP you are paying for a lot of back and forth which adds up if the VPN server is remote. I'm not sure how or even if the other settings helped. With these changes, read-only transaction overhead dropped from 480ms to 141ms but was still more than read-write's 100ms. Big win.
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. Each one of those calls are synchronous across a TCP connection to the remote database. 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. With the new version, read-only transaction overhead wnt down to 120ms. Read-write still at 100ms. Nice.
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.
WARNING: we actually rolled back adding
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 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). As Brett points out, it's a brittle solution that then would not work if hibernate at all changed its JDBC command order. An alternate solution would be to use different transaction managers based on name. Maybe something like the following with Spring XML support:
@Transactional(value = "readOnly", readOnly = true)
In any case, using my 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 since they can use their default read status and not make any changes.
Here's the source for my:
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.
Hope my experience helps others.
Free Spam Protection Android ORM Simple Java Zip JMX using HTTP Great Eggnog Recipe