MySQL集群性能优化指南

MySQL集群是一款实时、可扩展的事务性数据库。其设计初衷在于为对实时性能要求较高的应用程序打造一套嵌入式电信数据库，并实现运营商级别的可用性。不过MySQL集群也可以通过扩展实现诸多企业级解决方案，例如移动支付系统、实时分析、数据流与分析以及内容管理等事务。MySQL集群的横向扩展能力足以满足密集型工作负载的需求，其集群架构如下图所示： 整信集群由三类节点构成：数据节点、应用程序节点以及管理节点。

• 数据节点通常负责数据访问与存储事务。
• 应用程序节点提供由应用程序逻辑层及应用API指向数据节点的链接。
• 管理节点在集群配置体系中的作用至关重要，并在网络分区环境下负责负载指派。

在本文中，我们将共同探讨提升MySQL集群性能的最佳实践方案。通过这些方案，大家将能在各类工作负载中实现游刃有余的资源分配效果。 最适合MySQL集群的应用程序 MySQL集群能确保来自应用程序或者SQL节点的更新立即被用于集群体系中的每一个节点。表格被划分至一系列商用节点当中，从而实现数据库的横向可扩展性。 MYSQL集群在数据库层处理表格划分，这就避免了对应用程序层的影响。这一特性能够大大简化应用程序的开发与维护工作。在事务性工作负载方面，MySQL集群还能提供非常强大的数据吞吐能力。另外，这套集群可以利用多个并行SQL节点，且每个节点都提供多个连接。 最适合MySQL集群的应用程序包括：

• 交易平台/系统；
• 支付网关；
• 用户配置管理；
• 多人网络游戏；
• IMS服务；
• DHCP宽带接入；
• VoIP及视频会议。

识别性能问题 我们一直建议大家对延迟及事务数据吞吐量等性能指标进行重复检测方针。另一大关注重点在于考量数据库的特定变更及其可用性。大家应该在变更实际部署前进行性能测试，并在优化技术安置完毕后再次进行测试。 在某些情况下，我们可能无意中遇上必须满足的特殊需求。通过迭代流程，大家可以追踪目标的当前进展。除了检测应用程序的整体性能，大家还应该考虑个别事务的性能表现。某些查询请求可能需要很长时间才能执行完毕。 如果大家利用SQL对接入MySQL Enterprise Monitor的数据库进行查询，那么不妨试试Query Analyzer--它能追踪所有负载较高的事务。如果大家没有接入Enterprise Monitor，那么MySQL慢速查询日志也足以帮助我们找出处理过程过长的事务。我们可以通过对long_query_time变量的设定选择日志中到底应该保留哪些慢速查询（即慢到何种程度）。变量数值的单位是"秒"，将该数值设为"0"则代表在日志中记录下所有查询请求。 由于对long_query_time变量所做出的变更不会马上体现在活动连接当中，因此大家需要删除当前连接然后重新建立。 MySQL集群在数据节点内保留着全部细节信息。这部分数据可由名为NDBINFO的虚拟数据库直接使用。举例来说，NDBINFO能够显示磁盘页面缓冲区的使用信息。所谓磁盘页面缓冲区，其实是各个数据节点根据表格进行磁盘操作时的缓存。通常情况下，其缓冲命中率越高、则性能表现越好。调整这部分缓存的大小会对系统产生显著影响。大家可以通过mysql命令行访问磁盘页面缓冲数据。 性能优化 访问模式 要想让MySQL集群部署发挥出与预期相符的性能，最重要的一点在于了解数据库结构。有一点需要注意--MySQL集群表格中的数据并不会被保存在MySQL服务器当中。这些数据实际上被划分至由多个数据节点构成的资源池当中，如下图所示。 表格中的各行将被拆分成多个区块，每个数据节点保留一个区块的主片段及另一个区块的次片段。 如果查询需要多次网络跳转，例如由服务器向数据节点或者在不同数据节点之间，那么性能与可扩展性都可能受到影响。因此，要想让MySQL集群获得最佳性能表现，我们必须尽量减少网络跳转次数。表格划分基于主键散列，但我们可以对其进行重写以提高性能。简单的访问模式是创建可扩展且高性能解决方案的关键。 网络跳转的必要次数取决于来自各分支结构的结果集与接合深度。如果表格的接合深度过大，那么指向数据节点的每一次跳转也将耗费更多时间。要想获得最佳性能，我们需要利用主键查找--其完成时间是恒定的，与数据库规模及数据节点数目没有关系。 使用AQL 通过使用AQL（即适应性查询本地化），性能改善将体现得更为明显。 AQL能够将查询由MySQL服务器指派向全部数据节点，且查询由本地数据副本负责执行。然后它会向MySQL服务器发回合并结果集，从而最终实现减少网络跳转、提高性能表现的目的。 AQ能帮助MySQL集群高效处理涉及大量复杂查询事务的用例。 为了获得更理想的使用效果，大家可以在变更表格模式（包括添加、删除索引或者执行其它重要变更）后在某一台MySQL服务器中运行OPTIMIZE TABLE <tab-name>。 在实际使用环境中的测试结果显示，AQL能够将大量查询事务的整体效率提升70倍。在测试中，一套网络内容存储管理系统中包含有十一套复杂表格，在对其进行正常查询时，整个流程需要耗时87秒。但在AQL的帮助下，整个流程的时耗被直接缩短至1.26秒。 在默认情况下，AQL受到全局变量ndb_join_pushdown的控制。为了能够让AQL切实起效，我们需要对其进行如下规则修改：

• 要加入的列必须使用同一种数据类型，包括VARCHAR长度列在内。
• 无法对指向BLOB或TEXT列的连接起效。
• 不支持显式封锁，但我们可以通过基于NDB存储引擎锁定的隐式行实现同等效果。
• 无法对明显由HASH或者RANGE进行划分的连接起效。

分布感知应用 在利用MySQL集群向表格中添加行时，每一行都归属于一个分区。每个分区又都由集群中的一个特定数据节点所掌控。只有事务需要的全部数据都被划分到同一个分区当中时，集群性能才能实现最优。这意味着我们要利用一个单独数据节点来取消在多个节点之间往复传输所带来的延迟提升。 MySQL集群默认通过主键散列对数据进行划分。我们可以指定主键中的特定字段并将其提交给散列算法，从而改变默认方案。 大家也可以利用分区裁剪方案。分区裁剪流程是指通过单一数据节点进行索引扫描，其延迟改善效果取决于数据节点的数量及结果集的大小。数据节点越多、需要取回的记录越少，改进的效果也就越好。 下图显示了分区裁剪对性能的提升情况。 分区裁剪能够有效降低小型结果集的延迟状况，但对较大的结果集而言反倒会提高延迟表现。上图中的红色条形代表分区裁剪后的延迟数字。 在这种情况下，应用程序分布机制对于新增额外节点的识别能力就成了保证性能改善的关键。 使用连接池 MySQL集群在多个数据节点进行大量并行操作时的吞吐能力最强。有鉴于此，我们必须同时利用多台MySQL服务器且应用程序的各个线程全部接入这些服务器。要访问数据节点，mysqld进程会默认使用单独的NDB API连接。由于大量应用程序线程同时抢占这一条连接，我们需要设置一套互斥机制以避免吞吐操作发生冲突。 一种方案是让每个应用线程都使用其专用的mysqld进程，但这不仅浪费资源、同时也会增加应用程序的复杂性。 更为高效的方案是如上图所示，建立由mysqld进程向数据节点的多条NDB API连接。 为了使用连接池，每条（来自mysqld）连接都需要在config.ini文件中拥有自己的[mysqld]或者[api]分节号，然后在my.cnf文件中将ndbcluster-connection-pool值设置为大于1。另外，我们也绝不能在启动mysqld进程的同时将ndb-node-ida作为命令行选项。在我们的测试实例中，单一mysqld进程拥有四个NDB API连接。 上图显示了连接池所带来的性能提升。一般来说，应用程序的性能普遍能获得70%的提升，但在某些特殊情况下、其提升幅度可能超过150%。 添加节点以实现扩展 MySQL集群在设计思路中纳入了横向扩展考量。能够添加额外的MySQL服务器或者数据节点，大家可以显著提升整体性能。 我们还能够向处于运行状态的MySQL集群中添加新的MySQL服务器或者节点组。也就是说，我们完全不必关机并重新加载集群。现有表格中的数据将被重新划分到所有接入数据节点当中。最安全的处理方式是使用MySQL Cluster Manager。这款集群管理器能自动处理关于额外节点添加的所有事务，而且我们不必担心整个过程会造成任何服务丢失。 总结 在过去九年中（自2004年起），MySQL集群已经逐步成为一款成熟的解决方案，能够满足极端性能水平要求以及高达99.99%的严格正常运行标准。正如在文章开头所说，自适应查询本地化、参数调整以及分布感知等特性令MySQL集群在性能方面更上一层楼，从而有能力服务于更广泛的应用组合方案。作为数据库技术的核心要素，实时监控与报告功能的强化也帮助数据库开发人员及管理员们得以更好地在MySQl集群中扩展自己的用例。 原文链接： http://www.agileload.com/agileload/blog/2013/03/11/optimizing-the-performance-of-mysql-cluster