多机部署deepgreen，与greenplum部署方法类似。
本文仅用于测试。
与之前测试citus的环境一致，9台ECS虚拟机。
xxx.xxx.xxx.228 digoal-citus-gpdb-test001 xxx.xxx.xxx.224 digoal-citus-gpdb-test002 xxx.xxx.xxx.230 digoal-citus-gpdb-test003 xxx.xxx.xxx.231 digoal-citus-gpdb-test004 xxx.xxx.xxx.225 digoal-citus-gpdb-test005 xxx.xxx.xxx.227 digoal-citus-gpdb-test006 xxx.xxx.xxx.232 digoal-citus-gpdb-test007 xxx.xxx.xxx.226 digoal-citus-gpdb-test008 xxx.xxx.xxx.229 digoal-citus-gpdb-test009
1、配置时，请使用实际主机名
# hostname -s 例如返回： digoal-citus-gpdb-test005
2、ECS的配置
CentOS 7 x64 32 core 128G 2TB
1、新建OS普通用户，用于启动deepgreen
# useradd postgres # passwd postgres pwd .......
2、目录规划，目录权限
# mkdir /data01/dp # chown postgres:postgres /data01/dp
3、配置SSHd，允许KEY认证
# echo "PubkeyAuthentication yes" >> /etc/ssh/sshd_config # systemctl restart sshd.service
4、配置名字解析
# echo "xxx.xxx.xxx.228 digoal-citus-gpdb-test001" >>/etc/hosts # echo "xxx.xxx.xxx.224 digoal-citus-gpdb-test002" >>/etc/hosts # echo "xxx.xxx.xxx.230 digoal-citus-gpdb-test003" >>/etc/hosts # echo "xxx.xxx.xxx.231 digoal-citus-gpdb-test004" >>/etc/hosts # echo "xxx.xxx.xxx.225 digoal-citus-gpdb-test005" >>/etc/hosts # echo "xxx.xxx.xxx.227 digoal-citus-gpdb-test006" >>/etc/hosts # echo "xxx.xxx.xxx.232 digoal-citus-gpdb-test007" >>/etc/hosts # echo "xxx.xxx.xxx.226 digoal-citus-gpdb-test008" >>/etc/hosts # echo "xxx.xxx.xxx.229 digoal-citus-gpdb-test009" >>/etc/hosts
1、下载软件
# su - postgres $ wget https://s3.amazonaws.com/vitessedata/download/deepgreendb.18.08.rh7.x86_64.180825.bin
2、修改权限
$ chmod 700 deepgreendb.18.08.rh7.x86_64.180825.bin
3、安装软件
$ ./deepgreendb.18.08.rh7.x86_64.180825.bin
4、配置环境变量
$ vi ./deepgreendb/greenplum_path.sh #追加 # 使用实际目录 export MASTER_DATA_DIRECTORY=/data01/dp/dg-1 export PGDATA=$MASTER_DATA_DIRECTORY export PGHOST=127.0.0.1 export PGPORT=15432 # 使用os用户名 export PGUSER=postgres export PGPASSWORD=123 export PGDATABASE=postgres
5、使用环境变量
$ . ./deepgreendb/greenplum_path.sh
6、编写HOST文件 (9台ECS的HOSTNAME写入) （用于配置、初始化deepgreen集群）
$ vi hostfile digoal-citus-gpdb-test001 digoal-citus-gpdb-test002 digoal-citus-gpdb-test003 digoal-citus-gpdb-test004 digoal-citus-gpdb-test005 digoal-citus-gpdb-test006 digoal-citus-gpdb-test007 digoal-citus-gpdb-test008 digoal-citus-gpdb-test009
7、交换SSH KEY，配置KEY互信
$ gpssh-exkeys -f ./hostfile 输入一次 pwd.......
1、打包
$ tar -zcvf deepgreendb.18.08.180825.tar.gz deepgreendb.18.08.180825
2、拷贝到其他ECS
$ scp deepgreendb.18.08.180825.tar.gz digoal-citus-gpdb-test002:~/ $ scp deepgreendb.18.08.180825.tar.gz digoal-citus-gpdb-test003:~/ $ scp deepgreendb.18.08.180825.tar.gz digoal-citus-gpdb-test004:~/ $ scp deepgreendb.18.08.180825.tar.gz digoal-citus-gpdb-test005:~/ $ scp deepgreendb.18.08.180825.tar.gz digoal-citus-gpdb-test006:~/ $ scp deepgreendb.18.08.180825.tar.gz digoal-citus-gpdb-test007:~/ $ scp deepgreendb.18.08.180825.tar.gz digoal-citus-gpdb-test008:~/ $ scp deepgreendb.18.08.180825.tar.gz digoal-citus-gpdb-test009:~/
3、解压软件
$ gpssh -h digoal-citus-gpdb-test002 -h digoal-citus-gpdb-test003 -h digoal-citus-gpdb-test004 -h digoal-citus-gpdb-test005 -h digoal-citus-gpdb-test006 -h digoal-citus-gpdb-test007 -h digoal-citus-gpdb-test008 -h digoal-citus-gpdb-test009 => tar -zxvf deepgreendb.18.08.180825.tar.gz >/dev/null => ln -s `pwd`/deepgreendb.18.08.180825 `pwd`/deepgreendb
每个ECS上跑16个segment。数据目录
/data01/dp
hostfile里面包含9台ECS hostname，如果master不想跑segment，则把它从hostfile删掉即可。
digoal-citus-gpdb-test001 作为 master节点
1、按以上要求编写配置文件
$ vi cluster.conf ARRAY_NAME="mpp1 cluster" CLUSTER_NAME="mpp1 cluster" MACHINE_LIST_FILE=hostfile SEG_PREFIX=dg DATABASE_PREFIX=dg PORT_BASE=25432 declare -a DATA_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-test001 MASTER_DIRECTORY=/data01/dp MASTER_PORT=15432 IP_ALLOW=0.0.0.0/0 TRUSTED_SHELL=/usr/bin/ssh CHECK_POINT_SEGMENTS=32 ENCODING=UNICODE export MASTER_DATA_DIRECTORY export TRUSTED_SHELL DEFAULT_QD_MAX_CONNECT=25 QE_CONNECT_FACTOR=5
2、初始化数据库集群
gpinitsystem -c cluster.conf -h hostfile
生成集群，一共144 segments
3、参数
postgres=# select source,category,name,setting,unit from pg_settings where source<>'default' or name ~ '^gp_' or name ~ '^vitesse' order by source,category,name; source | category | name | setting | unit ----------------------+------------------------------------------------------+---------------------------------------------+----------------------------------------------------------------------------------+------ client | Reporting and Logging / What to Log | application_name | psql | command line | Client Connection Defaults / Locale and Formatting | TimeZone | PRC | command line | Client Connection Defaults / Locale and Formatting | timezone_abbreviations | Default | command line | Connections and Authentication / Connection Settings | listen_addresses | * | command line | Connections and Authentication / Connection Settings | port | 15432 | command line | Preset Options | gp_contentid | -1 | command line | Preset Options | gp_dbid | 1 | command line | Preset Options | gp_num_contents_in_cluster | 144 | command line | Preset Options | gp_standby_dbid | 0 | command line | Reporting and Logging / What to Log | log_timezone | PRC | configuration file | Append-Only Tables | max_appendonly_tables | 10000 | configuration file | Client Connection Defaults / Locale and Formatting | DateStyle | ISO, MDY | configuration file | Client Connection Defaults / Locale and Formatting | default_text_search_config | pg_catalog.english | configuration file | Client Connection Defaults / Locale and Formatting | lc_messages | en_US.utf8 | configuration file | Client Connection Defaults / Locale and Formatting | lc_monetary | en_US.utf8 | configuration file | Client Connection Defaults / Locale and Formatting | lc_numeric | en_US.utf8 | configuration file | Client Connection Defaults / Locale and Formatting | lc_time | en_US.utf8 | configuration file | Connections and Authentication / Connection Settings | max_connections | 25 | configuration file | Developer Options | gp_autostats_mode | ON_NO_STATS | configuration file | Developer Options | gp_autostats_on_change_threshold | 2147483647 | configuration file | Developer Options | gp_backup_directIO | off | configuration file | Developer Options | gp_backup_directIO_read_chunk_mb | 20 | configuration file | Greenplum Database / Array Tuning | gp_connections_per_thread | 0 | configuration file | Greenplum Database / Array Tuning | gp_interconnect_type | UDPIFC | configuration file | Greenplum Database / Array Tuning | gp_segment_connect_timeout | 600 | s configuration file | Greenplum Database / Array Tuning | gp_xliw_agent_enable | off | configuration file | Greenplum Database / Array Tuning | gp_xliw_log_level | 8 | configuration file | Greenplum Database / Array Tuning | gp_xliw_unix_path | /tmp/xliwagent.socket | configuration file | Greenplum Database / Array Tuning | gp_xliw_worker_num | 2 | configuration file | Reporting and Logging / What to Log | log_autostats | off | configuration file | Reporting and Logging / What to Log | log_statement | all | configuration file | Resource Usage | max_prepared_transactions | 250 | configuration file | Resource Usage / Free Space Map | max_fsm_pages | 200000 | configuration file | Resource Usage / Memory | gp_vmem_protect_limit | 8192 | configuration file | Resource Usage / Memory | shared_buffers | 4000 | 32kB configuration file | Resource Usage / Resources Management | gp_resqueue_memory_policy | eager_free | configuration file | Statistics / ANALYZE Database Contents | optimizer_analyze_root_partition | on | default | Append-Only Tables | gp_appendonly_compaction_threshold | 10 | default | Append-Only Tables | gp_default_storage_options | appendonly=false,blocksize=32768,compresstype=none,checksum=true,orientation=row | default | Client Connection Defaults / Other Defaults | gp_command_count | 26 | default | Client Connection Defaults / Other Defaults | gp_connection_send_timeout | 3600 | default | Client Connection Defaults / Other Defaults | gp_role | dispatch | default | Client Connection Defaults / Other Defaults | gp_session_id | 505 | default | Client Connection Defaults / Other Defaults | gp_vmem_idle_resource_timeout | 18000 | ms default | Customized Options | gp_hadoop_home | | default | Customized Options | gp_hadoop_target_version | hadoop | default | Customized Options | vitesse.copy_dump | | default | Customized Options | vitesse_cpuname | | default | Customized Options | vitesse.dbgbreak_mask | 0 | default | Customized Options | vitesse.enable | on | default | Customized Options | vitesse_explain_verbosity | 0 | default | Customized Options | vitesse.explain_verbosity | 0 | default | Customized Options | vitesse.exttab_jit | on | default | Customized Options | vitesse_jit_time | 0 | default | Customized Options | vitesse.jit_time | 0 | default | Customized Options | vitesse_log_level | 0 | default | Customized Options | vitesse.log_level | 0 | default | Customized Options | vitesse.motion_opt | off | default | Customized Options | vitesse.partsel_opt | off | default | Customized Options | vitesse_phi_home | | default | Customized Options | vitesse_plan_cost | 72 | default | Customized Options | vitesse.plan_cost | 72 | default | Customized Options | vitesse.print_tree | off | default | Customized Options | vitesse_rev | 7615c3b | default | Customized Options | vitesse.rev | 7615c3b | default | Customized Options | vitesse.seqscan_using_pg_pool | off | default | Customized Options | vitesse.spill_max | 0 | default | Customized Options | vitesse.stack_trace | 0 | default | Customized Options | vitesse.support_dump | | default | Customized Options | vitesse.threshold | 100 | default | Customized Options | vitesse.use_modified_utf8 | off | default | Customized Options | vitesse_version | Deepgreen DB 18.08 [rev 7615c3b on 2018-08-25] | default | Customized Options | vitesse.version | Deepgreen DB 18.08 [rev 7615c3b on 2018-08-25] | default | Customized Options | vitesse_xdrive_port | 6868 | default | Customized Options | vitesse.xdrive_port | 6868 | default | Customized Options | vitesse.xliw | 0 | default | Developer Options | gp_autostats_mode_in_functions | NONE | default | Developer Options | gp_debug_linger | 0 | s default | Developer Options | gp_reraise_signal | on | default | Developer Options | gp_workfile_compress_algorithm | none | default | Developer Options | vitesse_dbgbreak_mask | 0 | default | Developer Options | vitesse_stack_trace | 0 | default | Developer Options | vitesse_use_modified_utf8 | off | default | External Tables | gp_external_enable_exec | on | default | External Tables | gp_external_enable_filter_pushdown | off | default | External Tables | gp_external_max_segs | 64 | default | External Tables | gp_initial_bad_row_limit | 1000 | default | External Tables | gp_max_csv_line_length | 1048576 | default | GPDB Error Handling | gp_reject_percent_threshold | 300 | default | Greenplum Database / Array Tuning | gp_cached_segworkers_threshold | 5 | default | Greenplum Database / Array Tuning | gp_filerep_ct_batch_size | 65536 | default | Greenplum Database / Array Tuning | gp_filerep_tcp_keepalives_count | 2 | default | Greenplum Database / Array Tuning | gp_filerep_tcp_keepalives_idle | 60 | s default | Greenplum Database / Array Tuning | gp_filerep_tcp_keepalives_interval | 30 | s default | Greenplum Database / Array Tuning | gp_fts_probe_interval | 60 | s default | Greenplum Database / Array Tuning | gp_fts_probe_threadcount | 16 | default | Greenplum Database / Array Tuning | gp_fts_probe_timeout | 20 | s default | Greenplum Database / Array Tuning | gp_hashjoin_tuples_per_bucket | 5 | default | Greenplum Database / Array Tuning | gp_interconnect_cache_future_packets | on | default | Greenplum Database / Array Tuning | gp_interconnect_debug_retry_interval | 10 | default | Greenplum Database / Array Tuning | gp_interconnect_deepmesh_path | /tmp/dmagent.socket | default | Greenplum Database / Array Tuning | gp_interconnect_default_rtt | 20 | ms default | Greenplum Database / Array Tuning | gp_interconnect_fc_method | LOSS | default | Greenplum Database / Array Tuning | gp_interconnect_hash_multiplier | 2 | default | Greenplum Database / Array Tuning | gp_interconnect_min_retries_before_timeout | 100 | default | Greenplum Database / Array Tuning | gp_interconnect_min_rto | 20 | ms default | Greenplum Database / Array Tuning | gp_interconnect_queue_depth | 4 | default | Greenplum Database / Array Tuning | gp_interconnect_setup_timeout | 7200 | s default | Greenplum Database / Array Tuning | gp_interconnect_snd_queue_depth | 2 | default | Greenplum Database / Array Tuning | gp_interconnect_tcp_listener_backlog | 128 | default | Greenplum Database / Array Tuning | gp_interconnect_timer_checking_period | 20 | ms default | Greenplum Database / Array Tuning | gp_interconnect_timer_period | 5 | ms default | Greenplum Database / Array Tuning | gp_interconnect_transmit_timeout | 3600 | s default | Greenplum Database / Array Tuning | gp_max_packet_size | 8192 | default | Greenplum Database / Array Tuning | gp_udp_bufsize_k | 0 | default | Greenplum Database / Worker Process Identity | gp_master_addr | 127.0.0.1 | default | Preset Options | gp_max_partition_level | 0 | default | Preset Options | gp_max_slices | 0 | default | Preset Options | gp_server_version | 5.10.2+7615c3b build ga | default | Preset Options | gp_server_version_num | 51002 | default | Query Tuning | vitesse_threshold | 100 | default | Query Tuning / Other Planner Options | gp_enable_fast_sri | on | default | Query Tuning / Other Planner Options | gp_enable_predicate_propagation | on | default | Query Tuning / Other Planner Options | gp_workfile_checksumming | on | default | Query Tuning / Other Planner Options | vitesse_enable | on | default | Query Tuning / Planner Cost Constants | gp_motion_cost_per_row | 0 | default | Query Tuning / Planner Cost Constants | gp_segments_for_planner | 0 | default | Query Tuning / Planner Method Configuration | gp_adjust_selectivity_for_outerjoins | on | default | Query Tuning / Planner Method Configuration | gp_dynamic_partition_pruning | on | default | Query Tuning / Planner Method Configuration | gp_enable_agg_distinct | on | default | Query Tuning / Planner Method Configuration | gp_enable_agg_distinct_pruning | on | default | Query Tuning / Planner Method Configuration | gp_enable_direct_dispatch | on | default | Query Tuning / Planner Method Configuration | gp_enable_fallback_plan | on | default | Query Tuning / Planner Method Configuration | gp_enable_groupext_distinct_gather | on | default | Query Tuning / Planner Method Configuration | gp_enable_groupext_distinct_pruning | on | default | Query Tuning / Planner Method Configuration | gp_enable_multiphase_agg | on | default | Query Tuning / Planner Method Configuration | gp_enable_preunique | on | default | Query Tuning / Planner Method Configuration | gp_enable_relsize_collection | off | default | Query Tuning / Planner Method Configuration | gp_enable_sequential_window_plans | on | default | Query Tuning / Planner Method Configuration | gp_enable_sort_distinct | on | default | Query Tuning / Planner Method Configuration | gp_enable_sort_limit | on | default | Query Tuning / Planner Method Configuration | gp_idf_deduplicate | auto | default | Query Tuning / Planner Method Configuration | gp_statistics_pullup_from_child_partition | on | default | Query Tuning / Planner Method Configuration | gp_statistics_use_fkeys | on | default | Reporting and Logging | gp_email_connect_avoid_duration | 7200 | default | Reporting and Logging | gp_email_connect_failures | 5 | default | Reporting and Logging | gp_email_connect_timeout | 15 | default | Reporting and Logging | gp_email_from | | default | Reporting and Logging | gp_email_smtp_password | | default | Reporting and Logging | gp_email_smtp_server | localhost:25 | default | Reporting and Logging | gp_email_smtp_userid | | default | Reporting and Logging | gp_email_to | | default | Reporting and Logging | gp_snmp_community | public | default | Reporting and Logging | gp_snmp_monitor_address | | default | Reporting and Logging | gp_snmp_use_inform_or_trap | trap | default | Reporting and Logging / What to Log | gp_gpperfmon_send_interval | 1 | default | Reporting and Logging / Where to Log | gp_log_format | csv | default | Resource Usage | gp_resource_group_bypass | off | default | Resource Usage | gp_resource_group_cpu_limit | 0.9 | default | Resource Usage | gp_resource_group_cpu_priority | 10 | default | Resource Usage | gp_resource_group_memory_limit | 0.7 | default | Resource Usage | gp_resource_manager | queue | default | Resource Usage | gp_safefswritesize | 0 | default | Resource Usage | gp_subtrans_warn_limit | 16777216 | default | Resource Usage | gp_workfile_limit_files_per_query | 100000 | default | Resource Usage | gp_workfile_limit_per_query | 0 | default | Resource Usage | gp_workfile_limit_per_segment | 0 | default | Resource Usage / Kernel Resources | gp_set_proc_affinity | off | default | Resource Usage / Memory | gp_max_databases | 16 | default | Resource Usage / Memory | gp_max_filespaces | 8 | default | Resource Usage / Memory | gp_max_local_distributed_cache | 1024 | default | Resource Usage / Memory | gp_max_plan_size | 0 | kB default | Resource Usage / Memory | gp_max_tablespaces | 16 | default | Resource Usage / Memory | gp_vmem_protect_segworker_cache_limit | 500 | default | Resource Usage / Resources Management | gp_resgroup_memory_policy | eager_free | default | Resource Usage / Resources Management | gp_resqueue_priority | off | default | Resource Usage / Resources Management | gp_resqueue_priority_cpucores_per_segment | 4 | default | Resource Usage / Resources Management | gp_resqueue_priority_sweeper_interval | 1000 | default | Resource Usage / Resources Management | vitesse_index_mem | 0 | default | Resource Usage / Resources Management | vitesse_spill_max | 0 | default | Resource Usage / Resources Management | vitesse_spillz | 1 | default | Statistics / ANALYZE Database Contents | 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 | kB default | Version and Platform Compatibility | gp_create_table_random_default_distribution | off | default | Version and Platform Compatibility | gp_enable_exchange_default_partition | off | environment variable | Resource Usage / Memory | max_stack_depth | 2048 | kB override | Client Connection Defaults / Locale and Formatting | lc_collate | en_US.utf8 | override | Client Connection Defaults / Locale and Formatting | lc_ctype | en_US.utf8 | override | Client Connection Defaults / Locale and Formatting | server_encoding | UTF8 | override | Client Connection Defaults / Statement Behavior | transaction_isolation | read committed | override | Client Connection Defaults / Statement Behavior | transaction_read_only | off | override | Preset Options | data_checksums | on | (194 rows)
1、使用gp_tpch测试，200G数据集。TPC-H
# su - postgres
$ git clone https://github.com/digoal/gp_tpch
$ cd gp_tpch
$ make
$ ln -s `pwd` /tmp/dss-data
$ ./dbgen -s 200
$ for i in `ls *.tbl`; do sed 's/|$//' $i > ${i/tbl/csv}; echo $i; done;
$ SF=200
$ mkdir dss/queries
$ for q in `seq 1 22`
do
DSS_QUERY=dss/templates ./qgen -s $SF $q > dss/queries/$q.sql
sed 's/^select/explain select/' dss/queries/$q.sql > dss/queries/$q.explain.sql
done
模板
$ ./tpch.sh ./results_gpdb ip port tpch-db tpch-user password {row|column|redshift|pg|pg10|citus}
$ ./tpch.sh ./results_gpdb 127.0.0.1 15432 postgres postgres pwd column
2、测试结果（比citus好很多）
2018-09-03 17:14:24 [1535966064] : query 1 finished OK (3 seconds) 2018-09-03 17:14:28 [1535966068] : query 2 finished OK (4 seconds) 2018-09-03 17:14:36 [1535966076] : query 3 finished OK (7 seconds) 2018-09-03 17:14:38 [1535966078] : query 4 finished OK (2 seconds) 2018-09-03 17:14:56 [1535966096] : query 5 finished OK (17 seconds) 2018-09-03 17:14:57 [1535966097] : query 6 finished OK (0 seconds) 2018-09-03 17:15:20 [1535966120] : query 7 finished OK (23 seconds) 2018-09-03 17:15:26 [1535966126] : query 8 finished OK (5 seconds) 2018-09-03 17:15:39 [1535966139] : query 9 finished OK (12 seconds) 2018-09-03 17:15:43 [1535966143] : query 10 finished OK (4 seconds) 2018-09-03 17:15:44 [1535966144] : query 11 finished OK (1 seconds) 2018-09-03 17:15:48 [1535966148] : query 12 finished OK (3 seconds) 2018-09-03 17:15:53 [1535966153] : query 13 finished OK (4 seconds) 2018-09-03 17:15:58 [1535966158] : query 14 finished OK (5 seconds) 2018-09-03 17:16:05 [1535966165] : query 15 finished OK (6 seconds) 2018-09-03 17:16:11 [1535966171] : query 16 finished OK (6 seconds) 2018-09-03 17:16:32 [1535966192] : query 17 finished OK (20 seconds) 2018-09-03 17:16:38 [1535966198] : query 18 finished OK (5 seconds) 2018-09-03 17:16:41 [1535966201] : query 19 finished OK (3 seconds) 2018-09-03 17:16:47 [1535966207] : query 20 finished OK (5 seconds) 2018-09-03 17:16:57 [1535966217] : query 21 finished OK (9 seconds) 2018-09-03 17:17:01 [1535966221] : query 22 finished OK (4 seconds)
pgbench -i -s 1000 -h 127.0.0.1 -p 15432
1、只读
pgbench -M prepared -v -r -P 1 -c 20 -j 20 -T 120 -h 127.0.0.1 -p 15432 -S transaction type: <builtin: select only> scaling factor: 1000 query mode: prepared number of clients: 20 number of threads: 20 duration: 120 s number of transactions actually processed: 22557 latency average = 106.488 ms latency stddev = 38.781 ms tps = 187.690045 (including connections establishing) tps = 187.708953 (excluding connections establishing) script statistics: - statement latencies in milliseconds: 0.005 \set aid random(1, 100000 * :scale) 106.464 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
2、读写
pgbench -M prepared -v -r -P 1 -c 1 -j 1 -T 120 -h 127.0.0.1 -p 15432 transaction type: <builtin: TPC-B (sort of)> scaling factor: 1000 query mode: prepared number of clients: 1 number of threads: 1 duration: 120 s number of transactions actually processed: 2160 latency average = 55.561 ms latency stddev = 23.515 ms tps = 17.997435 (including connections establishing) tps = 17.998340 (excluding connections establishing) script statistics: - statement latencies in milliseconds: 0.004 \set aid random(1, 100000 * :scale) 0.001 \set bid random(1, 1 * :scale) 0.001 \set tid random(1, 10 * :scale) 0.001 \set delta random(-5000, 5000) 2.238 BEGIN; 2.927 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid; 8.060 SELECT abalance FROM pgbench_accounts WHERE aid = :aid; 3.537 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid; 2.357 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid; 3.992 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP); 32.442 END;
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
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
