datax,csv,load,copy Ø 数据同步结果确认与显示 • 数据同步方式 Ø gpfdist+外部表 ： UMGW大表 Ø db_sync同步程序 ： 底层库 + 同步逻辑 + Django界面 Ø 临时同步需求： datax , copy 29 Greenplum运维体系 数据库数据传输与同步-db_sync 30 Greenplum运维体系 数据库数据传输与同步-db_sync

372844366 rows – D - 75042462 rows – E - 2521897 rows 结论：超过6亿条历史数据导入，用时少于1.5小时，性能非常卓越。 • 全表扫描测试 – DWA测试环境：针对表C（372844366 rows）进行全表扫描，历时少于1.5 分钟。 – 客户投产环境：针对表C的一个子表（记录数约为C表的1/10） 进行全表扫 描，历时超过20分钟。 结论：如果采用DW 功 能 测 试 select count(coalesce(zh,'')) as zh from stage.fs_zh_cdfispad; 36396887 43.8s 37528247 1.5s 28x select count(cast(zfje as char(15))) from stage.fs_zh_cdfispad; 36396887 66.8s 37528247 3.8s

GPDB中通常的规则是,gp_vmem_protect_limit设置为: ( X * physical_memory_in_MB ) / #_of_primary_segments X =1~1.5，建议采用1，避免过多占用OS的内存.  调整资源队列中MEMORY_LIMIT的总和小于 gp_vmem_protect_limit *0.9.  调整资源中的Active_state

访问UDW数据仓库 Greenplum数据仓库 UDW Copyright © 2012-2021 UCloud 优刻得 57/206 注解： 如出现以上内容，则表⽰psqlodbc配置成功。 1.5 python客户端访问 客户端访问 $yum install python-psycopg2 ⽰例1. 连接UDW testconn.py #!/usr/bin/python 访问UDW数据仓库

"vm.dirty_ratio = 0" >> /etc/sysctl.d/10-gpdb.conf $ echo "vm.dirty_background_bytes = 1610612736 # 1.5GB" >> /etc/sy sctl.d/10-gpdb.conf $ echo "vm.dirty_bytes = 4294967296 # 4GB" >> /etc/sysctl.d/10-gpd "vm.dirty_ratio = 0" >> /etc/sysctl.d/10-gpdb.conf $ echo "vm.dirty_background_bytes = 1610612736 # 1.5GB" >> /etc/sy sctl.d/10-gpdb.conf $ echo "vm.dirty_bytes = 4294967296 # 4GB" >> /etc/sysctl.d/10-gpd dirty_ratio = 0 VMware Greenplum 6 Documentation VMware, Inc 413 vm.dirty_background_bytes = 1610612736 # 1.5GB vm.dirty_bytes = 4294967296 # 4GB For host systems with 64GB of memory or less, remove vm.dirty_background_bytes

"vm.dirty_ratio = 0" >> /etc/sysctl.d/10-gpdb.conf $ echo "vm.dirty_background_bytes = 1610612736 # 1.5GB" >> /etc/sy sctl.d/10-gpdb.conf $ echo "vm.dirty_bytes = 4294967296 # 4GB" >> /etc/sysctl.d/10-gpd "vm.dirty_ratio = 0" >> /etc/sysctl.d/10-gpdb.conf $ echo "vm.dirty_background_bytes = 1610612736 # 1.5GB" >> /etc/sy sctl.d/10-gpdb.conf $ echo "vm.dirty_bytes = 4294967296 # 4GB" >> /etc/sysctl.d/10-gpd recommended: vm.dirty_background_ratio = 0 vm.dirty_ratio = 0 vm.dirty_background_bytes = 1610612736 # 1.5GB vm.dirty_bytes = 4294967296 # 4GB For host systems with 64GB of memory or less, remove vm.dirty_background_bytes

"vm.dirty_ratio = 0" >> /etc/sysctl.d/10-gpdb.conf $ echo "vm.dirty_background_bytes = 1610612736 # 1.5GB" >> /etc/sy sctl.d/10-gpdb.conf $ echo "vm.dirty_bytes = 4294967296 # 4GB" >> /etc/sysctl.d/10-gpd "vm.dirty_ratio = 0" >> /etc/sysctl.d/10-gpdb.conf $ echo "vm.dirty_background_bytes = 1610612736 # 1.5GB" >> /etc/sy sctl.d/10-gpdb.conf $ echo "vm.dirty_bytes = 4294967296 # 4GB" >> /etc/sysctl.d/10-gpd recommended: vm.dirty_background_ratio = 0 vm.dirty_ratio = 0 vm.dirty_background_bytes = 1610612736 # 1.5GB vm.dirty_bytes = 4294967296 # 4GB For host systems with 64GB of memory or less, remove vm.dirty_background_bytes

recommended: vm.dirty_background_ratio = 0 vm.dirty_ratio = 0 vm.dirty_background_bytes = 1610612736 # 1.5GB vm.dirty_bytes = 4294967296 # 4GB For host systems with 64GB of memory or less, remove vm.dirty_background_bytes to the Greenplum Database master host: wget https://cran.r-project.org/src/contrib/Archive/arm/arm_1.5-03.tar.gz wget https://cran.r-project.org/src/contrib/Archive/Matrix/Matrix_0.9996875-1.tar.gz 3 to do this. gpscp -f hosts_all Matrix_0.9996875-1.tar.gz =:/home/gpadmin gpscp -f /hosts_all arm_1.5-03.tar.gz =:/home/gpadmin 4. Use the gpssh utility in interactive mode to log into each Greenplum

recommended: vm.dirty_background_ratio = 0 vm.dirty_ratio = 0 vm.dirty_background_bytes = 1610612736 # 1.5GB vm.dirty_bytes = 4294967296 # 4GB For host systems with 64GB of memory or less, remove vm.dirty_background_bytes to the Greenplum Database master host: wget https://cran.r-project.org/src/contrib/Archive/arm/arm_1.5-03.tar.gz Note: If you expand Greenplum Database and add segment hosts, you must install the R packages to do this. gpscp -f hosts_all Matrix_0.9996875-1.tar.gz =:/home/gpadmin gpscp -f /hosts_all arm_1.5-03.tar.gz =:/home/gpadmin 4. Use the gpssh utility in interactive mode to log into each Greenplum

recommended: vm.dirty_background_ratio = 0 vm.dirty_ratio = 0 vm.dirty_background_bytes = 1610612736 # 1.5GB vm.dirty_bytes = 4294967296 # 4GB For host systems with 64GB of memory or less, remove vm.dirty_background_bytes to the Greenplum Database master host: wget https://cran.r-project.org/src/contrib/Archive/arm/arm_1.5-03.tar.gz wget https://cran.r-project.org/src/contrib/Archive/Matrix/Matrix_0.9996875-1.tar.gz 3 to do this. gpscp -f hosts_all Matrix_0.9996875-1.tar.gz =:/home/gpadmin gpscp -f /hosts_all arm_1.5-03.tar.gz =:/home/gpadmin 4. Use the gpssh utility in interactive mode to log into each Greenplum