多机部署deepgreen，与greenplum部署方法类似。

本文仅用于测试。

与之前测试citus的环境一致，9台ECS虚拟机。

xxx.xxx.xxx.228digoal-citus-gpdb-test001xxx.xxx.xxx.224digoal-citus-gpdb-test002xxx.xxx.xxx.230digoal-citus-gpdb-test003xxx.xxx.xxx.231digoal-citus-gpdb-test004xxx.xxx.xxx.225digoal-citus-gpdb-test005xxx.xxx.xxx.227digoal-citus-gpdb-test006xxx.xxx.xxx.232digoal-citus-gpdb-test007xxx.xxx.xxx.226digoal-citus-gpdb-test008xxx.xxx.xxx.229digoal-citus-gpdb-test009

1、配置时，请使用实际主机名

#hostname-s例如返回：digoal-citus-gpdb-test005

2、ECS的配置

CentOS7x6432core128G2TB2 配置OS (所有ECS)

1、新建OS普通用户，用于启动deepgreen

#useraddpostgres#passwdpostgrespwd.......

2、目录规划，目录权限

#mkdir/data01/dp#chownpostgres:postgres/data01/dp

3、配置SSHd，允许KEY认证

#echo"PubkeyAuthenticationyes">>/etc/ssh/sshd_config#systemctlrestartsshd.service

4、配置名字解析

#echo"xxx.xxx.xxx.228digoal-citus-gpdb-test001">>/etc/hosts#echo"xxx.xxx.xxx.224digoal-citus-gpdb-test002">>/etc/hosts#echo"xxx.xxx.xxx.230digoal-citus-gpdb-test003">>/etc/hosts#echo"xxx.xxx.xxx.231digoal-citus-gpdb-test004">>/etc/hosts#echo"xxx.xxx.xxx.225digoal-citus-gpdb-test005">>/etc/hosts#echo"xxx.xxx.xxx.227digoal-citus-gpdb-test006">>/etc/hosts#echo"xxx.xxx.xxx.232digoal-citus-gpdb-test007">>/etc/hosts#echo"xxx.xxx.xxx.226digoal-citus-gpdb-test008">>/etc/hosts#echo"xxx.xxx.xxx.229digoal-citus-gpdb-test009">>/etc/hosts3 安装deepgreen软件 (master ECS)

1、下载软件

#su-postgres$wgethttps://s3.amazonaws.com/vitessedata/download/deepgreendb.18.08.rh7.x86_64.180825.bin

2、修改权限

$chmod700deepgreendb.18.08.rh7.x86_64.180825.bin

3、安装软件

$./deepgreendb.18.08.rh7.x86_64.180825.bin

4、配置环境变量

$vi./deepgreendb/greenplum_path.sh#追加#使用实际目录exportMASTER_DATA_DIRECTORY=/data01/dp/dg-1exportPGDATA=$MASTER_DATA_DIRECTORYexportPGHOST=127.0.0.1exportPGPORT=15432#使用os用户名exportPGUSER=postgresexportPGPASSWORD=123exportPGDATABASE=postgres

5、使用环境变量

$../deepgreendb/greenplum_path.sh

6、编写HOST文件 (9台ECS的HOSTNAME写入) （用于配置、初始化deepgreen集群）

$vihostfiledigoal-citus-gpdb-test001digoal-citus-gpdb-test002digoal-citus-gpdb-test003digoal-citus-gpdb-test004digoal-citus-gpdb-test005digoal-citus-gpdb-test006digoal-citus-gpdb-test007digoal-citus-gpdb-test008digoal-citus-gpdb-test009

7、交换SSH KEY，配置KEY互信

$gpssh-exkeys-f./hostfile输入一次pwd.......4 拷贝软件到其他ECS （master ECS）

1、打包

$tar-zcvfdeepgreendb.18.08.180825.tar.gzdeepgreendb.18.08.180825

2、拷贝到其他ECS

$scpdeepgreendb.18.08.180825.tar.gzdigoal-citus-gpdb-test002:~/$scpdeepgreendb.18.08.180825.tar.gzdigoal-citus-gpdb-test003:~/$scpdeepgreendb.18.08.180825.tar.gzdigoal-citus-gpdb-test004:~/$scpdeepgreendb.18.08.180825.tar.gzdigoal-citus-gpdb-test005:~/$scpdeepgreendb.18.08.180825.tar.gzdigoal-citus-gpdb-test006:~/$scpdeepgreendb.18.08.180825.tar.gzdigoal-citus-gpdb-test007:~/$scpdeepgreendb.18.08.180825.tar.gzdigoal-citus-gpdb-test008:~/$scpdeepgreendb.18.08.180825.tar.gzdigoal-citus-gpdb-test009:~/

3、解压软件

$gpssh-hdigoal-citus-gpdb-test002-hdigoal-citus-gpdb-test003-hdigoal-citus-gpdb-test004-hdigoal-citus-gpdb-test005-hdigoal-citus-gpdb-test006-hdigoal-citus-gpdb-test007-hdigoal-citus-gpdb-test008-hdigoal-citus-gpdb-test009=>tar-zxvfdeepgreendb.18.08.180825.tar.gz>/dev/null=>ln-s`pwd`/deepgreendb.18.08.180825`pwd`/deepgreendb5 初始化deepgreen 数据库集群 (master ECS)

每个ECS上跑16个segment。数据目录/data01/dp

hostfile里面包含9台ECS hostname，如果master不想跑segment，则把它从hostfile删掉即可。

digoal-citus-gpdb-test001 作为 master节点

1、按以上要求编写配置文件

$vicluster.confARRAY_NAME="mpp1cluster"CLUSTER_NAME="mpp1cluster"MACHINE_LIST_FILE=hostfileSEG_PREFIX=dgDATABASE_PREFIX=dgPORT_BASE=25432declare-aDATA_DIRECTORY=(/data01/dp/data01/dp/data01/dp/data01/dp/data01/dp/data01/dp/data01/dp/data01/dp/data01/dp/data01/dp/data01/dp/data01/dp/data01/dp/data01/dp/data01/dp/data01/dp)MASTER_HOSTNAME=digoal-citus-gpdb-test001MASTER_DIRECTORY=/data01/dpMASTER_PORT=15432IP_ALLOW=0.0.0.0/0TRUSTED_SHELL=/usr/bin/sshCHECK_POINT_SEGMENTS=32ENCODING=UNICODEexportMASTER_DATA_DIRECTORYexportTRUSTED_SHELLDEFAULT_QD_MAX_CONNECT=25QE_CONNECT_FACTOR=5

2、初始化数据库集群

gpinitsystem-ccluster.conf-hhostfile

生成集群，一共144 segments

3、参数

postgres=#selectsource,category,name,setting,unitfrompg_settingswheresource<>'default'orname~'^gp_'orname~'^vitesse'orderbysource,category,name;source|category|name|setting|unit----------------------+------------------------------------------------------+---------------------------------------------+----------------------------------------------------------------------------------+------client|ReportingandLogging/WhattoLog|application_name|psql|commandline|ClientConnectionDefaults/LocaleandFormatting|TimeZone|PRC|commandline|ClientConnectionDefaults/LocaleandFormatting|timezone_abbreviations|Default|commandline|ConnectionsandAuthentication/ConnectionSettings|listen_addresses|*|commandline|ConnectionsandAuthentication/ConnectionSettings|port|15432|commandline|PresetOptions|gp_contentid|-1|commandline|PresetOptions|gp_dbid|1|commandline|PresetOptions|gp_num_contents_in_cluster|144|commandline|PresetOptions|gp_standby_dbid|0|commandline|ReportingandLogging/WhattoLog|log_timezone|PRC|configurationfile|Append-OnlyTables|max_appendonly_tables|10000|configurationfile|ClientConnectionDefaults/LocaleandFormatting|DateStyle|ISO,MDY|configurationfile|ClientConnectionDefaults/LocaleandFormatting|default_text_search_config|pg_catalog.english|configurationfile|ClientConnectionDefaults/LocaleandFormatting|lc_messages|en_US.utf8|configurationfile|ClientConnectionDefaults/LocaleandFormatting|lc_monetary|en_US.utf8|configurationfile|ClientConnectionDefaults/LocaleandFormatting|lc_numeric|en_US.utf8|configurationfile|ClientConnectionDefaults/LocaleandFormatting|lc_time|en_US.utf8|configurationfile|ConnectionsandAuthentication/ConnectionSettings|max_connections|25|configurationfile|DeveloperOptions|gp_autostats_mode|ON_NO_STATS|configurationfile|DeveloperOptions|gp_autostats_on_change_threshold|2147483647|configurationfile|DeveloperOptions|gp_backup_directIO|off|configurationfile|DeveloperOptions|gp_backup_directIO_read_chunk_mb|20|configurationfile|GreenplumDatabase/ArrayTuning|gp_connections_per_thread|0|configurationfile|GreenplumDatabase/ArrayTuning|gp_interconnect_type|UDPIFC|configurationfile|GreenplumDatabase/ArrayTuning|gp_segment_connect_timeout|600|sconfigurationfile|GreenplumDatabase/ArrayTuning|gp_xliw_agent_enable|off|configurationfile|GreenplumDatabase/ArrayTuning|gp_xliw_log_level|8|configurationfile|GreenplumDatabase/ArrayTuning|gp_xliw_unix_path|/tmp/xliwagent.socket|configurationfile|GreenplumDatabase/ArrayTuning|gp_xliw_worker_num|2|configurationfile|ReportingandLogging/WhattoLog|log_autostats|off|configurationfile|ReportingandLogging/WhattoLog|log_statement|all|configurationfile|ResourceUsage|max_prepared_transactions|250|configurationfile|ResourceUsage/FreeSpaceMap|max_fsm_pages|200000|configurationfile|ResourceUsage/Memory|gp_vmem_protect_limit|8192|configurationfile|ResourceUsage/Memory|shared_buffers|4000|32kBconfigurationfile|ResourceUsage/ResourcesManagement|gp_resqueue_memory_policy|eager_free|configurationfile|Statistics/ANALYZEDatabaseContents|optimizer_analyze_root_partition|on|default|Append-OnlyTables|gp_appendonly_compaction_threshold|10|default|Append-OnlyTables|gp_default_storage_options|appendonly=false,blocksize=32768,compresstype=none,checksum=true,orientation=row|default|ClientConnectionDefaults/OtherDefaults|gp_command_count|26|default|ClientConnectionDefaults/OtherDefaults|gp_connection_send_timeout|3600|default|ClientConnectionDefaults/OtherDefaults|gp_role|dispatch|default|ClientConnectionDefaults/OtherDefaults|gp_session_id|505|default|ClientConnectionDefaults/OtherDefaults|gp_vmem_idle_resource_timeout|18000|msdefault|CustomizedOptions|gp_hadoop_home||default|CustomizedOptions|gp_hadoop_target_version|hadoop|default|CustomizedOptions|vitesse.copy_dump||default|CustomizedOptions|vitesse_cpuname||default|CustomizedOptions|vitesse.dbgbreak_mask|0|default|CustomizedOptions|vitesse.enable|on|default|CustomizedOptions|vitesse_explain_verbosity|0|default|CustomizedOptions|vitesse.explain_verbosity|0|default|CustomizedOptions|vitesse.exttab_jit|on|default|CustomizedOptions|vitesse_jit_time|0|default|CustomizedOptions|vitesse.jit_time|0|default|CustomizedOptions|vitesse_log_level|0|default|CustomizedOptions|vitesse.log_level|0|default|CustomizedOptions|vitesse.motion_opt|off|default|CustomizedOptions|vitesse.partsel_opt|off|default|CustomizedOptions|vitesse_phi_home||default|CustomizedOptions|vitesse_plan_cost|72|default|CustomizedOptions|vitesse.plan_cost|72|default|CustomizedOptions|vitesse.print_tree|off|default|CustomizedOptions|vitesse_rev|7615c3b|default|CustomizedOptions|vitesse.rev|7615c3b|default|CustomizedOptions|vitesse.seqscan_using_pg_pool|off|default|CustomizedOptions|vitesse.spill_max|0|default|CustomizedOptions|vitesse.stack_trace|0|default|CustomizedOptions|vitesse.support_dump||default|CustomizedOptions|vitesse.threshold|100|default|CustomizedOptions|vitesse.use_modified_utf8|off|default|CustomizedOptions|vitesse_version|DeepgreenDB18.08[rev7615c3bon2018-08-25]|default|CustomizedOptions|vitesse.version|DeepgreenDB18.08[rev7615c3bon2018-08-25]|default|CustomizedOptions|vitesse_xdrive_port|6868|default|CustomizedOptions|vitesse.xdrive_port|6868|default|CustomizedOptions|vitesse.xliw|0|default|DeveloperOptions|gp_autostats_mode_in_functions|NONE|default|DeveloperOptions|gp_debug_linger|0|sdefault|DeveloperOptions|gp_reraise_signal|on|default|DeveloperOptions|gp_workfile_compress_algorithm|none|default|DeveloperOptions|vitesse_dbgbreak_mask|0|default|DeveloperOptions|vitesse_stack_trace|0|default|DeveloperOptions|vitesse_use_modified_utf8|off|default|ExternalTables|gp_external_enable_exec|on|default|ExternalTables|gp_external_enable_filter_pushdown|off|default|ExternalTables|gp_external_max_segs|64|default|ExternalTables|gp_initial_bad_row_limit|1000|default|ExternalTables|gp_max_csv_line_length|1048576|default|GPDBErrorHandling|gp_reject_percent_threshold|300|default|GreenplumDatabase/ArrayTuning|gp_cached_segworkers_threshold|5|default|GreenplumDatabase/ArrayTuning|gp_filerep_ct_batch_size|65536|default|GreenplumDatabase/ArrayTuning|gp_filerep_tcp_keepalives_count|2|default|GreenplumDatabase/ArrayTuning|gp_filerep_tcp_keepalives_idle|60|sdefault|GreenplumDatabase/ArrayTuning|gp_filerep_tcp_keepalives_interval|30|sdefault|GreenplumDatabase/ArrayTuning|gp_fts_probe_interval|60|sdefault|GreenplumDatabase/ArrayTuning|gp_fts_probe_threadcount|16|default|GreenplumDatabase/ArrayTuning|gp_fts_probe_timeout|20|sdefault|GreenplumDatabase/ArrayTuning|gp_hashjoin_tuples_per_bucket|5|default|GreenplumDatabase/ArrayTuning|gp_interconnect_cache_future_packets|on|default|GreenplumDatabase/ArrayTuning|gp_interconnect_debug_retry_interval|10|default|GreenplumDatabase/ArrayTuning|gp_interconnect_deepmesh_path|/tmp/dmagent.socket|default|GreenplumDatabase/ArrayTuning|gp_interconnect_default_rtt|20|msdefault|GreenplumDatabase/ArrayTuning|gp_interconnect_fc_method|LOSS|default|GreenplumDatabase/ArrayTuning|gp_interconnect_hash_multiplier|2|default|GreenplumDatabase/ArrayTuning|gp_interconnect_min_retries_before_timeout|100|default|GreenplumDatabase/ArrayTuning|gp_interconnect_min_rto|20|msdefault|GreenplumDatabase/ArrayTuning|gp_interconnect_queue_depth|4|default|GreenplumDatabase/ArrayTuning|gp_interconnect_setup_timeout|7200|sdefault|GreenplumDatabase/ArrayTuning|gp_interconnect_snd_queue_depth|2|default|GreenplumDatabase/ArrayTuning|gp_interconnect_tcp_listener_backlog|128|default|GreenplumDatabase/ArrayTuning|gp_interconnect_timer_checking_period|20|msdefault|GreenplumDatabase/ArrayTuning|gp_interconnect_timer_period|5|msdefault|GreenplumDatabase/ArrayTuning|gp_interconnect_transmit_timeout|3600|sdefault|GreenplumDatabase/ArrayTuning|gp_max_packet_size|8192|default|GreenplumDatabase/ArrayTuning|gp_udp_bufsize_k|0|default|GreenplumDatabase/WorkerProcessIdentity|gp_master_addr|127.0.0.1|default|PresetOptions|gp_max_partition_level|0|default|PresetOptions|gp_max_slices|0|default|PresetOptions|gp_server_version|5.10.2+7615c3bbuildga|default|PresetOptions|gp_server_version_num|51002|default|QueryTuning|vitesse_threshold|100|default|QueryTuning/OtherPlannerOptions|gp_enable_fast_sri|on|default|QueryTuning/OtherPlannerOptions|gp_enable_predicate_propagation|on|default|QueryTuning/OtherPlannerOptions|gp_workfile_checksumming|on|default|QueryTuning/OtherPlannerOptions|vitesse_enable|on|default|QueryTuning/PlannerCostConstants|gp_motion_cost_per_row|0|default|QueryTuning/PlannerCostConstants|gp_segments_for_planner|0|default|QueryTuning/PlannerMethodConfiguration|gp_adjust_selectivity_for_outerjoins|on|default|QueryTuning/PlannerMethodConfiguration|gp_dynamic_partition_pruning|on|default|QueryTuning/PlannerMethodConfiguration|gp_enable_agg_distinct|on|default|QueryTuning/PlannerMethodConfiguration|gp_enable_agg_distinct_pruning|on|default|QueryTuning/PlannerMethodConfiguration|gp_enable_direct_dispatch|on|default|QueryTuning/PlannerMethodConfiguration|gp_enable_fallback_plan|on|default|QueryTuning/PlannerMethodConfiguration|gp_enable_groupext_distinct_gather|on|default|QueryTuning/PlannerMethodConfiguration|gp_enable_groupext_distinct_pruning|on|default|QueryTuning/PlannerMethodConfiguration|gp_enable_multiphase_agg|on|default|QueryTuning/PlannerMethodConfiguration|gp_enable_preunique|on|default|QueryTuning/PlannerMethodConfiguration|gp_enable_relsize_collection|off|default|QueryTuning/PlannerMethodConfiguration|gp_enable_sequential_window_plans|on|default|QueryTuning/PlannerMethodConfiguration|gp_enable_sort_distinct|on|default|QueryTuning/PlannerMethodConfiguration|gp_enable_sort_limit|on|default|QueryTuning/PlannerMethodConfiguration|gp_idf_deduplicate|auto|default|QueryTuning/PlannerMethodConfiguration|gp_statistics_pullup_from_child_partition|on|default|QueryTuning/PlannerMethodConfiguration|gp_statistics_use_fkeys|on|default|ReportingandLogging|gp_email_connect_avoid_duration|7200|default|ReportingandLogging|gp_email_connect_failures|5|default|ReportingandLogging|gp_email_connect_timeout|15|default|ReportingandLogging|gp_email_from||default|ReportingandLogging|gp_email_smtp_password||default|ReportingandLogging|gp_email_smtp_server|localhost:25|default|ReportingandLogging|gp_email_smtp_userid||default|ReportingandLogging|gp_email_to||default|ReportingandLogging|gp_snmp_community|public|default|ReportingandLogging|gp_snmp_monitor_address||default|ReportingandLogging|gp_snmp_use_inform_or_trap|trap|default|ReportingandLogging/WhattoLog|gp_gpperfmon_send_interval|1|default|ReportingandLogging/WheretoLog|gp_log_format|csv|default|ResourceUsage|gp_resource_group_bypass|off|default|ResourceUsage|gp_resource_group_cpu_limit|0.9|default|ResourceUsage|gp_resource_group_cpu_priority|10|default|ResourceUsage|gp_resource_group_memory_limit|0.7|default|ResourceUsage|gp_resource_manager|queue|default|ResourceUsage|gp_safefswritesize|0|default|ResourceUsage|gp_subtrans_warn_limit|16777216|default|ResourceUsage|gp_workfile_limit_files_per_query|100000|default|ResourceUsage|gp_workfile_limit_per_query|0|default|ResourceUsage|gp_workfile_limit_per_segment|0|default|ResourceUsage/KernelResources|gp_set_proc_affinity|off|default|ResourceUsage/Memory|gp_max_databases|16|default|ResourceUsage/Memory|gp_max_filespaces|8|default|ResourceUsage/Memory|gp_max_local_distributed_cache|1024|default|ResourceUsage/Memory|gp_max_plan_size|0|kBdefault|ResourceUsage/Memory|gp_max_tablespaces|16|default|ResourceUsage/Memory|gp_vmem_protect_segworker_cache_limit|500|default|ResourceUsage/ResourcesManagement|gp_resgroup_memory_policy|eager_free|default|ResourceUsage/ResourcesManagement|gp_resqueue_priority|off|default|ResourceUsage/ResourcesManagement|gp_resqueue_priority_cpucores_per_segment|4|default|ResourceUsage/ResourcesManagement|gp_resqueue_priority_sweeper_interval|1000|default|ResourceUsage/ResourcesManagement|vitesse_index_mem|0|default|ResourceUsage/ResourcesManagement|vitesse_spill_max|0|default|ResourceUsage/ResourcesManagement|vitesse_spillz|1|default|Statistics/ANALYZEDatabaseContents|gp_analyze_relative_error|0.25|default|Ungrouped|gp_enable_gpperfmon|off|default|Ungrouped|gp_enable_query_metrics|off|default|Ungrouped|gp_instrument_shmem_size|5120|kBdefault|VersionandPlatformCompatibility|gp_create_table_random_default_distribution|off|default|VersionandPlatformCompatibility|gp_enable_exchange_default_partition|off|environmentvariable|ResourceUsage/Memory|max_stack_depth|2048|kBoverride|ClientConnectionDefaults/LocaleandFormatting|lc_collate|en_US.utf8|override|ClientConnectionDefaults/LocaleandFormatting|lc_ctype|en_US.utf8|override|ClientConnectionDefaults/LocaleandFormatting|server_encoding|UTF8|override|ClientConnectionDefaults/StatementBehavior|transaction_isolation|readcommitted|override|ClientConnectionDefaults/StatementBehavior|transaction_read_only|off|override|PresetOptions|data_checksums|on|(194rows)6 sf=200 tpc-h 测试

1、使用gp_tpch测试，200G数据集。TPC-H

#su-postgres$gitclonehttps://github.com/digoal/gp_tpch$cdgp_tpch$make$ln-s`pwd`/tmp/dss-data$./dbgen-s200$foriin`ls*.tbl`;dosed's/|$//'$i>${i/tbl/csv};echo$i;done;$SF=200$mkdirdss/queries$forqin`seq122`doDSS_QUERY=dss/templates./qgen-s$SF$q>dss/queries/$q.sqlsed's/^select/explainselect/'dss/queries/$q.sql>dss/queries/$q.explain.sqldone模板$./tpch.sh./results_gpdbipporttpch-dbtpch-userpassword{row|column|redshift|pg|pg10|citus}$./tpch.sh./results_gpdb127.0.0.115432postgrespostgrespwdcolumn

2、测试结果（比citus好很多）

2018-09-0317:14:24[1535966064]:query1finishedOK(3seconds)2018-09-0317:14:28[1535966068]:query2finishedOK(4seconds)2018-09-0317:14:36[1535966076]:query3finishedOK(7seconds)2018-09-0317:14:38[1535966078]:query4finishedOK(2seconds)2018-09-0317:14:56[1535966096]:query5finishedOK(17seconds)2018-09-0317:14:57[1535966097]:query6finishedOK(0seconds)2018-09-0317:15:20[1535966120]:query7finishedOK(23seconds)2018-09-0317:15:26[1535966126]:query8finishedOK(5seconds)2018-09-0317:15:39[1535966139]:query9finishedOK(12seconds)2018-09-0317:15:43[1535966143]:query10finishedOK(4seconds)2018-09-0317:15:44[1535966144]:query11finishedOK(1seconds)2018-09-0317:15:48[1535966148]:query12finishedOK(3seconds)2018-09-0317:15:53[1535966153]:query13finishedOK(4seconds)2018-09-0317:15:58[1535966158]:query14finishedOK(5seconds)2018-09-0317:16:05[1535966165]:query15finishedOK(6seconds)2018-09-0317:16:11[1535966171]:query16finishedOK(6seconds)2018-09-0317:16:32[1535966192]:query17finishedOK(20seconds)2018-09-0317:16:38[1535966198]:query18finishedOK(5seconds)2018-09-0317:16:41[1535966201]:query19finishedOK(3seconds)2018-09-0317:16:47[1535966207]:query20finishedOK(5seconds)2018-09-0317:16:57[1535966217]:query21finishedOK(9seconds)2018-09-0317:17:01[1535966221]:query22finishedOK(4seconds)7 1亿 tpc-b

pgbench-i-s1000-h127.0.0.1-p15432

1、只读

pgbench-Mprepared-v-r-P1-c20-j20-T120-h127.0.0.1-p15432-Stransactiontype:<builtin:selectonly>scalingfactor:1000querymode:preparednumberofclients:20numberofthreads:20duration:120snumberoftransactionsactuallyprocessed:22557latencyaverage=106.488mslatencystddev=38.781mstps=187.690045(includingconnectionsestablishing)tps=187.708953(excludingconnectionsestablishing)scriptstatistics:-statementlatenciesinmilliseconds:0.005\setaidrandom(1,100000*:scale)106.464SELECTabalanceFROMpgbench_accountsWHEREaid=:aid;

2、读写

pgbench-Mprepared-v-r-P1-c1-j1-T120-h127.0.0.1-p15432transactiontype:<builtin:TPC-B(sortof)>scalingfactor:1000querymode:preparednumberofclients:1numberofthreads:1duration:120snumberoftransactionsactuallyprocessed:2160latencyaverage=55.561mslatencystddev=23.515mstps=17.997435(includingconnectionsestablishing)tps=17.998340(excludingconnectionsestablishing)scriptstatistics:-statementlatenciesinmilliseconds:0.004\setaidrandom(1,100000*:scale)0.001\setbidrandom(1,1*:scale)0.001\settidrandom(1,10*:scale)0.001\setdeltarandom(-5000,5000)2.238BEGIN;2.927UPDATEpgbench_accountsSETabalance=abalance+:deltaWHEREaid=:aid;8.060SELECTabalanceFROMpgbench_accountsWHEREaid=:aid;3.537UPDATEpgbench_tellersSETtbalance=tbalance+:deltaWHEREtid=:tid;2.357UPDATEpgbench_branchesSETbbalance=bbalance+:deltaWHEREbid=:bid;3.992INSERTINTOpgbench_history(tid,bid,aid,delta,mtime)VALUES(:tid,:bid,:aid,:delta,CURRENT_TIMESTAMP);32.442END;

UPDATE,DELETE都是表级锁，使用2PC，OLTP性能无法直视。（Citus这方面就做德很好。）

《PostgreSQL sharding : citus 系列2 - TPC-H》

《PostgreSQL sharding : citus 系列1 - 多机部署（含OLTP(TPC-B)测试）》

1、citus 适合oltp多一点，AP偏少的系统（如果有复杂的OLAP需求，必须write in sql , thinking in mapreduce， 或者使用greenplum生成的执行计划，把broadcase, remotion的动作割开，使用临时表代替来实现。）。

2、greenplum适合OLAP系统。(基本无法适合OLTP)。

《PostgreSQL sharding : citus 系列3 - 窗口函数调用限制 与 破解之法》

《PostgreSQL sharding : citus 系列2 - TPC-H》

《PostgreSQL sharding : citus 系列1 - 多机部署（含OLTP(TPC-B)测试）》

《[未完待续] PostgreSQL MPP EXTENSION citus(分布式 sharding) 简明手册》

《PostgreSQL citus, Greenplum 分布式执行计划 DEBUG》

《[转载] TPCH 22条SQL语句分析》

《(TPC-H测试 SF=10,SF=200) PostgreSQL 11 vs 10 vs Deepgreen》

《TPC-H测试 - PostgreSQL 10 vs Deepgreen(Greenplum)》

《PostgreSQL 并行计算tpc-h测试和优化分析》

《Greenplum,HAWQ interval parser带来的问题 - TPCH 测试注意啦》

https://github.com/digoal/gp_tpch

原文地址：https://github.com/digoal/blog/blob/master/201809/20180903_02.md