151 lines
6.0 KiB
Markdown
151 lines
6.0 KiB
Markdown
# 说明
|
|
|
|
为了方便测试 dbtest.tar.gz 包括了数据库安装包和测试需要的文件
|
|
包含 benchmarksql , sysbench 安装包，以及测试脚本 runTPCC\_loongdb.sh
|
|
|
|
|
|
# 安装数据库
|
|
|
|
|
|
# 配置参数
|
|
|
|
|
|
## 四路64核CPU配置参数 postgresql.conf
|
|
|
|
|
|
max_connections = '3000'
|
|
effective_cache_size = '128GB'
|
|
shared_buffers = '64GB'
|
|
maintenance_work_mem = '2GB'
|
|
temp_buffers = '32MB'
|
|
checkpoint_timeout = '2h'
|
|
checkpoint_completion_target = '0.9'
|
|
max_wal_size = '20GB'
|
|
min_wal_size = '1GB'
|
|
autovacuum_analyze_scale_factor = '0.01'
|
|
|
|
random_page_cost = 1.1
|
|
effective_io_concurrency = 1000
|
|
max_worker_processes = 64
|
|
|
|
|
|
## 修改配置
|
|
|
|
添加上面的配置到 postgresql.conf 文件的最后面
|
|
|
|
vim /var/lib/loongdb/1/data/postgresql.conf
|
|
|
|
重启数据库，使配置生效
|
|
|
|
systemctl restart loongdb-1.service
|
|
|
|
|
|
# benchmarksql
|
|
|
|
|
|
## 创建用户和数据库
|
|
|
|
su - loongdb
|
|
/usr/local/loongdb/loongdb-1/bin/psql -U loongdb -f ./benchmarksql_init.sql
|
|
|
|
benchmarksql\_init.sql 内容如下
|
|
|
|
create user benchmark with ENCRYPTED password '123456';
|
|
create database benchmark owner benchmark;
|
|
|
|
|
|
## 准备测试
|
|
|
|
tar xvzf dbtest.tar.gz
|
|
cd dbtest
|
|
tar xvzf benchmarksql-5.0.tar.gz
|
|
chmod +x runTPCC_loongdb.sh
|
|
|
|
sh ./runTPCC_loongdb.sh
|
|
|
|
|
|
## 查看结果
|
|
|
|
tail -n 10 ./benchmarksql-5.0/run/log/disk_log/*
|
|
|
|
记录下 tpmC (NewOrders) 和 tpmTOTAL 的值
|
|
|
|
14:51:39,913 [Thread-482] INFO jTPCC : Term-00, Measured tpmC (NewOrders) = 81180.6
|
|
14:51:39,913 [Thread-482] INFO jTPCC : Term-00, Measured tpmTOTAL = 180726.84
|
|
|
|
|
|
# Sysbench
|
|
|
|
|
|
## 创建用户和数据库
|
|
|
|
su - loongdb
|
|
/usr/local/loongdb/loongdb-1/bin/psql -U loongdb -f ./sysbench_init.sql
|
|
|
|
sysbench\_init.sql 内容如下
|
|
|
|
create user sysbench with ENCRYPTED password '123456';
|
|
create database sysbench owner sysbench;
|
|
|
|
|
|
## 软件仓库版本 sysbench, 测试脚本一般在 /usr/share/sysbench
|
|
|
|
--threads=CPU核数，根据实际情况调整，要测试不同并发，也可自行调整
|
|
|
|
sysbench /usr/share/sysbench/oltp_common.lua --db-driver=pgsql --pgsql-db=sysbench --pgsql-user=sysbench --pgsql-password=123456 --pgsql-port=5432 --pgsql-host=127.0.0.1 --tables=16 --table-size=1000000 --time=180 --max-requests=0 --threads=64 --report-interval=5 --forced-shutdown=1 prepare
|
|
|
|
sysbench /usr/share/sysbench/oltp_point_select.lua --db-driver=pgsql --pgsql-db=sysbench --pgsql-user=sysbench --pgsql-password=123456 --pgsql-port=5432 --pgsql-host=127.0.0.1 --tables=16 --table-size=1000000 --time=180 --max-requests=0 --threads=64 --report-interval=5 --forced-shutdown=1 run
|
|
|
|
sysbench /usr/share/sysbench/oltp_read_write.lua --db-driver=pgsql --pgsql-db=sysbench --pgsql-user=sysbench --pgsql-password=123456 --pgsql-port=5432 --pgsql-host=127.0.0.1 --tables=16 --table-size=1000000 --time=180 --max-requests=0 --threads=64 --report-interval=5 --forced-shutdown=1 run
|
|
|
|
sysbench /usr/share/sysbench/oltp_update_index.lua --db-driver=pgsql --pgsql-db=sysbench --pgsql-user=sysbench --pgsql-password=123456 --pgsql-port=5432 --pgsql-host=127.0.0.1 --tables=16 --table-size=1000000 --time=180 --max-requests=0 --threads=64 --report-interval=5 --forced-shutdown=1 run
|
|
|
|
sysbench /usr/share/sysbench/oltp_update_non_index.lua --db-driver=pgsql --pgsql-db=sysbench --pgsql-user=sysbench --pgsql-password=123456 --pgsql-port=5432 --pgsql-host=127.0.0.1 --tables=16 --table-size=1000000 --time=180 --max-requests=0 --threads=64 --report-interval=5 --forced-shutdown=1 run
|
|
|
|
|
|
## 本地编译 sysbench, 测试脚本一般在 /usr/local/share/sysbench
|
|
|
|
--threads=CPU核数，根据实际情况调整，要测试不同并发，也可自行调整
|
|
|
|
sysbench /usr/local/share/sysbench/oltp_common.lua --db-driver=pgsql --pgsql-db=sysbench --pgsql-user=sysbench --pgsql-password=123456 --pgsql-port=5432 --pgsql-host=127.0.0.1 --tables=16 --table-size=1000000 --time=180 --max-requests=0 --threads=64 --report-interval=5 --forced-shutdown=1 prepare
|
|
|
|
sysbench /usr/local/share/sysbench/oltp_point_select.lua --db-driver=pgsql --pgsql-db=sysbench --pgsql-user=sysbench --pgsql-password=123456 --pgsql-port=5432 --pgsql-host=127.0.0.1 --tables=16 --table-size=1000000 --time=180 --max-requests=0 --threads=64 --report-interval=5 --forced-shutdown=1 run
|
|
|
|
sysbench /usr/local/share/sysbench/oltp_read_write.lua --db-driver=pgsql --pgsql-db=sysbench --pgsql-user=sysbench --pgsql-password=123456 --pgsql-port=5432 --pgsql-host=127.0.0.1 --tables=16 --table-size=1000000 --time=180 --max-requests=0 --threads=64 --report-interval=5 --forced-shutdown=1 run
|
|
|
|
sysbench /usr/local/share/sysbench/oltp_update_index.lua --db-driver=pgsql --pgsql-db=sysbench --pgsql-user=sysbench --pgsql-password=123456 --pgsql-port=5432 --pgsql-host=127.0.0.1 --tables=16 --table-size=1000000 --time=180 --max-requests=0 --threads=64 --report-interval=5 --forced-shutdown=1 run
|
|
|
|
sysbench /usr/local/share/sysbench/oltp_update_non_index.lua --db-driver=pgsql --pgsql-db=sysbench --pgsql-user=sysbench --pgsql-password=123456 --pgsql-port=5432 --pgsql-host=127.0.0.1 --tables=16 --table-size=1000000 --time=180 --max-requests=0 --threads=64 --report-interval=5 --forced-shutdown=1 run
|
|
|
|
|
|
# pgbench
|
|
|
|
su - lonogdb
|
|
psql
|
|
|
|
create database pgbench;
|
|
\q
|
|
|
|
# 准备数据
|
|
pgbench -is 1000 -h127.0.0.1 pgbench
|
|
|
|
# 只读
|
|
pgbench -S -h127.0.0.1 -T180 -Mprepared -c 64 -j 64 -P 5 pgbench
|
|
|
|
# 读写(TPC-B)
|
|
pgbench -h127.0.0.1 -T180 -Mprepared -c 64 -j 64 -P 5 pgbench
|
|
|
|
|
|
CLIENTS="1 2 4 8 16 32 64 128 256 512 1024 2048"
|
|
for c in $CLIENTS; do
|
|
pgbench -n -S -h127.0.0.1 -T180 -Mprepared -c $c -j $c pgbench | grep -E "tps|latency average"
|
|
sleep 10
|
|
done
|
|
|
|
CLIENTS="1 2 4 8 16 32 64 128 256 512 1024 2048"
|
|
for c in $CLIENTS; do
|
|
pgbench -h 127.0.0.1 -T180 -Mprepared -c $c -j $c pgbench | grep -E "tps|latency average"
|
|
sleep 60
|
|
done
|
|
|