Greenplum 数据库最佳实践

❖介绍本文介绍PivotalGreenplumDatabase数据库（以下简称：Greenplum数据库，或GPDB）的最佳实践。最佳实践是指能持续产生比其他方法更好结果的方法或者技术，它来自于实战经验，并被证实了遵循这些方法可以获得可靠的预期结果。本最佳实践旨在通过利用所有可能的知识和技术为正确使用GPDB提供有效参考。本文不是在教您如何使用Greenplum数据库的功能，而是帮助您在设计、实现和使用Greenplum数据库时了解需要遵循哪些最佳实践。关于如何使用和实现具体的Greenplum数据库特性，请参考上的Sandbox和实践指南。本文目的不是要涵盖整个产品或者产品特性，而是概述GPDB实践中最重要的因素。本文不涉及依赖于GPDB具体特性的边缘用例，后者需要精通数据库特性和您的环境，包括SQL访问、查询执行、并发、负载和其他因素。通过掌握这些最佳实践知识，会增加GPDB集群在维护、支持、性能和可扩展性等方面的成功率。第一章最佳实践概述本部分概述了Greenplum数据库最佳实践所涉及的概念与要点。数据模型GPDB是一个基于大规模并行处理(MPP)和无共享架构的分析型数据库。这种数据库的数据模式与高度规范化的事务性SMP数据库显著不同。通过使用非规范化数据库模式，例如具有大事实表和小维度表的星型或者雪花模式，GPDB在处理MPP分析型业务时表现优异。跨表关联(JOIN)时字段使用相同的数据类型。详见数据库模式设计（后续章节)堆存储和追加优化存储(Append-Optimized，下称AO)若表和分区表需要进行迭代式的批处理或者频繁执行单个UPDATE、DELETE或INSERT操作，使用堆存储。若表和分区表需要并发执行UPDATE、DELETE或INSERT操作，使用堆存储。若表和分区表在数据初始加载后更新不频繁，且仅以批处理方式插入数据，则使用AO存储。不要对AO表执行单个INSERT、UPDATE或DELETE操作。不要对AO表执行并发批量UPDATE或DELETE操作，但可以并发执行批量INSERT操作。详见堆存储和AO存储（后续章节)行存储和列存储若数据需要经常更新或者插入，则使用行存储。若需要同时访问一个表的很多字段，则使用行存储。对于通用或者混合型业务，建议使用行存储。若查询访问的字段数目较少，或者仅在少量字段上进行聚合操作，则使用列存储。若仅常常修改表的某一字段而不修改其他字段，则使用列存储。详见行存储和列存储（后续章节)压缩对于大AO表和分区表使用压缩，以提高系统I/O。在字段级别配置压缩。考虑压缩比和压缩性能之间的平衡。详见压缩（后续章节)分布为所有表定义分布策略：要么定义分布键，要么使用随机分布。不要使用缺省分布方式。优先选择可均匀分布数据的单个字段做分布键。不要选择经常用于WHERE子句的字段做分布键。不要使用日期或时间字段做分布键。分布键和分区键不要使用同一字段。对经常执行JOIN操作的大表，优先考虑使用关联字段做分布键，尽量做到本地关联，以提高性能。数据初始加载后或者每次增量加载后，检查数据分布是否均匀。尽可能避免数据倾斜。详见分布（后续章节)内存管理设置vm.overcommit_memory为2不要为操作系统的页设置过大的值使用gp_vmem_protect_limit设置单个节点数据库（SegmentDatabase）可以为所有查询分配的最大内存量。不要设置过高的gp_vmem_protect_limit值，也不要大于系统的物理内存。gp_vmem_protect_limit的建议值计算公式为：(SWAP+(RAM*vm.overcommit_ratio))*0.9/number_Segments_per_server使用statement_mem控制节点数据库为单个查询分配的内存量。使用资源队列设置队列允许的当前最大查询数（ACTIVE_STATEMENTS）和允许使用的内存大小（MEMORY_LIMIT）。不要使用默认的资源队列，为所有用户都分配资源队列。根据负载和时间段，设置和队列实际需求相匹配的优先级（PRIORITY）。保证资源队列的内存配额不超过gp_vmem_protect_limit。动态更新资源队列配置以适应日常工作需要。详见内存和负载管理（后续章节)分区只为大表设置分区，不要为小表设置分区。仅在根据查询条件可以实现分区裁剪时使用分区表。建议优先使用范围(Range)分区，否则使用列表(List)分区。根据查询特点合理设置分区。不要使用相同的字段即做分区键又做分布键。不要使用默认分区。避免使用多级分区；尽量创建少量的分区，每个分区的数据更多些。通过查询计划的EXPLAIN结果来验证查询对分区表执行的是选择性扫描（分区裁剪）。对于列存储的表，不要创建过多的分区，否则会造成物理文件过多：Physicalfiles=Segments*Columns*Partitions。详见分区（后续章节)索引一般来说GPDB中索引不是必需的。对于高基数的列存储表，如果需要遍历且查询选择性较高，则创建单列索引。频繁更新的列不要建立索引。在加载大量数据之前删除索引，加载结束后再重新创建索引。优先使用B树索引。不要为需要频繁更新的字段创建位图索引。不要为唯一性字段，基数非常高或者非常低的字段创建位图索引。不要为事务性负载创建位图索引。一般来说不要索引分区表。如果需要建立索引，则选择与分区键不同的字段。详见索引（后续章节)资源队列使用资源队列管理集群的负载。为所有角色定义适当的资源队列。使用ACTIVE_STATEMENTS参数限制队列成员可以并发运行的查询总数。使用MEMORY_LIMIT参数限制队列中查询可以使用的内存总量。不要设置所有队列为MEDIUM，这样起不到管理负载的作用。根据负载和时间段动态调整资源队列。详见配置资源队列（后续章节)监控和维护根据《Greenplum数据库管理员指南》实现该书推荐的监控和管理任务。安装Greenplum数据库前建议运行gpcheckperf，安装后定期运行。保存输出结果，随着时间推移对系统性能进行比较。使用所有您可用的工具，以了解系统不同负载下的表现。检查任何不寻常的事件并确定原因。通过定期运行解释计划监控系统查询活动，以确保查询处于最佳运行状态。检查查询计划，以确定是否按预期使用了索引和进行了分区裁剪。了解系统日志文件的位置和内容，定期监控日志文件，而不是在出现问题时才查看。详见系统监控和维护以及监控GPDB日志文件。（后续章节)ANALYZE不要对整个数据库运行ANALYZE，只对需要的表运行该命令。建议数据加载后即刻运行ANALYZE。如果INSERT、UPDATE和DELETE等操作修改大量数据，建议运行ANALYZE。执行CREATEINDEX操作后建议运行ANALYZE。如果对大表ANALYZE耗时很久，则只对JOIN字段、WHERE、SORT、GROUPBY或HAVING字句的字段运行ANALYZE。详见使用ANALYZE更新统计信息。（后续章节)Vaccum批量UPDATE和DELETE操作后建议执行VACUUM。不建议使用VACUUMFULL。建议使用CTAS（CREATETABLE...AS）操作，然后重命名表名，并删除原来的表。对系统表定期运行VACUUM，以避免系统表臃肿和在系统表上执行VACUUMFULL操作。禁止杀死系统表的VACUUM任务。不建议使用VACUUMANALYZE。详见消除系统表臃肿。（后续章节)加载使用gpfdist进行数据的加载和导出。随着段数据库个数的增加，并行性增加。尽量将数据均匀地分布到多个ETL节点上。将非常大的数据文件切分成相同大小的块，并放在尽量多的文件系统上。一个文件系统运行两个gpfdist实例。在尽可能多的网络接口上运行gpfdsit。使用gp_external_max_segs控制访问每个gpfdist服务器的段数据库的个数。建议gp_external_max_segs的值和gpfdist进程个数为偶数。数据加载前删除索引；加载完后重建索引。数据加载完成后运行ANALYZE操作。数据加载过程中，设置gp_autostats_mode为NONE，取消统计信息的自动收集。若数据加载失败，使用VACUUM回收空间。详见加载数据。（后续章节)gptransfer为了更好的性能，建议使用gptransfer迁移数据到相同大小或者更大的集群。避免使用--full或者--schema-only选项。建议使用其他方法拷贝数据库模式到目标数据库，然后迁移数据。迁移数据前删除索引，迁移完成后重建索引。使用SQLCOPY命令迁移小表到目标数据库。使用gptransfer批量迁移大表。在正式迁移生产环境前测试运行gptransfer。试验--batch-size和--sub-batch-size选项以获得最大平行度。如果需要，迭代运行多次gptransfer来确定每次要迁移的表的批次。仅使用完全限定的表名。表名字中若含有点、空格、单引号和双引号，可能会导致问题。如果使用--validation选项在迁移后验证数据，则需要同时使用-x选项，以在源表上加排它锁。确保在目标数据库上创建了相应的角色、函数和资源队列。gptransfer-t不会迁移这些对象。从源数据库拷贝postgres.conf和pg_hba.conf到目标数据库集群。使用gppkg在目标数据库上安装需要的扩展。详见使用gptransfer迁移数据（后续章节)安全妥善保护gpadmin账号，只有在必要的时候才能允许系统管理员访问它。仅当执行系统维护任务（例如升级或扩容），管理员才能以gpadmin登录Greenplum集群。限制具有SUPERUSER角色属性的用户数。GPDB中，身为超级用户的角色会跳过所有访问权限检查和资源队列限制。仅有系统管理员具有数据库超级用户权限。参考《Greenplum数据库管理员指南》中的“修改角色属性”。严禁数据库用户以gpadmin身份登录，严禁以gpadmin身份执行ETL或者生产任务。为有登录需求的每个用户都分配一个不同的角色。考虑为每个应用或者网络服务分配一个不同的角色。使用用户组管理访问权限。保护好ROOT的密码。对于操作系统密码，强制使用强密码策略。确保保护好操作系统的重要文件。详见安全。（后续章节)加密加密和解密数据会影响性能，仅加密需要加密的数据。在生产系统中实现任何加密解决方案之前都要做性能测试。GPDB生产系统使用的服务器证书应由证书签名颁发机构（CA）签名，这样客户端可以验证服务器。如果所有客户端都是本地的，则可以使用本地CA。如果客户端与GPDB的连接会经过不安全的链路，则使用SSL加密。加密和解密使用相同密钥的对称加密方式比非对称加密具有更好的性能，如果密钥可以安全共享，则建议使用对称加密方式。使用pgcrypto包中的函数加密磁盘上的数据。数据的加密和解密都由数据库进程完成，为了避免传输明文数据，需要使用SSL加密客户端和数据库间的连接。数据加载和导出时，使用gpfdists协议保护ETL数据安全。详见加密数据和数据库连接。（后续章节)高可用使用8到24个磁盘的硬件RAID存储解决方案。使用RAID1、5或6，以使磁盘阵列可以容忍磁盘故障。为磁盘阵列配备热备磁盘，以便在检测到磁盘故障时自动开始重建。在重建时通过RAID卷镜像防止整个磁盘阵列故障和性能下降。定期监控磁盘利用率，并在需要时增加额外的空间。定期监控段数据库倾斜，以确保在所有段数据库上数据均匀分布，存储空间均匀消耗。配置备用主服务器，当主服务器发生故障时由备用主服务器接管。规划好当主服务器发生故障时如何切换客户端连接到新的主服务器实例，例如通过更新DNS中主服务器的地址。建立监控系统，当主服务器发生故障时，可以通过系统监控应用或电子邮件发送通知。分配主段数据库和其镜像到不同的主机上，以