# 说明

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