导航：首页 > 数据库 >

Deepgreen(Greenplum) 多机部署测试 , TPC-H VS citus

发表于：2024-11-22 作者：千家信息网编辑

千家信息网最后更新 2024年11月22日，背景多机部署deepgreen，与greenplum部署方法类似。本文仅用于测试。1 环境与之前测试citus的环境一致，9台ECS虚拟机。xxx.xxx.xxx.228 digoal-citus-g

千家信息网最后更新 2024年11月22日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 .......

# 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:   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:   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这方面就做德很好。）