Deepgreen(Greenplum) 多机部署测试 , TPC-H VS citus
背景
多机部署deepgreen，与greenplum部署方法类似。
本文仅用于测试。
1 环境
与之前测试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
2 配置OS (所有ECS)
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
3 安装deepgreen软件 (master ECS)
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.......
4 拷贝软件到其他ECS （master ECS）
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
5 初始化deepgreen 数据库集群 (master ECS)
每个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)
6 sf=200 tpc-h 测试
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)
7 1亿 tpc-b
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这方面就做德很好。）
greenplum VS 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》
《(TPC-H测试 SF=10,SF=200) PostgreSQL 11 vs 10 vs Deepgreen》
《TPC-H测试 - PostgreSQL 10 vs Deepgreen(Greenplum)》
《Greenplum,HAWQ interval parser带来的问题 - TPCH 测试注意啦》
https://github.com/digoal/gp_tpch