本文介绍了为什么spring / hibernate只读数据库事务运行速度慢于读写?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我一直在研究围绕只读与读写数据库事务的性能。 MySQL服务器是远程的,所以我很容易看到不同事务类型之间的差异。这是连接池,我知道工作的基础是比较第一次和第二次JDBC调用。



当我将Spring AOP配置为在我的DAO上使用只读事务调用,与读写相比,调用30-40%较慢

 < ;! - 较慢 - > 
< tx:method name =find *只读=truepropagation =REQUIRED/>

  @Transaction(readOnly = true)

Versus:

 <! - 更快 - > 
< tx:method name =find *只读=falsepropagation =REQUIRED/>

  //更快
@Transaction

纵观tcpdump,看起来像只读事务正在与MySQL进行更多来回的对话。以下是与。


  1. 任何人都可以解释为什么只读调用需要更长时间。这是预期的吗?

  2. 有没有什么我做错了什么,或者我可以做什么来提高他们的速度,除了改善网络?刚发现这个很棒的帖子,有一些。其他评论?


非常感谢。

为什么spring / hibernate只读数据库事务运行速度比读写慢?

好吧,这一直很有趣。很多让我学习和分享。下面的一些应该是显而易见的,但希望我的无知和我学到的东西会对其他人有所帮助。

问题#1的简短回答是,hibernate开始使用 set session.transaction.read.only 同步JDBC调用关闭 @Transaction(readOnly = true)设置session.transaction.read.write 调用结束。这些调用在进行读写调用时不会发送,这就是为什么只读调用较慢的原因。



问题#2的较长答案涉及以下步骤的详细信息我试图减少远程数据库的性能:


  1. 我们所做的第一件事是将数据库VPN从TCP切换到UDP阅读此后。叹。我应该知道这件事。我还将以下设置添加到OpenVPN客户端和服务器配置中。只读事务开销从480ms下降到141ms,但仍然大于读写的100ms。大赢。

     ;有这些来自:https://community.openvpn.net/openvpn/wiki/Gigabit_Networks_Linux 
    proto udp
    tun-mtu 6000
    片段0
    mssfix 0


  2. 仔细查看tcpdump输出( tcpdump ... -X 为胜利),我注意到有很多不必要的自动提交和只读/读写JDBC调用。升级到我们使用的令人敬畏的库的更新版本。在2.4.1版本中,他们添加了一些智能,这些智能减少了一些这些电话。只读事务开销低至120ms。读写仍在100ms。

  3. HikariCP的作者Brett Wooldridge指出我可能会提供帮助。多谢老兄。将以下设置添加到我们的MySQL JDBC URL中,告诉驱动程序使用连接的软件状态,而不是询问服务器的状态。

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

    这些设置导致更多的同步JDBC命令被删除。只读事务开销下降到60ms,现在与读写相同。 Woo hoo。



    编辑/警告:我们实际回滚添加 useLocalTransactionState = true 找到驱动程序没有发送交易信息的错误。 但是在查看tcpdump输出时,我仍然看到只读/读写正在发送交易设置。我的最后一个修正是编写一个自定义的只读检测池,如果它发现第一个调用连接是 connection.setReadOnly(true)

    使用此自定义池将只读和读写连接的事务开销减少到20ms。我认为它基本上删除了最后一个JDBC事务开销调用。以下是我在主页上撰写的的源代码所有这一切。代码相对脆弱,依赖于Hibernate首先执行 connection.setReadOnly(true),但它似乎运行良好,我仔细地将其记录在XML和代码中。


    因此,基本的 @Transaction 开销从480ms变为20ms经过几天的工作。 100个真实生活hibernate调用55秒开始的 dao.find(...)方法,并以4.5秒结束。漂亮的踢屁股。希望它可以很容易地提高10倍的速度。



    希望我的经验可以帮助别人。


    I've been doing some research around the performance of read-only versus read-write database transactions. The MySQL server is remote so it's easy for me to see differences between the different 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" />
    

    or

    // slower
    @Transaction(readOnly = true)
    

    Versus:

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

    or

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

    解决方案

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

    The longer answer to question #2 involves the following details of the steps that I took to try and reduce 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.

    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.

    这篇关于为什么spring / hibernate只读数据库事务运行速度慢于读写?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-04 06:47
查看更多