oracle 事务快照_了解事务和快照复制的分布规模

oracle 事务快照

背景 ( Background )

If an environment chooses to use snapshot or transactional replication, one useful exercise is to ask the technical end user (or client) what they think replication does. If you have access to a white board, you can even ask them to demonstrate what they think replication will do for their data. Generally, these technical end users will plot something similar to the below image, where we see a table with data being copied to another table with data.

如果环境选择使用快照复制或事务复制，则一个有用的练习是询问技术最终用户（或客户端）他们认为复制的作用。 如果您可以使用白板，甚至可以要求他们展示他们认为复制将对他们的数据产生什么影响。 通常，这些技术最终用户将绘制类似于下图的内容，其中我们看到一个表，该表的数据已复制到另一个表中的数据。

Replication carries many moving pieces, similar to the engine of a car where only one small piece, like a spark plug, can malfunction and cause the car to fail at starting, or experience an issue. For the sake of this article, we will discuss replication in the context of its OLTP engine – the distribution database – though this isn’t to say more is happening with the log reader, snapshot agent, etc. The goal of this article is to help people realize that the above image is nowhere close to replication so that they can optimize for increasing performance with replication.

复制具有许多运动部件，类似于汽车的引擎，其中只有一个小部件（例如火花塞）会发生故障，并导致汽车在启动时发生故障或遇到问题。 为了本文的目的，我们将在其OLTP引擎（分发数据库）的上下文中讨论复制，尽管这并不是说日志读取器，快照代理等正在发生更多的事情。本文的目的是帮助人们认识到上面的映像离复制还很遥远，因此他们可以优化以提高复制性能。

讨论区 ( Discussion )

When my brothers and I were teenagers, our parents gave us a go-cart that moved a little slower than our quick dog. We didn’t like that our dog could run faster, so we discovered that the go-cart had a governor which limited how fast we could go, and removed it. Since we didn’t grasp the laws of physics, we didn’t think anything was wrong with our decision and our go-cart went as fast as we wanted when we pressed the gas pedal. This would be like a straight bulk copy without any error logging, or any logging of any kind as to what was copied. It can work, it can perform quickly, and it can result in success, but without any logging or catching of errors, we could lose data in the process, or the entire copy could fail and we wouldn’t know why. This is similar to the first image where we take a data source and copy it to a data destination; it’s quick and requires little overhead.

当我的兄弟和我十几岁的时候，父母给了我们一辆购物车，它的移动速度比我们的快狗慢一些。 我们不希望狗跑得更快，所以我们发现购物车有一个调速器，它限制了我们走多快的速度，然后将其移开。 由于我们没有掌握物理定律，因此我们认为自己的决定没有任何问题，而且踩油门踏板时推车的行驶速度达到了我们想要的速度。 这就像是直接批量复制，没有任何错误日志记录，也没有任何关于已复制内容的日志记录。 它可以工作，可以快速执行，并且可以成功，但是如果没有任何日志记录或捕获错误，我们可能会在此过程中丢失数据，或者整个副本可能会失败，并且我们也不知道为什么。 这类似于我们获取数据源并将其复制到数据目标的第一张图片； 它快速且几乎不需要任何开销。

The distribution database for snapshot and transactional replication can act as a governor, which records the commands, the results, errors, and information about the replication. For an example, on our distribution database, we can search the publications table to see how many publications we have:

用于快照和事务复制的分发数据库可以充当调控器，它记录命令，结果，错误以及有关复制的信息。 例如，在我们的发行数据库上，我们可以搜索发布表以查看我们拥有多少发布：

 
SELECT *
FROM distribution..MSpublications
 

The distribution database holds these publications and this table is an example of the information we can find throughout the database – we can also find information about subscribers, errors, etc. Compare this to our straight bulk copy – what’s limiting the speed of the bulk copy and where are the errors logged if we didn’t build any error catching?

发行数据库中包含这些出版物，该表是我们在整个数据库中可以找到的信息的示例–我们还可以找到有关订户，错误等的信息。将其与我们的直接批量复制进行比较–限制了批量复制的速度如果我们没有构建任何错误捕获功能，那么错误记录在哪里？

The key here is that the distribution database can quickly become a bottleneck, relative to how many commands are backed up, how many errors are being generated, whether it can obtain access to the subscription servers, whether we have enough resources on the distributor, publisher and subscriber server, and a host of many other potential problems. Fundamentally, the distribution database is an OLTP engine and at the current time, Microsoft does not allow using in memory OLTP for the distribution databases’ tables (thanks to the engineer Harshal Bhargav at Microsoft for confirming this). If we don’t optimize for that, we’ll experience trouble in general because OLTP, OLAP and balanced environments can differ significantly.

关键是分发数据库可能很快成为瓶颈，相对于备份的命令数量，正在生成的错误数量，是否可以访问订阅服务器，我们在分发服务器，发布服务器上是否有足够的资源和订阅服务器，以及许多其他潜在问题。 从根本上说，分发数据库是OLTP引擎，并且当前，Microsoft不允许在内存中使用OLTP分发数据库的表（感谢Microsoft的工程师Harshal Bhargav确认了这一点）。 如果我们不为此进行优化，则通常会遇到麻烦，因为OLTP，OLAP和平衡环境可能会有很大差异。

This explains why you can find quite a few Microsoft MVPs who express dissatisfaction with replication. Unfortunately, for high availability solutions, each will come with both positives and negatives and if we decided to not log errors at all, as an example, then we might only be sending parts of data we need, or we might experience a failure when a data flow hasn’t fully completed. In most cases, partial data or inaccurate data is worse than no data. Instead of vetoing the idea, we can consider if it’s efficient for our environment to scale distributing data by publisher, such as using one distribution database per publisher. For an example:

这就解释了为什么您可以找到很多对复制不满意的Microsoft MVP。 不幸的是，对于高可用性解决方案，每个解决方案都会带来积极和消极的影响，例如，如果我们决定根本不记录错误，那么我们可能只会发送我们需要的部分数据，否则当出现故障时，我们可能会遇到故障。数据流尚未完全完成。 在大多数情况下，部分数据或不准确数据比没有数据更糟糕。 与其否决这个想法，不如考虑环境对于按发布者扩展分发数据是否有效，例如每个发布者使用一个分发数据库。 例如：

distribution_publisherone
distribution_publishertwo
distribution_publisherthree

distribution_publisherone
distribution_publishertwo
distribution_publisherthree

In the above example, we have a different distribution database per publisher. While this can work in some environments, if we add a large load to any particular publisher – such as publishing huge amounts of data – we could still find ourselves in a similar bottleneck. In a similar manner, we may find adding an index, or updating statistics on some of the tables in the distribution database useful, while in other cases it may do more harm than good. Generally, it is wise to use a distribution server independent of the publisher and subscriber, but for some environments, this may be too much in costs.

在上面的示例中，每个发布者都有一个不同的分发数据库。 尽管这可以在某些环境下工作，但是如果我们向任何特定的发布者添加大量负载（例如发布大量数据），我们仍然可能会遇到类似的瓶颈。 以类似的方式，我们可能会发现添加索引或更新分发数据库中某些表的统计信息很有用，而在其他情况下，这样做弊大于利。 通常，明智的做法是使用独立于发布者和订阅者的分发服务器，但是对于某些环境，这可能会增加成本。

I wish I could write that, “Oh the best practices for your distribution database are …” but that is untrue because it depends on your OLTP load – for an example, is it even throughout the day, or does it have a peak? What are your systems limitations? And finally, tell me about your weakest point as that will really show itself in replication?

我希望我可以这样写：“哦，您的分发数据库的最佳做法是……”，但这是不正确的，因为它取决于您的OLTP负载–例如，是否整天都出现，或者是否达到高峰？ 您的系统限制是什么？ 最后，请告诉我您最薄弱的一面，因为这将真正体现在复制中吗？

一些有用的问题要考虑 ( Some useful questions to consider )

One of the biggest keys to success is asking the right questions because it frames the technical approach from the beginning so that we isolate the right approach. Some excellent questions to ask about transactional or snapshot replication for environments are:

成功的最大关键之一就是提出正确的问题，因为它从一开始就构筑了技术方法，因此我们隔离了正确的方法。 有关环境的事务或快照复制的一些很好的问题是：

• Why are we using snapshot and transactional replication?
  为什么我们要使用快照和事务复制？
• What are the costs of scaling snapshot and transactional replication to handle the data load?
  扩展快照和事务复制以处理数据负载的成本是多少？
• What are the alternatives to snapshot and transactional replication?
  快照和事务复制有哪些替代方案？
• How often can our data be offline when issues with snapshot and transactional replication arise?
  当快照和事务复制出现问题时，我们的数据有多少次脱机？
• What other pieces (servers, databases, maintenance windows, etc) in our environment might be affected by our choice to use snapshot and transactional replication?
  我们选择使用快照和事务复制可能会影响环境中的其他部分（服务器，数据库，维护窗口等）？

None of these questions eliminate replication as a possible solution, but they may indicate that for our environment it’s not the most appropriate solution. Consider that I may use a completely different approach to storing Hong Kong stocks from the Hong Kong Stock Exchange than I use to store bitcoin stocks and peer-to-peer loans; the former has downtime (like holidays) whereas the latter is never offline, except for the individual sites. How will I have the time to install service packs or perform maintenance on a system that never goes down because data must constantly be replicated? This is a simple example of where considering what I need to accomplish will limit the tools that I use.

这些问题都没有消除复制作为可能的解决方案，但是它们可能表明对于我们的环境而言，这不是最合适的解决方案。 考虑到我可能会使用与存储比特币股票和点对点贷款完全不同的方法来存储来自香港联合交易所的香港股票； 前者有停机时间（如假期），而后者则永远不会离线，除了各个站点。 我如何有时间在永远不会停机的系统上安装Service Pack或执行维护，因为必须不断复制数据？ 这是一个简单的示例，说明在什么情况下考虑需要完成的工作会限制我使用的工具。

最后的想法 ( Final Thoughts )

Replication offers a useful high availability tool and in some situation offers the best tool for a business solution. DBAs and developers should still consider the limitations of replication, along with scaling it in an appropriate manner to their environment from the beginning.

复制提供有用的高可用性工具，并且在某些情况下提供用于业务解决方案的最佳工具。 DBA和开发人员仍应考虑复制的局限性，并从一开始就以适当的方式对其复制进行扩展。

翻译自: https://www.sqlshack.com/understanding-the-distribution-scale-of-transactional-and-snapshot-replication/

oracle 事务快照