Digoal.zhou

Digoal.zhou

公益是一辈子的事, I'm digoal, Just do it.

PostgreSQL 推荐 TPC-C 测试工具 sqlbench

4 minute read

背景

TPC-C测试工具比较多，比如使用JAVA写的BenchmarkSQL。

sqlbench是阿里云数据库内核团队的mark wong大大开源的一款TPC-C测试软件，完全遵循TPC-C标准编写，关键它是纯C的，效率非常高，并且在遵循TPC-C的标准同时对请求模型进行优化。

https://github.com/swida/sqlbench

使用者如果遇到什么问题，或者有什么建议可以发ISSUE给作者。

如何使用sqlbench

一、安装PostgreSQL

wget https://ftp.postgresql.org/pub/source/v10.4/postgresql-10.4.tar.bz2    
tar -jxvf postgresql-10.4.tar.bz2     
export USE_NAMED_POSIX_SEMAPHORES=1    
LIBS=-lpthread CFLAGS="-O3" ./configure --prefix=/home/digoal/pgsql10.4    
LIBS=-lpthread CFLAGS="-O3" make world -j 16    
LIBS=-lpthread CFLAGS="-O3" make install-world

二、初始化数据库

[digoal@digoal-Haier5000A ~]$ vi ~/env.sh     
    
export PS1="$USER@`/bin/hostname -s`-> "    
export PGPORT=1921    
export PGDATA=/data01/pg/pg_root$PGPORT    
export LANG=en_US.utf8    
export PGHOME=/home/digoal/pgsql10.4    
export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH    
export PATH=$PGHOME/bin:$PATH:.    
export DATE=`date +"%Y%m%d%H%M"`    
export MANPATH=$PGHOME/share/man:$MANPATH    
export PGHOST=$PGDATA    
export PGUSER=postgres    
export PGDATABASE=postgres    
alias rm='rm -i'    
alias ll='ls -lh'    
unalias vi

三、配置、启动数据库

1、初始化数据库集群

initdb -D $PGDATA -U postgres -E SQL_ASCII --locale=C

2、配置数据库

vi $PGDATA/postgresq.auto.conf    
    
listen_addresses = '0.0.0.0'    
port = 1921    
max_connections = 2000    
unix_socket_directories = '/tmp,.'    
shared_buffers = 32GB    
work_mem = 32MB    
maintenance_work_mem = 2GB    
dynamic_shared_memory_type = posix    
vacuum_cost_delay = 0    
vacuum_cost_limit = 10000    
bgwriter_delay = 10ms    
bgwriter_lru_maxpages = 900    
bgwriter_lru_multiplier = 5.0    
effective_io_concurrency = 0    
max_worker_processes = 100    
max_parallel_workers_per_gather = 0    
max_parallel_workers = 8    
wal_level = minimal      
synchronous_commit = off    
full_page_writes = off    
wal_buffers = 64MB    
wal_writer_delay = 10ms    
checkpoint_timeout = 35min    
max_wal_size = 64GB    
min_wal_size = 16GB    
checkpoint_completion_target = 0.1    
max_wal_senders = 0    
random_page_cost = 1.1    
log_destination = 'csvlog'    
logging_collector = on    
log_truncate_on_rotation = on    
log_checkpoints = on    
log_connections = on    
log_disconnections = on    
log_error_verbosity = verbose      
log_timezone = 'PRC'    
log_autovacuum_min_duration = 0    
autovacuum_freeze_max_age = 1200000000    
autovacuum_multixact_freeze_max_age = 1400000000    
autovacuum_vacuum_cost_delay = 0ms    
vacuum_freeze_table_age = 1150000000    
vacuum_multixact_freeze_table_age = 1150000000   
datestyle = 'iso, mdy'    
timezone = 'PRC'    
lc_messages = 'C'    
lc_monetary = 'C'    
lc_numeric = 'C'    
lc_time = 'C'    
default_text_search_config = 'pg_catalog.english'

四、设置环境变量

. ~/env.sh

五、安装sqlbench

git clone https://github.com/swida/sqlbench  
  
  
  
cd sqlbench  
  
  
autoreconf -if  
./configure --with-postgresql=$PGHOME  
make

六、使用sqlbench

在设置好之前创建的env.sh环境变量后，进入sqlbench源码目录操作。

如果你需要测试的数据库在远端，则修改以下链接配置即可。

1 删除表

PGUSER=$PGUSER PGPASSWORD=$PGPASSWORD PGHOST=$PGHOST PGPORT=$PGPORT DBT2DBNAME=$PGDATABASE src/scripts/pgsql/drop-tables

2 创建表

PGUSER=$PGUSER PGPASSWORD=$PGPASSWORD PGHOST=$PGHOST PGPORT=$PGPORT DBT2DBNAME=$PGDATABASE src/scripts/pgsql/create-tables

3 初始化数据

usage: src/core/datagen [-t <dbms>] -w # [-c #] [-i #] [-o #] [-s #] [-n #] [-j #] [-d <str>]  
  
-t <dbms>  
        available: postgresql  
        for postgresql: --dbname=<dbname> --host=<host> --port=<port> --user=<user>  
  
-w #  
        warehouse cardinality  
-c #  
        customer cardinality, default 3000  
-i #  
        item cardinality, default 100000  
-o #  
        order cardinality, default 3000  
-n #  
        new-order cardinality, default 900  
-j #  
        Number of worker threads within datagen, default is 1  
-d <path>  
        output path of data files

导入数据，设置线程数，仓库个数

PGPASSWORD=$PGPASSWORD src/core/datagen -t postgresql --dbname=$PGDATABASE --host=$PGHOST --port=$PGPORT --user=$PGUSER -j10 -w100

4 创建索引

1、可以指定表空间

mkdir /data02/pg/tbs_tpcc  

DBT2TSDIR=/data02/pg/tbs_tpcc PGUSER=$PGUSER PGPASSWORD=$PGPASSWORD PGHOST=$PGHOST PGPORT=$PGPORT DBT2DBNAME=$PGDATABASE src/scripts/pgsql/create-indexes -t

2、也可以使用默认表空间

PGUSER=$PGUSER PGPASSWORD=$PGPASSWORD PGHOST=$PGHOST PGPORT=$PGPORT DBT2DBNAME=$PGDATABASE src/scripts/pgsql/create-indexes

5 分析

psql <<EOF
VACUUM analyze customer;  
VACUUM analyze district;  
vacuum analyze history;  
vacuum analyze item;  
vacuum analyze new_order;  
vacuum analyze order_line;  
vacuum analyze orders;  
vacuum analyze stock;  
vacuum analyze warehouse;  
checkpoint;  
EOF

6 执行计划输出

PGUSER=$PGUSER PGPASSWORD=$PGPASSWORD PGHOST=$PGHOST PGPORT=$PGPORT DBT2DBNAME=$PGDATABASE src/scripts/pgsql/plans -o plans.out

7 性能测试

usage: ./sqlbench -t <dbms> -c # -w # -l # [-r #] [-s #] [-e #] [-o p] [-z]  
-t <dbms>  
        available: postgresql  
        for postgresql: --dbname=<dbname> --host=<host> --port=<port> --user=<user>  
-c #  
        number of database connections     建议与数据库CPU核数相同，或者是倍数，建议3倍以内  
-w #  
        warehouse cardinality, default 1   与前面初始化数据时设置的-w一致  
-l #  
        the duration of the run in seconds  连接数达到后，持续运行时间  
  
-r #  
        the duration of ramp up in seconds  持续升温时间(连接加载到指定个数时间)  
-s #  
        lower warehouse id, default 1  
-e #  
        upper warehouse id, default <w>  
-o p  
        output directory of log files, default current directory  
-z  
        perform database integrity check  
  
--customer #  
        customer cardinality, default 3000  
--item #  
        item cardinality, default 100000  
--order #  
        order cardinality, default 3000  
--new-order #  
        new-order cardinality, default 900  
  
--mixp %  
        mix percentage of Payment transaction, default 0.43  
--mixo %  
        mix percentage of Order-Status transaction, default 0.04  
--mixd %  
        mix percentage of Delivery transaction, default 0.04  
--mixs %  
        mix percentage of Stock-Level transaction, default 0.04  
  
--ktd #  
        delivery keying time, default 2 s  
--ktn #  
        new-order keying time, default 18 s  
--kto #  
        order-status keying time, default 2 s  
--ktp #  
        payment keying time, default 3 s  
--kts #  
        stock-level keying time, default 2 s  
--ttd #  
        delivery thinking time, default 5000 ms  
--ttn #  
        new-order thinking time, default 12000 ms  
--tto #  
        order-status thinking time, default 10000 ms  
--ttp #  
        payment thinking time, default 12000 ms  
--tts #  
        stock-level thinking time, default 5000 ms  
--no-thinktime  
        no think time and keying time to every transaction    
  
--tpw #  
        terminals started per warehouse, default 10  
  
--seed #  
        random number seed  
--altered #  
        run with a thread per user, and will start # threads  
--sleep #  
        number of milliseconds to sleep between terminal creation, openning db connections  
--sqlapi   三种压测模式，建议使用extended， 性能最好的话是使用storeproc(减少了调用次数，逻辑全部封装到了数据库函数中)。    
        run test using sql interface, available:  
        simple      default, just send sql statement to database  
        extended    use extended (prepare/bind/execute) protocol, better than simple  
        storeproc   use store procedure

storeproce模式需要预先安装好对应的函数，源码在如下目录中

src/storeproc/pgsql

压测用例

src/core/sqlbench -t postgresql --dbname=$PGDATABASE --user=$PGUSER --host=$PGHOST --port=$PGPORT --no-thinktime -w100 --sleep 10 -c56 -l300 -r5 --sqlapi extended

8 生成报告

src/utils/post_process -l mix.log  

                         Response Time (s)  
 Transaction      %    Average :    90th %        Total        Rollbacks      %  
------------  -----  ---------------------  -----------  ---------------  -----  
    Delivery   4.00      0.124 :     0.158        43144                0   0.00  
   New Order  44.94      0.019 :     0.027       484714            16984   3.50  
Order Status   4.03      0.003 :     0.004        43428                0   0.00  
     Payment  43.04      0.004 :     0.006       464256                0   0.00  
 Stock Level   4.00      0.006 :     0.008        43125                0   0.00  
------------  -----  ---------------------  -----------  ---------------  -----  
  
96942.80 new-order transactions per minute (NOTPM)  
5.0 minute duration  
0 total unknown errors  
5 second(s) ramping up

小结

sqlbench需要调用psql，需要依赖postgresql软件。

结合perf, oprofile, dtrace, strace, ptrace, stap等工具可以定位压测过程中遇到的瓶颈。

《阿里云 PostgreSQL 产品生态；案例、开发实践、管理实践、学习资料、学习视频 - 珍藏级》

参考

https://github.com/swida/sqlbench

《数据库界的华山论剑 tpc.org》

《BenchmarkSQL 支持多个 schema》

《BenchmarkSQL 测试PostgreSQL 9.5.0 TPC-C 性能》

Flag Counter

digoal’s 大量PostgreSQL文章入口

You May Also Enjoy

PostgreSQL(PPAS 兼容Oracle) 从零开始入门手册 - 珍藏版

17 minute read

背景 云数据库PPAS版，是阿里云与EnterpriseDB公司(简称EDB)合作基于PostgreSQL高度兼容Oracle语法的数据库服务，为用户提供易于操作的迁移工具，兼容范围涵盖：PL/SQL、数据类型、高级函数、表分区等。 用户可以直接在阿里云购买PPAS进行使用。 如果在购买PPAS前，想试用一下...

PostgreSQL pipelinedb 流计算插件 - IoT应用 - 实时轨迹聚合

1 minute read

背景 IoT场景，车联网场景，共享单车场景，人的行为位点等，终端实时上报的是孤立的位点，我们需要将其补齐成轨迹。 例如共享单车，下单，开锁，生成订单，骑行，关闭订单，关锁。这个过程有一个唯一的订单号，每次上报的位点会包含时间，订单号，位置。 根据订单号，将点聚合为轨迹。 使用pipelinedb插件，可以实...

PostgreSQL datediff 日期间隔（单位转换）兼容SQL用法

2 minute read

背景 使用datediff，对时间或日期相减，得到的间隔，转换为目标单位(日、月、季度、年、小时、秒。。。等)的数值。 DATEDIFF ( datepart, {date|timestamp}, {date|timestamp} ) 周 select datediff(week,'2009-01-0...