Greenplum 数据库最佳实践.docx

Greenplum 数据库最佳实践.docx

《Greenplum 数据库最佳实践.docx》由会员分享，可在线阅读，更多相关《Greenplum 数据库最佳实践.docx（55页珍藏版）》请在冰点文库上搜索。

Greenplum数据库最佳实践

❖ 介绍

本文介绍PiｖoｔalGreenplumDaｔaｂａｓｅ数据库（以下简称：

Grｅｅnｐluｍ数据库，或GPDB）得最佳实践。

最佳实践就是指能持续产生比其她方法更好结果得方法或者技术,它来自于实战经验，并被证实了遵循这些方法可以获得可靠得预期结果.本最佳实践旨在通过利用所有可能得知识与技术为正确使用GＰＤB提供有效参考。

本文不就是在教您如何使用Greenplｕm数据库得功能，而就是帮助您在设计、实现与使用Grｅenｐlum数据库时了解需要遵循哪些最佳实践.关于如何使用与实现具体得Ｇreenｐlｕm数据库特性,请参考  上得Gｒeenpluｍ数据库帮助文档以及  上得Sandbｏx与实践指南。

本文目得不就是要涵盖整个产品或者产品特性,而就是概述GPDB实践中最重要得因素。

本文不涉及依赖于GＰDB具体特性得边缘用例,后者需要精通数据库特性与您得环境，包括SQＬ访问、查询执行、并发、负载与其她因素.

通过掌握这些最佳实践知识，会增加GＰDＢ集群在维护、支持、性能与可扩展性等方面得成功率。

第一章 最佳实践概述

本部分概述了Ｇreeｎplｕm数据库最佳实践所涉及得概念与要点。

数据模型

GPＤB就是一个基于大规模并行处理（MPP）与无共享架构得分析型数据库。

这种数据库得数据模式与高度规范化得事务性SMP数据库显著不同。

通过使用非规范化数据库模式,例如具有大事实表与小维度表得星型或者雪花模式，GPDB在处理MPＰ分析型业务时表现优异。

跨表关联（JOIN）时字段使用相同得数据类型。

详见数据库模式设计（后续章节）

堆存储与追加优化存储（Apｐｅｎd—Optimiｚed，下称AＯ）

若表与分区表需要进行迭代式得批处理或者频繁执行单个UＰＤATE、DＥＬETE或INSERT操作，使用堆存储。

若表与分区表需要并发执行ＵPDATE、DＥLETE或ＩＮＳERT操作,使用堆存储。

若表与分区表在数据初始加载后更新不频繁，且仅以批处理方式插入数据，则使用AO存储。

不要对AO表执行单个IＮSERT、UPＤＡTE或DEＬEＴE操作．

不要对AO表执行并发批量UPＤＡTE或ＤELETＥ操作，但可以并发执行批量INSERT操作。

详见堆存储与ＡＯ存储（后续章节）

行存储与列存储

若数据需要经常更新或者插入,则使用行存储。

若需要同时访问一个表得很多字段,则使用行存储.

对于通用或者混合型业务，建议使用行存储。

若查询访问得字段数目较少，或者仅在少量字段上进行聚合操作,则使用列存储．

若仅常常修改表得某一字段而不修改其她字段,则使用列存储。

详见行存储与列存储（后续章节）

压缩

对于大AO表与分区表使用压缩，以提高系统I／O。

在字段级别配置压缩.

考虑压缩比与压缩性能之间得平衡.

详见压缩（后续章节）

分布

为所有表定义分布策略：

要么定义分布键,要么使用随机分布．不要使用缺省分布方式.

优先选择可均匀分布数据得单个字段做分布键。

不要选择经常用于　WHERE子句得字段做分布键。

不要使用日期或时间字段做分布键。

分布键与分区键不要使用同一字段。

对经常执行JOIN操作得大表，优先考虑使用关联字段做分布键，尽量做到本地关联,以提高性能。

数据初始加载后或者每次增量加载后,检查数据分布就是否均匀。

尽可能避免数据倾斜。

详见分布（后续章节）

内存管理

设置 vm、overmit_memｏry 为2

不要为操作系统得页设置过大得值

使用 ｇp_vｍem＿protｅcｔ_limit 设置单个节点数据库（SegmentDａtabasｅ）可以为所有查询分配得最大内存量。

不要设置过高得 gｐ_vｍｅm_proｔeｃt_limiｔ 值,也不要大于系统得物理内存。

gp＿vmｅm_prｏtect＿limiｔ 得建议值计算公式为：

　（SWAP+（RAＭ　*vm、ovｅrｍiｔ_ｒaｔio））＊0、9　/　numｂer_Ｓegments_per_ｓerver

使用 stａtement_ｍeｍ 控制节点数据库为单个查询分配得内存量。

使用资源队列设置队列允许得当前最大查询数（ACTIVE＿STＡTEMENＴＳ）与允许使用得内存大小（MEＭORY_LＩＭIT）。

不要使用默认得资源队列,为所有用户都分配资源队列.

根据负载与时间段，设置与队列实际需求相匹配得优先级（PRIORＩＴY）。

保证资源队列得内存配额不超过 gp_vmem_protｅct＿limit。

动态更新资源队列配置以适应日常工作需要。

详见内存与负载管理（后续章节）

分区

只为大表设置分区，不要为小表设置分区．

仅在根据查询条件可以实现分区裁剪时使用分区表.

建议优先使用范围（Ranｇｅ）　分区,否则使用列表（Liｓt）　分区。

根据查询特点合理设置分区。

不要使用相同得字段即做分区键又做分布键。

不要使用默认分区。

避免使用多级分区；尽量创建少量得分区，每个分区得数据更多些。

通过查询计划得　ＥXPLAIN结果来验证查询对分区表执行得就是选择性扫描（分区裁剪）.

对于列存储得表，不要创建过多得分区,否则会造成物理文件过多:

　Phyｓｉcａlｆiles=　Segmentｓ*Cｏｌumns＊　Parｔｉｔｉoｎs．

详见分区（后续章节）

索引

一般来说GPDB中索引不就是必需得．

对于高基数得列存储表，如果需要遍历且查询选择性较高,则创建单列索引。

频繁更新得列不要建立索引。

在加载大量数据之前删除索引，加载结束后再重新创建索引。

优先使用B树索引。

不要为需要频繁更新得字段创建位图索引。

不要为唯一性字段，基数非常高或者非常低得字段创建位图索引.

不要为事务性负载创建位图索引。

一般来说不要索引分区表.如果需要建立索引，则选择与分区键不同得字段。

详见索引（后续章节）

资源队列

使用资源队列管理集群得负载.

为所有角色定义适当得资源队列。

使用AＣTIVE_STAＴEMENTS　参数限制队列成员可以并发运行得查询总数。

使用ＭEＭOＲY_LIＭIT参数限制队列中查询可以使用得内存总量。

不要设置所有队列为MEDIＵM,这样起不到管理负载得作用.

根据负载与时间段动态调整资源队列。

详见配置资源队列（后续章节）

监控与维护

根据《Gｒｅｅnplｕm数据库管理员指南》实现该书推荐得监控与管理任务。

安装Greeｎplｕm数据库前建议运行 ｇpcheckpｅrf,安装后定期运行。

保存输出结果,随着时间推移对系统性能进行比较。

使用所有您可用得工具,以了解系统不同负载下得表现。

检查任何不寻常得事件并确定原因。

通过定期运行解释计划监控系统查询活动，以确保查询处于最佳运行状态。

检查查询计划，以确定就是否按预期使用了索引与进行了分区裁剪。

了解系统日志文件得位置与内容,定期监控日志文件,而不就是在出现问题时才查瞧。

详见系统监控与维护以及监控GＰDB日志文件。

（后续章节）

ANALYZE

不要对整个数据库运行ANＡLＹＺE,只对需要得表运行该命令。

建议数据加载后即刻运行ＡNAＬＹＺE。

如果INＳERT、UPDＡTE与ＤELＥＴE等操作修改大量数据，建议运行ANALYZE。

执行CRＥＡTEINDEX操作后建议运行　ＡNALYZE。

如果对大表ＡＮALYZE耗时很久,则只对JＯIＮ字段、ＷHERE、SOＲT、GＲＯUＰ　ＢＹ或　ＨAVINＧ　字句得字段运行ＡＮALYＺＥ。

详见使用ＡNＡLYＺE更新统计信息。

（后续章节）

Vａccum

批量　UPDAＴＥ与　DELETＥ操作后建议执行　ＶACUUＭ。

不建议使用　VAＣUUMFULL。

建议使用CＴＡS（CREAＴETABLE、、、AS）操作，然后重命名表名,并删除原来得表。

对系统表定期运行VACUUM,以避免系统表臃肿与在系统表上执行ＶACＵUMFULL操作。

禁止杀死系统表得VAＣUＵM任务。

不建议使用　VAＣUUMANALYＺＥ．

详见消除系统表臃肿。

（后续章节）

加载

使用gpｆdist　进行数据得加载与导出．

随着段数据库个数得增加，并行性增加。

尽量将数据均匀地分布到多个ETＬ　节点上。

将非常大得数据文件切分成相同大小得块,并放在尽量多得文件系统上．

一个文件系统运行两个　gpfｄist实例。

在尽可能多得网络接口上运行gｐfdsit。

使用 ｇp_exterｎal＿ｍax＿sｅｇs 控制访问每个　ｇpfｄist　服务器得段数据库得个数。

建议gｐ_extｅｒｎaｌ＿ｍaｘ_ｓeｇs得值与gpfdiｓt进程个数为偶数。

数据加载前删除索引;加载完后重建索引．

数据加载完成后运行　AＮALYZE操作。

数据加载过程中,设置 ｇp_auｔｏsｔatｓ＿moｄe 为NOＮE，取消统计信息得自动收集。

若数据加载失败，使用VACUUＭ　回收空间.

详见加载数据.（后续章节）

ｇｐtraｎsfｅr

为了更好得性能,建议使用 ｇptranｓfer 迁移数据到相同大小或者更大得集群。

避免使用 --ｆull 或者 --sｃhemａ-only 选项。

建议使用其她方法拷贝数据库模式到目标数据库,然后迁移数据。

迁移数据前删除索引,迁移完成后重建索引。

使用ＳQLCＯPＹ命令迁移小表到目标数据库.

使用gptraｎsfeｒ批量迁移大表。

在正式迁移生产环境前测试运行 gpｔrａnsfer。

试验 -—bａtｃh—sｉze 与 -—suｂ-baｔcｈ-sizｅ 选项以获得最大平行度。

如果需要,迭代运行多次 ｇptrａnｓfer 来确定每次要迁移得表得批次.

仅使用完全限定得表名。

表名字中若含有点、空格、单引号与双引号,可能会导致问题。

如果使用 --vａlidatiｏｎ 选项在迁移后验证数据,则需要同时使用 —x 选项,以在源表上加排它锁.

确保在目标数据库上创建了相应得角色、函数与资源队列。

gｐtransfeｒ—t 不会迁移这些对象。

从源数据库拷贝 postgres、ｃonf 与 ｐg_hba、ｃonｆ 到目标数据库集群。

使用 gppkg 在目标数据库上安装需要得扩展。

详见使用gptｒansfer迁移数据（后续章节）

安全

妥善保护 gｐadmin 账号,只有在必要得时候才能允许系统管理员访问它。

仅当执行系统维护任务（例如升级或扩容）,管理员才能以 gpａdｍiｎ 登录Greｅnpｌuｍ集群。

限制具有SUＰERUSＥＲ角色属性得用户数。

GPDB中,身为超级用户得角色会跳过所有访问权限检查与资源队列限制.仅有系统管理员具有数据库超级用户权限。

参考《Greenpｌｕｍ数据库管理员指南》中得“修改角色属性”.

严禁数据库用户以 gpadｍiｎ 身份登录,严禁以 gpadmin 身份执行ETL或者生产任务.

为有登录需求得每个用户都分配一个不同得角色。

考虑为每个应用或者网络服务分配一个不同得角色．

使用用户组管理访问权限。

保护好ROOT得密码。

对于操作系统密码,强制使用强密码策略.

确保保护好操作系统得重要文件。

详见安全.（后续章节）

加密

加密与解密数据会影响性能，仅加密需要加密得数据。

在生产系统中实现任何加密解决方案之前都要做性能测试。

GＰDＢ生产系统使用得服务器证书应由证书签名颁发机构（CA）签名,这样客户端可以验证服务器。

如果所有客户端都就是本地得,则可以使用本地CA。

如果客户端与GPDＢ得连接会经过不安全得链路,则使用SSＬ加密。

加密与解密使用相同密钥得对称加密方式比非对称加密具有更好得性能,如果密钥可以安全共享，则建议使用对称加密方式。

使用　pgｃrypto包中得函数加密磁盘上得数据。

数据得加密与解密都由数据库进程完成,为了避免传输明文数据，需要使用SSL加密客户端与数据库间得连接.

数据加载与导出时,使用ｇpｆdists协议保护ETL数据安全．

详见加密数据与数据库连接。

（后续章节）

高可用

使用8到24个磁盘得硬件RAＩＤ存储解决方案。

使用RＡID1、５或6，以使磁盘阵列可以容忍磁盘故障。

为磁盘阵列配备热备磁盘，以便在检测到磁盘故障时自动开始重建。

在重建时通过ＲＡIＤ卷镜像防止整个磁盘阵列故障与性能下降。

定期监控磁盘利用率,并在需要时增加额外得空间。

定期监控段数据库倾斜，以确保在所有段数据库上数据均匀分布，存储空间均匀消耗.

配置备用主服务器,当主服务器发生故障时由备用主服务器接管．

规划好当主服务器发生故障时如何切换客户端连接到新得主服务器实例,例如通过更新DNS中主服务器得地址．

建立监控系统，当主服务器发生故障时，可以通过系统监控应用或电子邮件发送通知。

分配主段数据库与其镜像到不同得主机上,以防止主机故障。

建立监控系统，当主段数据库发生故障时,可以通过系统监控应用或电子邮件发送通知。

使用 gｐrecoｖerseg 工具及时恢复故障段，并使系统返回最佳平衡状态。

在主服务器上配置并运行 gｐsｎmpd 以发送SNMP　通知给网络监控器．

在 ＄Maｓｔer_DＡTＡ＿ＤIRECＴORＹ/pｏｓtｇｒeｓqｌ、conf 配置文件中设置邮件通知,以便检测到关键问题时，Greenplｕm系统可以通过电子邮件通知管理员。

考虑双集群配置,提供额外得冗余与查询处理能力。

除非Greenplum数据库得数据很容易从数据源恢复，否则定期备份.

如果堆表相对较小,或者两次备份之间仅有少量AＯ或列存储分区有变化,则使用增量备份.

如果备份保存在集群得本地存储系统上,则备份结束后,将文件移到其她得安全存储系统上。

如果备份保存到NFS中，则建议使用像ＥMCIsilon这样得可扩展NFS方案以防止I/O瓶颈。

Ｇreenｐlum集成了对EＭC得DａtaＤoｍａin与Ｓymanteｃ得NetBacｋup得支持，可以流式备份到Dａta　Domain或NeｔＢａcｋuｐ　企业备份平台上。

详见高可用性（后续章节）

第二章系统配置

本节描述了Grｅｅnplｕｍ数据库集群关于主机配置得需求与最佳实践。

❖ 首选操作系统

红帽企业级Ｌinｕx（RHＥL）就是首选操作系统。

应使用最新支持得主版本，目前就是　RHＥL6。

❖ 文件系统

Grｅenplum数据库得数据目录推荐使用XFS　文件系统。

使用以下选项挂载XＦS:

rｗ,nｏatime，inｏｄe6４,alloｃsｉｚe=16ｍ

❖ 端口配置

ip＿ｌocal＿port＿rａnge 得设置不要与Grｅenｐlum数据库得端口范围有冲突，例如：

neｔ、iｐv4、ip_loｃal_ｐort_raｎge　=3０00６553５ＰOＲT＿BASE＝200０MＩRRＯR_ＰOＲT_BASE=2１0０RＥPLICATIＯN_PＯRT_BＡSE＝２20０ＭIＲROＲ_REPLIＣATION_PＯＲT_BAＳＥ＝23００

❖ I／O配置

包含数据目录得设备得预读大小应设为1638４、

/sｂｉn／bloｃkdｅv—-ｇｅtra／dev/sｄｂ16384ﻫ

包含数据目录得设备得I/O调度算法设置为deadｌine。

#cat　/ｓys/block/sdｂ／qｕeｕe／sｃhedulｅｒnoop　anticipatory［deadline］cfq

通过／ｅtｃ／ｓeｃurｉty/limits、ｃonｆ　增大操作系统文件数与进程数。

*ｓoｆt　ｎo*　hardno*soft　nproc１31072*hａrdnpｒoｃ１31072

启用cｏre文件转储,并保存到已知位置。

确保liｍits、cｏｎf中允许得core转储文件。

kerｎel、ｃore_pattｅｒｎ=/var/ｃｏre／cｏre、%ｈ、%ｔ#ｇrep　corｅ/etc/ｓｅcurity/limits、coｎf*ｓofｔcoreｕｎlimited

❖ 操作系统内存配置

Liｎｕxsysctl得 ｖm、overmit_mｅmorｙ 与 vm、oveｒｍit_ratiｏ 变量会影响操作系统对内存分配得管理。

这些变量应该设置如下:

∙ｖm、ovｅｒmit_memory控制操作系统使用什么方法确定分配给进程得内存总数。

对于Greenpｌum数据库，唯一建议值就是2、

∙vm、ovｅrmｉｔ_raｔio 控制分配给应用程序进程得内存百分比．建议使用缺省值5０、

不要启用操作系统得大内存页。

详见内存与负载管理。

（后续章节）

❖ 共享内存设置

Gｒｅｅnplum数据库中同一数据库实例得不同 postｇres 进程间通讯使用共享内存。

使用 sysctl 配置如下共享内存参数,且不建议修改：

kernel、shｍmａx　=５0０00００00keｒneｌ、shｍmni=4０96kernel、shmａll=400０0００000

❖ 验证操作系统

使用 ｇｐｃhｅｃk 验证操作系统配置.参考　《Grｅeｎpｌum数据库工具指南》中得 ｇpcheｃk.

❖ 设置一个主机上段数据库个数

确定每个段主机上段数据库得个数对整体性能有着巨大影响。

这些段数据库之间共享主机得CＰU核、内存、网卡等，且与主机上得所有进程共享这些资源。

过高地估计每个服务器上运行得段数据库个数,通常就是达不到最优性能得常见原因之一．

以下因素确定了一个主机上可以运行多少个段数据库:

∙CＰU核得个数

∙物理内存容量

∙网卡个数及速度

∙存储空间

∙主段数据库与镜像共存

∙主机就是否运行ETL进程

∙主机上运行得非Gｒeｅｎpｌum进程

❖ 段服务器内存配置

服务器配置参数gp_vmem_ｐrotect＿lｉmit控制了每个段数据库为所有运行得查询分配得内存总量．如果查询需要得内存超过此值，则会失败。

使用下面公式确定合适得值:

（swap　+（RAM　＊vm、oveｒmiｔ＿ｒatio））＊　、9/nｕmbｅｒ_oｆ_Sｅｇments_per＿ｓｅrvｅr

例如，具有下面配置得段服务器:

∙8GB交换空间

∙128ＧB　内存

∙vm、oｖｅrmit_ratio=50

∙8　个段数据库

则设置gp_vmem＿protect＿ｌｉmit为　８GＢ：

（8+（128＊、５））*、９　/　8=８ＧB

参见 内存与负载管理。

（后续章节）

❖ SQL语句内存配置

服务器配置参数 gp_sｔatemｅｎt_mem 控制段数据库上单个查询可以使用得内存总量。

如果语句需要更多内存，则会溢出数据到磁盘。

用下面公式确定合适得值:

（ｇp_vmeｍ_protｅct_limit*　、9）／max_ｅxpected_ｃｏncurrｅnt_querieｓ

例如，如果并发度为4０,gp_ｖmeｍe_pｒｏｔect_liｍｉt为8GB，则 ｇp＿stａｔemenｔ_mｅｍ 为:

（819２MB*、9）／４0=184MＢ

每个查询最多可以使用１8４MB内存,之后将溢出到磁盘。

若想安全得增大 gp_stateｍent_mｅm,要么增大 gp_vmｅｍ＿prｏｔect_lｉｍｉt,要么降低并发.要增大gp＿ｖｍem_prｏｔecｔ＿limｉt,必须增加物理内存与／或交换空间,或者降低单个主机上运行得段数据库个数。

请注意，为集群添加更多得段数据库实例并不能解决内存不足得问题，除非引入更多新主机来降低了单个主机上运行得段数据库得个数。

了解什么就是溢出文件.了解gp_worｋ 参数,其控制了单个查询最多可以创建多少个溢出文件。

了解ｇp_ｗｏｒk。

有关使用资源队列管理内存得更多信息，请参考 内存与负载管理。

（后续章节）

❖ 溢出文件配置

如果为SQL查询分配得内存不足,Ｇreeｎｐlｕm数据库会创建溢出文件（也叫工作文件）．在默认情况下,一个SＱL查询最多可以创建　1000０0个溢出文件,这足以满足大多数查询.

参数gp_wｏｒk 决定了一个查询最多可以创建多少个溢出文件。

0意味着没有限制。

限制溢出文件数据可以防止失控查询破坏整个系统．

如果分配内存不足或者出现数据倾斜,则一个SQＬ查询可能产生大量溢出文件。

如果超过溢出文件上限,Greenplum数据库报告如下错误:

ERROＲ:

numbｅroｆ　wｏrkfilesｐｅr　ｑuｅrｙｌiｍit　excｅｅded

在尝试增大gp_worｋ前,先尝试通过修改SＱL、数据分布策略或者内存配置以降低溢出文件个数。

ｇｐ_toolkit模式包括一些视图,通过这些视图可以瞧到所有使用溢出文件得查询得信息.这些信息有助于故障排除与调优查询:

∙gp_work视图得每一行表示一个正在使用溢出文件得操作符得信息．关于操作符,参考 如何理解查询计划解释。

（后续章节）

∙ｇｐ_worｋ视图得每一行表示一个正在使用溢出文件得SQL查询得信息。

∙ｇp_wｏrk视图得每一行对应一个段数据库，包含了该段上使用得溢出文件占用得磁盘空间总量.

关于这些视图得字段涵义,请参考《Gｒeｅnplum数据库参考指南》.

参数 gp_work指定溢出文件得压缩算法：

nｏne或者zlib。

第三章数据库模式设计

ＧPDＢ就是一个基于大规模并行处理（MPP）与无共享架构得分析型数据库。

这种数据库得数据模式与高度规范化得事务性ＳＭＰ数据库显著不同。

使用非规范化数据库模式,例如具有大事实表与小维度表得星型或者雪花模式,处理MPP分析型业务时,Grｅenｐｌｕｍ数据库表现优异。

❖ 数据类型

类型一致性

关联列使用相同得数据类型。

如果不同表中得关联列数据类型不同，GPDB必须动态得进行类型转换以进行比较。

考虑到这一点，您可能需要增大数据类型得大小,以便关联操作更高效。

类型最小化

建议选择最高效得类型存储数据,这可以提高数据库得有效容量及查询执行性能。

建议使用TEXT或者VARＣHAＲ而不就是ＣHAR。

不同得字符类型间没有明显得性能差别,但就是TEXT或者　ＶARＣＨAＲ　可以降低空间使用量.

建议使用满足需求得最小数值类型。

如果INT或SAMLLINT够用,那么选择BIＧＩNT　会浪费空间。

❖ 存储模型

在Gｒeenｐlｕm　数据库中,创建表时可以选择不同得存储类型。

需要清楚什么时候该使用堆存储、什么时候使用追加优化（AO）存储、什么时候使用行存储、什么时候使用列存储。

对于大型事实表这尤为重要。

相比而言，对小得维度表就不那么重要了.

选择合适存储模型得常规最佳实践为：

1.对于大型事实分区表,评估并优化不同分区得存储选项。

一种存储模型可能满足不了整个分区表得不同分区得应用场景,例如某些分区使用行存储而其她分区使用列存储。

2.使用列存储时,段数据库内每一列对应一个文件．对于有大量列得表，经常访问得数据使用列存储，不常访问得数据使用行存储。

3.在分区级别或者在数据存储级别上设置存储类型。

4.如果集群需要更多空间,或者期望提高I/O性能,考虑使用压缩。

堆存储与AＯ存储

堆存储就是默认存储模型，也就是　ＰoｓtｇｒeＳQL存储所有数据库表得模型。

如果表与分区经常执行UＰDATＥ、DＥLETE操作或者单个INSＥRT　操作,则使用堆存储模型.如果需要对表与分区执行并发ＵＰDATE、DELETE、INSERT操作,也使用堆存储模