一个网址建多个网站,云南凡科建站哪家好,泰安做网站优化,番禺网站制作哪里有Citus的TPCC、TPCH性能测试 文章目录 Citus的TPCC、TPCH性能测试测试的目的适用范围测试环境架构信息硬件配置操作系统软件版本 测试结果TPCC测试测试结果TPCH测试测试结果 一、环境部署1.1、安装BenchmarkSQL1.2、PostgreSQL安装1.3、nmon部署1.4、TPC-H测试的生成数据工具安装…Citus的TPCC、TPCH性能测试 文章目录 Citus的TPCC、TPCH性能测试测试的目的适用范围测试环境架构信息硬件配置操作系统软件版本 测试结果TPCC测试测试结果TPCH测试测试结果 一、环境部署1.1、安装BenchmarkSQL1.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、测试结果汇总 测试的目的
本次为CitusPostgreSQL分片数据库性能测试,测试目的为测试CitusPostgreSQL数据库的事务处理能力,编写本文档,以作参考.
适用范围
本测试计划适用于PostgreSQL数据库功能和性能测试.
测试环境
架构信息
主机名IP操作系统内存/空间角色说明pgcn192.168.6.108rhel72c/18G 400Gcoordinate安装PGDB16.4 Citus 12.1-1pgwk01192.168.6.109rhel72c/18G 400Gworker安装PGDB16.4 Citus 12.1-1pgwk02192.168.6.110rhel72c/18G 400Gworker安装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_64PostgreSQL 16.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit CitusCitus 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 BenchmarkSQLVersion 5.0 nmonnmon version 16e
测试结果
TPCC测试测试结果
测试结果
以下是整理后的数据表格包括扩容前后的变化对比
扩容前 vs 扩容后 数据对比表
以下是根据您提供的数据更新后的表格
指标 \ 阶段扩容前扩容后变化描述tpmC4725.595102.58376.99新订单事务每分钟数tpmTOTAL10512.1511363.44851.29总事务每分钟数Transaction Count21123227991676事务总数Citus集群节点数1✖Coordinator 2✖Worker1✖Coordinator 3✖Worker1个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 扩容后 数据对比表
阶段指标扩容前扩容后提升/下降百分比数据量数据量866124586万866124586万0%Q1查询响应时间1151.499 ms (00:01.151)1169.370 ms (00:01.169)-1.05%Q2查询响应时间225.830 ms224.707 ms0.5%Q3查询响应时间474.648 ms446.181 ms5.98%Q4查询响应时间342.354 ms318.128 ms7.07%Q5查询响应时间645.930 ms628.814 ms2.28%Q6查询响应时间234.146 ms258.494 ms-10.41%Q7查询响应时间378.565 ms363.685 ms3.92%Q8查询响应时间689.490 ms580.927 ms15.75%Q9查询响应时间865.872 ms835.688 ms3.48%Q10查询响应时间567.329 ms545.771 ms3.79%Q11查询响应时间134.626 ms137.307 ms-1.99%Q12查询响应时间332.472 ms311.096 ms6.39%Q13查询响应时间5030.917 ms (00:05.031)5671.297 ms (00:05.671)-12.85%Q14查询响应时间265.414 ms261.195 ms1.65%Q15查询响应时间1134.336 ms (00:01.134)964.076 ms15%Q16查询响应时间273.432 ms274.727 ms-0.47%Q18查询响应时间808.995 ms797.505 ms1.84%Q19查询响应时间412.101 ms375.844 ms8.76%Q21查询响应时间371.585 ms365.105 ms1.3%Citus集群节点数1✖Coordinator 2✖Worker1✖Coordinator 3✖Worker-每个节点所占有的分片数nodename | count 192.168.6.108 | 28 192.168.6.110 | 26 192.168.6.109 | 28nodename | 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源云环境
[rootpostgres yum.repos.d]# cat CentOS-Base.repo
# CentOS-Base.repo
[base]
nameCentOS-$releasever - Base - mirrors.aliyun.com
failovermethodpriority
baseurlhttp://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/
gpgcheck1
gpgkeyhttp://mirrors.aliyun.com/centos/RPM-GPG-KEY-CentOS-7#released updates
[updates]
nameCentOS-$releasever - Updates - mirrors.aliyun.com
failovermethodpriority
baseurlhttp://mirrors.aliyun.com/centos/7/updates/$basearch/http://mirrors.aliyuncs.com/centos/$releasever/updates/$basearch/http://mirrors.cloud.aliyuncs.com/centos/$releasever/updates/$basearch/
gpgcheck1
gpgkeyhttp://mirrors.aliyun.com/centos/RPM-GPG-KEY-CentOS-7[rootpostgres yum.repos.d]# 安装Ant
yum -y install ant安装R语言
yum -y install epel-release
yum -y install R检查jdk环境要求JDK 7以上版本
[rootpostgres 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.zipunzip ./benchmarksql-5.0.zip###编译BenchmarkSQL源码
[pgpostgres benchmarksql-5.0]$ pwd
/home/pg/benchmarksql-5.0
[pgpostgres benchmarksql-5.0]$ ant##创建配置文件,进入run目录, 复制props.pg文件并编辑产生的副本设置与基准测试有关的参数
[pgpostgres benchmarksql-5.0]$ cd run
[pgpostgres run]$ cp props.pg my_postgres.properties
[pgpostgres run]$ vi my_postgres.properties参数配置说明
connjdbc:postgresql://localhost:5432/postgres
将其中的 “localhost” 修改为postgresql所在服务器的ip地址将“5432”修改为 postgresql所在的端口将末尾的 “postgres” 修改为所测量的数据库。本次实验将其修改为 connjdbc:postgresql://localhost:5432/ benchmarksqluserfbase
将“benchmarksql”修改为用来测试的用户。本次实验不做修改。passwordfbase
将“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参数说明
参数配置值说明-p8432数据库监听端口默认值为 8432-u用户名运行和安装 FBase 的系统用户-b/usr/local/fbase/‘fbase_version’FBase 的主目录默认值为 /usr/local/fbase/‘fbase_version’-d数据目录FBase 的数据目录-DSSD数据存储设备的类型有效选项为 HDD 或 SSD默认值为 SSD-c100FBase 的最大连接数默认值为 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 -重命名
[rootsdw-135 nmon]# cp nmon_x86_64_centos7 /usr/local/bin/nmon
[rootsdw-135 nmon]# nmon
nmon-16e------[H for help]---Hostnamepostgres-----Refresh 2secs ---13:56.44
| |
| ------------------------------ |
| _ __ _ __ ___ ___ _ __ For help type H or ... |
| | _ \| _ _ \ / _ \| _ \ nmon -? - hint |
| | | | | | | | | | (_) | | | | nmon -h - full details |
| |_| |_|_| |_| |_|\___/|_| |_| |
| To stop nmon type q to Quit |
| ------------------------------ |
| |
| NAMERed Hat Enterprise Linux Server VERSION7.7 (Maipo) |
| AuthenticAMD AMD Ryzen 5 4600H with Radeon Graphics |
| MHz2994.374 bogomips5988.74 |
| ProcessorChips1 PhyscalCores1 |
| 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/nmonlognohup ./runBenchmark.sh my_postgres.properties citus.txt 21
nohup ./runBenchmark.sh my_single.properties single.txt 21
#开启收集-s 每隔x秒收集一次 -c 收集次数
nmon -f -s 2 -c 960 -m /root/nmonlog
ps -ef | grep nmon[rootpostgres ~]# nmon -f -s 5 -c 10 -m /home/pg/nmonlog
[rootpostgres ~]# 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 --colorauto nmon
[rootpostgres ~]# cd /home/pg/nmonlog
[rootpostgres 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 命令
pgpostgres tpch-kit-master]$ make编译完成后会自动生成2个执行文件
[pgpostgres dbgen]$ ls *gen
dbgen qgen
[pgpostgres dbgen]$ dbgen生成表数据(csv文件)qgen生成复杂查询SQL
**需要注意**直接执行 qgen 会出现找不到的错误
设置环境变量
export DSS_QUERY./queries再次执行qgen成功生成22条复杂查询SQL
二、TPCC测试
测试过程
生产测试数据-压测- 扩容- 压测。
1测试过程benchmarksql采用相同配置,配置如下
dbpostgres # 数据库类型设置为 PostgreSQL
driverorg.postgresql.Driver # JDBC 驱动程序类
connjdbc:postgresql://localhost:5432/postgres # JDBC 连接 URL连接到本地 PostgreSQL 数据库
userfbase # 数据库用户名
passwordfbase # 数据库用户密码warehouses100 # 使用的仓库数量
loadWorkers50 # 负载生成器的工作线程数量terminals60 # 运行的终端数量
runTxnsPerTerminal0 # 每个终端要运行的事务数量,0表示不限制
runMins2 # 测试运行时间单位为分钟
limitTxnsPerMin30000000 # 每分钟允许的最大事务数
terminalWarehouseFixedtrue # 是否每个终端固定使用一个仓库
newOrderWeight45 # 新订单事务的权重
paymentWeight43 # 支付事务的权重
orderStatusWeight4 # 订单状态查询事务的权重
deliveryWeight4 # 配送事务的权重
stockLevelWeight4 # 库存水平查询事务的权重
resultDirectorymy_result_%tY-%tm-%td_%tH%tM%tS # 结果数据保存的目录名称模板
osCollectorScript./misc/os_collector_linux.py # 操作系统性能数据收集脚本的路径
osCollectorInterval1 # 操作系统性能数据收集的间隔时间单位为秒
//osCollectorSSHAddruserdbhost # 操作系统性能数据收集的 SSH 地址被注释掉
osCollectorDevicesnet_ens34 blk_sda # 指定要监控的系统设备2整个citus数据库集群和系统调整不同节点数进程测试其中这些测试用例的参数配置完全一致改变的只有citus集群节点数。
2.1、测试用例描述
各个节点的磁盘I/O速率
使用命令dd if/dev/sda of/tmp/test bs64k count40k oflagdsync测出磁盘I/O速率如下所示
节点速度108节点246 MB/s109节点162 MB/s110节点151 MB/s107节点180 MB/s
参数说明
参数说明if/dev/sda输入文件或设备从 /dev/sda 读取数据of/tmp/test输出文件写入到 /tmp/testbs64k块大小每次读写 64 KB 数据count40k数据块数量总共 40,000 个块oflagdsync输出标志每次写入同步到磁盘
benchmarksql的参数配置说明
**主要调整以下参数**warehouses100 # 使用的仓库数量数值越大生成的数据量越大。
loadWorkers50 # 负载生成器的工作线程数量terminals60 # 运行的终端数量可以模拟连接数据库的客户端数量。
runTxnsPerTerminal0 # 每个终端要运行的事务数量,0表示不限制
runMins2 # 测试运行时间单位为分钟一般测试为1~3小时
limitTxnsPerMin30000000 # 每分钟允许的最大事务数注意warehouses不建议设置的很多不然生成数据的时间过长
测试用例1和测试用例2主要区别在于Citus节点数量不同测试用例2使用的citus节点比测试用例1多一个。
主要测试的内容为添加一个节点citus分片集群性能是否有明显的提升
2.2、测试用例1扩容前
本次测试使用的配置参数如下所示
warehouses6 ##表示数据量的大小大约有11张表、6G的数据量
loadWorkers18 ##工作进程数目terminals10 ##表示并发数runTxnsPerTerminal0
runMins2
limitTxnsPerMin3000000 ##每分钟允许的最大事务数这表示测试过程中每分钟的最大事务负载为 300 万次。如果内存较小不建议设置特别大。##查看每个节点的分片数
SELECT nodename, COUNT(*)
FROM citus_shards
GROUP BY nodename;nodename | count
----------------------192.168.6.110 | 81192.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_idx1on bmsql_customer (c_w_id, c_d_id, c_last, c_first);create unique index bmsql_oorder_idx1on bmsql_oorder (o_w_id, o_d_id, o_carrier_id, o_id)2.2.2、生成数据
## 生成数据
./runDatabaseBuild.sh my_postgres.properties2.2.3、调整参数、压力测试
##开始测试
./runBenchmark.sh my_postgres.properties2.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 | f2 | 1 | 192.168.6.109 | 8432 | default | t | t | primary | default | t | t3 | 2 | 192.168.6.110 | 8432 | default | t | t | primary | default | t | t
(3 rows)--清除环境删除原来的表
[fbasepostgres 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;
[fbasepostgres 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 | heapbmsql_customer | distributed | c_w_id | 219 | 128 MB | 32 | fbase | heapbmsql_district | distributed | d_w_id | 219 | 544 kB | 32 | fbase | heapbmsql_history | distributed | h_c_id | 219 | 18 MB | 32 | fbase | heapbmsql_item | distributed | i_id | 219 | 14 MB | 32 | fbase | heapbmsql_new_order | distributed | no_w_id | 219 | 5096 kB | 32 | fbase | heapbmsql_oorder | distributed | o_w_id | 219 | 25 MB | 32 | fbase | heapbmsql_order_line | distributed | ol_w_id | 219 | 241 MB | 32 | fbase | heapbmsql_stock | distributed | s_w_id | 219 | 223 MB | 32 | fbase | heapbmsql_warehouse | distributed | w_id | 219 | 544 kB | 32 | fbase | heap
(10 rows)--两个worker节点
postgres# select distinct nodename from citus_shards;nodename
---------------192.168.6.109192.168.6.110
(2 rows)--生成数据这里不建议把warehouses配置的特别多因为生成数据很慢
[fbasepostgres run]$ ./runDatabaseBuild.sh my_postgres.properties
# ------------------------------------------------------------
# Loading SQL file ./sql.common/tableCreates.sql
# ------------------------------------------------------------
Starting BenchmarkSQL LoadDataWorker 000: Loading ITEM
Worker 001: Loading Warehouse 1
Worker 002: Loading Warehouse 2Worker 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 | heapbmsql_customer | distributed | c_w_id | 23 | 21 MB | 32 | fbase | heapbmsql_district | distributed | d_w_id | 23 | 304 kB | 32 | fbase | heapbmsql_item | distributed | i_id | 23 | 14 MB | 32 | fbase | heapbmsql_oorder | distributed | o_w_id | 23 | 4520 kB | 32 | fbase | heapbmsql_warehouse | distributed | w_id | 23 | 304 kB | 32 | fbase | heap
(6 rows)postgres# \dList of relationsSchema | 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)--开始测试
[fbasepostgres 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, dbpostgres
15:27:16,899 [main] INFO jTPCC : Term-00, driverorg.postgresql.Driver
15:27:16,899 [main] INFO jTPCC : Term-00, connjdbc:postgresql://localhost:8432/postgres
15:27:16,899 [main] INFO jTPCC : Term-00, userfbase
15:27:16,899 [main] INFO jTPCC : Term-00,
15:27:16,899 [main] INFO jTPCC : Term-00, warehouses10
15:27:16,899 [main] INFO jTPCC : Term-00, terminals10
15:27:16,900 [main] INFO jTPCC : Term-00, runMins3
15:27:16,900 [main] INFO jTPCC : Term-00, limitTxnsPerMin300000
15:27:16,900 [main] INFO jTPCC : Term-00, terminalWarehouseFixedtrue
15:27:16,901 [main] INFO jTPCC : Term-00,
15:27:16,901 [main] INFO jTPCC : Term-00, newOrderWeight45
15:27:16,901 [main] INFO jTPCC : Term-00, paymentWeight43
15:27:16,901 [main] INFO jTPCC : Term-00, orderStatusWeight4
15:27:16,901 [main] INFO jTPCC : Term-00, deliveryWeight4
15:27:16,901 [main] INFO jTPCC : Term-00, stockLevelWeight4
15:27:16,901 [main] INFO jTPCC : Term-00,
15:27:16,901 [main] INFO jTPCC : Term-00, resultDirectorymy_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, osCollectorInterval1
15:27:16,926 [main] INFO jTPCC : Term-00, osCollectorSSHAddrnull
15:27:16,926 [main] INFO jTPCC : Term-00, osCollectorDevicesnet_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
[fbasepostgres run]$ 测试报告分析
在进行TPC-CTransaction 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: 测试会话期间处理的事务总数。
根据提供的测试报告我们可以将这些指标整理到一个表格中
指标值描述tpmC4725.59新订单事务每分钟数tpmTOTAL10512.15总事务每分钟数Session Start Time2024-09-18 15:34:14会话开始时间Session End Time2024-09-18 15:36:14会话结束时间Transaction Count21123事务总数
这个表格展示了测试的关键性能指标帮助了解数据库在TPC-C负载下的性能表现。
生成具有HTML的报告
测试结束后run目录下会生成一个新目录它的命名格式为 my_result_%tY-%tm-%td_%tH%tM%tS。 使用 generateReport.sh my_result_* 脚本创建具有图形的 HTML 文件
[fbasepostgres 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
[fbasepostgres 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.110192.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 | 45192.168.6.107 | 63192.168.6.110 | 54
(3 rows)2.4、测试用例3扩容后
本次测试使用的配置参数如下所示
warehouses10 ##表示数据量的大小大约有11张表、1G的数据量
loadWorkers20 ##工作进程数目terminals10 ##表示并发数runTxnsPerTerminal0
runMins3
limitTxnsPerMin3000000 ##每分钟允许的最大事务数这表示测试过程中每分钟的最大事务负载为 300 万次。如果内存较小不建议设置特别大。##查看每个节点的分片数
select nodename,count(*) from citus_shards group by nodename;nodename | count
----------------------192.168.6.107 | 36192.168.6.109 | 72192.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.properties2.4.2、测试结果输出
##生成具有html的报告其中也包括系统IO等性能。
./generateReport.sh my_result_2024-09-11_131737/信息输出
[fbasepostgres 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, dbpostgres
15:44:32,745 [main] INFO jTPCC : Term-00, driverorg.postgresql.Driver
15:44:32,745 [main] INFO jTPCC : Term-00, connjdbc:postgresql://localhost:8432/postgres
15:44:32,745 [main] INFO jTPCC : Term-00, userfbase
15:44:32,745 [main] INFO jTPCC : Term-00,
15:44:32,745 [main] INFO jTPCC : Term-00, warehouses10
15:44:32,745 [main] INFO jTPCC : Term-00, terminals10
15:44:32,747 [main] INFO jTPCC : Term-00, runMins3
15:44:32,747 [main] INFO jTPCC : Term-00, limitTxnsPerMin300000
15:44:32,747 [main] INFO jTPCC : Term-00, terminalWarehouseFixedtrue
15:44:32,747 [main] INFO jTPCC : Term-00,
15:44:32,747 [main] INFO jTPCC : Term-00, newOrderWeight45
15:44:32,747 [main] INFO jTPCC : Term-00, paymentWeight43
15:44:32,747 [main] INFO jTPCC : Term-00, orderStatusWeight4
15:44:32,747 [main] INFO jTPCC : Term-00, deliveryWeight4
15:44:32,747 [main] INFO jTPCC : Term-00, stockLevelWeight4
15:44:32,747 [main] INFO jTPCC : Term-00,
15:44:32,747 [main] INFO jTPCC : Term-00, resultDirectorymy_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, osCollectorInterval1
15:44:32,759 [main] INFO jTPCC : Term-00, osCollectorSSHAddrnull
15:44:32,759 [main] INFO jTPCC : Term-00, osCollectorDevicesnet_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
[fbasepostgres run]$ 测试结果分析
根据提供的测试报告我们可以将这些指标整理到一个表格中
指标值描述tpmC5102.58新订单事务每分钟数tpmTOTAL11363.44总事务每分钟数Session Start Time2024-09-13 15:44:32会话开始时间Session End Time2024-09-13 15:47:33会话结束时间Transaction Count22799事务总数
这个表格展示了测试的关键性能指标帮助了解数据库在TPC-C负载下的性能表现。
HTML的图片暂时先不展示了
2.5、测试结果汇总
综合两次测试结果来看正常情况下添加一个节点性能会线性增长由于条件有限服务器无法承载大量的数据没有达到预期。
三、TPCH测试
测试过程
生产测试数据-压测- 扩容- 压测
3.1、测试用例描述
一定要修改citus为OlAP模式set citus.enable_repartition_joins on;set citus.task_executor_typetask-tracker;set citus.task_executor_typereal-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 | 28192.168.6.110 | 26192.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;##查看生成的数据
[fbasepostgres dbgen]$ ls *.tbl
customer.tbl nation.tbl partsupp.tbl region.tbl
lineitem.tbl orders.tbl part.tbl supplier.tbl
[fbasepostgres dbgen]$ ###导入数据脚本
$ cat import_tables.sh #!/bin/bash
# 确保脚本接收到一个参数
if [ $# -ne 1 ]; thenecho Usage: $0 filenameexit 1
fi
file$1
# 确保输入的文件存在
if [ ! -f $file ]; thenecho 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 | 28192.168.6.110 | 26192.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_NAMEpostgres
RESULT_DIR./result# 确保结果目录存在
mkdir -p $RESULT_DIR# 执行 SQL 文件
for i in {1..21}
doif [[ $i -ne 17 $i -ne 20 ]]thenSQL_FILEd${i}.sqlRESULT_FILE${RESULT_DIR}/result${i}.txtecho Running $SQL_FILE...psql -d $DB_NAME -f $SQL_FILE $RESULT_FILEelseecho Skipping d${i}.sqlfi
done# 提取出SQL语句的响应时间 for i in {1..21}
doRESULT_FILE${RESULT_DIR}/result${i}.txtif [[ $i -eq 15 ]]; thenecho -n Q$i response tail -n 3 $RESULT_FILE | head -n 1elif [[ $i -ne 17 $i -ne 20 ]]; thenecho -n Q$i response tail -n 1 $RESULT_FILEfi
done开启nmon的时候测试tpch
##开启检测进程
[rootpostgres ~]# nmon -f -s 2 -c 100 -m /home/pg/nmonlog##查看状态
[rootpostgres ~]# ps -ef|grep nmon
3.2.4、输出测试结果
输出信息
[fbasepostgres dbgen]$ sh import_tables.sh part.tbl
Processing part.tbl
COPY 200000
[fbasepostgres dbgen]$ sh import_tables.sh supplier.tbl
Processing supplier.tbl
COPY 10000
[fbasepostgres dbgen]$ sh import_tables.sh customer.tbl
Processing customer.tbl
COPY 150000
[fbasepostgres dbgen]$ sh import_tables.sh partsupp.tbl
Processing partsupp.tbl
COPY 800000
[fbasepostgres dbgen]$ sh import_tables.sh orders.tbl
Processing orders.tbl
COPY 1500000
[fbasepostgres dbgen]$ sh import_tables.sh lineitem.tbl
Processing lineitem.tbl
COPY 6001215
[fbasepostgres 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 msnmon系统使用收集指标
可以使用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 | 22192.168.6.107 | 22192.168.6.110 | 22192.168.6.109 | 22
(4 rows)3.4、测试用例3扩容后
执行命令,获取查询语句响应时间
##执行测试脚本获取查询语句响应时间
sh execute.sh 输出的信息
##执行测试脚本获取查询语句响应时间
[fbasepostgres 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 ms3.5、测试结果汇总
以下是整理后的数据表格展示了扩容前后的关键指标对比
扩容前 vs 扩容后 数据对比表
阶段指标扩容前扩容后提升/下降百分比数据量数据量866124586万866124586万0%Q1查询响应时间1151.499 ms (00:01.151)1169.370 ms (00:01.169)-1.05%Q2查询响应时间225.830 ms224.707 ms0.5%Q3查询响应时间474.648 ms446.181 ms5.98%Q4查询响应时间342.354 ms318.128 ms7.07%Q5查询响应时间645.930 ms628.814 ms2.28%Q6查询响应时间234.146 ms258.494 ms-10.41%Q7查询响应时间378.565 ms363.685 ms3.92%Q8查询响应时间689.490 ms580.927 ms15.75%Q9查询响应时间865.872 ms835.688 ms3.48%Q10查询响应时间567.329 ms545.771 ms3.79%Q11查询响应时间134.626 ms137.307 ms-1.99%Q12查询响应时间332.472 ms311.096 ms6.39%Q13查询响应时间5030.917 ms (00:05.031)5671.297 ms (00:05.671)-12.85%Q14查询响应时间265.414 ms261.195 ms1.65%Q15查询响应时间1134.336 ms (00:01.134)964.076 ms15%Q16查询响应时间273.432 ms274.727 ms-0.47%Q18查询响应时间808.995 ms797.505 ms1.84%Q19查询响应时间412.101 ms375.844 ms8.76%Q21查询响应时间371.585 ms365.105 ms1.3%Citus集群节点数1✖Coordinator 2✖Worker1✖Coordinator 3✖Worker-个个节点分片数nodename | count 192.168.6.108 | 28 192.168.6.110 | 26 192.168.6.109 | 28nodename | 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度量指标
测试中测量的基础数据都与执行时间有关这些时间又可分为装载数据的每一步操作时间、每个查询执行时间和每个更新操作执行时间由这些时间可计算出数据装载时间、QphHSize, PowerSize, and ThroughputSize.
