Vertica并发DML操作性能瓶颈的产生与优化（转）

文章来源：中国联通网研院网优网管部IT技术研究团队

作者：陆昕

1. 引言

众所周知，MPP数据库以其分布式的超大存储能力以及列式的高速汇总能力，已经成为大数据分析比不可少的工具。Vertica就是这个领域的佼佼者，其最新版本已经发布到7.2版，甚至在Twitter、Facebook等尊崇开源平台的互联网公司也有大规模的集群部署。

然而万物有其利必有其弊，MPP数据库高速的汇总计算能力是建立在其列式存储、主动压缩等一系列技术基础上的。物理上的存储方式导致了这类数据库的UPDATE、DELETE等DML语句操作极其低效，有的MPP数据库甚至取消了这两类语句。Vertica虽然保留了这两句DML语句，但是其执行效率，尤其实在高并发场景下的执行效率如何，并不为常人所熟知。本文对这一疑问进行了探索，希望对Vertica系统的开发人员有所启发。

2.  Vertica并发DML测试

测试环境

• 5节点Vertica集群

• 单节点12核CPU，128G内存，10T硬盘

• 最大并发数：10个，排队超时门限：5分钟

测试方法

• 测试程序分别同时开启5个并发与10个并发

• 将10000条数据**（INSERT）同一张表、进行单个字段执行10000条更新（UPDATE）、最后删除（DELETE）10000条数据

• 对上述三项DML操作做无限循环，记录每条操作消耗的时间，直至系统性能显著恶化 

3. Vertica并发DML瓶颈分析

分析以上测试结果可以发现，在执行大规模并发DML类任务时，Vertica会遇到两类瓶颈，一类由高并发引发排队导致，另一类则由于DML语句锁表导致。

1） 高并发排队导致的第一类性能瓶颈

Vertica的最大执行并发数为每个节点CPU核数，在测试环境中，每台节点拥有12个CPU，其中系统通常需要占用其中的1-2个核，因此该系统配置了10个最大并发数，对于超过并发数的任务，系统会自动排队，排队超时门限5分钟。理解了以上这一点，就不难解释图1中10个并发UPDATE语句的耗时为什么远远超过5个并发任务的耗时了。

图1 UPDATE性能对比

• 并发任务数为5个时，此时并发数小于系统最大并发设置（10个），系统整体并发性能良好，基本没有排队，执行DML语句的耗时缓慢抬升，但是整体平稳，耗时在1-2s上下，此时性能只受到第二类瓶颈限制（下文详述）。

• 并发任务数为10个时，并发数已经达到系统最大并发数，系统整体并发性能明显下降。从图2中可以看出执行DML语句的耗时显著高于5个并发的情况，并且呈10个一组的周期性增长分布。在每个周期内，并发DML语句的耗时呈线性增长，增长速率极高（从2s飙涨至20s），此时高并发排队引发严重的性能瓶颈：前一句语句占用着CPU资源，后来的语句只能排队等待资源释放，这就造成了执行时间不断增长，直到每一个循环结束释放所有资源。与下面要解释的并发相比，高并发请求造成的性能恶化要远远高于锁表造成的性能瓶颈。可以预见，当并发数大于10个时，DML语句执行效率会更加恶化，因此控制DML并发数是系统要解决的首要问题。

图2 UPDATE性能（10并发）

2）DML语句锁表导致的第二类性能瓶颈

在5并发和10并发测试中，都存在由此类锁表造成的性能问题。由图1可见，5并发DML语句的执行耗时远远低于10并发，几乎完全不受并发瓶颈影响。为排除第一类瓶颈造成的干扰，下面主要讨论5个并发时的情况。

图3 DML性能（5并发情况）

从上面三张图可以看到，INSERT语句耗时极少，几乎自始至终都保持在200ms上下，而UPDATE和DELETE语句的执行时间要长出不少，在1000ms以上。这是所有MPP数据库的相同的特性：对数据做增量操作很容易（HDFS同理），相比之下DELETE需要从列式物理存储文件中定位到特定行，再标记为无效（即删除），耗时较长，而对于UPDATE语句，许多MPP数据库甚至不支持（例如基于HDFS的Impala），Vertica支持UPDATE，本质上却是DELETE+INSERT的组合。因此DELETE与UPDATE具有相似特征也就不奇怪了。

回到第二类性能瓶颈上，上图中DELETE和UPDATE语句随着时间的增长，执行耗时会持续抬升，执行速度逐渐下降（当然抬升速率远远不及第一类性能瓶颈）。这主要是由于在执行DML语句时，Vertica对全表加锁以保证操作的原子性：加锁后的表必须等待本次DML操作完成后才会释放，从而进行下一次DML操作，其中DELETE和UPDATE用到的排它锁（X锁）比INSERT用到的数据**锁（I锁）更严格，这也是为什么前二者对系统性能的影响要远远大于后者。

此处尤其需要注意的时，在经过一段时间连续执行DML语句后（约10分钟），原本缓慢上升的执行时间曲线突然开始剧烈波动并一直持续至测试结束，意味着整体并发性能严重恶化。这种恶化在多次测试中是可重现的，很有可能是Vertica的锁表机制的缺陷或者是底层硬件如硬盘读写和网络IO出现较大波动造成的。好在大多Vertica的应用不会像这类测试一样丧心病狂地对表数据进行并发操作。

从上面的测试可知，基于Vertica的应用应尽量避免频繁数据增、删、改操作。毕竟作为列式MPP数据库，对海量数据进行快速汇总和分析运算才是其强项，频繁的DML操作还是交给Oracle等传统交易型数据库来做吧。对于那些必须使用Vertica进行DML操作的应用场景，下面探讨一些靠谱的（以及不靠谱的）方案供参考。

4.  Vertica系统DML并发优化方案

1） 限制并发量

上面以及提及，由并发任务排队导致的性能瓶颈是在系统设计阶段一定要想办法避免的。这类优化的关键点就是将应用的并发需求与物理硬件的并发能力匹配起来。具体可以从以下几个方面考虑：

• 采用CPU物理核核数较多的服务器搭建Vertica集群；

• 在应用端通过合理设置任务请求数，控制数据库连接数，以限制同时执行的任务数小于Vertica设置的最大并发数；

• 对不同类型应用分配彼此独立的资源池（Resource Pool），例如对频繁的DML操作单独设置专用高优先级资源池，而对于数据装载这类应用分配优先级较低的资源池，这样在高并发情况下，系统会优先将内存、CPU等资源分配给容易形成瓶颈的DML语句。在进行资源池分配的时候，需要重点关注优先级（PRIORITY）、最大并发数（MAXCONCURRENCY）以及排队超时时长（QUEUETIMEOUT）这几个参数。由于MPP数据库对大多数语句的执行时间都在秒级，对少数超长的操作单独设置资源池，只要避免如测试时的极端连续点击情况，大多数任务可以避免排队，而在合理的时间内执行完成，而对于超长等待只需设置排队时间，超时拒绝即可。

2）改变业务逻辑，只进行增量操作

由上面的分析可知，在Vertica中存在着由UPDATE和DELETE语句锁表造成的性能瓶颈，而INSERT语句的执行则十分平稳，并不会对系统性能造成太大波动。因此我们可以通过改变业务逻辑，只对表进行增量操作而避免使用UPDATE和DELETE语句，来提升系统性能。对于那些原本需要更新或者删除的记录，我们只需要在数据表中增加一个字段标记每条记录有效性，每次需要进行更新操作时，直接**新的数据，并根据时间戳将最新的这条记录标记为有效，旧有记录标记无效。这样在对字段进行汇总分析时通过有效性标识筛选最新的记录，就相当于在操作一张更新后的数据表。最后，在系统中增加定期清理过时数据的策略即可。

3) 不靠谱策略之一：Purge

上述两条是经过验证，实测可用的优化策略。接下来要分享几条理论上似乎可行，实际测试效果不佳的优化策略，目的是进一步理解Vertica运行机制，让后来人少走弯路。不靠谱策略之一就是在每次执行完UPDATE/DELETE语句后，增加一条Purge命令，以手动清空数据表中的冗余数据。

我们已经知道，Vertica的UPDATE/DELETE机制本质上就是将无效数据打上删除标识，并在闲时由系统自动将标记删除数据进行物理删除操作。当对某一张数据表更新、删除操作十分频繁时，相关的存储文件也会十分庞大，导致后续DML操作定位数据要耗费较长时间，从而影响执行速度。加上Purge本身耗时非常短（通常在2、3ms），似乎在每次更新数据后手动Purge是一个不错的方法。但是对比下图与图3中DELETE操作可以发现，在应用了Purge之后，原本初期还算平稳的DELETE语句也变得不稳定起来，执行性能无法得到保障。Update也有类似的表现。

图4 应用Purge后性能反而恶化

4） 不靠谱策略之二：DBD

众所周知，Vertica优化有三宝：统计、排序与压缩，而Vertica提供了自动化的数据库优化工具DBD（Database Designer）能够根据具体应用的SQL自动化地生成优化策略。那么理所当然地，我们认为DBD也能对DML语句进行优化。是否能如愿呢？我们先来回顾一下DBD是如何优化依据普通的查询分析语句的。

以一个简单的关联查询为例：

SELECT

a.lac,a.ci,a.duration,a.imsi,b.latitude,b.longitude

FROM gn_gprs_data.Gn_GPRS_SH_201411 a

INNER JOIN wrnophq.NE_CELL_W b

ON a.lac=b.lac AND a.ci=b.ci;

DBD首先通过ANALYZE_STATISTICS函数收集这两张数据表的统计信息，以便让系统更好地理解这两张表存放的数据分布。在进过DBD优化后，生成如下优化脚本，其中可见Vertica新建了一个存放数据的projection，根据字段特点对SQL查询用到的字段应用了不同的压缩算法，并对关联字段lac、ci进行了排序优化，这两项优化策略都有助于减少关联操作中快速索引。

图5 DBD对JOIN进行的优化

图6对比了优化前后的执行计划，可以发现：

• 资源消耗减少了

• 统计信息补全了

• 减少了网络间数据传输（BROADTEST标识消失了）

• 两表Join算法变成了更具效率的MergeJoin

图6 DBD优化前后SQL语句的执行计划对比

然而，遗憾的是DBD对Vertica的优化，更多地集中在如何提升数据分析的性能上，而对DML操作的优化几乎没有。在将UPDATE和DELETE语句输入DBD后，直接提示该类语句不是可以被优化的类型而拒绝生成新的projection，在执行时也就没有任何优选路径可以选择。进一步的测试可以发现，DBD “优化”后UPDATE语句的执行计划并没有改变，而时延测试结果也印证了最终结论，即DBD并不能优化DML语句，对系统并发性能也没有任何影响。

图7 UPDATE性能并未得到DBD优化

5. 总结

本文探讨了Vertica系统的DML并发性能瓶颈的产生现象、成因以及优化方法。虽然文中使用的是基于Verica的案例，但是我相信各家MPP数据库的实现原理都是相近的，其产生类似的瓶颈问题是可以预料的，当然上面提到的几项优化方案也是完全可以借鉴的。而对于哪些同时包含大量更新操作和大数据分析的应用来说，搭建交易型数据库（如Oracle）和分析型数据库（如Vertica）的混合架构是一个更理想的选择。

比起上述研究得到的一些具体结论，我更想与大家分享的是一些性能优化方面的感悟：性能瓶颈的产生通常具有高度相似的现象，但是仔细分析后就会发现，其背后的产生机理不尽相同，并且往往错综复杂；而要真正地优化一个系统的性能，不仅要从硬件架构、软件配置、算法优化等各个方面进行综合考虑，还要有一个严谨和实事求是的态度，在理解技术原理的基础上，对优化方案进行全面的探索与测试，在这个过程中，你会发现许多看似合理的方案其实是无效的甚至最终结果是与优化目标完全向左的。