使用sysbench测试PostgreSQL性能

尽管PostgreSQL提供了pgbench，但有时候为了吊打一下MySQL，还是需要用到sysbench的。
著： 冯若航（@Vonng） |

sysbench首页：https://github.com/akopytov/sysbench

安装

二进制安装，在Mac上，使用brew安装sysbench。

brew install sysbench --with-postgresql

源代码编译（CentOS）：

yum -y install make automake libtool pkgconfig libaio-devel
# For MySQL support, replace with mysql-devel on RHEL/CentOS 5
yum -y install mariadb-devel openssl-devel
# For PostgreSQL support
yum -y install postgresql-devel

源代码编译

brew install automake libtool openssl pkg-config
# For MySQL support
brew install mysql
# For PostgreSQL support
brew install postgresql
# openssl is not linked by Homebrew, this is to avoid "ld: library not found for -lssl"
export LDFLAGS=-L/usr/local/opt/openssl/lib

编译：

./autogen.sh

# --with-pgsql --with-pgsql-libs --with-pgsql-includes
# -- without-mysql
./configure 

make -j
make install

准备

创建一个压测用PostgreSQL数据库：bench

初始化测试用数据库：

sysbench /usr/local/share/sysbench/oltp_read_write.lua \
	--db-driver=pgsql \
	--pgsql-host=127.0.0.1 \
	--pgsql-port=5432 \
	--pgsql-user=vonng \
	--pgsql-db=bench \
	--table_size=100000 \
	--tables=3 \
	prepare

输出：

Creating table 'sbtest1'...
Inserting 100000 records into 'sbtest1'
Creating a secondary index on 'sbtest1'...
Creating table 'sbtest2'...
Inserting 100000 records into 'sbtest2'
Creating a secondary index on 'sbtest2'...
Creating table 'sbtest3'...
Inserting 100000 records into 'sbtest3'
Creating a secondary index on 'sbtest3'...

压测

sysbench /usr/local/share/sysbench/oltp_read_write.lua \
	--db-driver=pgsql \
	--pgsql-host=127.0.0.1 \
	--pgsql-port=5432 \
	--pgsql-user=vonng \
	--pgsql-db=bench \
	--table_size=100000 \
    --tables=3 \
    --threads=4 \
    --time=12 \
    run

输出

sysbench 1.1.0-e6e6a02 (using bundled LuaJIT 2.1.0-beta3)

Running the test with following options:
Number of threads: 4
Initializing random number generator from current time


Initializing worker threads...

Threads started!

SQL statistics:
    queries performed:
        read:                            127862
        write:                           36526
        other:                           18268
        total:                           182656
    transactions:                        9131   (760.56 per sec.)
    queries:                             182656 (15214.20 per sec.)
    ignored errors:                      2      (0.17 per sec.)
    reconnects:                          0      (0.00 per sec.)

Throughput:
    events/s (eps):                      760.5600
    time elapsed:                        12.0056s
    total number of events:              9131

Latency (ms):
         min:                                    4.30
         avg:                                    5.26
         max:                                   15.20
         95th percentile:                        5.99
         sum:                                47995.39

Threads fairness:
    events (avg/stddev):           2282.7500/4.02
    execution time (avg/stddev):   11.9988/0.00