XA与非XA JDBC驱动程序性能? [英] XA vs. Non-XA JDBC Driver Performance?

问题描述

在不需要XA JDBC驱动程序(不参与分布式事务的只读工作)的情况下，我们将使用它.

We are using an XA JDBC driver in a case where it is not required (read-only work that doesn't participate in a distributed transaction).

只是想知道切换到Non-XA JDBC驱动程序是否有任何已知的性能提升-如果不是，那不值得切换吗?

Just wondering if there are any known performance gains to be had to switch to the Non-XA JDBC driver - if not it's probably not worth switching?

FWIW我们正在使用MySQL 5.1

FWIW we are using MySQL 5.1

推荐答案

与所有与性能相关的事物一样，答案是:它取决于.具体来说，这取决于您如何使用驱动程序.

As with all things performance related, the answer is: it depends. Specifically, it depends on exactly how you are using the driver.

与数据库进行事务交互的成本大致分为:代码复杂性开销，通信开销，sql处理和磁盘I/O.

The cost of interacting transactionally with a database is divided roughly into: code complexity overhead, communication overhead, sql processing and disk I/O.

XA和非XA情况下的通信开销有所不同.在所有其他条件相同的情况下，XA事务在这里要花更多的钱，因为它需要更多的数据库往返.对于处于手动提交模式的非XA事务，成本至少为两次调用:sql操作和提交.在XA情况下，它是开始，SQL操作，结束，准备和提交.对于您的特定用例，它将自动优化以开始，SQL操作，结束，准备.并非所有调用的成本都相等:结果集中移动的数据通常会占主导地位.在LAN上，额外往返的费用通常并不大.

Communication overhead differs somewhat between the XA and non-XA cases. All else being equal, an XA transaction carries a little more cost here as it requires more round trips to the db. For a non-XA transaction in manual commit mode, the cost is at least two calls: the sql operation(s) and the commit. In the XA case it's start, sql operation(s), end, prepare and commit. For your specific use case that will automatically optimize to start, sql operation(s), end, prepare. Not all the calls are of equal cost: the data moved in the result set will usually dominate. On a LAN the cost of the additional round trips is not usually significant.

但是请注意，有一些有趣的陷阱正在潜伏着等待.例如，某些驱动程序不支持XA模式下的预备语句缓存，这意味着XA使用会带来每次调用时重新解析SQL的额外开销，或者要求您在驱动程序顶部使用单独的语句池.在池的主题上，正确池XA连接要比池非XA连接复杂得多，因此，根据连接池的实现，您可能还会发现对池的轻微影响.如果某些ORM框架使用积极的连接释放并在事务范围内重新获取，则特别容易受到连接池开销的影响.如果可能，请配置为在tx的生命周期内抓住并保持连接，而不是多次访问该池.

Note however that there are some interesting gotchas lurking in wait for the unwary. For example, some drivers don't support prepared statement caching in XA mode, which means that XA usage carries the added overhead of re-parsing the SQL on every call, or requires you to use a separate statement pool on top of the driver. Whilst on the topic of pools, correctly pooling XA connections is a little more complex than pooling non-XA ones, so depending on the connection pool implementation you may see a slight hit there too. Some ORM frameworks are particularly vulnerable to connection pooling overhead if they use aggressive connection release and reacquire within transaction scope. If possible, configure to grab and hold a connection for the lifetime of the tx instead of hitting the pool multiple times.

使用前面提到的有关准备好的语句的缓存的警告，XA和非XA tx之间的sql处理成本没有实质性的区别.但是，数据库服务器上的资源使用情况之间存在很小的差异:在某些情况下，在非XA情况下，服务器可能会更快地释放资源.但是，交易通常足够短，因此这不是一个重要的考虑因素.

With the caveat mentioned previously regarding the caching of prepared statements, there is no material difference in the cost of the sql handling between XA and non-XA tx. There is however a small difference to resource usage on the db server: in some cases it may be possible for the server to release resources sooner in the non-XA case. However, transactions are normally short enough that this is not a significant consideration.

现在，我们考虑磁盘I/O开销.在这里，我们关心的是XA协议引起的I/O，而不是业务逻辑所使用的SQL，因为在任何一种情况下，后者都保持不变.对于只读事务，情况很简单:明智的db和tx管理器不会执行任何日志写入操作，因此没有开销.对于写情况，由于XA的单阶段提交优化，在db是唯一涉及的资源的情况下也是如此.对于2PC情况，每个db服务器或其他资源管理器都需要两次磁盘写入，而不是在非XA情况下使用一次磁盘写入，而tx管理器同样需要两次写入.由于磁盘存储的速度较慢，这是XA与非XA相比性能开销的主要来源.

Now we consider disk I/O overhead. Here we are concerned with I/O occasioned by the XA protocol rather than the SQL used for the business logic, as the latter is unchanged in either case. For read-only transactions the situation is simple: a sensible db and tx manager won't do any log writes, so there is no overhead. For write cases the same is true where the db is the only resource involved, due to XA's one phase commit optimization. For the 2PC case each db server or other resource manager needs two disk writes instead of the one used in non-XA cases, and the tx manager likewise needs two. Thanks to the slowness of disk storage this is the dominant source of performance overhead in XA vs. non-XA.

后面几段我提到了代码的复杂性.与非XA相比，XA需要更多的代码执行量.在大多数情况下，复杂性被隐藏在事务管理器中，尽管您当然可以根据需要直接驱动XA.成本通常是微不足道的，但要遵守已经提到的警告.除非您使用特别差的交易管理器，否则您可能会遇到问题.只读情况特别有趣-事务管理器提供程序通常将优化工作放在磁盘I/O代码中，而锁定争用对于只读用例(尤其是在高度并发的系统上)来说是更重要的问题.

Several paragraphs back I mentioned code complexity. XA requires slightly more code execution than non-XA. In most cases the complexity is buried in the transaction manager, although you can of course drive XA directly if you prefer. The cost is mostly trivial, subject to the caveats already mentioned. Unless you are using a particularly poor transaction manager, in which case you may have a problem. The read-only case is particularly interesting - transaction manager providers usually put their optimization effort into the disk I/O code, whereas lock contention is a more significant issue for read-only use cases, particularly on highly concurrent systems.

还要注意，在以应用服务器或其他标准事务管理器提供程序为特征的体系结构中，tx管理器中的代码复杂性是一种红鲱鱼，因为它们通常使用与XA和非XA事务协调相同的代码.在非XA情况下，要完全错过tx管理器，通常必须告诉应用服务器/框架将连接视为非事务性，然后直接使用JDBC驱动提交.

Note also that code complexity in the tx manager is something of a red-herring in architectures featuring an app server or other standard transaction manager provider, as these usually use much the same code for XA and non-XA transaction coordination. In non-XA cases, to miss out the tx manager entirely you typically have to tell the app server / framework to treat the connection as non-transactional and then drive the commit directly using JDBC.

摘要为:除非您选择了XA/非XA，否则您的sql查询费用将决定只读事务的时间弄乱配置中的某些内容或在每个TX中进行特别琐碎的sql操作，后者是您的业务逻辑可能使用某些重组来更改每个tx中的tx管理开销与业务逻辑的比率的信号.

So the summary is: The cost of your sql queries is going to dominate the read-only transaction time regardless of the XA/non-XA choice, unless you mess up something in the configuration or do particularly trivial sql operations in each tx, the latter being a sign your business logic could probably use some restructuring to change the ratio of tx management overhead to business logic in each tx.

因此，对于只读情况，适用通常的事务协议不可知建议:在ORM解决方案中考虑事务感知级别的二级缓存，而不是每次都访问数据库.如果失败，请调整sql，然后增加数据库的缓冲区高速缓存，直到看到90％+的命中率，或者最大化服务器的RAM插槽，以先到者为准.完成此操作后，只需担心XA与非XA对比，发现事情仍然太慢了.

For read-only cases the usual transaction protocol agnostic advise therefore applies: consider a transaction aware level second level cache in an ORM solution rather than hitting the DB each time. Failing that, tune the sql, then increase the db's buffer cache until you see a 90%+ hit rate or you max out the server's RAM slots, whichever comes first. Only worry about XA vs. non-XA once you've done that and found things are still too slow.

这篇关于XA与非XA JDBC驱动程序性能?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！