Greenplum 数据库最佳实践

1、介绍本文介绍了Pivotal Greenplum数据库(以下简称Greenplum数据库，简称GPDB)的最佳实践。最佳实践是指能够持续产生比其他方法更好的结果的方法或技术。它来自实战经验，并且已经证明，遵循这些方法可以获得可靠的预期效果。本最佳实践旨在通过利用所有可能的知识和技术，为正确使用GPDB提供有效的参考。本文不是教您如何使用Greenplum数据库的功能，而是帮助您理解在设计、实现和使用Greenplum数据库时应该遵循的最佳实践。关于如何使用和实现特定的Greenplum数据库功能，请参阅Greenplum数据库帮助文档(位于http:/pdb . docs . pivotal

2、. io和Sandbox上)和的实践指南。本文的目的不是涵盖整个产品或产品特性，而是概述GPDB实践中最重要的因素。本文不涵盖依赖于GPDB特定特性的边缘用例，GPDB需要精通数据库特性和您的环境，包括SQL访问、查询执行、并发性、负载和其他因素。掌握这些最佳实践知识将提高GPDB集群在维护、支持、性能和可扩展性方面的成功率。第一章最佳实践概述本节概述了Greenplum数据库最佳实践中涉及的概念和要点。数据模型GPDB是一个基于大规模并行处理和无共享架构的分析数据库。该数据库的数据模式与高度标准化的事务性SMP数据库明显不同。通过使用非标准化的数据库模式

3、，如带有大事实表和小维度表的星型或雪花型模式，GPDB在处理MPP分析业务时表现良好。跨表连接时，字段使用相同的数据类型。有关详细信息，请参见数据库模式设计(后续章节)堆存储和附加优化存储如果表和分区表需要迭代批处理或频繁的单次更新、删除或插入操作，则使用堆存储。如果表和分区表需要同时执行更新、删除或插入操作，请使用堆存储。如果表和分区表在数据初始加载后没有频繁更新，并且数据仅在批处理模式下插入，则使用AO存储。不要对AO表执行一次插入、更新或删除操作。不要对AO表执行并发批处理更新或删除操作，而是执行并发批处理插入操作。有关详细信息，请参见堆存储和AO存储(后续章节)行存储和列存储如果需要频

4、繁更新或插入数据，则使用行存储。如果您需要同时访问一个表的多个字段，请使用行存储。对于一般或混合服务，建议使用行存储。如果查询访问的字段数量很少，或者只对少量字段执行聚合操作，则使用列存储。如果经常只修改表中的一个字段，而不修改其他字段，则使用列存储。详见行存储和列存储(后续章节)压缩对大型AO表和分区表使用压缩来提高系统输入/输出.在字段级别配置压缩。考虑压缩比和压缩性能之间的平衡。详见压缩(后续章节)分布为所有表定义分布策略：要么定义分布键，要么使用随机分布。不要使用默认分布。优先选择能够均匀分布数据的单个字段作为分布关键字。不要选择在WHERE子句中经常使用的字段作为分发密钥。不要使用日

5、期或时间字段作为分发密钥。分发密钥和分区密钥不要使用相同的字段。对于经常执行JOIN操作的大型表，应该优先使用关联字段作为分发密钥，并且应该尽可能多地进行本地关联以提高性能。检查数据是在初始加载后还是在每次增量加载后均匀分布。尽可能避免数据倾斜。详情见发行版(后续章节)内存管理将vm.overcommit_memory设置为2不要为操作系统页面设置太大的值使用gp_vmem_protect_limit设置单个节点数据库可以为所有查询分配的最大内存。不要将gp_vmem_protect_limit设置得太高，也不要超过系统的物理内存。gp_vmem_protect_limit的建议值计算公式为：

6、(交换(ram * VM . over-commit _ ratio)* 0.9/每台服务器的段数使用语句mem控制节点数据库为单个查询分配的内存量。使用资源队列设置当前最大查询数(ACTIVE _ STATEMENTS)和允许的内存大小(MEMORY_LIMIT)。不要使用默认的资源队列，为所有用户分配资源队列。根据负载和时间段，设置与队列实际需求相匹配的优先级。确保资源队列的内存配额不超过gp_vmem_protect_limit。动态更新资源队列配置以满足日常工作的需要。有关详细信息，请参见内存和负载管理(后续章节)分区只为大表设置分区，不为小表设置分区。只有根据查询条件可以实现分区裁剪

7、时，才使用分区表。建议优先使用范围分区，否则使用列表分区。根据查询特征合理设置分区。不要使用与分区密钥和分发密钥相同的字段。不要使用默认分区。避免使用多级分区；尝试创建尽可能少的分区，每个分区都有更多的数据。通过查询计划的解释结果，验证了查询对分区表执行选择性扫描(分区裁剪)。对于带列存储的表，不要创建太多的分区，否则会有太多的物理文件：物理文件=段*列*分区。详见分区(后续章节)指数一般来说，GPDB中的索引是不必要的。对于具有高基数的列存储表，如果需要遍历并且查询选择性高，则创建单列索引。不要索引经常更新的列。在加载大量数据之前删除索引，并在加载后重新创建索引。b树索引是首选。不要为需要频

8、繁更新的字段创建位图索引。不要为唯一字段、基数非常高或非常低的字段创建位图索引。不要为事务性工作负载创建位图索引。一般来说，不要索引分区表。如果需要索引，请选择不同于分区键的字段。详见索引(后续章节)资源排队使用资源队列管理集群负载。为所有角色定义适当的资源队列。使用ACTIVE_STATEMENTS参数来限制队列成员可以并发运行的查询总数。使用MEMORY_LIMIT参数来限制队列中的查询可以使用的内存总量。不要将所有队列都设置为中等，这不能管理负载。根据负载和时间段。有关详细信息，请参见配置资源队列(后续章节)监控和维护根据Greenplum数据库管理员指南执行本书中推荐的监控和管理任务。

9、建议在安装Greenplum数据库之前运行gpcheckperf，并在安装后定期运行它。保存输出结果并比较一段时间内的系统性能。使用所有可用工具了解系统在不同负载下的性能。检查任何异常事件并确定原因。通过定期运行解释计划来监控系统的查询活动，以确保查询处于最佳运行状态。检查查询计划以确定是否使用了索引，以及分区是否按预期进行了裁剪。了解系统日志文件的位置和内容，并定期监控日志文件，而不是在出现问题时查看它。有关详细信息，请参见系统监控和维护以及监控GPDB日志文件。(后续章节)分析不要对整个数据库运行ANALYZE，而只对所需的表运行。建议在加载数据后立即运行ANALYZE。如果插入、更新和删

10、除等操作修改了大量数据，建议运行分析。建议在创建索引操作后运行分析。如果分析一个大型表需要很长时间，那么只对具有连接字段、WHERE、SORT、GROUP BY或HAVING语句的字段运行ANALYZE。请参见使用ANALYZE更新统计信息。(后续章节)真空批量更新和删除操作后，建议使用真空。不建议使用真空充满。建议使用CTAS(创建表格.操作，然后重命名表名并删除原始表。定期对系统表进行真空操作，以避免系统表膨胀，并对系统表进行真空完全操作。禁止杀死系统表的真空任务。不建议使用真空分析仪。详见消除系统表超编。(后续章节)负荷使用gpfdist加载和导出数据。随着段数据库数量的增加，并行性也随

11、之增加。尝试将数据平均分布到多个ETL节点。将非常大的数据文件切割成相同大小的块，并将其放在尽可能多的文件系统中。一个文件系统运行两个gpfdist实例。在尽可能多的网络接口上运行gpfdsit。使用gp_external_max_segs控制访问每个gpfdist服务器的段数据库的数量。gp_external_max_segs的值和gpfdist进程的数量建议为偶数。数据加载前删除索引；加载后重建索引。加载数据后运行分析操作。在数据加载过程中，将gp_autostats_mode设置为NONE以取消统计信息的自动收集。如果数据加载失败，请使用真空回收空间。有关详细信息，请参见加载数据。(后续

12、章节)gptransfer为了获得更好的性能，建议使用gptransfer将数据迁移到相同大小或更大的集群。避免使用- full或- schema-only选项。建议使用其他方法将数据库模式复制到目标数据库，然后迁移数据。迁移数据前删除索引，迁移后重建索引。使用SQL COPY命令将小表迁移到目标数据库。使用gptransfer大规模迁移大型表。在正式迁移生产环境之前，测试并运行gptransfer。使用批量和子批量选项进行实验，以获得最大并行度。如有必要，反复运行几次gptransfer，以确定每次要迁移的表的批次。仅使用完全限定的表名。如果表名包含点、空格、单引号和双引号，可能会导致问题。

13、如果在迁移后使用- validation选项来验证数据，则需要同时使用-x选项来对源表进行独占锁定。确保在目标数据库上创建了适当的角色、功能和资源队列。Gptransfer -t不迁移这些对象。将postgres.conf和pg_hba.conf从源数据库复制到目标数据库群集。使用gppkg在目标数据库上安装所需的扩展。有关详细信息，请参见使用gptransfer迁移数据(后续章节)安全正确保护gpadmin帐户，仅在必要时允许系统管理员访问它。管理员只能在执行系统维护任务(如升级或容量扩展)时以gpadmin身份登录Greenplum群集。限制具有超级用户角色属性的用户数量。在GPDB中，超

14、级用户角色会跳过所有访问检查和资源队列限制。只有系统管理员拥有数据库超级用户权限。请参考Greenplum数据库管理员指南中的“修改角色属性”。严禁数据库用户以gpadmin身份登录并以gpadmin身份执行ETL或生产任务。为每个有登录要求的用户分配不同的角色。考虑为每个应用程序或网络服务分配不同的角色。使用用户组管理访问权限。保护ROOT的密码。对操作系统密码实施强密码策略。确保操作系统的重要文件得到保护。详情参见安全。(后续章节)加密加密和解密数据会影响性能，只有需要加密的数据才会被加密。在生产系统中实施任何加密解决方案之前，应该进行性能测试。GPDB生产系统使用的服务器证书应该由证书签

15、名机构(CA)签名，以便客户端可以验证服务器。如果所有客户端都是本地的，则可以使用本地ca。如果客户端和GPDB之间的连接通过不安全的链接，则使用SSL加密。使用相同密钥进行加密和解密的对称加密比非对称加密具有更好的性能。如果密钥可以安全共享，建议使用对称加密。使用pgcrypto包中的函数加密磁盘上的数据。数据的加密和解密由数据库进程完成。为了避免传输明文数据，需要使用SSL来加密客户端和数据库之间的连接。在加载和导出数据时，gpfdists协议用于保护ETL数据安全。有关详细信息，请参见加密数据和数据库连接。(后续章节)高度可用使用8到24个磁盘的硬件磁盘阵列存储解决方案。使用RAID1、

16、5或6，以便磁盘阵列能够承受磁盘故障。为磁盘阵列配备热备盘，以便在检测到磁盘故障时自动开始重建。磁盘阵列卷镜像用于防止整个磁盘阵列在重建过程中出现故障和性能下降。定期监控磁盘利用率，并在需要时添加额外空间。定期监控段数据库的倾向，以确保所有段数据库上的数据分布均匀，存储空间消耗均匀。配置备用主服务器，它将在主服务器出现故障时接管。规划当主服务器出现故障时，如何切换客户端以连接到新的主服务器实例，例如，通过更新主服务器在域名系统中的地址。建立监控系统。当主服务器出现故障时，您可以通过系统监控应用程序或电子邮件发送通知。将主段数据库及其镜像分配给不同的主机，以防止主机故障。建立监控系统。当主段数据库出现故障时，您可以通过系统监控应用程序或电子邮件发送通知。使用gprecoverseg工具及时恢复故障部分，使系统恢复到最佳平衡状态。在主服务器上配置并运行gpsnmpd，向网络监视器发送SNMP通知。在$ master _ data _ directory/PostgreSQL . conf配置文件中设置电子邮件通知，以便Greenplum系统可以在检测到严重问题时通过电子邮件通知管理员。考虑到双集群配置，它提供了额外的冗余和查询处理能力。除非Greenplum数据库的数据可以很容易地从数据源中恢复，否则应该定期备份。