首页 > 基础资料 博客日记
Postgresql TPC-H OLAP测试全流程
2026-06-07 23:09:04基础资料围观8次
本篇文章分享Postgresql TPC-H OLAP测试全流程，对你有帮助的话记得收藏一下，看极客资料网收获更多编程知识
编译
1. 测试能否进入 PostgreSQL
先执行：
sudo -u postgres psql -c "SELECT version();"
如果能输出 PostgreSQL 版本，说明数据库服务正常。
2. 创建 TPC-H 测试数据库
你当前 Linux 用户是 username，先检查 PostgreSQL 里有没有同名用户：
sudo -u postgres psql -c "\du"
如果里面没有 username，执行：
sudo -u postgres createuser -s username
然后创建数据库：
createdb tpch
测试连接：
psql -d tpch -c "SELECT current_database(), current_user;"
正常的话应该看到类似：
current_database | current_user
------------------+--------------
tpch | username
如果 createdb tpch 提示数据库已存在，可以忽略，继续下一步。
3. 进入 dbgen 目录
你现在在：
~/TPCH/TPC-H
进入官方数据生成工具目录：
cd ~/TPCH/TPC-H/dbgen
ls
你应该能看到类似这些文件：
makefile.suite
dss.ddl
dss.ri
queries
4. 编译 TPC-H 工具
复制 Makefile：
cp makefile.suite Makefile
编辑：
vim Makefile
找到这几行，改成：
CC = gcc
DATABASE= POSTGRESQL
MACHINE = LINUX
WORKLOAD = TPCH
保存退出后执行：
make
编译成功后检查：
ls -l dbgen qgen
如果能看到 dbgen 和 qgen，说明编译成功。
修复 qgen 编译失败
这个问题通常是因为你在 Makefile 里设置了：
DATABASE= POSTGRESQL
但你这份官方工具源码里没有完整的 PostgreSQL 宏定义，所以 qgen.c 编译时找不到这些宏。
进入目录：
cd ~/TPCH/TPC-H/dbgen
先查这些宏定义应该在哪个文件里：
grep -R "SET_ROWCOUNT\|START_TRAN\|GEN_QUERY_PLAN" -n .
大概率会看到它们在 tpcd.h 或类似头文件里，对 DB2、ORACLE、INFORMIX 有定义，但没有 POSTGRESQL。
然后执行下面这个补丁命令：
cp tpcd.h tpcd.h.bak
cat >> tpcd.h <<'EOF'
/* PostgreSQL definitions added for qgen compile */
#ifdef POSTGRESQL
#define SET_ROWCOUNT "-- SET_ROWCOUNT %d"
#define START_TRAN "BEGIN;"
#define END_TRAN "COMMIT;"
#define SET_DBASE "-- SET_DBASE %s"
#define SET_OUTPUT "\\o"
#define GEN_QUERY_PLAN "EXPLAIN"
#endif
EOF
然后重新编译：
make clean
make
5. 生成 SF=1 测试数据
先跑小规模 1GB：
./dbgen -s 1
检查生成的数据文件：
ls *.tbl
然后去掉每行末尾多余的 |：
for f in *.tbl; do
sed -i 's/|$//' "$f"
done
6. 建表并导入数据
建表：
psql -d tpch -f dss.ddl
创建导入脚本：
cat > load.sql <<'SQL'
\set ON_ERROR_STOP on
\copy region from 'region.tbl' with (format csv, delimiter '|');
\copy nation from 'nation.tbl' with (format csv, delimiter '|');
\copy supplier from 'supplier.tbl' with (format csv, delimiter '|');
\copy customer from 'customer.tbl' with (format csv, delimiter '|');
\copy part from 'part.tbl' with (format csv, delimiter '|');
\copy partsupp from 'partsupp.tbl' with (format csv, delimiter '|');
\copy orders from 'orders.tbl' with (format csv, delimiter '|');
\copy lineitem from 'lineitem.tbl' with (format csv, delimiter '|');
SQL
导入：
psql -d tpch -f load.sql
导入完成后建约束：
psql -d tpch -f dss.ri
收集统计信息：
psql -d tpch -c "VACUUM ANALYZE;"
你现在建议直接从这里开始执行：
sudo -u postgres psql -c "SELECT version();"
sudo -u postgres psql -c "\du"
sudo -u postgres createuser -s username
createdb tpch
psql -d tpch -c "SELECT current_database(), current_user;"
如果 createuser 或 createdb 报“already exists”，不是问题，继续往下走。
7. 生成数据 建表 导入数据
编译成功后：
./dbgen -s 1
去掉 .tbl 每行最后的多余 |：
for f in *.tbl; do
sed -i 's/|$//' "$f"
done
建表：
psql -d tpch -f dss.ddl
然后导入数据。
数据导入
1. 先确认 .tbl 文件都在
在当前目录执行：
ls -lh *.tbl
应该能看到：
customer.tbl
lineitem.tbl
nation.tbl
orders.tbl
part.tbl
partsupp.tbl
region.tbl
supplier.tbl
2. 创建导入脚本
在 ~/TPCH/TPC-H/dbgen 目录下执行：
cat > load.sql <<'SQL'
\set ON_ERROR_STOP on
\copy region from 'region.tbl' with (format csv, delimiter '|');
\copy nation from 'nation.tbl' with (format csv, delimiter '|');
\copy supplier from 'supplier.tbl' with (format csv, delimiter '|');
\copy customer from 'customer.tbl' with (format csv, delimiter '|');
\copy part from 'part.tbl' with (format csv, delimiter '|');
\copy partsupp from 'partsupp.tbl' with (format csv, delimiter '|');
\copy orders from 'orders.tbl' with (format csv, delimiter '|');
\copy lineitem from 'lineitem.tbl' with (format csv, delimiter '|');
SQL
3. 执行导入
psql -d tpch -f load.sql
这一步会花一点时间，尤其是 lineitem.tbl 有 600 万行左右。
成功时会看到类似：
COPY 5
COPY 25
COPY 10000
COPY 150000
COPY 200000
COPY 800000
COPY 1500000
COPY 6001215
4. 检查导入行数
导入完成后执行：
psql -d tpch -c "
SELECT 'region' AS table_name, count(*) FROM region
UNION ALL SELECT 'nation', count(*) FROM nation
UNION ALL SELECT 'supplier', count(*) FROM supplier
UNION ALL SELECT 'customer', count(*) FROM customer
UNION ALL SELECT 'part', count(*) FROM part
UNION ALL SELECT 'partsupp', count(*) FROM partsupp
UNION ALL SELECT 'orders', count(*) FROM orders
UNION ALL SELECT 'lineitem', count(*) FROM lineitem;
"
SF=1 正常结果大概是：
region 5
nation 25
supplier 10000
customer 150000
part 200000
partsupp 800000
orders 1500000
lineitem 6001215
PostgreSQL 版主键外键添加
1. 新建 PostgreSQL 版约束脚本
因为是先删除，再创建，所以会有这样的信息：
psql:pg_ri.sql:9: NOTICE: constraint "partsupp_fk2" of relation "partsupp" does not exist, skipping
ALTER TABLE
psql:pg_ri.sql:10: NOTICE: constraint "customer_fk1" of relation "customer" does not exist, skipping
ALTER TABLE
psql:pg_ri.sql:11: NOTICE: constraint "supplier_fk1" of relation "supplier" does not exist, skipping
cat > pg_ri.sql <<'SQL'
\set ON_ERROR_STOP on
BEGIN;
ALTER TABLE lineitem DROP CONSTRAINT IF EXISTS lineitem_fk1;
ALTER TABLE lineitem DROP CONSTRAINT IF EXISTS lineitem_fk2;
ALTER TABLE orders DROP CONSTRAINT IF EXISTS orders_fk1;
ALTER TABLE partsupp DROP CONSTRAINT IF EXISTS partsupp_fk1;
ALTER TABLE partsupp DROP CONSTRAINT IF EXISTS partsupp_fk2;
ALTER TABLE customer DROP CONSTRAINT IF EXISTS customer_fk1;
ALTER TABLE supplier DROP CONSTRAINT IF EXISTS supplier_fk1;
ALTER TABLE nation DROP CONSTRAINT IF EXISTS nation_fk1;
ALTER TABLE lineitem DROP CONSTRAINT IF EXISTS lineitem_pkey;
ALTER TABLE orders DROP CONSTRAINT IF EXISTS orders_pkey;
ALTER TABLE partsupp DROP CONSTRAINT IF EXISTS partsupp_pkey;
ALTER TABLE part DROP CONSTRAINT IF EXISTS part_pkey;
ALTER TABLE customer DROP CONSTRAINT IF EXISTS customer_pkey;
ALTER TABLE supplier DROP CONSTRAINT IF EXISTS supplier_pkey;
ALTER TABLE nation DROP CONSTRAINT IF EXISTS nation_pkey;
ALTER TABLE region DROP CONSTRAINT IF EXISTS region_pkey;
ALTER TABLE region
ADD CONSTRAINT region_pkey PRIMARY KEY (r_regionkey);
ALTER TABLE nation
ADD CONSTRAINT nation_pkey PRIMARY KEY (n_nationkey);
ALTER TABLE supplier
ADD CONSTRAINT supplier_pkey PRIMARY KEY (s_suppkey);
ALTER TABLE customer
ADD CONSTRAINT customer_pkey PRIMARY KEY (c_custkey);
ALTER TABLE part
ADD CONSTRAINT part_pkey PRIMARY KEY (p_partkey);
ALTER TABLE partsupp
ADD CONSTRAINT partsupp_pkey PRIMARY KEY (ps_partkey, ps_suppkey);
ALTER TABLE orders
ADD CONSTRAINT orders_pkey PRIMARY KEY (o_orderkey);
ALTER TABLE lineitem
ADD CONSTRAINT lineitem_pkey PRIMARY KEY (l_orderkey, l_linenumber);
ALTER TABLE nation
ADD CONSTRAINT nation_fk1
FOREIGN KEY (n_regionkey) REFERENCES region (r_regionkey);
ALTER TABLE supplier
ADD CONSTRAINT supplier_fk1
FOREIGN KEY (s_nationkey) REFERENCES nation (n_nationkey);
ALTER TABLE customer
ADD CONSTRAINT customer_fk1
FOREIGN KEY (c_nationkey) REFERENCES nation (n_nationkey);
ALTER TABLE partsupp
ADD CONSTRAINT partsupp_fk1
FOREIGN KEY (ps_suppkey) REFERENCES supplier (s_suppkey);
ALTER TABLE partsupp
ADD CONSTRAINT partsupp_fk2
FOREIGN KEY (ps_partkey) REFERENCES part (p_partkey);
ALTER TABLE orders
ADD CONSTRAINT orders_fk1
FOREIGN KEY (o_custkey) REFERENCES customer (c_custkey);
ALTER TABLE lineitem
ADD CONSTRAINT lineitem_fk1
FOREIGN KEY (l_orderkey) REFERENCES orders (o_orderkey);
ALTER TABLE lineitem
ADD CONSTRAINT lineitem_fk2
FOREIGN KEY (l_partkey, l_suppkey) REFERENCES partsupp (ps_partkey, ps_suppkey);
COMMIT;
SQL
2. 执行这个新脚本
psql -d tpch -f pg_ri.sql
这一步可能会花一些时间，尤其是 lineitem 表比较大。
成功的话会看到很多：
ALTER TABLE
最后看到：
COMMIT
3. 收集统计信息
约束加完后执行：
psql -d tpch -c "VACUUM ANALYZE;"
4. 检查表和约束
psql -d tpch -c "\dt"
psql -d tpch -c "\d lineitem"
如果 \d lineitem 里能看到：
Primary key
Foreign-key constraints
说明约束已经加成功。
生成查询并修复查询语句
先执行下面这一整段，生成查询。
cd ~/TPCH/TPC-H/dbgen
mkdir -p queries_pg results_baseline
for i in $(seq 1 22); do
echo "Generating Q$i"
DSS_QUERY=./queries ./qgen -s 1 $i > queries_pg/q$i.sql
done
1. 先修复已经生成的查询文件
在当前目录执行：
cd ~/TPCH/TPC-H/dbgen
perl -pi -e "s/interval '([0-9]+)' day \(3\)/interval '\1 day'/g" queries_pg/*.sql
然后确认 Q1 已经改掉：
grep -n "interval" queries_pg/q1.sql
你应该看到类似：
l_shipdate <= date '1998-12-01' - interval '77 day'
2. 再跑 Q1
psql -d tpch -f queries_pg/q1.sql
如果 Q1 正常输出结果，就继续批量跑 22 条。
3. 批量跑 baseline
mkdir -p results_baseline
for i in $(seq 1 22); do
echo "Running Q$i baseline"
/usr/bin/time -f "Q$i elapsed: %e sec" \
psql -d tpch -f queries_pg/q$i.sql \
> results_baseline/q$i.out 2> results_baseline/q$i.time
done
查看耗时：
for i in $(seq 1 22); do
echo -n "Q$i: "
cat results_baseline/q$i.time
done
Q17和Q20卡住的处理情况
先加一组针对 TPC-H 常见连接列 + Q18/Q21 的索引，然后重新 ANALYZE，再单独重跑 Q18、Q21。
1. 创建索引脚本
进入目录：
cd ~/TPCH/TPC-H/dbgen
创建索引脚本：
cd ~/TPCH/TPC-H/dbgen
cat > pg_indexes_q17_q20.sql <<'SQL'
\set ON_ERROR_STOP on
-- Q17: part 过滤 + lineitem 按 partkey/quantity 聚合
CREATE INDEX IF NOT EXISTS idx_part_brand_container_partkey
ON part (p_brand, p_container, p_partkey);
CREATE INDEX IF NOT EXISTS idx_lineitem_partkey_quantity_price
ON lineitem (l_partkey, l_quantity, l_extendedprice);
-- Q20: part name 前缀匹配 + partsupp + lineitem 日期数量聚合
CREATE INDEX IF NOT EXISTS idx_part_name_pattern_partkey
ON part (p_name text_pattern_ops, p_partkey);
CREATE INDEX IF NOT EXISTS idx_partsupp_partkey_suppkey_availqty
ON partsupp (ps_partkey, ps_suppkey, ps_availqty);
CREATE INDEX IF NOT EXISTS idx_lineitem_part_supp_shipdate_quantity
ON lineitem (l_partkey, l_suppkey, l_shipdate, l_quantity);
CREATE INDEX IF NOT EXISTS idx_supplier_nation_supp_name
ON supplier (s_nationkey, s_suppkey, s_name);
CREATE INDEX IF NOT EXISTS idx_nation_name_nationkey
ON nation (n_name, n_nationkey);
VACUUM ANALYZE;
SQL
psql -d tpch -f pg_indexes_q17_q20.sql
建完索引后最好再执行一次
psql -d tpch -c "VACUUM ANALYZE;"
3. 重新跑 Q17 和 Q20
mkdir -p results_q17_q20
/usr/bin/time -f "Q17 elapsed: %e sec" \
psql -d tpch -f queries_pg/q17.sql \
> results_q17_q20/q17.out \
2> results_q17_q20/q17.time
/usr/bin/time -f "Q20 elapsed: %e sec" \
psql -d tpch -f queries_pg/q20.sql \
> results_q17_q20/q20.out \
2> results_q17_q20/q20.time
cat results_q17_q20/q17.time
cat results_q17_q20/q20.time
4. 再跑一轮完整 indexed 结果
mkdir -p results_indexed
for i in $(seq 1 22); do
echo "Running Q$i indexed"
/usr/bin/time -f "Q$i elapsed: %e sec" \
env PGOPTIONS='-c statement_timeout=30min' \
psql -d tpch -f queries_pg/q$i.sql \
> results_indexed/q$i.out \
2> results_indexed/q$i.time
if [ $? -ne 0 ]; then
echo "Q$i TIMEOUT or ERROR" >> results_indexed/q$i.time
fi
done
查看汇总：
for i in $(seq 1 22); do
echo "===== Q$i ====="
cat results_indexed/q$i.time
done
切换负载因子
强烈建议这样做：保留两个数据库，分别放 SF=1 和 SF=10。
比如：
tpch -> SF=1
tpch10 -> SF=10
这样以后不用来回清空、导入，只需要连接不同数据库。
你现在已有 SF=1 数据库
你现在的 tpch 就保留为 SF=1：
psql -d tpch
SF=10 新建一个数据库：
createdb tpch10
如果已经存在并且你想重建：
dropdb tpch10
createdb tpch10
1. 生成 SF=10 数据到单独目录
cd ~/TPCH/TPC-H/dbgen
mkdir -p data_sf10
rm -f data_sf10/*.tbl
./dbgen -s 10
mv *.tbl data_sf10/
cd data_sf10
for f in *.tbl; do
sed -i 's/|$//' "$f"
done
注意：这里会把当前目录下新生成的 .tbl 移动到 data_sf10/。你原来 SF=1 数据已经在 PostgreSQL 的 tpch 数据库里了，所以不受影响。
2. 生成查询
直接生成 SF=10 查询即可：
cd ~/TPCH/TPC-H/dbgen
mkdir -p queries_pg_sf10
for i in $(seq 1 22); do
DSS_QUERY=./queries ./qgen -s 10 $i > queries_pg_sf10/q$i.sql
done
perl -pi -e "s/interval '([0-9]+)' day \(3\)/interval '\1 day'/g" queries_pg_sf10/*.sql
生成后检查：
ls -lh queries_pg_sf10/q1.sql
ls queries_pg_sf10
3. 在 tpch10 里建表
cd ~/TPCH/TPC-H/dbgen
psql -d tpch10 -f dss.ddl
如果提示表已存在，说明 tpch10 不是空的。可以重建：
dropdb tpch10
createdb tpch10
psql -d tpch10 -f dss.ddl
4. 导入 SF=10 数据
创建导入脚本：
cat > load_sf10.sql <<'SQL'
\set ON_ERROR_STOP on
\copy region from 'data_sf10/region.tbl' with (format csv, delimiter '|');
\copy nation from 'data_sf10/nation.tbl' with (format csv, delimiter '|');
\copy supplier from 'data_sf10/supplier.tbl' with (format csv, delimiter '|');
\copy customer from 'data_sf10/customer.tbl' with (format csv, delimiter '|');
\copy part from 'data_sf10/part.tbl' with (format csv, delimiter '|');
\copy partsupp from 'data_sf10/partsupp.tbl' with (format csv, delimiter '|');
\copy orders from 'data_sf10/orders.tbl' with (format csv, delimiter '|');
\copy lineitem from 'data_sf10/lineitem.tbl' with (format csv, delimiter '|');
SQL
执行：
psql -d tpch10 -f load_sf10.sql
结果：
region 5
nation 25
supplier 100000
customer 1500000
part 2000000
partsupp 8000000
orders 15000000
lineitem 59986052
5. 给 tpch10 加主键外键
你之前的 PostgreSQL 版约束脚本 pg_ri.sql 可以复用：
psql -d tpch10 -f pg_ri.sql
加索引：
psql -d tpch10 -f pg_indexes_q17_q20.sql
然后收集统计信息：
psql -d tpch10 -c "VACUUM ANALYZE;"
运行：
mkdir -p results_indexed_sf10
for i in $(seq 1 22); do
echo "Running Q$i indexed"
/usr/bin/time -f "Q$i elapsed: %e sec" \
env PGOPTIONS='-c statement_timeout=30min' \
psql -d tpch10 -f queries_pg/q$i.sql \
> results_indexed/q$i.out \
2> results_indexed/q$i.time
if [ $? -ne 0 ]; then
echo "Q$i TIMEOUT or ERROR" >> results_indexed/q$i.time
fi
done
6. 查询时怎么区分 SF=1 和 SF=10
连接 SF=1：
psql -d tpch
连接 SF=10：
psql -d tpch10
跑 SF=1 查询：
psql -d tpch -f queries_pg/q1.sql
跑 SF=10 查询：
psql -d tpch10 -f queries_pg_sf10/q1.sql
6. 以后目录建议这样放
~/TPCH/TPC-H/dbgen/
├── data_sf10/ # SF=10 的 .tbl 数据文件
├── queries_pg/ # SF=1 查询
├── queries_pg_sf10/ # SF=10 查询
├── results_baseline/ # SF=1 结果
├── results_sf10_baseline/ # SF=10 结果
├── tpch 数据库 # SF=1
└── tpch10 数据库 # SF=10
文章来源:https://www.cnblogs.com/aslanvon/p/20362058
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若内容造成侵权/违法违规/事实不符，请联系邮箱：jacktools123@163.com进行投诉反馈，一经查实，立即删除！
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若内容造成侵权/违法违规/事实不符，请联系邮箱：jacktools123@163.com进行投诉反馈，一经查实，立即删除！
标签：
相关文章
最新发布
点击排行
本站推荐
