dbtest/README.md

说明

为了方便测试 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