08-02-2023, 04:02 PM
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][1] versus [read-write][2].
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][3]. Any other comments?
Thanks much.
[1]:
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][1] versus [read-write][2].
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][3]. Any other comments?
Thanks much.
[1]:
[To see links please register here]
[2]:[To see links please register here]
[3]:[To see links please register here]