Citus的TPCC、TPCH性能测试
Citus的TPCC、TPCH性能测试
文章目录
- Citus的TPCC、TPCH性能测试
- 测试的目的
- 适用范围
- 测试环境
- 架构信息
- 硬件配置
- 操作系统
- 软件版本
- 测试结果
- TPCC测试测试结果
- TPCH测试测试结果
- 一、环境部署
- 1.1、安装BenchmarkSQL
- 1.2、PostgreSQL安装
- 1.3、nmon部署
- 1.4、TPC-H测试的生成数据工具安装
- 二、TPCC测试
- 2.1、测试用例描述
- 2.2、测试用例1（扩容前）
- 2.2.1、创建模式和初始化数据库
- 2.2.2、生成数据
- 2.2.3、调整参数、压力测试
- 2.2.4、测试结果输出
- 2.3、测试用例2：扩容
- 2.4、测试用例3（扩容后）
- 2.4.1、调整参数、压力测试
- 2.4.2、测试结果输出
- 2.5、测试结果汇总
- 三、TPCH测试
- 3.1、测试用例描述
- 3.2、测试用例1（扩容前）
- 3.2.1、初始化数据库、创建表格
- 3.2.2、调整参数、生成数据
- 3.2.3、调整参数、压力测试
- 3.2.4、输出测试结果
- 3.3、测试用例2：扩容
- 3.4、测试用例3（扩容后）
- 3.5、测试结果汇总
测试的目的
本次为Citus+PostgreSQL分片数据库性能测试,测试目的为测试Citus+PostgreSQL数据库的事务处理能力,编写本文档,以作参考.
适用范围
本测试计划适用于PostgreSQL数据库功能和性能测试.
测试环境
架构信息
| 主机名 | IP | 操作系统 | 内存/空间 | 角色 | 说明 |
|---|---|---|---|---|---|
| pgcn | 192.168.6.108 | rhel7 | 2c/18G 400G | coordinate | 安装PGDB16.4+ Citus 12.1-1 |
| pgwk01 | 192.168.6.109 | rhel7 | 2c/18G 400G | worker | 安装PGDB16.4+ Citus 12.1-1 |
| pgwk02 | 192.168.6.110 | rhel7 | 2c/18G 400G | worker | 安装PGDB16.4+ Citus 12.1-1 |
硬件配置
-
2*E5-2680 v4(2.40GHz 14核)
-
8*32GB RDIMM-2400内存
-
8*1.6T MLC SSD
-
2*双万兆光口网卡
操作系统
- Operating System: Red Hat Enterprise Linux Server 7.7 (Maipo)
- Architecture: x86-64
软件版本
-
PGDB-16.4-8k-rhel7-x86_64：PostgreSQL 16.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
-
Citus：Citus 12.1.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
-
BenchmarkSQL：Version 5.0
-
nmon：nmon version 16e
测试结果
TPCC测试测试结果
测试结果
以下是整理后的数据表格，包括扩容前后的变化对比：
扩容前 vs 扩容后 数据对比表
以下是根据您提供的数据更新后的表格：
| 指标 \ 阶段 | 扩容前 | 扩容后 | 变化 | 描述 |
|---|---|---|---|---|
| tpmC | 4725.59 | 5102.58 | +376.99 | 新订单事务每分钟数 |
| tpmTOTAL | 10512.15 | 11363.44 | +851.29 | 总事务每分钟数 |
| Transaction Count | 21123 | 22799 | +1676 | 事务总数 |
| Citus集群节点数 | 1✖Coordinator + 2✖Worker | 1✖Coordinator + 3✖Worker | +1个Worker | 节点配置 |
| 每个节点所占有的分片数 | 节点 | 分片数 192.168.6.109 | 81 192.168.6.110 | 81 | 节点 | 分片数 192.168.6.107 | 63 192.168.6.109 | 45 192.168.6.110 | 54 | - | 节点和分片数 |
| 数据量 | 300万条 | 300万条 | 无变化 | 本次测试有300万条数据 |
重新分配分片所用的时间：Time: 22610.374 ms (00:22.610)
总结：
- tpmC 和 tpmTOTAL 指标在扩容后有所减少，变动幅度较小。
- Transaction Count 稍微减少。
- Citus集群节点数 增加了1个Worker节点，提高了扩展性。
- 每个节点所占有的分片数 调整后，分片在各节点间更为均匀。
TPCH测试测试结果
扩容前 vs 扩容后 数据对比表
| 阶段 | 指标 | 扩容前 | 扩容后 | 提升/下降百分比 |
|---|---|---|---|---|
| 数据量 | 数据量 | 8661245（86万） | 8661245（86万） | 0% |
| Q1查询响应时间 | 1151.499 ms (00:01.151) | 1169.370 ms (00:01.169) | -1.05% | |
| Q2查询响应时间 | 225.830 ms | 224.707 ms | 0.5% | |
| Q3查询响应时间 | 474.648 ms | 446.181 ms | 5.98% | |
| Q4查询响应时间 | 342.354 ms | 318.128 ms | 7.07% | |
| Q5查询响应时间 | 645.930 ms | 628.814 ms | 2.28% | |
| Q6查询响应时间 | 234.146 ms | 258.494 ms | -10.41% | |
| Q7查询响应时间 | 378.565 ms | 363.685 ms | 3.92% | |
| Q8查询响应时间 | 689.490 ms | 580.927 ms | 15.75% | |
| Q9查询响应时间 | 865.872 ms | 835.688 ms | 3.48% | |
| Q10查询响应时间 | 567.329 ms | 545.771 ms | 3.79% | |
| Q11查询响应时间 | 134.626 ms | 137.307 ms | -1.99% | |
| Q12查询响应时间 | 332.472 ms | 311.096 ms | 6.39% | |
| Q13查询响应时间 | 5030.917 ms (00:05.031) | 5671.297 ms (00:05.671) | -12.85% | |
| Q14查询响应时间 | 265.414 ms | 261.195 ms | 1.65% | |
| Q15查询响应时间 | 1134.336 ms (00:01.134) | 964.076 ms | 15% | |
| Q16查询响应时间 | 273.432 ms | 274.727 ms | -0.47% | |
| Q18查询响应时间 | 808.995 ms | 797.505 ms | 1.84% | |
| Q19查询响应时间 | 412.101 ms | 375.844 ms | 8.76% | |
| Q21查询响应时间 | 371.585 ms | 365.105 ms | 1.3% | |
| Citus集群 | 节点数 | 1✖Coordinator + 2✖Worker | 1✖Coordinator + 3✖Worker | - |
| 每个节点所占有的分片数 | nodename | count 192.168.6.108 | 28 192.168.6.110 | 26 192.168.6.109 | 28 | nodename | count 192.168.6.108 | 22 192.168.6.107 | 22 192.168.6.110 | 22 192.168.6.109 | 22 | - |
扩容后重新分配分片持续时间：Time: 11652.738 ms (00:11.653)
总结
- 扩容后，多个查询的响应时间显著提升，尤其是Q15（提升15%）、Q8（提升15.75%）和Q4（提升7.07%），显示出明显的性能改进。
- 尽管部分查询如Q6和Q13的响应时间有所增加，但整体上大多数查询性能均有所提升。
- 整体来看，扩容后的系统在查询响应效率上表现优越，尤其在提升幅度较大的查询上，系统性能得到了明显改善。
一、环境部署
1.1、安装BenchmarkSQL
安装EPEL仓库
su -c 'rpm -Uvh https://download.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm'
配置yum源云环境
[root@postgres yum.repos.d]# cat CentOS-Base.repo
# CentOS-Base.repo
[base]
name=CentOS-$releasever - Base - mirrors.aliyun.com
failovermethod=priority
baseurl=http://mirrors.aliyun.com/centos/7/os/x86_64/
http://mirrors.aliyuncs.com/centos/7/os/$basearch/
http://mirrors.cloud.aliyuncs.com/centos/7/os/$basearch/
gpgcheck=1
gpgkey=http://mirrors.aliyun.com/centos/RPM-GPG-KEY-CentOS-7
#released updates
[updates]
name=CentOS-$releasever - Updates - mirrors.aliyun.com
failovermethod=priority
baseurl=http://mirrors.aliyun.com/centos/7/updates/$basearch/
http://mirrors.aliyuncs.com/centos/$releasever/updates/$basearch/
http://mirrors.cloud.aliyuncs.com/centos/$releasever/updates/$basearch/
gpgcheck=1
gpgkey=http://mirrors.aliyun.com/centos/RPM-GPG-KEY-CentOS-7
[root@postgres yum.repos.d]#
安装Ant
yum -y install ant
安装R语言
yum -y install epel-release
yum -y install R
检查jdk环境（要求JDK 7以上版本）
[root@postgres install]# java -version
openjdk version "1.8.0_412"
OpenJDK Runtime Environment (build 1.8.0_412-b08)
OpenJDK 64-Bit Server VM (build 25.412-b08, mixed mode)
下载并安装BenchmarkSQL
wget https://downloads.sourceforge.net/project/benchmarksql/benchmarksql-5.0.zip
unzip ./benchmarksql-5.0.zip
###编译BenchmarkSQL源码
[pg@postgres benchmarksql-5.0]$ pwd
/home/pg/benchmarksql-5.0
[pg@postgres benchmarksql-5.0]$ ant
##创建配置文件,进入run目录, 复制props.pg文件并编辑产生的副本，设置与基准测试有关的参数：
[pg@postgres benchmarksql-5.0]$ cd run
[pg@postgres run]$ cp props.pg my_postgres.properties
[pg@postgres run]$ vi my_postgres.properties
参数配置说明
conn=jdbc:postgresql://localhost:5432/postgres
将其中的 “localhost” 修改为postgresql所在服务器的ip地址，将“5432”修改为 postgresql所在的端口，将末尾的 “postgres” 修改为所测量的数据库。本次实验，将其修改为 conn=jdbc:postgresql://localhost:5432/ benchmarksql
user=fbase
将“benchmarksql”修改为用来测试的用户。本次实验不做修改。
password=fbase
将“PWbmsql”修改为测试的用户的密码。本次实验将其修改为“changeme”。
1.2、PostgreSQL安装
安装依赖包
yum -y install lrzsz sysstat e4fsprogs ntp readline-devel zlib zlib-devel openssl openssl-devel pam-devel libxml2-devel libxslt-devel python-devel tcl-devel gcc make flex bison perl perl-devel perl-ExtUtils* OpenIPMI-tools systemtap-sdt-devel smartmontools
安装PostgreSQL数据库软件
##上传软件压缩包,使用root用户
$ PGDB-16.4-8k-rhel7-x86_64.tar.gz
$ tar -xvf PGDB-16.4-8k-rhel7-x86_64.tar.gz
$ cd PGDB-16.4-8k-rhel7-x86_64/extra_tools/install
sh install.sh -p 8432 -u fbase -b /usr/local/fbase/15.5 -d /data/fbase/fbdata -D HDD -c 100 -w
参数说明：
| 参数 | 配置值 | 说明 |
|---|---|---|
| -p | 8432 | 数据库监听端口，默认值为 8432 |
| -u | 用户名 | 运行和安装 FBase 的系统用户 |
| -b | /usr/local/fbase/‘fbase_version’ | FBase 的主目录，默认值为 /usr/local/fbase/‘fbase_version’ |
| -d | 数据目录 | FBase 的数据目录 |
| -D | SSD | 数据存储设备的类型，有效选项为 HDD 或 SSD，默认值为 SSD |
| -c | 100 | FBase 的最大连接数，默认值为 100 |
| -w | 是/否 | 是否使用默认密码 |
1.3、nmon部署
在命令行中使用wget 下载
官网下载路径
wget http://sourceforge.net/projects/nmon/files/nmon16m_helpsystems.tar.gz
下载与解压（不需要编译安装）
mkdir /nmon
cd nmon/
tar -zxvf nmon16m_helpsystems.tar.gz -解压
mv nmon_x86_64_centos7 nmon -重命名
[root@sdw-135 nmon]# cp nmon_x86_64_centos7 /usr/local/bin/nmon
[root@sdw-135 nmon]# nmon
nmon-16e------[H for help]---Hostname=postgres-----Refresh= 2secs ---13:56.44
| |
| ------------------------------ |
| _ __ _ __ ___ ___ _ __ For help type H or ... |
| | '_ \| '_ ` _ \ / _ \| '_ \ nmon -? - hint |
| | | | | | | | | | (_) | | | | nmon -h - full details |
| |_| |_|_| |_| |_|\___/|_| |_| |
| To stop nmon type q to Quit |
| ------------------------------ |
| |
| NAME="Red Hat Enterprise Linux Server" VERSION="7.7 (Maipo)" |
| AuthenticAMD AMD Ryzen 5 4600H with Radeon Graphics |
| MHz=2994.374 bogomips=5988.74 |
| ProcessorChips=1 PhyscalCores=1 |
| Hyperthreads =0 VirtualCPUs =2 |
| |
| Use these keys to toggle statistics on/off: |
| c = CPU l = CPU Long-term - = Faster screen updates |
| C = " WideView U = Utilisation + = Slower screen updates |
| m = Memory V = Virtual memory j = File Systems |
| d = Disks n = Network . = only busy disks/procs |
| r = Resource N = NFS h = more options |
| k = Kernel t = Top-processes q = Quit |
|----------------------------------------------------------------------------|
| |
| |
+----------------------------------------------------------------------------+
开启nmon收集系统数据
#创建收集数据目录
mkdir /home/pg/nmonlog
nohup ./runBenchmark.sh my_postgres.properties > citus.txt 2>&1 &
nohup ./runBenchmark.sh my_single.properties > single.txt 2>&1 &
#开启收集（-s 每隔x秒收集一次； -c 收集次数）
nmon -f -s 2 -c 960 -m /root/nmonlog
ps -ef | grep nmon
[root@postgres ~]# nmon -f -s 5 -c 10 -m /home/pg/nmonlog
[root@postgres ~]# ps -ef|grep nmon
root 32060 1 0 13:58 pts/1 00:00:00 nmon -f -s 5 -c 10 -m /home/pg/nmonlog
root 32131 28389 0 13:58 pts/1 00:00:00 grep --color=auto nmon
[root@postgres ~]# cd /home/pg/nmonlog
[root@postgres nmonlog]# ls
postgres_240911_0627.nmon postgres_240911_1358.nmon
postgres_240911_0629.nmon
#下载postgres_240911_1358.nmon文件到本地。
1.4、TPC-H测试的生成数据工具安装
tpch-kit组件：GitHub 仓库地址
编译安装
编辑项目定义的头文件 tpch-kit-master/dbgen/tpcd.h，不同的数据库使用不用的定义。文件中内置了一些数据库，根据自己需要使用的数据库配置，如果文件中已经包含了，那么不需要配置。如果没有可以参考下方，增加到文件中即可。
【PostgreSQL定义】
#ifdef POSTGRESQL
#define GEN_QUERY_PLAN "explain"
#define START_TRAN "start transaction"
#define END_TRAN "commit;"
#define SET_OUTPUT ""
#define SET_ROWCOUNT "limit %d;\n"
#define SET_DBASE ""
#endif
【修改编译文件】
编辑编译文件 tpch-kit-master/dbgen/Makefile，找到如下内容按情况修改。
################
## CHANGE NAME OF ANSI COMPILER HERE
################
CC = gcc
# Current values for DATABASE are: INFORMIX, DB2, TDAT (Teradata)
# SQLSERVER, SYBASE, ORACLE, VECTORWISE, POSTGRESQL
# Current values for MACHINE are: ATT, DOS, HP, IBM, ICL, MVS, MACOS
# SGI, SUN, U2200, VMS, LINUX, WIN32
# Current values for WORKLOAD are: TPCH
DATABASE = POSTGRESQL
MACHINE = LINUX
WORKLOAD = TPCH
上述2个文件配置完成后，cd tpch-kit-master/dbgen/ 执行 make 命令
pg@postgres tpch-kit-master]$ make
编译完成后会自动生成2个执行文件
[pg@postgres dbgen]$ ls *gen
dbgen qgen
[pg@postgres dbgen]$
- dbgen：生成表数据(csv文件)
- qgen：生成复杂查询SQL
**需要注意：**直接执行 qgen 会出现找不到的错误
设置环境变量
export DSS_QUERY=./queries
再次执行qgen（成功），生成22条复杂查询SQL
二、TPCC测试
测试过程
生产测试数据->压测-> 扩容-> 压测。
1）测试过程benchmarksql采用相同配置,配置如下：
db=postgres # 数据库类型，设置为 PostgreSQL
driver=org.postgresql.Driver # JDBC 驱动程序类
conn=jdbc:postgresql://localhost:5432/postgres # JDBC 连接 URL，连接到本地 PostgreSQL 数据库
user=fbase # 数据库用户名
password=fbase # 数据库用户密码
warehouses=100 # 使用的仓库数量
loadWorkers=50 # 负载生成器的工作线程数量
terminals=60 # 运行的终端数量
runTxnsPerTerminal=0 # 每个终端要运行的事务数量,0表示不限制
runMins=2 # 测试运行时间，单位为分钟
limitTxnsPerMin=30000000 # 每分钟允许的最大事务数
terminalWarehouseFixed=true # 是否每个终端固定使用一个仓库
newOrderWeight=45 # 新订单事务的权重
paymentWeight=43 # 支付事务的权重
orderStatusWeight=4 # 订单状态查询事务的权重
deliveryWeight=4 # 配送事务的权重
stockLevelWeight=4 # 库存水平查询事务的权重
resultDirectory=my_result_%tY-%tm-%td_%tH%tM%tS # 结果数据保存的目录名称模板
osCollectorScript=./misc/os_collector_linux.py # 操作系统性能数据收集脚本的路径
osCollectorInterval=1 # 操作系统性能数据收集的间隔时间，单位为秒
//osCollectorSSHAddr=user@dbhost # 操作系统性能数据收集的 SSH 地址（被注释掉）
osCollectorDevices=net_ens34 blk_sda # 指定要监控的系统设备
2）整个citus数据库集群和系统，调整不同节点数进程测试，其中这些测试用例的参数配置完全一致，改变的只有citus集群节点数。
2.1、测试用例描述
各个节点的磁盘I/O速率
使用命令dd if=/dev/sda of=/tmp/test bs=64k count=40k oflag=dsync测出磁盘I/O速率如下所示：
| 节点 | 速度 |
|---|---|
| 108节点 | 246 MB/s |
| 109节点 | 162 MB/s |
| 110节点 | 151 MB/s |
| 107节点 | 180 MB/s |
参数说明
| 参数 | 说明 |
|---|---|
if=/dev/sda | 输入文件或设备（从 /dev/sda 读取数据） |
of=/tmp/test | 输出文件（写入到 /tmp/test） |
bs=64k | 块大小（每次读写 64 KB 数据） |
count=40k | 数据块数量（总共 40,000 个块） |
oflag=dsync | 输出标志（每次写入同步到磁盘） |
benchmarksql的参数配置说明：
**主要调整以下参数**
warehouses=100 # 使用的仓库数量，数值越大，生成的数据量越大。
loadWorkers=50 # 负载生成器的工作线程数量
terminals=60 # 运行的终端数量，可以模拟连接数据库的客户端数量。
runTxnsPerTerminal=0 # 每个终端要运行的事务数量,0表示不限制
runMins=2 # 测试运行时间，单位为分钟，一般测试为1~3小时
limitTxnsPerMin=30000000 # 每分钟允许的最大事务数
注意：warehouses不建议设置的很多，不然生成数据的时间过长
测试用例1和测试用例2主要区别在于Citus节点数量不同，测试用例2使用的citus节点比测试用例1多一个。
主要测试的内容为：添加一个节点，citus分片集群性能是否有明显的提升
2.2、测试用例1（扩容前）
本次测试使用的配置参数如下所示：
warehouses=6 ##表示数据量的大小，大约有11张表、6G的数据量
loadWorkers=18 ##工作进程数目
terminals=10 ##表示并发数
runTxnsPerTerminal=0
runMins=2
limitTxnsPerMin=3000000 ##每分钟允许的最大事务数，这表示测试过程中每分钟的最大事务负载为 300 万次。如果内存较小，不建议设置特别大。
##查看每个节点的分片数
SELECT nodename, COUNT(*)
FROM citus_shards
GROUP BY nodename;
nodename | count
---------------+-------
192.168.6.110 | 81
192.168.6.109 | 81
(2 rows)
##大约有300万条数据
SELECT
(SELECT COUNT(*) FROM public.bmsql_config) +
(SELECT COUNT(*) FROM public.bmsql_customer) +
(SELECT COUNT(*) FROM public.bmsql_district) +
(SELECT COUNT(*) FROM public.bmsql_history) +
(SELECT COUNT(*) FROM public.bmsql_item) +
(SELECT COUNT(*) FROM public.bmsql_new_order) +
(SELECT COUNT(*) FROM public.bmsql_oorder) +
(SELECT COUNT(*) FROM public.bmsql_order_line) +
(SELECT COUNT(*) FROM public.bmsql_stock) +
(SELECT COUNT(*) FROM public.bmsql_warehouse) AS total_rows;
total_rows
------------
3093093
(1 row)
2.2.1、创建模式和初始化数据库
##清除环境
./runDatabaseDestroy.sh my_postgres.properties
##保证分片平衡
set citus.shard_count = 18;
-- Create table statements
create table bmsql_config (
cfg_name varchar(30) primary key,
cfg_value varchar(50)
);
create table bmsql_warehouse (
w_id integer not null,
w_ytd decimal(12,2),
w_tax decimal(4,4),
w_name varchar(10),
w_street_1 varchar(20),
w_street_2 varchar(20),
w_city varchar(20),
w_state char(2),
w_zip char(9),
primary key (w_id)
);
create table bmsql_district (
d_w_id integer not null,
d_id integer not null,
d_ytd decimal(12,2),
d_tax decimal(4,4),
d_next_o_id integer,
d_name varchar(10),
d_street_1 varchar(20),
d_street_2 varchar(20),
d_city varchar(20),
d_state char(2),
d_zip char(9),
primary key (d_w_id, d_id)
);
create table bmsql_customer (
c_w_id integer not null,
c_d_id integer not null,
c_id integer not null,
c_discount decimal(4,4),
c_credit char(2),
c_last varchar(16),
c_first varchar(16),
c_credit_lim decimal(12,2),
c_balance decimal(12,2),
c_ytd_payment decimal(12,2),
c_payment_cnt integer,
c_delivery_cnt integer,
c_street_1 varchar(20),
c_street_2 varchar(20),
c_city varchar(20),
c_state char(2),
c_zip char(9),
c_phone char(16),
c_since timestamp,
c_middle char(2),
c_data varchar(500),
primary key (c_w_id, c_d_id, c_id)
);
create table bmsql_history (
hist_id integer,
h_c_id integer,
h_c_d_id integer,
h_c_w_id integer,
h_d_id integer,
h_w_id integer,
h_date timestamp,
h_amount decimal(6,2),
h_data varchar(24)
);
create table bmsql_new_order (
no_w_id integer not null,
no_d_id integer not null,
no_o_id integer not null,
primary key (no_w_id, no_d_id, no_o_id)
);
create table bmsql_oorder (
o_w_id integer not null,
o_d_id integer not null,
o_id integer not null,
o_c_id integer,
o_carrier_id integer,
o_ol_cnt integer,
o_all_local integer,
o_entry_d timestamp,
primary key (o_w_id, o_d_id, o_id)
);
create table bmsql_order_line (
ol_w_id integer not null,
ol_d_id integer not null,
ol_o_id integer not null,
ol_number integer not null,
ol_i_id integer not null,
ol_delivery_d timestamp,
ol_amount decimal(6,2),
ol_supply_w_id integer,
ol_quantity integer,
ol_dist_info char(24),
primary key (ol_w_id, ol_d_id, ol_o_id, ol_number)
);
create table bmsql_item (
i_id integer not null,
i_name varchar(24),
i_price decimal(5,2),
i_data varchar(50),
i_im_id integer,
primary key (i_id)
);
create table bmsql_stock (
s_w_id integer not null,
s_i_id integer not null,
s_quantity integer,
s_ytd integer,
s_order_cnt integer,
s_remote_cnt integer,
s_data varchar(50),
s_dist_01 char(24),
s_dist_02 char(24),
s_dist_03 char(24),
s_dist_04 char(24),
s_dist_05 char(24),
s_dist_06 char(24),
s_dist_07 char(24),
s_dist_08 char(24),
s_dist_09 char(24),
s_dist_10 char(24),
primary key (s_w_id, s_i_id)
);
-- Create distributed table statements
SELECT create_distributed_table('bmsql_config', 'cfg_name');
SELECT create_distributed_table('bmsql_district', 'd_w_id');
SELECT create_distributed_table('bmsql_customer', 'c_w_id');
SELECT create_distributed_table('bmsql_oorder', 'o_w_id');
SELECT create_distributed_table('bmsql_item', 'i_id');
SELECT create_distributed_table('bmsql_warehouse', 'w_id');
select create_distributed_table('bmsql_new_order', 'no_w_id');
select create_distributed_table('bmsql_stock', 's_w_id');
select create_distributed_table('bmsql_order_line', 'ol_w_id');
select create_distributed_table('bmsql_history', 'h_c_id');
create index bmsql_customer_idx1
on bmsql_customer (c_w_id, c_d_id, c_last, c_first);
create unique index bmsql_oorder_idx1
on bmsql_oorder (o_w_id, o_d_id, o_carrier_id, o_id)
2.2.2、生成数据
## 生成数据
./runDatabaseBuild.sh my_postgres.properties
2.2.3、调整参数、压力测试
##开始测试
./runBenchmark.sh my_postgres.properties
2.2.4、测试结果输出
##生成具有html的报告，其中也包括系统IO等性能。
./generateReport.sh my_result_2024-09-11_131737/
输出的信息
--查看citus初始节点数
postgres=# select * from pg_dist_node;
nodeid | groupid | nodename | nodeport | noderack | hasmetadata | isactive | noderole | nodecluster | metadatasynced | shouldhaveshards
--------+---------+---------------+----------+----------+-------------+----------+----------+-------------+----------------+------------------
1 | 0 | 192.168.6.108 | 8432 | default | t | t | primary | default | t | f
2 | 1 | 192.168.6.109 | 8432 | default | t | t | primary | default | t | t
3 | 2 | 192.168.6.110 | 8432 | default | t | t | primary | default | t | t
(3 rows)
--清除环境，删除原来的表
[fbase@postgres run]$ ./runDatabaseDestroy.sh my_postgres.properties
# ------------------------------------------------------------
# Loading SQL file ./sql.common/tableDrops.sql
# ------------------------------------------------------------
drop table bmsql_config;
drop table bmsql_new_order;
drop table bmsql_order_line;
drop table bmsql_oorder;
drop table bmsql_history;
drop table bmsql_customer;
drop table bmsql_stock;
drop table bmsql_item;
drop table bmsql_district;
drop table bmsql_warehouse;
drop sequence bmsql_hist_id_seq;
[fbase@postgres run]$ psql
psql (16.4)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES128-GCM-SHA256, compression: off)
Type "help" for help.
--表格分片已经分发成功
postgres=# select * from citus_tables ;
table_name | citus_table_type | distribution_column | colocation_id | table_size | sh
ard_count | table_owner | access_method
------------------+------------------+---------------------+---------------+------------+---
----------+-------------+---------------
bmsql_config | distributed | cfg_name | 218 | 400 kB |
32 | fbase | heap
bmsql_customer | distributed | c_w_id | 219 | 128 MB |
32 | fbase | heap
bmsql_district | distributed | d_w_id | 219 | 544 kB |
32 | fbase | heap
bmsql_history | distributed | h_c_id | 219 | 18 MB |
32 | fbase | heap
bmsql_item | distributed | i_id | 219 | 14 MB |
32 | fbase | heap
bmsql_new_order | distributed | no_w_id | 219 | 5096 kB |
32 | fbase | heap
bmsql_oorder | distributed | o_w_id | 219 | 25 MB |
32 | fbase | heap
bmsql_order_line | distributed | ol_w_id | 219 | 241 MB |
32 | fbase | heap
bmsql_stock | distributed | s_w_id | 219 | 223 MB |
32 | fbase | heap
bmsql_warehouse | distributed | w_id | 219 | 544 kB |
32 | fbase | heap
(10 rows)
--两个worker节点
postgres=# select distinct nodename from citus_shards;
nodename
---------------
192.168.6.109
192.168.6.110
(2 rows)
--生成数据（这里不建议把warehouses配置的特别多，因为生成数据很慢）
[fbase@postgres run]$ ./runDatabaseBuild.sh my_postgres.properties
# ------------------------------------------------------------
# Loading SQL file ./sql.common/tableCreates.sql
# ------------------------------------------------------------
Starting BenchmarkSQL LoadData
Worker 000: Loading ITEM
Worker 001: Loading Warehouse 1
Worker 002: Loading Warehouse 2
Worker 000: Loading ITEM done
CWorker 002: Loading Warehouse 2 done
Worker 001: Loading Warehouse 1 done
--查看表中数据的分布信息
postgres=# select * from citus_tables ;
table_name | citus_table_type | distribution_column | colocation_id | table_size | shard_count | table_owner | access_method
-----------------+------------------+---------------------+---------------+------------+-------------+-------------+---------------
bmsql_config | distributed | cfg_name | 22 | 400 kB | 32 | fbase | heap
bmsql_customer | distributed | c_w_id | 23 | 21 MB | 32 | fbase | heap
bmsql_district | distributed | d_w_id | 23 | 304 kB | 32 | fbase | heap
bmsql_item | distributed | i_id | 23 | 14 MB | 32 | fbase | heap
bmsql_oorder | distributed | o_w_id | 23 | 4520 kB | 32 | fbase | heap
bmsql_warehouse | distributed | w_id | 23 | 304 kB | 32 | fbase | heap
(6 rows)
postgres=# \d+
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+-------------------+----------+-------+-------------+---------------+------------+-------------
public | bmsql_config | table | fbase | permanent | heap | 0 bytes |
public | bmsql_customer | table | fbase | permanent | heap | 8192 bytes |
public | bmsql_district | table | fbase | permanent | heap | 0 bytes |
public | bmsql_hist_id_seq | sequence | fbase | permanent | | 8192 bytes |
public | bmsql_history | table | fbase | permanent | heap | 2648 kB |
public | bmsql_item | table | fbase | permanent | heap | 0 bytes |
public | bmsql_new_order | table | fbase | permanent | heap | 440 kB |
public | bmsql_oorder | table | fbase | permanent | heap | 0 bytes |
public | bmsql_order_line | table | fbase | permanent | heap | 29 MB |
public | bmsql_stock | table | fbase | permanent | heap | 34 MB |
public | bmsql_warehouse | table | fbase | permanent | heap | 0 bytes |
public | citus_schemas | view | fbase | permanent | | 0 bytes |
public | citus_tables | view | fbase | permanent | | 0 bytes |
(13 rows)
--开始测试
[fbase@postgres run]$ ./runBenchmark.sh my_postgres.properties
15:27:16,888 [main] INFO jTPCC : Term-00,
15:27:16,892 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+
15:27:16,892 [main] INFO jTPCC : Term-00, BenchmarkSQL v5.0
15:27:16,893 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+
15:27:16,893 [main] INFO jTPCC : Term-00, (c) 2003, Raul Barbosa
15:27:16,893 [main] INFO jTPCC : Term-00, (c) 2004-2016, Denis Lussier
15:27:16,898 [main] INFO jTPCC : Term-00, (c) 2016, Jan Wieck
15:27:16,898 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+
15:27:16,898 [main] INFO jTPCC : Term-00,
15:27:16,899 [main] INFO jTPCC : Term-00, db=postgres
15:27:16,899 [main] INFO jTPCC : Term-00, driver=org.postgresql.Driver
15:27:16,899 [main] INFO jTPCC : Term-00, conn=jdbc:postgresql://localhost:8432/postgres
15:27:16,899 [main] INFO jTPCC : Term-00, user=fbase
15:27:16,899 [main] INFO jTPCC : Term-00,
15:27:16,899 [main] INFO jTPCC : Term-00, warehouses=10
15:27:16,899 [main] INFO jTPCC : Term-00, terminals=10
15:27:16,900 [main] INFO jTPCC : Term-00, runMins=3
15:27:16,900 [main] INFO jTPCC : Term-00, limitTxnsPerMin=300000
15:27:16,900 [main] INFO jTPCC : Term-00, terminalWarehouseFixed=true
15:27:16,901 [main] INFO jTPCC : Term-00,
15:27:16,901 [main] INFO jTPCC : Term-00, newOrderWeight=45
15:27:16,901 [main] INFO jTPCC : Term-00, paymentWeight=43
15:27:16,901 [main] INFO jTPCC : Term-00, orderStatusWeight=4
15:27:16,901 [main] INFO jTPCC : Term-00, deliveryWeight=4
15:27:16,901 [main] INFO jTPCC : Term-00, stockLevelWeight=4
15:27:16,901 [main] INFO jTPCC : Term-00,
15:27:16,901 [main] INFO jTPCC : Term-00, resultDirectory=my_result_%tY-%tm-%td_%tH%tM%tS
15:27:16,901 [main] INFO jTPCC : Term-00, osCollectorScript=./misc/os_collector_linux.py
15:27:16,901 [main] INFO jTPCC : Term-00,
15:27:16,925 [main] INFO jTPCC : Term-00, copied my_postgres.properties to my_result_2024-09-13_152716/run.properties
15:27:16,925 [main] INFO jTPCC : Term-00, created my_result_2024-09-13_152716/data/runInfo.csv for runID 22
15:27:16,925 [main] INFO jTPCC : Term-00, writing per transaction results to my_result_2024-09-13_152716/data/result.csv
15:27:16,926 [main] INFO jTPCC : Term-00, osCollectorScript=./misc/os_collector_linux.py
15:27:16,926 [main] INFO jTPCC : Term-00, osCollectorInterval=1
15:27:16,926 [main] INFO jTPCC : Term-00, osCollectorSSHAddr=null
15:27:16,926 [main] INFO jTPCC : Term-00, osCollectorDevices=net_ens34 blk_sda
15:27:17,019 [main] INFO jTPCC : Term-00,
15:27:17,109 [main] INFO jTPCC : Term-00, C value for C_LAST during load: 32
15:27:17,110 [main] INFO jTPCC : Term-00, C value for C_LAST this run: 116
15:27:17,110 [main] INFO jTPCC : Term-00,
Term-00, Running Average tpmTOTAL: 10531.62 Current tpmTOTAL: 140244 Memory Usage: 38M08:43:03,249 [Thread-9] INFO jTPCC : Term-00,
08:43:03,249 [Thread-9] INFO jTPCC : Term-00,
08:43:03,250 [Thread-9] INFO jTPCC : Term-00, Measured tpmC (NewOrders) = 4725.59
08:43:03,250 [Thread-9] INFO jTPCC : Term-00, Measured tpmTOTAL = 10512.15
08:43:03,250 [Thread-9] INFO jTPCC : Term-00, Session Start = 2024-09-19 08:41:02
08:43:03,250 [Thread-9] INFO jTPCC : Term-00, Session End = 2024-09-19 08:43:03
08:43:03,250 [Thread-9] INFO jTPCC : Term-00, Transaction Count = 21123
[fbase@postgres run]$
测试报告分析
在进行TPC-C（Transaction Processing Performance Council Benchmark C）测试时，通常需要关注以下几个主要指标：
- tpmC (Transactions Per Minute for New Orders): 表示每分钟处理的新订单事务数。
- tpmTOTAL (Total Transactions Per Minute): 表示每分钟处理的所有事务的总数。
- Session Start Time: 测试会话的开始时间。
- Session End Time: 测试会话的结束时间。
- Transaction Count: 测试会话期间处理的事务总数。
根据提供的测试报告，我们可以将这些指标整理到一个表格中：
| 指标 | 值 | 描述 |
|---|---|---|
| tpmC | 4725.59 | 新订单事务每分钟数 |
| tpmTOTAL | 10512.15 | 总事务每分钟数 |
| Session Start Time | 2024-09-18 15:34:14 | 会话开始时间 |
| Session End Time | 2024-09-18 15:36:14 | 会话结束时间 |
| Transaction Count | 21123 | 事务总数 |
这个表格展示了测试的关键性能指标，帮助了解数据库在TPC-C负载下的性能表现。
生成具有HTML的报告
测试结束后，run目录下会生成一个新目录，它的命名格式为 my_result_%tY-%tm-%td_%tH%tM%tS。
使用 generateReport.sh my_result_* 脚本创建具有图形的 HTML 文件：
[fbase@postgres run]$ ./generateReport.sh my_result_2024-09-12_110847/
Generating my_result_2024-09-12_110847//tpm_nopm.png ... OK
Generating my_result_2024-09-12_110847//latency.png ... OK
Generating my_result_2024-09-12_110847//cpu_utilization.png ... OK
Generating my_result_2024-09-12_110847//dirty_buffers.png ... OK
Generating my_result_2024-09-12_110847//blk_sda_iops.png ... OK
Generating my_result_2024-09-12_110847//blk_sda_kbps.png ... OK
Generating my_result_2024-09-12_110847//net_ens34_iops.png ... OK
Generating my_result_2024-09-12_110847//net_ens34_kbps.png ... OK
Generating my_result_2024-09-12_110847//report.html ... OK
[fbase@postgres run]$
测试结果展示
2.3、测试用例2：扩容
使用的命令
##使用命令添加节点
select master_add_node('192.168.6.107',5432);
##创建复制标识
alter table bmsql_history REPLICA IDENTITY full;
##重新分配分片
select * from rebalance_table_shards();
##验证分配完成
select distinct nodename,count(*) from citus_shards group by nodename;
输出的信息
--添加节点前的分片状态
postgres=# select distinct nodename from citus_shards;
nodename
---------------
192.168.6.110
192.168.6.109
(2 rows)
--执行开始重新分配命令
postgres=# select * from rebalance_table_shards();
NOTICE: Moving shard 112323 from 192.168.6.110:8432 to 192.168.6.107:5432 ...
NOTICE: Moving shard 112320 from 192.168.6.109:8432 to 192.168.6.107:5432 ...
NOTICE: Moving shard 112352 from 192.168.6.109:8432 to 192.168.6.107:5432 ...
NOTICE: Moving shard 112357 from 192.168.6.110:8432 to 192.168.6.107:5432 ...
rebalance_table_shards
------------------------
(1 row)
Time: 22610.374 ms (00:22.610)
--验证分配完成
postgres=# select distinct nodename,count(*) from citus_shards group by nodename;
nodename | count
---------------+-------
192.168.6.109 | 45
192.168.6.107 | 63
192.168.6.110 | 54
(3 rows)
2.4、测试用例3（扩容后）
本次测试使用的配置参数如下所示：
warehouses=10 ##表示数据量的大小，大约有11张表、1G的数据量
loadWorkers=20 ##工作进程数目
terminals=10 ##表示并发数
runTxnsPerTerminal=0
runMins=3
limitTxnsPerMin=3000000 ##每分钟允许的最大事务数，这表示测试过程中每分钟的最大事务负载为 300 万次。如果内存较小，不建议设置特别大。
##查看每个节点的分片数
select nodename,count(*) from citus_shards group by nodename;
nodename | count
---------------+-------
192.168.6.107 | 36
192.168.6.109 | 72
192.168.6.110 | 54
(3 rows)
##大约有300万条数据
SELECT
(SELECT COUNT(*) FROM public.bmsql_config) +
(SELECT COUNT(*) FROM public.bmsql_customer) +
(SELECT COUNT(*) FROM public.bmsql_district) +
(SELECT COUNT(*) FROM public.bmsql_history) +
(SELECT COUNT(*) FROM public.bmsql_item) +
(SELECT COUNT(*) FROM public.bmsql_new_order) +
(SELECT COUNT(*) FROM public.bmsql_oorder) +
(SELECT COUNT(*) FROM public.bmsql_order_line) +
(SELECT COUNT(*) FROM public.bmsql_stock) +
(SELECT COUNT(*) FROM public.bmsql_warehouse) AS total_rows;
total_rows
------------
3093093
(1 row)
2.4.1、调整参数、压力测试
必要的时候，可以修改my_postgres.properties参数文件
##执行测试命令
./runBenchmark.sh my_postgres.properties
2.4.2、测试结果输出
##生成具有html的报告，其中也包括系统IO等性能。
./generateReport.sh my_result_2024-09-11_131737/
信息输出
[fbase@postgres run]$ ./runBenchmark.sh my_postgres.properties
15:44:32,740 [main] INFO jTPCC : Term-00,
15:44:32,742 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+
15:44:32,742 [main] INFO jTPCC : Term-00, BenchmarkSQL v5.0
15:44:32,743 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+
15:44:32,743 [main] INFO jTPCC : Term-00, (c) 2003, Raul Barbosa
15:44:32,743 [main] INFO jTPCC : Term-00, (c) 2004-2016, Denis Lussier
15:44:32,745 [main] INFO jTPCC : Term-00, (c) 2016, Jan Wieck
15:44:32,745 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+
15:44:32,745 [main] INFO jTPCC : Term-00,
15:44:32,745 [main] INFO jTPCC : Term-00, db=postgres
15:44:32,745 [main] INFO jTPCC : Term-00, driver=org.postgresql.Driver
15:44:32,745 [main] INFO jTPCC : Term-00, conn=jdbc:postgresql://localhost:8432/postgres
15:44:32,745 [main] INFO jTPCC : Term-00, user=fbase
15:44:32,745 [main] INFO jTPCC : Term-00,
15:44:32,745 [main] INFO jTPCC : Term-00, warehouses=10
15:44:32,745 [main] INFO jTPCC : Term-00, terminals=10
15:44:32,747 [main] INFO jTPCC : Term-00, runMins=3
15:44:32,747 [main] INFO jTPCC : Term-00, limitTxnsPerMin=300000
15:44:32,747 [main] INFO jTPCC : Term-00, terminalWarehouseFixed=true
15:44:32,747 [main] INFO jTPCC : Term-00,
15:44:32,747 [main] INFO jTPCC : Term-00, newOrderWeight=45
15:44:32,747 [main] INFO jTPCC : Term-00, paymentWeight=43
15:44:32,747 [main] INFO jTPCC : Term-00, orderStatusWeight=4
15:44:32,747 [main] INFO jTPCC : Term-00, deliveryWeight=4
15:44:32,747 [main] INFO jTPCC : Term-00, stockLevelWeight=4
15:44:32,747 [main] INFO jTPCC : Term-00,
15:44:32,747 [main] INFO jTPCC : Term-00, resultDirectory=my_result_%tY-%tm-%td_%tH%tM%tS
15:44:32,748 [main] INFO jTPCC : Term-00, osCollectorScript=./misc/os_collector_linux.py
15:44:32,748 [main] INFO jTPCC : Term-00,
15:44:32,758 [main] INFO jTPCC : Term-00, copied my_postgres.properties to my_result_2024-09-13_154432/run.properties
15:44:32,758 [main] INFO jTPCC : Term-00, created my_result_2024-09-13_154432/data/runInfo.csv for runID 23
15:44:32,758 [main] INFO jTPCC : Term-00, writing per transaction results to my_result_2024-09-13_154432/data/result.csv
15:44:32,759 [main] INFO jTPCC : Term-00, osCollectorScript=./misc/os_collector_linux.py
15:44:32,759 [main] INFO jTPCC : Term-00, osCollectorInterval=1
15:44:32,759 [main] INFO jTPCC : Term-00, osCollectorSSHAddr=null
15:44:32,759 [main] INFO jTPCC : Term-00, osCollectorDevices=net_ens34 blk_sda
15:44:32,822 [main] INFO jTPCC : Term-00,
15:44:32,896 [main] INFO jTPCC : Term-00, C value for C_LAST during load: 32
15:44:32,897 [main] INFO jTPCC : Term-00, C value for C_LAST this run: 131
15:44:32,897 [main] INFO jTPCC : Term-00,
Term-00, Running Average tpmTOTAL: 11370.40 Current tpmTOTAL: 151512 Memory Usage: 35M08:51:35,353 [Thread-24] INFO jTPCC : Term-00,
08:51:35,353 [Thread-24] INFO jTPCC : Term-00,
08:51:35,354 [Thread-24] INFO jTPCC : Term-00, Measured tpmC (NewOrders) = 5102.58
08:51:35,354 [Thread-24] INFO jTPCC : Term-00, Measured tpmTOTAL = 11363.44
08:51:35,354 [Thread-24] INFO jTPCC : Term-00, Session Start = 2024-09-19 08:49:34
08:51:35,354 [Thread-24] INFO jTPCC : Term-00, Session End = 2024-09-19 08:51:35
08:51:35,356 [Thread-24] INFO jTPCC : Term-00, Transaction Count = 22799
[fbase@postgres run]$
测试结果分析
根据提供的测试报告，我们可以将这些指标整理到一个表格中：
| 指标 | 值 | 描述 |
|---|---|---|
| tpmC | 5102.58 | 新订单事务每分钟数 |
| tpmTOTAL | 11363.44 | 总事务每分钟数 |
| Session Start Time | 2024-09-13 15:44:32 | 会话开始时间 |
| Session End Time | 2024-09-13 15:47:33 | 会话结束时间 |
| Transaction Count | 22799 | 事务总数 |
这个表格展示了测试的关键性能指标，帮助了解数据库在TPC-C负载下的性能表现。
HTML的图片暂时先不展示了
2.5、测试结果汇总
综合两次测试结果来看，正常情况下添加一个节点性能会线性增长，由于条件有限（服务器无法承载大量的数据），没有达到预期。
三、TPCH测试
测试过程
生产测试数据->压测-> 扩容-> 压测
3.1、测试用例描述
一定要修改citus为OlAP模式
set citus.enable_repartition_joins = on;
set citus.task_executor_type='task-tracker';
set citus.task_executor_type='real-time';
使得分片更均衡
SELECT master_set_node_property('192.168.6.108', 8432, 'shouldhaveshards', true);
测试用例1比测试用例2少一个节点，测试的目的为：添加一个节点，citus分片集群性能是否有明显的提升
测试的数据量
##查看每个节点的分片数
select nodename,count(*) from citus_shards group by nodename;
nodename | count
---------------+-------
192.168.6.108 | 28
192.168.6.110 | 26
192.168.6.109 | 28
(3 rows)
##大约有86万条数据
SELECT
(SELECT COUNT(*) FROM t_user) +
(SELECT COUNT(*) FROM t_user_log_20191212) +
(SELECT COUNT(*) FROM customer) +
(SELECT COUNT(*) FROM lineitem) +
(SELECT COUNT(*) FROM nation) +
(SELECT COUNT(*) FROM orders) +
(SELECT COUNT(*) FROM part) +
(SELECT COUNT(*) FROM partsupp) +
(SELECT COUNT(*) FROM region) +
(SELECT COUNT(*) FROM supplier) AS total_rows;
total_rows
------------
8661245
(1 row)
3.2、测试用例1（扩容前）
3.2.1、初始化数据库、创建表格
DROP TABLE IF EXISTS public.citus_schemas CASCADE;
DROP TABLE IF EXISTS public.citus_tables CASCADE;
DROP TABLE IF EXISTS public.customer CASCADE;
DROP TABLE IF EXISTS public.lineitem CASCADE;
DROP TABLE IF EXISTS public.nation CASCADE;
DROP TABLE IF EXISTS public.orders CASCADE;
DROP TABLE IF EXISTS public.part CASCADE;
DROP TABLE IF EXISTS public.partsupp CASCADE;
DROP TABLE IF EXISTS public.region CASCADE;
DROP TABLE IF EXISTS public.supplier CASCADE;
CREATE TABLE NATION ( N_NATIONKEY INTEGER NOT NULL,
N_NAME CHAR(25) NOT NULL,
N_REGIONKEY INTEGER NOT NULL,
N_COMMENT VARCHAR(152));
CREATE TABLE REGION ( R_REGIONKEY INTEGER NOT NULL,
R_NAME CHAR(25) NOT NULL,
R_COMMENT VARCHAR(152));
CREATE TABLE PART ( P_PARTKEY INTEGER NOT NULL,
P_NAME VARCHAR(55) NOT NULL,
P_MFGR CHAR(25) NOT NULL,
P_BRAND CHAR(10) NOT NULL,
P_TYPE VARCHAR(25) NOT NULL,
P_SIZE INTEGER NOT NULL,
P_CONTAINER CHAR(10) NOT NULL,
P_RETAILPRICE DECIMAL(15,2) NOT NULL,
P_COMMENT VARCHAR(23) NOT NULL );
CREATE TABLE SUPPLIER ( S_SUPPKEY INTEGER NOT NULL,
S_NAME CHAR(25) NOT NULL,
S_ADDRESS VARCHAR(40) NOT NULL,
S_NATIONKEY INTEGER NOT NULL,
S_PHONE CHAR(15) NOT NULL,
S_ACCTBAL DECIMAL(15,2) NOT NULL,
S_COMMENT VARCHAR(101) NOT NULL);
CREATE TABLE CUSTOMER ( C_CUSTKEY INTEGER NOT NULL,
C_NAME VARCHAR(25) NOT NULL,
C_ADDRESS VARCHAR(40) NOT NULL,
C_NATIONKEY INTEGER NOT NULL,
C_PHONE CHAR(15) NOT NULL,
C_ACCTBAL DECIMAL(15,2) NOT NULL,
C_MKTSEGMENT CHAR(10) NOT NULL,
C_COMMENT VARCHAR(117) NOT NULL);
-- For table REGION
ALTER TABLE REGION
ADD PRIMARY KEY (R_REGIONKEY);
-- For table NATION
ALTER TABLE NATION
ADD PRIMARY KEY (N_NATIONKEY);
ALTER TABLE NATION
ADD FOREIGN KEY (N_REGIONKEY) references REGION;
-- For table PART
ALTER TABLE PART
ADD PRIMARY KEY (P_PARTKEY);
-- For table SUPPLIER
ALTER TABLE SUPPLIER
ADD PRIMARY KEY (S_SUPPKEY);
ALTER TABLE SUPPLIER
ADD FOREIGN KEY (S_NATIONKEY) references NATION;
-- For table CUSTOMER
ALTER TABLE CUSTOMER
ADD PRIMARY KEY (C_CUSTKEY);
ALTER TABLE CUSTOMER
ADD FOREIGN KEY (C_NATIONKEY) references NATION;
-- 将 'nation' 表创建为参考表
SELECT create_reference_table('nation');
-- 将 'part' 表创建为参考表
SELECT create_reference_table('part');
-- 将 'region' 表创建为参考表
SELECT create_reference_table('region');
-- 将 'supplier' 表创建为参考表
SELECT create_reference_table('supplier');
CREATE TABLE PARTSUPP ( PS_PARTKEY INTEGER NOT NULL,
PS_SUPPKEY INTEGER NOT NULL,
PS_AVAILQTY INTEGER NOT NULL,
PS_SUPPLYCOST DECIMAL(15,2) NOT NULL,
PS_COMMENT VARCHAR(199) NOT NULL );
-- For table PARTSUPP
ALTER TABLE PARTSUPP
ADD PRIMARY KEY (PS_PARTKEY, PS_SUPPKEY);
-- For table PARTSUPP
ALTER TABLE PARTSUPP
ADD FOREIGN KEY (PS_SUPPKEY) references SUPPLIER;
ALTER TABLE PARTSUPP
ADD FOREIGN KEY (PS_PARTKEY) references PART;
-- 将表设置为参考表
SELECT create_reference_table('partsupp');
SELECT create_reference_table('customer');
CREATE TABLE ORDERS ( O_ORDERKEY INTEGER NOT NULL,
O_CUSTKEY INTEGER NOT NULL,
O_ORDERSTATUS CHAR(1) NOT NULL,
O_TOTALPRICE DECIMAL(15,2) NOT NULL,
O_ORDERDATE DATE NOT NULL,
O_ORDERPRIORITY CHAR(15) NOT NULL,
O_CLERK CHAR(15) NOT NULL,
O_SHIPPRIORITY INTEGER NOT NULL,
O_COMMENT VARCHAR(79) NOT NULL);
-- For table ORDERS
ALTER TABLE ORDERS
ADD PRIMARY KEY (O_ORDERKEY);
-- For table ORDERS
ALTER TABLE ORDERS
ADD FOREIGN KEY (O_CUSTKEY) references CUSTOMER;
-- 将表设置为分布式表
select create_distributed_table('orders', 'o_orderkey');
CREATE TABLE LINEITEM ( L_ORDERKEY INTEGER NOT NULL,
L_PARTKEY INTEGER NOT NULL,
L_SUPPKEY INTEGER NOT NULL,
L_LINENUMBER INTEGER NOT NULL,
L_QUANTITY DECIMAL(15,2) NOT NULL,
L_EXTENDEDPRICE DECIMAL(15,2) NOT NULL,
L_DISCOUNT DECIMAL(15,2) NOT NULL,
L_TAX DECIMAL(15,2) NOT NULL,
L_RETURNFLAG CHAR(1) NOT NULL,
L_LINESTATUS CHAR(1) NOT NULL,
L_SHIPDATE DATE NOT NULL,
L_COMMITDATE DATE NOT NULL,
L_RECEIPTDATE DATE NOT NULL,
L_SHIPINSTRUCT CHAR(25) NOT NULL,
L_SHIPMODE CHAR(10) NOT NULL,
L_COMMENT VARCHAR(44) NOT NULL);
-- For table LINEITEM
ALTER TABLE LINEITEM
ADD PRIMARY KEY (L_ORDERKEY, L_LINENUMBER);
-- For table LINEITEM
ALTER TABLE LINEITEM
ADD FOREIGN KEY (L_ORDERKEY) references ORDERS;
--设置为分布表
select create_distributed_table('lineitem', 'l_orderkey');
ALTER TABLE LINEITEM
ADD FOREIGN KEY (L_PARTKEY, L_SUPPKEY) references PARTSUPP;
3.2.2、调整参数、生成数据
--生成数据
./dbgen -s 1 -f -v
-s 1 表示生成1G数据
-f 覆盖之前产生的文件
--多（8）个线程生成数据
#!/bin/sh
./dbgen -vf -s 500 -S 1 -C 8 &
./dbgen -vf -s 500 -S 2 -C 8 &
./dbgen -vf -s 500 -S 3 -C 8 &
./dbgen -vf -s 500 -S 4 -C 8 &
./dbgen -vf -s 500 -S 5 -C 8 &
./dbgen -vf -s 500 -S 6 -C 8 &
./dbgen -vf -s 500 -S 7 -C 8 &
./dbgen -vf -s 500 -S 8 -C 8 &
##转换成csv格式
for i in `ls *.tbl`;do sed 's/|$//' $i > ${i/tbl/csv};echo $i;done;
##查看生成的数据
[fbase@postgres dbgen]$ ls *.tbl
customer.tbl nation.tbl partsupp.tbl region.tbl
lineitem.tbl orders.tbl part.tbl supplier.tbl
[fbase@postgres dbgen]$
###导入数据脚本
$ cat import_tables.sh
#!/bin/bash
# 确保脚本接收到一个参数
if [ "$#" -ne 1 ]; then
echo "Usage: $0 <filename>"
exit 1
fi
file=$1
# 确保输入的文件存在
if [ ! -f "$file" ]; then
echo "File $file does not exist."
exit 1
fi
# 提取文件名（不包括扩展名）
name=$(basename "$file" .tbl)
# 处理文件
echo "Processing $file"
sed -i 's/|$//' "$file"
psql -c "COPY $name FROM '$(pwd)/$file' DELIMITER '|' ENCODING 'LATIN1';"
##导入顺序
sh import_tables.sh region.tbl
sh import_tables.sh nation.tbl
sh import_tables.sh part.tbl
sh import_tables.sh supplier.tbl
sh import_tables.sh customer.tbl
sh import_tables.sh partsupp.tbl
sh import_tables.sh orders.tbl
sh import_tables.sh lineitem.tbl
##添加索引
##查看分布式集群的分片数
postgres=# SELECT nodename, COUNT(*)
FROM citus_shards
GROUP BY nodename;
nodename | count
---------------+-------
192.168.6.108 | 28
192.168.6.110 | 26
192.168.6.109 | 28
(3 rows)
3.2.3、调整参数、压力测试
生成SQL查询语句
##生成SQL查询语句
for i in {1..22}
do
name="./sql/d$i.sql"
echo $name
./qgen -d $i >$name
done
编写查询脚本，自动获取SQL响应时间
##编写查询脚本，自动获取SQL响应时间
cat execute.sh
#!/bin/bash
# 定义数据库名称和结果目录
DB_NAME="postgres"
RESULT_DIR="./result"
# 确保结果目录存在
mkdir -p $RESULT_DIR
# 执行 SQL 文件
for i in {1..21}
do
if [[ $i -ne 17 && $i -ne 20 ]]
then
SQL_FILE="d${i}.sql"
RESULT_FILE="${RESULT_DIR}/result${i}.txt"
echo "Running $SQL_FILE..."
psql -d $DB_NAME -f $SQL_FILE > $RESULT_FILE
else
echo "Skipping d${i}.sql"
fi
done
# 提取出SQL语句的响应时间
for i in {1..21}
do
RESULT_FILE="${RESULT_DIR}/result${i}.txt"
if [[ $i -eq 15 ]]; then
echo -n "Q$i response " && tail -n 3 $RESULT_FILE | head -n 1
elif [[ $i -ne 17 && $i -ne 20 ]]; then
echo -n "Q$i response " && tail -n 1 $RESULT_FILE
fi
done
开启nmon的时候测试tpch
##开启检测进程
[root@postgres ~]# nmon -f -s 2 -c 100 -m /home/pg/nmonlog
##查看状态
[root@postgres ~]# ps -ef|grep nmon
3.2.4、输出测试结果
输出信息
[fbase@postgres dbgen]$ sh import_tables.sh part.tbl
Processing part.tbl
COPY 200000
[fbase@postgres dbgen]$ sh import_tables.sh supplier.tbl
Processing supplier.tbl
COPY 10000
[fbase@postgres dbgen]$ sh import_tables.sh customer.tbl
Processing customer.tbl
COPY 150000
[fbase@postgres dbgen]$ sh import_tables.sh partsupp.tbl
Processing partsupp.tbl
COPY 800000
[fbase@postgres dbgen]$ sh import_tables.sh orders.tbl
Processing orders.tbl
COPY 1500000
[fbase@postgres dbgen]$ sh import_tables.sh lineitem.tbl
Processing lineitem.tbl
COPY 6001215
[fbase@postgres dbgen]$
sh execute.sh
Running d1.sql...
Running d2.sql...
Running d3.sql...
Running d4.sql...
Running d5.sql...
Running d6.sql...
Running d7.sql...
Running d8.sql...
Running d9.sql...
Running d10.sql...
Running d11.sql...
Running d12.sql...
Running d13.sql...
Running d14.sql...
Running d15.sql...
Running d16.sql...
Skipping d17.sql
Running d18.sql...
Running d19.sql...
Skipping d20.sql
Running d21.sql...
Q1 response Time: 1151.499 ms (00:01.151)
Q2 response Time: 225.830 ms
Q3 response Time: 474.648 ms
Q4 response Time: 342.354 ms
Q5 response Time: 645.930 ms
Q6 response Time: 234.146 ms
Q7 response Time: 378.565 ms
Q8 response Time: 689.490 ms
Q9 response Time: 865.872 ms
Q10 response Time: 567.329 ms
Q11 response Time: 134.626 ms
Q12 response Time: 332.472 ms
Q13 response Time: 5030.917 ms (00:05.031)
Q14 response Time: 265.414 ms
Q15 response Time: 1134.336 ms (00:01.134)
Q16 response Time: 273.432 ms
Q18 response Time: 808.995 ms
Q19 response Time: 412.101 ms
Q21 response Time: 371.585 ms
nmon系统使用收集指标
可以使用nmonchart生成html格式的，但是需要VPN去Google浏览器渲染图片，也可以下载nmon_analyser_v分析组件，但是需要商业版的wps才可以打开他。
现在生成的nmon文件到本地windows，用官方提供的分析工具打开，设置excel宏打开。
打开nmon分析文件
3.3、测试用例2：扩容
添加节点到citus集群，执行命令
##添加节点
select citus_add_node('192.168.6.107',5432);
##平衡分片
select rebalance_table_shards();
##查看个个节点的分片数
select nodename,count(*) from citus_shards group by nodename;
输出的信息
##添加节点
postgres=# select citus_add_node('192.168.6.107',5432);
citus_add_node
----------------
16
(1 row)
##平衡分片
postgres=# select rebalance_table_shards();
NOTICE: replicating reference table 'nation' to 192.168.6.107:5432 ...
NOTICE: Moving shard 116770 from 192.168.6.108:8432 to 192.168.6.107:5432 ...
NOTICE: Moving shard 116774 from 192.168.6.109:8432 to 192.168.6.107:5432 ...
NOTICE: Moving shard 116776 from 192.168.6.108:8432 to 192.168.6.107:5432 ...
NOTICE: Moving shard 116754 from 192.168.6.110:8432 to 192.168.6.107:5432 ...
NOTICE: Moving shard 116771 from 192.168.6.109:8432 to 192.168.6.107:5432 ...
NOTICE: Moving shard 116758 from 192.168.6.108:8432 to 192.168.6.107:5432 ...
NOTICE: Moving shard 116775 from 192.168.6.110:8432 to 192.168.6.107:5432 ...
NOTICE: Moving shard 116753 from 192.168.6.109:8432 to 192.168.6.107:5432 ...
rebalance_table_shards
------------------------
(1 row)
Time: 11652.738 ms (00:11.653)
##查看个个节点的分片数
postgres=# select nodename,count(*) from citus_shards group by nodename;
nodename | count
---------------+-------
192.168.6.108 | 22
192.168.6.107 | 22
192.168.6.110 | 22
192.168.6.109 | 22
(4 rows)
3.4、测试用例3（扩容后）
执行命令,获取查询语句响应时间
##执行测试脚本，获取查询语句响应时间
sh execute.sh
输出的信息
##执行测试脚本，获取查询语句响应时间
[fbase@postgres sql]$ sh execute.sh
Running d1.sql...
Running d2.sql...
Running d3.sql...
Running d4.sql...
Running d5.sql...
Running d6.sql...
Running d7.sql...
Running d8.sql...
Running d9.sql...
Running d10.sql...
Running d11.sql...
Running d12.sql...
Running d13.sql...
Running d14.sql...
Running d15.sql...
Running d16.sql...
Skipping d17.sql
Running d18.sql...
Running d19.sql...
Skipping d20.sql
Running d21.sql...
Q1 response Time: 1169.370 ms (00:01.169)
Q2 response Time: 224.707 ms
Q3 response Time: 446.181 ms
Q4 response Time: 318.128 ms
Q5 response Time: 628.814 ms
Q6 response Time: 258.494 ms
Q7 response Time: 363.685 ms
Q8 response Time: 580.927 ms
Q9 response Time: 835.688 ms
Q10 response Time: 545.771 ms
Q11 response Time: 137.307 ms
Q12 response Time: 311.096 ms
Q13 response Time: 5671.297 ms (00:05.671)
Q14 response Time: 261.195 ms
Q15 response Time: 964.076 ms
Q16 response Time: 274.727 ms
Q18 response Time: 797.505 ms
Q19 response Time: 375.844 ms
Q21 response Time: 365.105 ms
3.5、测试结果汇总
以下是整理后的数据表格，展示了扩容前后的关键指标对比：
扩容前 vs 扩容后 数据对比表
| 阶段 | 指标 | 扩容前 | 扩容后 | 提升/下降百分比 |
|---|---|---|---|---|
| 数据量 | 数据量 | 8661245（86万） | 8661245（86万） | 0% |
| Q1查询响应时间 | 1151.499 ms (00:01.151) | 1169.370 ms (00:01.169) | -1.05% | |
| Q2查询响应时间 | 225.830 ms | 224.707 ms | 0.5% | |
| Q3查询响应时间 | 474.648 ms | 446.181 ms | 5.98% | |
| Q4查询响应时间 | 342.354 ms | 318.128 ms | 7.07% | |
| Q5查询响应时间 | 645.930 ms | 628.814 ms | 2.28% | |
| Q6查询响应时间 | 234.146 ms | 258.494 ms | -10.41% | |
| Q7查询响应时间 | 378.565 ms | 363.685 ms | 3.92% | |
| Q8查询响应时间 | 689.490 ms | 580.927 ms | 15.75% | |
| Q9查询响应时间 | 865.872 ms | 835.688 ms | 3.48% | |
| Q10查询响应时间 | 567.329 ms | 545.771 ms | 3.79% | |
| Q11查询响应时间 | 134.626 ms | 137.307 ms | -1.99% | |
| Q12查询响应时间 | 332.472 ms | 311.096 ms | 6.39% | |
| Q13查询响应时间 | 5030.917 ms (00:05.031) | 5671.297 ms (00:05.671) | -12.85% | |
| Q14查询响应时间 | 265.414 ms | 261.195 ms | 1.65% | |
| Q15查询响应时间 | 1134.336 ms (00:01.134) | 964.076 ms | 15% | |
| Q16查询响应时间 | 273.432 ms | 274.727 ms | -0.47% | |
| Q18查询响应时间 | 808.995 ms | 797.505 ms | 1.84% | |
| Q19查询响应时间 | 412.101 ms | 375.844 ms | 8.76% | |
| Q21查询响应时间 | 371.585 ms | 365.105 ms | 1.3% | |
| Citus集群 | 节点数 | 1✖Coordinator + 2✖Worker | 1✖Coordinator + 3✖Worker | - |
| 个个节点分片数 | nodename | count 192.168.6.108 | 28 192.168.6.110 | 26 192.168.6.109 | 28 | nodename | count 192.168.6.108 | 22 192.168.6.107 | 22 192.168.6.110 | 22 192.168.6.109 | 22 | - |
扩容后重新分配分片持续时间：Time: 11652.738 ms (00:11.653)
总结
- 扩容后，多个查询的响应时间显著提升，尤其是Q15（提升15%）、Q8（提升15.75%）和Q4（提升7.07%），显示出明显的性能改进。
- 尽管部分查询如Q6和Q13的响应时间有所增加，但整体上大多数查询性能均有所提升。
- 整体来看，扩容后的系统在查询响应效率上表现优越，尤其在提升幅度较大的查询上，系统性能得到了明显改善。
TPCH度量指标
测试中测量的基础数据都与执行时间有关，这些时间又可分为：装载数据的每一步操作时间、每个查询执行时间和每个更新操作执行时间，由这些时间可计算出：数据装载时间、QphH@Size, Power@Size, and Throughput@Size.