资源池化POC性能测试指南

内容简介

本章节主要介绍资源池化架构下TPCC的极限性能的测试方法，以及为达到最佳tpmC性能所依赖的相关GUC参数调优和OS调优。

硬件环境

  • 计算 目前最佳TPCC结果是使用2个2路鲲鹏服务器（128C, 512G-1024G内存） OS版本为openEuler 20.03 LTS
  • 存储 使用企业集中式存储，目前通过验证的硬件型号为OceanStor Dorado 5300/5500/18000/16000 V6系列
  • 网络 鲲鹏配套的Hi1822（如果使用ROCE网络，网卡当前仅支持Mellox CX5）

软件环境

  • 数据库：openGauss 5.0.1 正式发布版本
  • TPCC客户端：使用tidb优化过的BenchmarkSQL 5.0（<https://github.com/pingcap/benchmarksql>）
  • 依赖包
所需软件 建议版本
numactl –
jdk 1.8.0-242
ant 1.10.5
htop –

主机BIOS和OS部分调优

  1. 主机BIOS参数设置

  2. BIOS>Advanced>MISC Config，配置Support Smmu为Disabled

  3. BIOS>Advanced>MISC Config，配置CPU Prefetching Configuration为Disabled
  4. BIOS>Advanced>Memory Config，配置Die Interleaving为Disable
  5. BIOS>Advanced>Performance Config，配置Power Policy为Performance

  6. 操作系统优化

  7. 修改操作系统内核PAGESIZE为64KB（默认值）

  8. 关闭irqbalance

    1. systemctl stop irqbalance - 调整numa_balance

    1. echo 0 > /proc/sys/kernel/numa_balancing - 调整透明大页

    1. echo 'never' > /sys/kernel/mm/transparent_hugepage/enabled 2. echo 'never' > /sys/kernel/mm/transparent_hugepage/defrag 3. 网络配置

  9. 网络平面说明

一般情况下，数据库集群的网络可以分为3个平面：存储网络，计算节点间网络，业务侧网络

  • 存储网络：计算节点与磁阵之间连接的网络，建议网卡带宽不低于10GE
  • 计算网络：计算节点之间连接的网络，建议网卡带宽不低于10GE
  • 业务侧网络：计算节点与业务客户端（或TPCC压测客户端）的网络，建议网卡带宽不低于10GE

性能测试场景下，需要对存储网络使用的网卡进行中断绑定CPU核的设置（因为存储IO流量最大，如果是主备同时压测，那么计算网络也会有一定的流量），计算网络可以按需要设置 如果网卡配置中使用了BOND或者VxLAN，需要对实际的物理网卡做中断绑定CPU核的设置 如果部分网络平面重合，比如存储网络和计算网络共用，或者计算网络和业务侧网络共用，那么也需要对实际的物理网卡做中断绑定CPU核的设置 - 网卡多中断队列设置

1. ##此处enp4s0为存储网络的物理网卡 2. [root@localhost]# ethtool -L enp4s0 combined 16 - 中断调优开启tso，lro，gro，gso特性。

1. ethtool -K enp4s0 tso on 2. ethtool -K enp4s0 lro on 3. ethtool -K enp4s0 gro on 4. ethtool -K enp4s0 gso on - 网卡固件确认与更新

1. [root@localhost]# ethtool -i enp4s0 2. driver: hinic 3. version: 2.3.2.11 4. firmware-version: 2.4.1.0 5. expansion-rom-version: 6. bus-info: 0000:04:00.0 7. supports-statistics: yes 8. supports-test: yes 9. supports-eeprom-access: no 10. supports-register-dump: no 11. supports-priv-flags: no

网卡固件版本应为2.4.1.0

安装数据库

安装一主一备openGauss资源池化环境。参考资源池化OM安装部署。

磁盘IO队列调度机制设置

  • 针对磁盘IO队列调度机制设置，此处的sb*表示使用的磁阵的LUN映射在主机侧的块设备盘符。由于安装过程中OM会修改该配置，所以需要在安装完成后执行。

1. echo none > /sys/block/sb*/queue/scheduler

网卡中断绑核

首先获取网卡对应的中断号

1. [root@localhost]# cat /proc/interrupts | grep enp4s0 | awk '{print $1$NF}'
2. 405:enp4s0_qp0
3. 406:enp4s0_qp1
4. 407:enp4s0_qp2
5. 408:enp4s0_qp3
6. 409:enp4s0_qp4
7. 410:enp4s0_qp5
8. 411:enp4s0_qp6
9. 412:enp4s0_qp7
10. 413:enp4s0_qp8
11. 414:enp4s0_qp9
12. 415:enp4s0_qp10
13. 416:enp4s0_qp11
14. 417:enp4s0_qp12
15. 418:enp4s0_qp13
16. 419:enp4s0_qp14
17. 420:enp4s0_qp15

绑定对应的CPU核

1. [root@localhost]# echo 28 >  /proc/irq/405/smp_affinity_list
2. [root@localhost]# echo 29 >  /proc/irq/406/smp_affinity_list
3. [root@localhost]# echo 30 >  /proc/irq/407/smp_affinity_list
4. [root@localhost]# echo 31 >  /proc/irq/408/smp_affinity_list
5. [root@localhost]# echo 60 >  /proc/irq/409/smp_affinity_list
6. [root@localhost]# echo 61 >  /proc/irq/410/smp_affinity_list
7. [root@localhost]# echo 62 >  /proc/irq/411/smp_affinity_list
8. [root@localhost]# echo 63 >  /proc/irq/412/smp_affinity_list
9. [root@localhost]# echo 92 >  /proc/irq/413/smp_affinity_list
10. [root@localhost]# echo 93 >  /proc/irq/414/smp_affinity_list
11. [root@localhost]# echo 94 >  /proc/irq/415/smp_affinity_list
12. [root@localhost]# echo 95 >  /proc/irq/416/smp_affinity_list
13. [root@localhost]# echo 124 > /proc/irq/417/smp_affinity_list
14. [root@localhost]# echo 125 > /proc/irq/418/smp_affinity_list
15. [root@localhost]# echo 126 > /proc/irq/419/smp_affinity_list
16. [root@localhost]# echo 127 > /proc/irq/420/smp_affinity_list

准备测试工具

  • 准备TPCC测试客户端

  • 下载TPCC标准测试工具BenchmarkSQL5.0。

    1. [root@localhost]# git clone -b 5.0-mysql-support-opt-2.1 https://github.com/pingcap/benchmarksql.git 2. Cloning into 'tpcc-mysql'... 3. remote: Enumerating objects: 106, done. 4. remote: Total 106 (delta 0), reused 0 (delta 0), pack-reused 106 5. Receiving objects: 100% (106/106), 64.46 KiB | 225.00 KiB/s, done. 6. Resolving deltas: 100% (30/30), done. 2. 下载安装JDK和ant依赖包。

    1. [root@localhost]# rpm -ivh ant-1.10.5-6.oe1.noarch.rpm jdk-8u281-linux-aarch64.rpm --force --nodeps 2. warning: ant-1.10.5-6.oe1.noarch.rpm: Header V3 RSA/SHA1 Signature, key ID b25e7f66: NOKEY 3. warning: jdk-8u281-linux-aarch64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY 4. Verifying... ################################# [100%] 5. Preparing... ################################# [100%] 6. Updating / installing... 7. 1:jdk1.8-2000:1.8.0_281-fcs ################################# [ 50%] 8. Unpacking JAR files... 9. tools.jar... 10. rt.jar... 11. jsse.jar... 12. charsets.jar... 13. localedata.jar... 14. 2:ant-0:1.10.5-6.oe1 ################################# [100%] 3. 配置JAVA环境变量

    1. [root@localhost]# tail -3 /root/.bashrc 2. export JAVA_HOME=/usr/java/jdk1.8.0_281-aarch64 3. export PATH=$JAVA_HOME/bin:$PATH 4. export CLASSPATH=.:JAVA_HOME/lib:$BENCHMARKSQLPATH/run/ojdbc7.jar 4. 在BenchmarkSQL所在目录下输入ant命令进行编译，编译成功后会生成build和dist两个目录。

    ``` 1. [root@localhost]# pwd 2. /tmp/benchmarksql-5.0-mysql-support-opt-2.1 3. [root@localhost]# ant 4. Buildfile: /tmp/benchmarksql-5.0-mysql-support-opt-2.1/build.xml

    1. init:

    2. [mkdir] Created dir: /tmp/benchmarksql-5.0-mysql-support-opt-2.1/build

    3. compile:

    4. [javac] Compiling 12 source files to /tmp/benchmarksql-5.0-mysql-support-opt-2.1/build

    5. dist:

    6. [mkdir] Created dir: /tmp/benchmarksql-5.0-mysql-support-opt-2.1/dist

    7. [jar] Building jar: /tmp/benchmarksql-5.0-mysql-support-opt-2.1/dist/BenchmarkSQL-5.0.jar

    8. BUILD SUCCESSFUL

    9. Total time: 1 second ```
    10. 下载对应的JDBC驱动至BenchmarkSQL目录的lib/postgresql文件夹，并解压，删除自带的JDBC驱动。

    1. [root@localhost]# pwd 2. /tmp/benchmarksql-5.0-mysql-support-opt-2.1/lib/postgres/ 3. [root@localhost]# ls 4. openGauss-5.1.0-JDBC.tar.gz postgresql-9.3-1102.jdbc41.jar 5. [root@localhost]# rm -f postgresql-9.3-1102.jdbc41.jar 6. [root@localhost]# tar -xf openGauss-5.1.0-JDBC.tar.gz 7. [root@localhost]# ls 8. openGauss-5.1.0-JDBC.tar.gz postgresql.jar 6. 调整性能测试相关配置 - 连接串

    1. conn=jdbc:opengauss://20.0.0.103:26000/tpcc?prepareThreshold=1&batchMode=on&fetchsize=10&loggerLevel=off - 修改文件内容将数据分散，调整FILLFACTOR，数据分区。

    ``` 1. [root@localhost]# ls benchmarksql-5.0-mysql-support-opt-2.1/run/sql.common/tableCreates.sql 2. benchmarksql-5.0-mysql-support-opt-2.1/run/sql.common/tableCreates.sql 3. [root@localhost]# cat tableCreates.sql 4. CREATE TABLESPACE example2 relative location 'tablespace2'; 5. CREATE TABLESPACE example3 relative location 'tablespace3';

    1. create table bmsql_config (
    2. cfg_name varchar(30),
    3. cfg_value varchar(50)

    4. );

    5. create table bmsql_warehouse (

    6. w_id integer not null,
    7. w_ytd decimal(12,2),
    8. w_tax decimal(4,4),
    9. w_name varchar(10),
    10. w_street_1 varchar(20),
    11. w_street_2 varchar(20),
    12. w_city varchar(20),
    13. w_state char(2),
    14. w_zip char(9)

    15. ) WITH (FILLFACTOR=80);

    16. create table bmsql_district (

    17. d_w_id integer not null,
    18. d_id integer not null,
    19. d_ytd decimal(12,2),
    20. d_tax decimal(4,4),
    21. d_next_o_id integer,
    22. d_name varchar(10),
    23. d_street_1 varchar(20),
    24. d_street_2 varchar(20),
    25. d_city varchar(20),
    26. d_state char(2),
    27. d_zip char(9)

    28. ) WITH (FILLFACTOR=80);

    29. create table bmsql_customer (

    30. c_w_id integer not null,
    31. c_d_id integer not null,
    32. c_id integer not null,
    33. c_discount decimal(4,4),
    34. c_credit char(2),
    35. c_last varchar(16),
    36. c_first varchar(16),
    37. c_credit_lim decimal(12,2),
    38. c_balance decimal(12,2),
    39. c_ytd_payment decimal(12,2),
    40. c_payment_cnt integer,
    41. c_delivery_cnt integer,
    42. c_street_1 varchar(20),
    43. c_street_2 varchar(20),
    44. c_city varchar(20),
    45. c_state char(2),
    46. c_zip char(9),
    47. c_phone char(16),
    48. c_since timestamp,
    49. c_middle char(2),
    50. c_data varchar(500)

    51. ) WITH (FILLFACTOR=80) tablespace example2;

    52. create sequence bmsql_hist_id_seq;

    53. create table bmsql_history (

    54. hist_id integer,
    55. h_c_id integer,
    56. h_c_d_id integer,
    57. h_c_w_id integer,
    58. h_d_id integer,
    59. h_w_id integer,
    60. h_date timestamp,
    61. h_amount decimal(6,2),
    62. h_data varchar(24)

    63. ) WITH (FILLFACTOR=80);

    64. create table bmsql_new_order (

    65. no_w_id integer not null,
    66. no_d_id integer not null,
    67. no_o_id integer not null

    68. ) WITH (FILLFACTOR=80);

    69. create table bmsql_oorder (

    70. o_w_id integer not null,
    71. o_d_id integer not null,
    72. o_id integer not null,
    73. o_c_id integer,
    74. o_carrier_id integer,
    75. o_ol_cnt integer,
    76. o_all_local integer,
    77. o_entry_d timestamp

    78. ) WITH (FILLFACTOR=80);

    79. create table bmsql_order_line (

    80. ol_w_id integer not null,
    81. ol_d_id integer not null,
    82. ol_o_id integer not null,
    83. ol_number integer not null,
    84. ol_i_id integer not null,
    85. ol_delivery_d timestamp,
    86. ol_amount decimal(6,2),
    87. ol_supply_w_id integer,
    88. ol_quantity integer,
    89. ol_dist_info char(24)

    90. ) WITH (FILLFACTOR=80);

    91. create table bmsql_item (

    92. i_id integer not null,
    93. i_name varchar(24),
    94. i_price decimal(5,2),
    95. i_data varchar(50),
    96. i_im_id integer

    97. );

    98. create table bmsql_stock (

    99. s_w_id integer not null,
    100. s_i_id integer not null,
    101. s_quantity integer,
    102. s_ytd integer,
    103. s_order_cnt integer,
    104. s_remote_cnt integer,
    105. s_data varchar(50),
    106. s_dist_01 char(24),
    107. s_dist_02 char(24),
    108. s_dist_03 char(24),
    109. s_dist_04 char(24),
    110. s_dist_05 char(24),
    111. s_dist_06 char(24),
    112. s_dist_07 char(24),
    113. s_dist_08 char(24),
    114. s_dist_09 char(24),
    115. s_dist_10 char(24)
    116. ) WITH (FILLFACTOR=80) tablespace example3; ```
    117. 准备sysbench客户端

  • 下载sysbench测试工具。

    1. [root@localhost]# git clone https://gitee.com/mirrors/sysbench.git 2. 切换到1.0.18分支，然后执行编译。

    1. [root@localhost]# cd sysbench 2. [root@localhost]# git checkout 1.0.18 -b test_1.0.18 3. [root@localhost]# ./autogen.sh 4. ##如果configure命令报错，需要根据报错信息安装相应的三方依赖，一般情况下使用yum install postgresql-devel和yum install postgresql-libs即可 5. [root@localhost]# ./configure --without-mysql --with-pgsql 6. [root@localhost]# make -sj 7. [root@localhost]# make install -sj

计算节点数据库进程绑核

  • 计算节点上安装numactl

1. yum install numactl -y - 计算节点数据库进程绑核 关闭集群

1. [omm@localhost]$ cm_ctl stop

替换启动数据库节点命令：

1. ## $GAUSSHOME为数据库二进制安装的目录 2. [omm@localhost]$ sed -i 's/nohup/nohup numactl -C 1-28,32-60,64-92,96-124/' $GAUSSHOME/bin/dms_contrl.sh

设置性能测试数据库参数（极限性能测试使用）

  • 切换到数据库安装用户下面执行如下命令

1. gs_guc set -N all -I all -h "host all all 0.0.0.0/0 sha256" 2. gs_guc set -N all -I all -c "listen_addresses ='*'" 3. gs_guc set -N all -I all -c "max_connections = 4096" 4. gs_guc set -N all -I all -c "remote_read_mode = non_authentication" 5. gs_guc set -N all -I all -c "allow_concurrent_tuple_update = true" 6. gs_guc set -N all -I all -c "audit_enabled = off" 7. gs_guc set -N all -I all -c "cstore_buffers = 16MB" 8. gs_guc set -N all -I all -c "enable_alarm = off" 9. gs_guc set -N all -I all -c "enable_codegen = false" 10. gs_guc set -N all -I all -c "enable_data_replicate = off" 11. gs_guc set -N all -I all -c "full_page_writes = off" 12. gs_guc set -N all -I all -c "max_files_per_process = 100000" 13. gs_guc set -N all -I all -c "max_prepared_transactions = 2048" 14. gs_guc set -N all -I all -c "max_process_memory = 500GB" 15. gs_guc set -N all -I all -c "shared_buffers = 300GB" 16. gs_guc set -N all -I all -c "use_workload_manager = off" 17. gs_guc set -N all -I all -c "wal_buffers = 1GB" 18. gs_guc set -N all -I all -c "work_mem = 1MB" 19. gs_guc set -N all -I all -c "transaction_isolation = 'read committed'" 20. gs_guc set -N all -I all -c "default_transaction_isolation = 'read committed'" 21. gs_guc set -N all -I all -c "synchronous_commit = on" 22. gs_guc set -N all -I all -c "fsync = on" 23. gs_guc set -N all -I all -c "maintenance_work_mem = 2GB" 24. gs_guc set -N all -I all -c "vacuum_cost_limit = 10000" 25. gs_guc set -N all -I all -c "autovacuum = on" 26. gs_guc set -N all -I all -c "autovacuum_mode = vacuum" 27. gs_guc set -N all -I all -c "autovacuum_max_workers = 20" 28. gs_guc set -N all -I all -c "autovacuum_naptime = 5s" 29. gs_guc set -N all -I all -c "autovacuum_vacuum_cost_delay = 10" 30. gs_guc set -N all -I all -c "update_lockwait_timeout = 20min" 31. gs_guc set -N all -I all -c "enable_mergejoin = off" 32. gs_guc set -N all -I all -c "enable_nestloop = off" 33. gs_guc set -N all -I all -c "enable_hashjoin = off" 34. gs_guc set -N all -I all -c "enable_material = off" 35. gs_guc set -N all -I all -c "wal_log_hints = off" 36. gs_guc set -N all -I all -c "log_duration = off" 37. gs_guc set -N all -I all -c "checkpoint_timeout = 15min" 38. gs_guc set -N all -I all -c "autovacuum_vacuum_scale_factor = 0.1" 39. gs_guc set -N all -I all -c "autovacuum_analyze_scale_factor = 0.02" 40. gs_guc set -N all -I all -c "enable_save_datachanged_timestamp = false" 41. gs_guc set -N all -I all -c "enable_double_write = off" 42. gs_guc set -N all -I all -c "enable_incremental_checkpoint = on" 43. gs_guc set -N all -I all -c "enable_opfusion = on" 44. gs_guc set -N all -I all -c "advance_xlog_file_num = 5" 45. gs_guc set -N all -I all -c "track_activities = off" 46. gs_guc set -N all -I all -c "enable_instr_track_wait = off" 47. gs_guc set -N all -I all -c "enable_instr_rt_percentile = off" 48. gs_guc set -N all -I all -c "track_counts = off" 49. gs_guc set -N all -I all -c "track_sql_count = off" 50. gs_guc set -N all -I all -c "enable_instr_cpu_timer = off" 51. gs_guc set -N all -I all -c "plog_merge_age = 0" 52. gs_guc set -N all -I all -c "session_timeout = 0" 53. gs_guc set -N all -I all -c "enable_instance_metric_persistent = off" 54. gs_guc set -N all -I all -c "enable_logical_io_statistics = off" 55. gs_guc set -N all -I all -c "enable_page_lsn_check = off" 56. gs_guc set -N all -I all -c "enable_user_metric_persistent = off" 57. gs_guc set -N all -I all -c "enable_xlog_prune = off" 58. gs_guc set -N all -I all -c "enable_resource_track = off" 59. gs_guc set -N all -I all -c "instr_unique_sql_count=0" 60. gs_guc set -N all -I all -c "remote_read_mode=non_authentication" 61. gs_guc set -N all -I all -c "wal_level = archive" 62. gs_guc set -N all -I all -c "hot_standby = off" 63. gs_guc set -N all -I all -c "hot_standby_feedback = off" 64. gs_guc set -N all -I all -c "client_min_messages = ERROR" 65. gs_guc set -N all -I all -c "log_min_messages = FATAL" 66. gs_guc set -N all -I all -c "enable_asp = off" 67. gs_guc set -N all -I all -c "enable_bbox_dump = off" 68. gs_guc set -N all -I all -c "bgwriter_flush_after = 32" 69. gs_guc set -N all -I all -c "wal_keep_segments = 1025" 70. gs_guc set -N all -I all -c "enable_bitmapscan = off" 71. gs_guc set -N all -I all -c "enable_seqscan = off" 72. gs_guc set -N all -I all -c "enable_beta_opfusion=on" 73. gs_guc set -N all -I all -c "checkpoint_segments=8000" 74. gs_guc set -N all -I all -c "enable_stmt_track=false" 75. gs_guc set -N all -I all -c "bgwriter_delay = 5s" 76. gs_guc set -N all -I all -c "incremental_checkpoint_timeout = 5min" 77. gs_guc set -N all -I all -c "xloginsert_locks = 16" 78. gs_guc set -N all -I all -c "walwriter_cpu_bind = 0" 79. gs_guc set -N all -I all -c "wal_file_init_num = 20" 80. gs_guc set -N all -I all -c "pagewriter_sleep = 5s" 81. gs_guc set -N all -I all -c "walwriter_sleep_threshold = 50000" 82. gs_guc set -N all -I all -c "gs_clean_timeout = 0" 83. gs_guc set -N all -I all -c "undo_zone_count = 0" 84. gs_guc set -N all -I all -c "pagewriter_thread_num = 4" 85. gs_guc set -N all -I all -c "max_redo_log_size = 400GB" 86. gs_guc set -N all -I all -c "max_io_capacity = 10GB" 87. gs_guc set -N all -I all -c "local_syscache_threshold = 32MB" 88. gs_guc set -N all -I all -c "segment_buffers = 1GB" 89. gs_guc set -N all -I all -c "numa_distribute_mode ='all'" 90. gs_guc set -N all -I all -c "enable_cachedplan_mgr = off" 91. gs_guc set -N all -I all -c "light_comm = on" 92. gs_guc set -N all -I all -c "enable_indexscan_optimization = on" 93. gs_guc set -N all -I all -c "ss_enable_ssl = off" 94. gs_guc set -N all -I all -c "ssl = off" 95. gs_guc set -N all -I all -c "ss_interconnect_channel_count = 32" 96. gs_guc set -N all -I all -c "ss_work_thread_count = 128" 97. gs_guc set -N all -I all -c "ss_recv_msg_pool_size = 1GB" 98. gs_guc set -N all -I all -c "enable_global_syscache = on"

创建测试数据库和用户

  • 在主机侧创建数据库tpcc及用户tpcc。

1. [omm@localhost]$ gsql -d postgres -p 26000 -r 2. postgres=# create database tpcc; 3. CREATE DATABASE 4. postgres=# \q 5. [omm@localhost]$ gsql -d tpcc -p 26000 -r 6. tpcc=# CREATE USER tpcc WITH PASSWORD "xxxxxx"; 7. CREATE ROLE 8. tpcc=# GRANT ALL ON schema public TO tpcc; 9. GRANT 10. tpcc=# ALTER User tpcc sysadmin; 11. ALTER ROLE

修改测试客户端配置

  • 进入BenchmarkSQL目录下的run文件夹，编辑benchmarksql配置文件，修改测试参数，包括数据库用户名、密码、IP、端口、数据库。

1. [root@localhost]# cd /tmp/benchmarksql-5.0-mysql-support-opt-2.1/run 2. [root@localhost]# vim props.og 3. db=postgres 4. driver=org.opengauss.Driver 5. conn=jdbc:opengauss://20.20.20.103:26000/tpcc?prepareThreshold=1&batchMode=on&fetchsize=10&loggerLevel=off #修改连接字符串, 包含IP、端口号、数据库 6. user=tpcc #用户名 7. password=tpcc@123 #密码 8. warehouses=1000 #仓位数 9. terminals=600 #并发数 10. runMins=15 #运行时间 11. runTxnsPerTerminal=0 12. loadWorkers=100 13. limitTxnsPerMin=0 14. terminalWarehouseFixed=true 15. newOrderWeight=45 16. paymentWeight=43 17. orderStatusWeight=4 18. deliveryWeight=4 19. stockLevelWeight=4

导入测试数据

  • 如果是TPCC测试，执行如下命令导入测试数据（TPCC测试）。

1. [root@localhost]# sh runDatabaseBuild.sh props.og 2. # ------------------------------------------------------------ 3. # Loading SQL file ./sql.common/tableCreates.sql 4. # ------------------------------------------------------------ 5. create table bmsql_config ( 6. cfg_name varchar(30) primary key, 7. cfg_value varchar(50) 8. ); 9. ...... 10. # ------------------------------------------------------------ 11. # Loading SQL file ./sql.postgres/buildFinish.sql 12. # ------------------------------------------------------------ 13. -- ---- 14. -- Extra commands to run after the tables are created, loaded, 15. -- indexes built and extra's created. 16. -- PostgreSQL version. 17. -- ---- 18. vacuum analyze; - 如果是使用sysbench测试，执行如下命令导入测试数据（sysbench测试）。

1. ##下述命令中使用的用户名为tpcc，数据库名称为tpcc，用户密码为tpcc@123，数据库端口为26000 2. [root@localhost]# sysbench --pgsql-password=xxxxxx --pgsql-port=26000 --report-interval='1' --pgsql-host=20.20.20.103 --tables='8' --db-driver='pgsql' --events='0' --histogram='on' --percentile='95' --threads='512' --table-size='64000000' --time='1200' --pgsql-user=tpcc --rand-type='uniform' --pgsql-db=tpcc oltp_read_write prepare

执行测试

  • TPCC测试

  • 执行如下命令开始测试。

    1. [root@localhost]# sh runBenchmark.sh props.og 2. 为了避免多次测试导致数据量太大，影响性能，可以把数据清空重新开始。

    1. [root@localhost]# sh runDatabaseDestroy.sh props.og 2. # ------------------------------------------------------------ 3. # Loading SQL file ./sql.common/tableDrops.sql 4. # ------------------------------------------------------------ 5. drop table bmsql_config; 6. drop table bmsql_new_order; 7. drop table bmsql_order_line; 8. drop table bmsql_oorder; 9. drop table bmsql_history; 10. drop table bmsql_customer; 11. drop table bmsql_stock; 12. drop table bmsql_item; 13. drop table bmsql_district; 14. drop table bmsql_warehouse; 15. drop sequence bmsql_hist_id_seq; 3. 如果调整配置后重新测试，或者复测，需要重新执行 Build / Run / Destroy。 - sysbench测试

  • 执行如下命令开始测试。

    1. [root@localhost]# sysbench --pgsql-password=xxxxxx --pgsql-port=26000 --report-interval='1' --pgsql-host=20.20.20.103 --tables='8' --db-driver='pgsql' --events='0' --histogram='on' --percentile='95' --threads='512' --table-size='64000000' --time='1200' --pgsql-user=tpcc --rand-type='uniform' --pgsql-db=tpcc oltp_read_write run 2. 为了避免多次测试导致数据量太大，影响性能，可以把数据清空重新开始。

    1. [root@localhost]# sysbench --pgsql-password=xxxxxx --pgsql-port=26000 --report-interval='1' --pgsql-host=20.20.20.103 --tables='8' --db-driver='pgsql' --events='0' --histogram='on' --percentile='95' --threads='512' --table-size='64000000' --time='1200' --pgsql-user=tpcc --rand-type='uniform' --pgsql-db=tpcc oltp_read_write cleanup 3. 如果调整配置后重新测试，或者复测，需要重新执行 prepare / run / cleanup。

主机读写加备机只读性能测试

  • 上述步骤是测试单独压主机读写的测试，如果需要测试主机读写同时备机只读，需要新增一个数据库配置参数

1. gs_guc set -N all -I all -c ""ss_enable_bcast_snapshot = on" - 如果需要测试TPCC的主机读写同时备机只读，那么需要启动2个BenchmarkSQL分别对主机和备机进行压测

  • 主机侧的BenchmarkSQL的测试配置如下（与只测试主机读写相同）：

1. db=postgres 2. driver=org.opengauss.Driver 3. conn=jdbc:opengauss://20.20.20.103:26000/tpcc?prepareThreshold=1&batchMode=on&fetchsize=10&loggerLevel=off #修改连接字符串, 包含IP、端口号、数据库 4. user=tpcc #用户名 5. password=xxxxxx #密码 6. warehouses=1000 #仓位数 7. terminals=600 #并发数 8. runMins=15 #运行时间 9. runTxnsPerTerminal=0 10. loadWorkers=100 11. limitTxnsPerMin=0 12. terminalWarehouseFixed=true 13. newOrderWeight=45 14. paymentWeight=43 15. orderStatusWeight=4 16. deliveryWeight=4 17. stockLevelWeight=4

  • 备机侧的BenchmarkSQL的测试配置如下：

1. db=postgres 2. driver=org.opengauss.Driver 3. conn=jdbc:opengauss://20.20.20.104:26000/tpcc?prepareThreshold=1&batchMode=on&fetchsize=10&loggerLevel=off #修改连接字符串, 包含IP、端口号、数据库 4. user=tpcc #用户名 5. password=xxxxxx #密码 6. warehouses=1000 #仓位数 7. terminals=100 #并发数 8. runMins=15 #运行时间 9. runTxnsPerTerminal=0 10. loadWorkers=100 11. limitTxnsPerMin=0 12. terminalWarehouseFixed=true 13. newOrderWeight=0 14. paymentWeight=0 15. orderStatusWeight=50 16. deliveryWeight=0 17. stockLevelWeight=50 - 如果是测试sysbench，主机读写同时备机只读，那么需要启动2个sysbench客户端分别对主机和备机进行压测

  • 主机侧执行读写混合负载

1. ##下述命令中使用的用户名为tpcc，数据库名称为tpcc，用户密码为tpcc@123，数据库端口为26000 2. [root@localhost]# sysbench --pgsql-password=xxxxxx --pgsql-port=26000 --report-interval='1' --pgsql-host=20.20.20.103 --tables='8' --db-driver='pgsql' --events='0' --histogram='on' --percentile='95' --threads='512' --table-size='64000000' --time='1200' --pgsql-user=tpcc --rand-type='uniform' --pgsql-db=tpcc oltp_read_write run

  • 备机侧执行只读测试

1. ##下述命令中使用的用户名为tpcc，数据库名称为tpcc，用户密码为tpcc@123，数据库端口为26000 2. [root@localhost]# sysbench --pgsql-password=xxxxxx --pgsql-port=26000 --report-interval='1' --pgsql-host=20.20.20.105 --tables='8' --db-driver='pgsql' --events='0' --histogram='on' --percentile='95' --threads='256' --table-size='64000000' --time='1200' --pgsql-user=tpcc --rand-type='uniform' --pgsql-db=tpcc oltp_read_only run - 除了启动2个独立的测试客户端执行测试和新增的配置项外，其余的数据库参数、OS参数、网卡绑中断等配置与单独测试主机相同。 - 如果是测试sysbench，因为sysbench构造数据的随机性，建议在测试前，在主机和备机侧先将所有数据加载到内存中，以达到最佳测试结果。具体的操作方式可以通过并发执行select命令进行数据的查询来加载。

基于典型生产参数测试

  • 在某些POC测试中，客户希望用数据库的典型生产参数测试，而不是用极限性能参数设置，故这里给出一套典型的生产参数举例。
  • 典型生产参数举例，切换到数据库安装用户下面执行如下命令：

1. gs_guc set -N all -I all -h "host all all 0.0.0.0/0 sha256" 2. gs_guc set -N all -I all -c "authentication_timeout=15s" 3. gs_guc set -N all -I all -c "cstore_buffers = 32MB" 4. gs_guc set -N all -I all -c "enable_alarm = off" 5. gs_guc set -N all -I all -c "enable_delta_store = on" 6. gs_guc set -N all -I all -c "enable_double_write = off" 7. gs_guc set -N all -I all -c "enable_incremental_checkpoint = on" 8. gs_guc set -N all -I all -c "enable_wdr_snapshot = off" 9. gs_guc set -N all -I all -c "enable_xlog_prune = on" 10. gs_guc set -N all -I all -c "log_min_duration_statement = 1s" 11. gs_guc set -N all -I all -c "maintenance_work_mem=2GB" 12. gs_guc set -N all -I all -c "max_connections = 2000" 13. gs_guc set -N all -I all -c "max_files_per_process = 10000" 14. gs_guc set -N all -I all -c "max_prepared_transactions = 2000" 15. gs_guc set -N all -I all -c "modify_initial_password = off" 16. gs_guc set -N all -I all -c "password_effect_time = 0" 17. gs_guc set -N all -I all -c "password_lock_time = 0" 18. gs_guc set -N all -I all -c "password_reuse_time=0" 19. gs_guc set -N all -I all -c "password_min_digital=1" 20. gs_guc set -N all -I all -c "password_min_length=8" 21. gs_guc set -N all -I all -c "password_min_lowercase=1" 22. gs_guc set -N all -I all -c "password_min_special=1" 23. gs_guc set -N all -I all -c "password_min_uppercase=1" 24. gs_guc set -N all -I all -c "password_policy=1" 25. gs_guc set -N all -I all -c "session_timeout = 0" 26. gs_guc set -N all -I all -c "shared_buffers = 150GB" 27. gs_guc set -N all -I all -c "synchronous_commit = on" 28. gs_guc set -N all -I all -c "temp_buffers = 1GB" 29. gs_guc set -N all -I all -c "update_lockwait_timeout = 20min" 30. gs_guc set -N all -I all -c "wal_buffers = 64MB" 31. gs_guc set -N all -I all -c "wdr_snapshot_interval = 10min" 32. gs_guc set -N all -I all -c "work_mem = 512MB" 33. gs_guc set -N all -I all -c "log_temp_files = 100MB" 34. gs_guc set -N all -I all -c "most_available_sync = ON" 35. gs_guc set -N all -I all -c "enable_mergejoin = ON" 36. gs_guc set -N all -I all -c "enable_nestloop = ON" 37. gs_guc set -N all -I all -c "advance_xlog_file_num = 10" 38. gs_guc set -N all -I all -c "pagewriter_sleep = 1000ms" 39. gs_guc set -N all -I all -c "xloginsert_locks = 50" 40. gs_guc set -N all -I all -c "lockwait_timeout = 20min" 41. gs_guc set -N all -I all -c "enable_opfusion = off" 42. gs_guc set -N all -I all -c "max_process_memory=200GB" 43. gs_guc set -N all -I all -c "log_file_mode = 0755" 44. gs_guc set -N all -I all -c "audit_enabled=on" 45. gs_guc set -N all -I all -c "audit_resource_policy=on" 46. gs_guc set -N all -I all -c "audit_space_limit=10GB" 47. gs_guc set -N all -I all -c "audit_set_parameter=0" 48. gs_guc set -N all -I all -c "audit_system_object=524287" 49. gs_guc set -N all -I all -c "audit_grant_revoke=1" 50. gs_guc set -N all -I all -c "audit_database_process=1" 51. gs_guc set -N all -I all -c "audit_user_locked=1" 52. gs_guc set -N all -I all -c "audit_login_logout=2" 53. gs_guc set -N all -I all -c "max_size_for_xlog_prune=10GB" 54. gs_guc set -N all -I all -c "wal_sender_timeout=180s" 55. gs_guc set -N all -I all -c "wal_receiver_timeout=180s" 56. gs_guc set -N all -I all -c "log_hostname=off" 57. gs_guc set -N all -I all -c "instr_unique_sql_count=20000" 58. gs_guc set -N all -I all -c "track_activity_query_size=10240" 59. gs_guc set -N all -I all -c "enable_asp=on" 60. gs_guc set -N all -I all -c "listen_addresses='*'" 61. gs_guc set -N all -I all -c "wal_keep_segments=640" 62. gs_guc set -N all -I all -c "pagewriter_sleep=500ms" 63. gs_guc set -N all -I all -c "bgwriter_delay=1s" 64. gs_guc set -N all -I all -c "enable_auto_clean_unique_sql=on" 65. gs_guc set -N all -I all -c "autoanalyze_timeout=1200" 66. gs_guc set -N all -I all -c "autovacuum_max_workers=10" 67. gs_guc set -N all -I all -c "autovacuum_naptime=300" 68. gs_guc set -N all -I all -c "autovacuum_vacuum_threshold=1000" 69. gs_guc set -N all -I all -c "autovacuum_analyze_threshold=1000" 70. gs_guc set -N all -I all -c "autovacuum_vacuum_scale_factor=0.05" 71. gs_guc set -N all -I all -c "autovacuum_vacuum_cost_delay=0" 72. gs_guc set -N all -I all -c "autovacuum_vacuum_cost_limit=10000" 73. gs_guc set -N all -I all -c "vacuum_cost_delay=0" 74. gs_guc set -N all -I all -c "vacuum_cost_limit=10000" 75. gs_guc set -N all -I all -c "log_lock_waits=on" 76. gs_guc set -N all -I all -c "wal_level=logical" 77. gs_guc set -I all -N all -c "enable_segment=on" 78. gs_guc set -I all -N all -c "recovery_max_workers=4" 79. gs_guc set -I all -N all -c "local_syscache_threshold=32MB" 80. gs_guc set -I all -N all -c "idle_in_transaction_session_timeout=3600" 81. gs_guc set -N all -I all -c "ss_interconnect_channel_count = 32" 82. gs_guc set -N all -I all -c "ss_work_thread_count = 128" 83. gs_guc set -N all -I all -c "ss_recv_msg_pool_size = 1GB" 84. gs_guc set -N all -I all -c "enable_global_syscache = on" - 除了上述数据库参数的差异外，其余OS参数、网卡绑中断等配置与极限参数配置下的测试相同。

观察系统资源工具

  • htop 观察CPU使用情况，arm平台需要从源码编译。

使用htop监控数据库服务端和tpcc客户端CPU利用情况，最佳性能测试情况下，各个业务CPU的占用率都非常高（> 80%）。如果有CPU占用率没有达标，可能是绑核方式不对或其他问题，需要定位找到根因进行调整。 - iostat 查看系统IO使用情况。 - sar 查看网络使用情况。

部分数据截图

  • 理想情况下，运行压力测试时当数据库htop显示如下。可以看到网卡中断所绑定的CPU核的系统调用较高。数据库使用的CPU核的用户态调用较高。 htop

理想结果

  • 在2路鲲鹏128C的服务器+OceanStor Dorado 18000 V6集中式存储的环境上，按上述步骤执行TPCC测试的参考结果如下，测试数据量为1000仓：
测试场景 测试并发数 测试使用参数 测试结果
主机 备机 主机 备机 主机（tpmC） 备机（tpmTOTAL）
读写 空载 600 NA 极限性能参数 120W NA
读写 只读 600 100 极限性能参数 97W 105.1W
读写 空载 600 NA 典型生产参数 54W NA
读写 只读 600 100 典型生产参数 51.4W 102.2W
  • 在2路鲲鹏128C的服务器+OceanStor Dorado 18000 V6集中式存储的环境上，按上述步骤执行sysbench测试的参考结果如下，测试数据量为8张表，每张表有64000000记录：
测试场景 测试并发数 测试使用参数 测试结果
主机 备机 主机 备机 主机（TPS） 备机（TPS）
读写 空载 512 NA 极限性能参数 3~3.3W NA
读写 只读 512 256 极限性能参数 1.3W 2.2W
读写 空载 512 NA 典型生产参数 1.6~1.9W NA
读写 只读 512 256 典型生产参数 8K 2.1W