压测必须在产品上线前进行，这里采用的是ECS 8C/32G IO优化型实例，生产环境场景,MySQL压测测试工具使用经典的sysbench。

大家有空的话可以对着文档练练手，压测的过程中注意观察服务器的IO消耗情况，以及其它资源的消耗。

一、依赖安装

[root@iZt6c63b3qfb3ckcelau81Z soft]#rpm -ivh MySQL-server-5.6.26-1.linux_glibc2.5.x86_64.rpm[root@iZt6c63b3qfb3ckcelau81Z soft]# rpm -ivh MySQL-devel-5.6.26-1.linux_glibc2.5.x86_64.rpmwarning: MySQL-devel-5.6.26-1.linux_glibc2.5.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEYPreparing... ########################################### [100%]1:MySQL-devel ########################################### [100%][root@iZt6c63b3qfb3ckcelau81Z soft]# rpm -ivh MySQL-client-5.6.26-1.linux_glibc2.5.x86_64.rpmwarning: MySQL-client-5.6.26-1.linux_glibc2.5.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEYPreparing... ########################################### [100%]1:MySQL-client ########################################### [100%][root@iZt6c63b3qfb3ckcelau81Z soft]# rpm -ivh MySQL-shared-5.6.26-1.linux_glibc2.5.x86_64.rpmwarning: MySQL-shared-5.6.26-1.linux_glibc2.5.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEYPreparing... ########################################### [100%]1:MySQL-shared ########################################### [100%]

二、sysbench安装

[root@iZt6c63b3qfb3ckcelau81Z soft]# cd ../application/sysbench-0.5[root@iZt6c63b3qfb3ckcelau81Z sysbench-0.5]# lsCOPYING Makefile.am README.md autogen.sh config.log configure.ac libtool mkinstalldirsChangeLog Makefile.in TODO autom4te.cache config.status doc m4 sysbenchMakefile README-WIN.txt aclocal.m4 config configure install-sh missing[root@iZt6c63b3qfb3ckcelau81Z sysbench-0.5]# ll /usr/lib64/mysqltotal 113680。。。[root@iZt6c63b3qfb3ckcelau81Z sysbench-0.5]# ./configure --build=x86_64 --prefix=/data/sysbench --with-mysql --with-mysql-includes=/usr/include/mysql --with-mysql-libs=/var/lib/mysql/checking build system type... x86_64-pc-nonechecking host system type... x86_64-pc-nonechecking target system type... x86_64-pc-nonechecking for a BSD-compatible install... /usr/bin/install -cchecking whether build environment is sane... yeschecking for a thread-safe mkdir -p... /bin/mkdir -pchecking for gawk... gawkchecking whether make sets $(MAKE)... yeschecking for style of include used by make... GNUchecking for gcc... gccchecking for C compiler default output file name... a.outchecking whether the C compiler works... yes。。。。。[root@iZt6c63b3qfb3ckcelau81Z sysbench-0.5]# make && make installMaking all in docmake[1]: Entering directory `/data/application/sysbench-0.5/doc'Making all in xslmake[2]: Entering directory `/data/application/sysbench-0.5/doc/xsl'make[2]: Nothing to be done for `all'.make[2]: Leaving directory `/data/application/sysbench-0.5/doc/xsl'make[2]: Entering directory `/data/application/sysbench-0.5/doc'make[2]: Nothing to be done for `all-am'.make[2]: Leaving directory `/data/application/sysbench-0.5/doc'make[1]: Leaving directory `/data/application/sysbench-0.5/doc'Making all in sysbenchmake[1]: Entering directory `/data/application/sysbench-0.5/sysbench'Making all in driversmake[2]: Entering directory `/data/application/sysbench-0.5/sysbench/drivers'Making all in mysqlmake[3]: Entering directory `/data/application/sysbench-0.5/sysbench/drivers/mysql'make[3]: Nothing to be done for `all'.[root@iZt6c63b3qfb3ckcelau81Z sysbench-0.5]# cd /data/[root@iZt6c63b3qfb3ckcelau81Z data]# lsapplication backup lost+found scripts server soft sysbench webapps.tar.gz[root@iZt6c63b3qfb3ckcelau81Z data]# cd sysbench/[root@iZt6c63b3qfb3ckcelau81Z sysbench]# lsbin share[root@iZt6c63b3qfb3ckcelau81Z sysbench]# cd bin/[root@iZt6c63b3qfb3ckcelau81Z bin]# lssysbench

三、小试牛刀

参数说明:

参数名称 功能意义

--mysql-host 主机IP

--mysql-port MySQL端口号

--mysql-user MySQL用户名

--mysql-password MySQL密码

--oltp-test-mode 执行模式simple、nontrx和complex

--oltp-tables-count 测试表的数量

--oltp-table-size 测试表的记录数

--threads 并发连接数

--time 测试执行时间，单位秒

--report-interval 生成报告单的时间间隔，秒

simple： 测试查询 不测试写入

nontrx: 测试无事务的增删改查

complex : 测试有事务的增删改查

prepare： 准备测试数据

run 执行测试

cleanup 清除测试数据

queries performed ： 执行测试的次数

read : 读操作执行了多少次

write ： 写操作执行了多少次

other：其他操作执行了多少次

total：总共执行了多少次

transcations（TPS）：执行的事务次数多少次，pxc集群每秒可以执行的事务操作多少次

queries(QPS)：处理的请求数是多少，pxc集群每秒钟可以执行的增删改查操作

ignored errors： 忽略的错误数量，每秒钟平均错误数量多少次，可能是节点之间冲突造成

reconnects：数据库重新连接的次数，0代表没有发生数据库连接断开的情况

[root@iZt6c63b3qfb3ckcelau81Z bin]# ./sysbench \--mysql-host=10.104.97.191 \--mysql-port=3302 \--mysql-user=xianjqx_data \--mysql-password=xxx你的密码xxx \--mysql-db=test \--oltp-tables-count=10 \--oltp-table-size=100000 \--num-threads=50 \--max-requests=1000000 \--report-interval=1 \--max-time=20 \--test=/data/application/sysbench-0.5/sysbench/tests/db/oltp.lua runsysbench 0.5: multi-threaded system evaluation benchmarkRunning the test with following options:Number of threads: 50Report intermediate results every 1 second(s)Random number generator seed is 0 and will be ignoredInitializing worker threads...Threads started![ 1s] threads: 50, tps: 804.93, reads: 11763.95, writes: 3252.71, response time: 94.84ms (95%), errors: 0.00, reconnects: 0.00[ 2s] threads: 50, tps: 894.00, reads: 12433.02, writes: 3567.01, response time: 73.91ms (95%), errors: 0.00, reconnects: 0.00[ 3s] threads: 50, tps: 868.00, reads: 12172.99, writes: 3475.00, response time: 103.54ms (95%), errors: 0.00, reconnects: 0.00[ 4s] threads: 50, tps: 899.00, reads: 12544.02, writes: 3632.00, response time: 89.23ms (95%), errors: 0.00, reconnects: 0.00[ 5s] threads: 50, tps: 912.00, reads: 12909.02, writes: 3630.01, response time: 82.45ms (95%), errors: 0.00, reconnects: 0.00[ 6s] threads: 50, tps: 927.00, reads: 12993.01, writes: 3680.00, response time: 80.35ms (95%), errors: 0.00, reconnects: 0.00[ 7s] threads: 50, tps: 915.00, reads: 12805.01, writes: 3662.00, response time: 80.86ms (95%), errors: 0.00, reconnects: 0.00[ 8s] threads: 50, tps: 907.00, reads: 12668.02, writes: 3621.00, response time: 85.10ms (95%), errors: 0.00, reconnects: 0.00[ 9s] threads: 50, tps: 902.99, reads: 12690.84, writes: 3622.95, response time: 82.67ms (95%), errors: 0.00, reconnects: 0.00[ 10s] threads: 50, tps: 896.01, reads: 12477.13, writes: 3582.04, response time: 80.16ms (95%), errors: 0.00, reconnects: 0.00[ 11s] threads: 50, tps: 906.89, reads: 12745.43, writes: 3637.55, response time: 78.85ms (95%), errors: 0.00, reconnects: 0.00[ 12s] threads: 50, tps: 928.11, reads: 12945.55, writes: 3704.44, response time: 79.42ms (95%), errors: 0.00, reconnects: 0.00[ 13s] threads: 50, tps: 912.00, reads: 12785.04, writes: 3644.01, response time: 84.09ms (95%), errors: 0.00, reconnects: 0.00[ 14s] threads: 50, tps: 909.00, reads: 12683.01, writes: 3645.00, response time: 81.76ms (95%), errors: 0.00, reconnects: 0.00[ 15s] threads: 50, tps: 912.00, reads: 12841.96, writes: 3647.99, response time: 80.78ms (95%), errors: 0.00, reconnects: 0.00[ 16s] threads: 50, tps: 909.00, reads: 12767.04, writes: 3635.01, response time: 76.18ms (95%), errors: 0.00, reconnects: 0.00[ 17s] threads: 50, tps: 906.96, reads: 12651.49, writes: 3637.85, response time: 85.90ms (95%), errors: 0.00, reconnects: 0.00[ 18s] threads: 50, tps: 920.05, reads: 12828.64, writes: 3680.18, response time: 78.66ms (95%), errors: 0.00, reconnects: 0.00[ 19s] threads: 50, tps: 915.99, reads: 12778.92, writes: 3662.98, response time: 80.52ms (95%), errors: 0.00, reconnects: 0.00[ 20s] threads: 50, tps: 906.00, reads: 12819.97, writes: 3611.99, response time: 80.21ms (95%), errors: 0.00, reconnects: 0.00OLTP test statistics:queries performed:read: 253428write: 72408other: 36204total: 362040transactions: 18102 (904.05 per sec.)read/write requests: 325836 (16272.92 per sec.)other operations: 36204 (1808.10 per sec.)ignored errors: 0 (0.00 per sec.)reconnects: 0 (0.00 per sec.)General statistics:total time: 20.0232stotal number of events: 18102total time taken by event execution: 1000.6618sresponse time:min: 11.47msavg: 55.28msmax: 271.40msapprox. 95 percentile: 82.27msThreads fairness:events (avg/stddev): 362.0400/9.28execution time (avg/stddev): 20.0132/0.01。。。。

四、写压测，10张表，每张1千万数据，客户端并发50，100万个请求。

[root@iZt6c63b3qfb3ckcelau81Z bin]# ./sysbench \--mysql-host=10.104.97.191 \--mysql-port=3302 \--mysql-user=xianjqx_data \--mysql-password=xxx你的密码xxx \--mysql-db=test \--oltp-tables-count=10 \--oltp-table-size=10000000 \--num-threads=50 \--max-requests=1000000 \--report-interval=1 \--max-time=20 \--test=/data/application/sysbench-0.5/sysbench/tests/db/insert.lua runsysbench 0.5: multi-threaded system evaluation benchmarkRunning the test with following options:Number of threads: 50Report intermediate results every 1 second(s)Random number generator seed is 0 and will be ignoredInitializing worker threads...Threads started![ 1s] threads: 50, tps: 0.00, reads: 0.00, writes: 37390.61, response time: 2.78ms (95%), errors: 0.00, reconnects: 0.00[ 2s] threads: 50, tps: 0.00, reads: 0.00, writes: 36695.47, response time: 2.72ms (95%), errors: 0.00, reconnects: 0.00[ 3s] threads: 50, tps: 0.00, reads: 0.00, writes: 37671.24, response time: 2.66ms (95%), errors: 0.00, reconnects: 0.00[ 4s] threads: 50, tps: 0.00, reads: 0.00, writes: 36645.89, response time: 2.77ms (95%), errors: 0.00, reconnects: 0.00[ 5s] threads: 50, tps: 0.00, reads: 0.00, writes: 35638.11, response time: 2.87ms (95%), errors: 0.00, reconnects: 0.00[ 6s] threads: 50, tps: 0.00, reads: 0.00, writes: 36675.98, response time: 2.70ms (95%), errors: 0.00, reconnects: 0.00[ 7s] threads: 50, tps: 0.00, reads: 0.00, writes: 36154.10, response time: 2.76ms (95%), errors: 0.00, reconnects: 0.00[ 8s] threads: 50, tps: 0.00, reads: 0.00, writes: 36354.07, response time: 2.75ms (95%), errors: 0.00, reconnects: 0.00[ 9s] threads: 50, tps: 0.00, reads: 0.00, writes: 37083.99, response time: 2.65ms (95%), errors: 0.00, reconnects: 0.00[ 10s] threads: 50, tps: 0.00, reads: 0.00, writes: 36220.95, response time: 2.78ms (95%), errors: 0.00, reconnects: 0.00[ 11s] threads: 50, tps: 0.00, reads: 0.00, writes: 36392.94, response time: 2.77ms (95%), errors: 0.00, reconnects: 0.00[ 12s] threads: 50, tps: 0.00, reads: 0.00, writes: 36704.74, response time: 2.68ms (95%), errors: 0.00, reconnects: 0.00[ 13s] threads: 50, tps: 0.00, reads: 0.00, writes: 36271.25, response time: 2.80ms (95%), errors: 0.00, reconnects: 0.00[ 14s] threads: 50, tps: 0.00, reads: 0.00, writes: 35718.00, response time: 2.79ms (95%), errors: 0.00, reconnects: 0.00[ 15s] threads: 50, tps: 0.00, reads: 0.00, writes: 35564.37, response time: 2.76ms (95%), errors: 0.00, reconnects: 0.00[ 16s] threads: 50, tps: 0.00, reads: 0.00, writes: 37285.62, response time: 2.73ms (95%), errors: 0.00, reconnects: 0.00[ 17s] threads: 50, tps: 0.00, reads: 0.00, writes: 37128.31, response time: 2.74ms (95%), errors: 0.00, reconnects: 0.00[ 18s] threads: 50, tps: 0.00, reads: 0.00, writes: 37356.98, response time: 2.64ms (95%), errors: 0.00, reconnects: 0.00[ 19s] threads: 50, tps: 0.00, reads: 0.00, writes: 37050.81, response time: 2.75ms (95%), errors: 0.00, reconnects: 0.00[ 20s] threads: 49, tps: 0.00, reads: 0.00, writes: 36750.90, response time: 2.75ms (95%), errors: 0.00, reconnects: 0.00OLTP test statistics:queries performed:read: 0write: 732793other: 0total: 732793transactions: 0 (0.00 per sec.)read/write requests: 732793 (36637.48 per sec.)other operations: 0 (0.00 per sec.)ignored errors: 0 (0.00 per sec.)reconnects: 0 (0.00 per sec.)General statistics:total time: 20.0012stotal number of events: 732793total time taken by event execution: 998.4445sresponse time:min: 0.27msavg: 1.36msmax: 31.07msapprox. 95 percentile: 2.74msThreads fairness:events (avg/stddev): 14655.8600/145.87execution time (avg/stddev): 19.9689/0.00。。。

4.1、20秒写1000W条数据压测

[root@iZt6c63b3qfb3ckcelau81Z bin]# ./sysbench \  --mysql-host=10.104.97.191 \  --mysql-port=3301 \  --mysql-user=xianjqx_data \  --mysql-password=xxx你的密码xxx\  --mysql-db=test \  --oltp-tables-count=10 \  --oltp-table-size=10000000 \  --num-threads=50 \  --max-requests=1000000 \  --report-interval=1 \  --max-time=20 \  --test=/data/application/sysbench-0.5/sysbench/tests/db/insert.lua runsysbench 0.5: multi-threaded system evaluation benchmarkRunning the test with following options:Number of threads: 50Report intermediate results every 1 second(s)Random number generator seed is 0 and will be ignoredInitializing worker threads...Threads started![ 1s] threads: 50, tps: 0.00, reads: 0.00, writes: 37803.81, response time: 2.68ms (95%), errors: 0.00, reconnects: 0.00[ 2s] threads: 50, tps: 0.00, reads: 0.00, writes: 36829.03, response time: 2.76ms (95%), errors: 0.00, reconnects: 0.00[ 3s] threads: 50, tps: 0.00, reads: 0.00, writes: 37959.07, response time: 2.59ms (95%), errors: 0.00, reconnects: 0.00[ 4s] threads: 50, tps: 0.00, reads: 0.00, writes: 37151.17, response time: 2.66ms (95%), errors: 0.00, reconnects: 0.00[ 5s] threads: 50, tps: 0.00, reads: 0.00, writes: 34607.31, response time: 3.02ms (95%), errors: 0.00, reconnects: 0.00[ 6s] threads: 50, tps: 0.00, reads: 0.00, writes: 36084.00, response time: 3.06ms (95%), errors: 0.00, reconnects: 0.00[ 7s] threads: 50, tps: 0.00, reads: 0.00, writes: 38405.85, response time: 2.57ms (95%), errors: 0.00, reconnects: 0.00[ 8s] threads: 50, tps: 0.00, reads: 0.00, writes: 37113.94, response time: 2.71ms (95%), errors: 0.00, reconnects: 0.00[ 9s] threads: 50, tps: 0.00, reads: 0.00, writes: 37506.26, response time: 2.64ms (95%), errors: 0.00, reconnects: 0.00[ 10s] threads: 50, tps: 0.00, reads: 0.00, writes: 36582.46, response time: 2.58ms (95%), errors: 0.00, reconnects: 0.00[ 11s] threads: 50, tps: 0.00, reads: 0.00, writes: 37132.73, response time: 2.76ms (95%), errors: 0.00, reconnects: 0.00[ 12s] threads: 50, tps: 0.00, reads: 0.00, writes: 36239.04, response time: 2.75ms (95%), errors: 0.00, reconnects: 0.00[ 13s] threads: 50, tps: 0.00, reads: 0.00, writes: 36847.62, response time: 2.71ms (95%), errors: 0.00, reconnects: 0.00[ 14s] threads: 50, tps: 0.00, reads: 0.00, writes: 37190.24, response time: 2.57ms (95%), errors: 0.00, reconnects: 0.00[ 15s] threads: 50, tps: 0.00, reads: 0.00, writes: 38992.76, response time: 2.47ms (95%), errors: 0.00, reconnects: 0.00[ 16s] threads: 50, tps: 0.00, reads: 0.00, writes: 37666.99, response time: 2.71ms (95%), errors: 0.00, reconnects: 0.00[ 17s] threads: 50, tps: 0.00, reads: 0.00, writes: 37164.44, response time: 2.57ms (95%), errors: 0.00, reconnects: 0.00[ 18s] threads: 50, tps: 0.00, reads: 0.00, writes: 37092.24, response time: 2.70ms (95%), errors: 0.00, reconnects: 0.00[ 19s] threads: 50, tps: 0.00, reads: 0.00, writes: 38164.87, response time: 2.57ms (95%), errors: 0.00, reconnects: 0.00OLTP test statistics:queries performed:read: 0write: 744347other: 0total: 744347transactions: 0 (0.00 per sec.)read/write requests: 744347 (37215.28 per sec.)other operations: 0 (0.00 per sec.)ignored errors: 0 (0.00 per sec.)reconnects: 0 (0.00 per sec.)General statistics:total time: 20.0011stotal number of events: 744347total time taken by event execution: 998.4342sresponse time:min: 0.26msavg: 1.34msmax: 33.37msapprox. 95 percentile: 2.68msThreads fairness:events (avg/stddev): 14886.9400/124.69execution time (avg/stddev): 19.9687/0.00

[root@iZt6c63b3qfb3ckcelau81Z bin]# ./sysbench --mysql-host=10.104.97.191 --mysql-port=3303 --mysql-user=xianjqx_data --mysql-password=xxx你的密码xxx --mysql-db=test --oltp-tables-count=10 --oltp-table-size=10000000 --num-threads=50 --max-requests=1000000 --report-interval=1 --max-time=20 --test=/data/application/sysbench-0.5/sysbench/tests/db/insert.lua runsysbench 0.5: multi-threaded system evaluation benchmarkRunning the test with following options:Number of threads: 50Report intermediate results every 1 second(s)Random number generator seed is 0 and will be ignoredInitializing worker threads...Threads started![ 1s] threads: 50, tps: 0.00, reads: 0.00, writes: 32152.54, response time: 3.23ms (95%), errors: 0.00, reconnects: 0.00[ 2s] threads: 50, tps: 0.00, reads: 0.00, writes: 34487.86, response time: 3.04ms (95%), errors: 0.00, reconnects: 0.00[ 3s] threads: 50, tps: 0.00, reads: 0.00, writes: 37649.75, response time: 2.61ms (95%), errors: 0.00, reconnects: 0.00[ 4s] threads: 50, tps: 0.00, reads: 0.00, writes: 38688.12, response time: 2.55ms (95%), errors: 0.00, reconnects: 0.00[ 5s] threads: 50, tps: 0.00, reads: 0.00, writes: 38551.01, response time: 2.53ms (95%), errors: 0.00, reconnects: 0.00[ 6s] threads: 50, tps: 0.00, reads: 0.00, writes: 37773.84, response time: 2.65ms (95%), errors: 0.00, reconnects: 0.00[ 7s] threads: 50, tps: 0.00, reads: 0.00, writes: 34898.25, response time: 2.74ms (95%), errors: 0.00, reconnects: 0.00[ 8s] threads: 50, tps: 0.00, reads: 0.00, writes: 38503.69, response time: 2.45ms (95%), errors: 0.00, reconnects: 0.00[ 9s] threads: 50, tps: 0.00, reads: 0.00, writes: 39192.94, response time: 2.48ms (95%), errors: 0.00, reconnects: 0.00[ 10s] threads: 50, tps: 0.00, reads: 0.00, writes: 37934.43, response time: 2.62ms (95%), errors: 0.00, reconnects: 0.00[ 11s] threads: 50, tps: 0.00, reads: 0.00, writes: 36729.85, response time: 2.74ms (95%), errors: 0.00, reconnects: 0.00[ 12s] threads: 50, tps: 0.00, reads: 0.00, writes: 36679.67, response time: 2.75ms (95%), errors: 0.00, reconnects: 0.00[ 13s] threads: 50, tps: 0.00, reads: 0.00, writes: 34892.65, response time: 2.75ms (95%), errors: 0.00, reconnects: 0.00[ 14s] threads: 50, tps: 0.00, reads: 0.00, writes: 37181.71, response time: 2.71ms (95%), errors: 0.00, reconnects: 0.00[ 15s] threads: 50, tps: 0.00, reads: 0.00, writes: 39184.04, response time: 2.41ms (95%), errors: 0.00, reconnects: 0.00[ 16s] threads: 50, tps: 0.00, reads: 0.00, writes: 37762.01, response time: 2.55ms (95%), errors: 0.00, reconnects: 0.00[ 17s] threads: 50, tps: 0.00, reads: 0.00, writes: 37140.55, response time: 2.78ms (95%), errors: 0.00, reconnects: 0.00[ 18s] threads: 50, tps: 0.00, reads: 0.00, writes: 38503.39, response time: 2.56ms (95%), errors: 0.00, reconnects: 0.00[ 19s] threads: 50, tps: 0.00, reads: 0.00, writes: 34271.07, response time: 2.76ms (95%), errors: 0.00, reconnects: 0.00[ 20s] threads: 50, tps: 0.00, reads: 0.00, writes: 37234.60, response time: 2.69ms (95%), errors: 0.00, reconnects: 0.00OLTP test statistics:queries performed:read: 0write: 739463other: 0total: 739463transactions: 0 (0.00 per sec.)read/write requests: 739463 (36971.10 per sec.)other operations: 0 (0.00 per sec.)ignored errors: 0 (0.00 per sec.)reconnects: 0 (0.00 per sec.)General statistics:total time: 20.0011stotal number of events: 739463total time taken by event execution: 998.5937sresponse time:min: 0.26msavg: 1.35msmax: 97.69msapprox. 95 percentile: 2.66msThreads fairness:events (avg/stddev): 14789.2600/113.70execution time (avg/stddev): 19.9719/0.00

[root@iZt6c63b3qfb3ckcelau81Z bin]# ./sysbench --mysql-host=10.104.97.191 --mysql-port=3301 --mysql-user=xianjqx_data --mysql-password=xxx你的密码xxx --mysql-db=test --oltp-tables-count=10 --oltp-table-size=20000000 --num-threads=50 --max-requests=1000000 --report-interval=1 --max-time=20 --test=/data/application/sysbench-0.5/sysbench/tests/db/insert.lua runsysbench 0.5: multi-threaded system evaluation benchmarkRunning the test with following options:Number of threads: 50Report intermediate results every 1 second(s)Random number generator seed is 0 and will be ignoredInitializing worker threads...Threads started![ 1s] threads: 50, tps: 0.00, reads: 0.00, writes: 37788.58, response time: 2.70ms (95%), errors: 0.00, reconnects: 0.00[ 2s] threads: 50, tps: 0.00, reads: 0.00, writes: 37165.22, response time: 2.73ms (95%), errors: 0.00, reconnects: 0.00[ 3s] threads: 50, tps: 0.00, reads: 0.00, writes: 37555.49, response time: 2.56ms (95%), errors: 0.00, reconnects: 0.00[ 4s] threads: 50, tps: 0.00, reads: 0.00, writes: 37269.00, response time: 2.66ms (95%), errors: 0.00, reconnects: 0.00[ 5s] threads: 50, tps: 0.00, reads: 0.00, writes: 37501.66, response time: 2.67ms (95%), errors: 0.00, reconnects: 0.00[ 6s] threads: 50, tps: 0.00, reads: 0.00, writes: 38457.77, response time: 2.55ms (95%), errors: 0.00, reconnects: 0.00[ 7s] threads: 50, tps: 0.00, reads: 0.00, writes: 38487.97, response time: 2.58ms (95%), errors: 0.00, reconnects: 0.00[ 8s] threads: 50, tps: 0.00, reads: 0.00, writes: 39031.99, response time: 2.48ms (95%), errors: 0.00, reconnects: 0.00[ 9s] threads: 50, tps: 0.00, reads: 0.00, writes: 36709.92, response time: 2.67ms (95%), errors: 0.00, reconnects: 0.00[ 10s] threads: 50, tps: 0.00, reads: 0.00, writes: 37699.63, response time: 2.57ms (95%), errors: 0.00, reconnects: 0.00[ 11s] threads: 50, tps: 0.00, reads: 0.00, writes: 37401.19, response time: 2.56ms (95%), errors: 0.00, reconnects: 0.00[ 12s] threads: 50, tps: 0.00, reads: 0.00, writes: 35666.11, response time: 2.82ms (95%), errors: 0.00, reconnects: 0.00[ 13s] threads: 50, tps: 0.00, reads: 0.00, writes: 36531.82, response time: 2.72ms (95%), errors: 0.00, reconnects: 0.00[ 14s] threads: 50, tps: 0.00, reads: 0.00, writes: 36987.90, response time: 2.70ms (95%), errors: 0.00, reconnects: 0.00[ 15s] threads: 50, tps: 0.00, reads: 0.00, writes: 37143.99, response time: 2.67ms (95%), errors: 0.00, reconnects: 0.00[ 16s] threads: 50, tps: 0.00, reads: 0.00, writes: 37120.37, response time: 2.69ms (95%), errors: 0.00, reconnects: 0.00[ 17s] threads: 50, tps: 0.00, reads: 0.00, writes: 34328.07, response time: 3.12ms (95%), errors: 0.00, reconnects: 0.00[ 18s] threads: 50, tps: 0.00, reads: 0.00, writes: 34132.23, response time: 3.07ms (95%), errors: 0.00, reconnects: 0.00[ 19s] threads: 50, tps: 0.00, reads: 0.00, writes: 36324.66, response time: 2.75ms (95%), errors: 0.00, reconnects: 0.00[ 20s] threads: 50, tps: 0.00, reads: 0.00, writes: 36780.48, response time: 2.64ms (95%), errors: 0.00, reconnects: 0.00OLTP test statistics:queries performed:read: 0write: 740134other: 0total: 740134transactions: 0 (0.00 per sec.)read/write requests: 740134 (37003.62 per sec.)other operations: 0 (0.00 per sec.)ignored errors: 0 (0.00 per sec.)reconnects: 0 (0.00 per sec.)General statistics:total time: 20.0017stotal number of events: 740134total time taken by event execution: 998.4475sresponse time:min: 0.26msavg: 1.35msmax: 28.27msapprox. 95 percentile: 2.69msThreads fairness:events (avg/stddev): 14802.6800/107.92execution time (avg/stddev): 19.9690/0.00

五、重新生成1000W条数据

[root@iZt6c63b3qfb3ckcelau81Z bin]# ./sysbench --mysql-host=10.104.97.191 --mysql-port=3301 --mysql-user=xianjqx_data --mysql-password=xxx你的密码xxx --mysql-db=test --oltp-tables-count=10 --oltp-table-size=10000000 --num-threads=50 --max-requests=1000000 --report-interval=1 --max-time=20 --test=/data/application/sysbench-0.5/sysbench/tests/db/oltp.lua preparesysbench 0.5: multi-threaded system evaluation benchmarkCreating table 'sbtest1'...Inserting 10000000 records into 'sbtest1'Creating table 'sbtest2'...Inserting 10000000 records into 'sbtest2'Creating table 'sbtest3'...Inserting 10000000 records into 'sbtest3'^C

[root@iZt6c63b3qfb3ckcelau81Z bin]# ./sysbench --mysql-host=10.104.97.191 --mysql-port=3301 --mysql-user=xianjqx_data --mysql-password=xxx你的密码xxx --mysql-db=test --oltp-tables-count=1 --oltp-table-size=100000 --num-threads=50 --max-requests=1000000 --report-interval=1 --max-time=20 --test=/data/application/sysbench-0.5/sysbench/tests/db/oltp.lua runsysbench 0.5: multi-threaded system evaluation benchmarkRunning the test with following options:Number of threads: 50Report intermediate results every 1 second(s)Random number generator seed is 0 and will be ignoredInitializing worker threads...Threads started![ 1s] threads: 50, tps: 780.92, reads: 11579.79, writes: 3175.67, response time: 94.05ms (95%), errors: 0.00, reconnects: 0.00[ 2s] threads: 50, tps: 804.00, reads: 11116.07, writes: 3182.02, response time: 94.50ms (95%), errors: 0.00, reconnects: 0.00[ 3s] threads: 50, tps: 799.00, reads: 11191.00, writes: 3213.00, response time: 99.11ms (95%), errors: 0.00, reconnects: 0.00[ 4s] threads: 50, tps: 831.99, reads: 11655.83, writes: 3321.95, response time: 94.84ms (95%), errors: 0.00, reconnects: 0.00[ 5s] threads: 50, tps: 817.02, reads: 11385.35, writes: 3262.10, response time: 97.11ms (95%), errors: 0.00, reconnects: 0.00[ 6s] threads: 50, tps: 813.94, reads: 11358.19, writes: 3260.77, response time: 98.55ms (95%), errors: 0.00, reconnects: 0.00[ 7s] threads: 50, tps: 846.05, reads: 11935.68, writes: 3379.19, response time: 89.95ms (95%), errors: 0.00, reconnects: 0.00[ 8s] threads: 50, tps: 816.01, reads: 11480.08, writes: 3273.02, response time: 95.61ms (95%), errors: 0.00, reconnects: 0.00[ 9s] threads: 50, tps: 856.99, reads: 11916.85, writes: 3421.96, response time: 89.60ms (95%), errors: 0.00, reconnects: 0.00[ 10s] threads: 50, tps: 855.00, reads: 12012.01, writes: 3420.00, response time: 91.47ms (95%), errors: 0.00, reconnects: 0.00[ 11s] threads: 50, tps: 828.00, reads: 11617.00, writes: 3317.00, response time: 96.13ms (95%), errors: 0.00, reconnects: 0.00[ 12s] threads: 50, tps: 861.92, reads: 11991.86, writes: 3447.67, response time: 94.73ms (95%), errors: 0.00, reconnects: 0.00[ 13s] threads: 50, tps: 837.09, reads: 11684.27, writes: 3339.36, response time: 89.87ms (95%), errors: 0.00, reconnects: 0.00^

[root@iZt6c63b3qfb3ckcelau81Z bin]# ./sysbench --mysql-host=10.104.97.191 --mysql-port=3301 --mysql-user=xianjqx_data --mysql-password=xxx你的密码xxx --mysql-db=test --oltp-tables-count=1 --oltp-table-size=100000 --num-threads=50 --max-requests=1000000 --report-interval=1 --max-time=20 --test=/data/application/sysbench-0.5/sysbench/tests/db/insert.lua runsysbench 0.5: multi-threaded system evaluation benchmarkRunning the test with following options:Number of threads: 50Report intermediate results every 1 second(s)Random number generator seed is 0 and will be ignoredInitializing worker threads...Threads started![ 1s] threads: 50, tps: 0.00, reads: 0.00, writes: 12433.35, response time: 22.63ms (95%), errors: 0.00, reconnects: 0.00[ 2s] threads: 50, tps: 0.00, reads: 0.00, writes: 15593.24, response time: 16.36ms (95%), errors: 0.00, reconnects: 0.00[ 3s] threads: 50, tps: 0.00, reads: 0.00, writes: 28653.17, response time: 4.01ms (95%), errors: 0.00, reconnects: 0.00[ 4s] threads: 50, tps: 0.00, reads: 0.00, writes: 30319.82, response time: 3.85ms (95%), errors: 0.00, reconnects: 0.00[ 5s] threads: 50, tps: 0.00, reads: 0.00, writes: 30990.70, response time: 3.60ms (95%), errors: 0.00, reconnects: 0.00[ 6s] threads: 50, tps: 0.00, reads: 0.00, writes: 29858.10, response time: 3.95ms (95%), errors: 0.00, reconnects: 0.00[ 7s] threads: 50, tps: 0.00, reads: 0.00, writes: 31203.40, response time: 3.64ms (95%), errors: 0.00, reconnects: 0.00[ 8s] threads: 50, tps: 0.00, reads: 0.00, writes: 28780.34, response time: 4.07ms (95%), errors: 0.00, reconnects: 0.00[ 9s] threads: 50, tps: 0.00, reads: 0.00, writes: 28733.79, response time: 3.83ms (95%), errors: 0.00, reconnects: 0.00[ 10s] threads: 50, tps: 0.00, reads: 0.00, writes: 28258.26, response time: 4.53ms (95%), errors: 0.00, reconnects: 0.00[ 11s] threads: 50, tps: 0.00, reads: 0.00, writes: 30780.03, response time: 3.64ms (95%), errors: 0.00, reconnects: 0.00[ 12s] threads: 50, tps: 0.00, reads: 0.00, writes: 28901.05, response time: 4.22ms (95%), errors: 0.00, reconnects: 0.00[ 13s] threads: 50, tps: 0.00, reads: 0.00, writes: 29570.52, response time: 4.07ms (95%), errors: 0.00, reconnects: 0.00[ 14s] threads: 50, tps: 0.00, reads: 0.00, writes: 29767.32, response time: 3.88ms (95%), errors: 0.00, reconnects: 0.00[ 15s] threads: 50, tps: 0.00, reads: 0.00, writes: 29028.18, response time: 4.11ms (95%), errors: 0.00, reconnects: 0.00[ 16s] threads: 50, tps: 0.00, reads: 0.00, writes: 29852.78, response time: 3.80ms (95%), errors: 0.00, reconnects: 0.00[ 17s] threads: 50, tps: 0.00, reads: 0.00, writes: 29760.11, response time: 4.04ms (95%), errors: 0.00, reconnects: 0.00[ 18s] threads: 50, tps: 0.00, reads: 0.00, writes: 30666.94, response time: 3.73ms (95%), errors: 0.00, reconnects: 0.00[ 19s] threads: 50, tps: 0.00, reads: 0.00, writes: 29163.70, response time: 4.01ms (95%), errors: 0.00, reconnects: 0.00[ 20s] threads: 46, tps: 0.00, reads: 0.00, writes: 28117.04, response time: 4.37ms (95%), errors: 0.00, reconnects: 0.00OLTP test statistics:queries performed:read: 0write: 560462other: 0total: 560462transactions: 0 (0.00 per sec.)read/write requests: 560462 (28020.09 per sec.)other operations: 0 (0.00 per sec.)ignored errors: 0 (0.00 per sec.)reconnects: 0 (0.00 per sec.)General statistics:total time: 20.0021stotal number of events: 560462total time taken by event execution: 998.8447sresponse time:min: 0.26msavg: 1.78msmax: 109.29msapprox. 95 percentile: 4.16msThreads fairness:events (avg/stddev): 11209.2400/164.64execution time (avg/stddev): 19.9769/0.00

[root@iZt6c63b3qfb3ckcelau81Z bin]# ./sysbench --mysql-host=10.104.97.191 --mysql-port=3301 --mysql-user=xianjqx_data --mysql-password=xxx你的密码xxx --mysql-db=test --oltp-tables-count=1 --oltp-table-size=200000 --num-threads=50 --max-requests=1000000 --report-interval=1 --max-time=20 --test=/data/application/sysbench-0.5/sysbench/tests/db/insert.lua runsysbench 0.5: multi-threaded system evaluation benchmarkRunning the test with following options:Number of threads: 50Report intermediate results every 1 second(s)Random number generator seed is 0 and will be ignoredInitializing worker threads...Threads started![ 1s] threads: 50, tps: 0.00, reads: 0.00, writes: 28119.45, response time: 4.22ms (95%), errors: 0.00, reconnects: 0.00[ 2s] threads: 50, tps: 0.00, reads: 0.00, writes: 29751.91, response time: 3.99ms (95%), errors: 0.00, reconnects: 0.00[ 3s] threads: 50, tps: 0.00, reads: 0.00, writes: 28974.88, response time: 3.98ms (95%), errors: 0.00, reconnects: 0.00[ 4s] threads: 50, tps: 0.00, reads: 0.00, writes: 27188.19, response time: 4.27ms (95%), errors: 0.00, reconnects: 0.00[ 5s] threads: 50, tps: 0.00, reads: 0.00, writes: 27051.92, response time: 4.60ms (95%), errors: 0.00, reconnects: 0.00[ 6s] threads: 50, tps: 0.00, reads: 0.00, writes: 28387.05, response time: 4.17ms (95%), errors: 0.00, reconnects: 0.00[ 7s] threads: 50, tps: 0.00, reads: 0.00, writes: 27526.93, response time: 4.17ms (95%), errors: 0.00, reconnects: 0.00[ 8s] threads: 50, tps: 0.00, reads: 0.00, writes: 29055.88, response time: 3.93ms (95%), errors: 0.00, reconnects: 0.00[ 9s] threads: 50, tps: 0.00, reads: 0.00, writes: 30810.90, response time: 3.65ms (95%), errors: 0.00, reconnects: 0.00[ 10s] threads: 50, tps: 0.00, reads: 0.00, writes: 30249.97, response time: 3.88ms (95%), errors: 0.00, reconnects: 0.00[ 11s] threads: 50, tps: 0.00, reads: 0.00, writes: 29534.29, response time: 3.89ms (95%), errors: 0.00, reconnects: 0.00^C

[root@iZt6c63b3qfb3ckcelau81Z bin]# ./sysbench --mysql-host=10.104.97.191 \  --mysql-port=3301 \  --mysql-user=xianjqx_data \  --mysql-password=xxx你的密码xxx\  --mysql-db=test \  --oltp-tables-count=1 \  --oltp-table-size=20000000 \  --num-threads=50 \  --max-requests=1 \  --report-interval=1 \  --max-time=20 \  --test=/data/application/sysbench-0.5/sysbench/tests/db/oltp.lua preparesysbench 0.5: multi-threaded system evaluation benchmarkCreating table 'sbtest1'...Inserting 20000000 records into 'sbtest1'

[root@iZt6c63b3qfb3ckcelau81Z bin]# time ./sysbench --mysql-host=10.104.97.191 --mysql-port=3301 --mysql-user=xianjqx_data --mysql-password=xxx你的密码xxx --mysql-db=test --oltp-tables-count=1 --oltp-table-size=200000 --num-threads=50 --max-requests=1000000 --report-interval=1 --max-time=20 --test=/data/application/sysbench-0.5/sysbench/tests/db/insert.lua runsysbench 0.5: multi-threaded system evaluation benchmarkRunning the test with following options:Number of threads: 50Report intermediate results every 1 second(s)Random number generator seed is 0 and will be ignoredInitializing worker threads...Threads started![ 1s] threads: 50, tps: 0.00, reads: 0.00, writes: 16591.27, response time: 15.42ms (95%), errors: 0.00, reconnects: 0.00[ 2s] threads: 50, tps: 0.00, reads: 0.00, writes: 14475.10, response time: 19.07ms (95%), errors: 0.00, reconnects: 0.00[ 3s] threads: 50, tps: 0.00, reads: 0.00, writes: 17053.20, response time: 15.47ms (95%), errors: 0.00, reconnects: 0.00[ 4s] threads: 50, tps: 0.00, reads: 0.00, writes: 20432.93, response time: 15.89ms (95%), errors: 0.00, reconnects: 0.00[ 5s] threads: 50, tps: 0.00, reads: 0.00, writes: 26256.87, response time: 8.43ms (95%), errors: 0.00, reconnects: 0.00[ 6s] threads: 50, tps: 0.00, reads: 0.00, writes: 30827.40, response time: 3.80ms (95%), errors: 0.00, reconnects: 0.00[ 7s] threads: 50, tps: 0.00, reads: 0.00, writes: 30526.33, response time: 3.83ms (95%), errors: 0.00, reconnects: 0.00[ 8s] threads: 50, tps: 0.00, reads: 0.00, writes: 31340.72, response time: 3.90ms (95%), errors: 0.00, reconnects: 0.00[ 9s] threads: 50, tps: 0.00, reads: 0.00, writes: 29438.79, response time: 4.05ms (95%), errors: 0.00, reconnects: 0.00[ 10s] threads: 50, tps: 0.00, reads: 0.00, writes: 30624.59, response time: 3.92ms (95%), errors: 0.00, reconnects: 0.00[ 11s] threads: 50, tps: 0.00, reads: 0.00, writes: 30201.34, response time: 3.99ms (95%), errors: 0.00, reconnects: 0.00[ 12s] threads: 50, tps: 0.00, reads: 0.00, writes: 29844.09, response time: 4.15ms (95%), errors: 0.00, reconnects: 0.00[ 13s] threads: 50, tps: 0.00, reads: 0.00, writes: 30078.70, response time: 3.91ms (95%), errors: 0.00, reconnects: 0.00[ 14s] threads: 50, tps: 0.00, reads: 0.00, writes: 29924.12, response time: 4.14ms (95%), errors: 0.00, reconnects: 0.00[ 15s] threads: 50, tps: 0.00, reads: 0.00, writes: 30281.30, response time: 4.05ms (95%), errors: 0.00, reconnects: 0.00[ 16s] threads: 50, tps: 0.00, reads: 0.00, writes: 29874.80, response time: 4.10ms (95%), errors: 0.00, reconnects: 0.00[ 17s] threads: 50, tps: 0.00, reads: 0.00, writes: 30530.84, response time: 3.97ms (95%), errors: 0.00, reconnects: 0.00[ 18s] threads: 50, tps: 0.00, reads: 0.00, writes: 30115.30, response time: 3.98ms (95%), errors: 0.00, reconnects: 0.00[ 19s] threads: 50, tps: 0.00, reads: 0.00, writes: 30373.87, response time: 3.93ms (95%), errors: 0.00, reconnects: 0.00[ 20s] threads: 48, tps: 0.00, reads: 0.00, writes: 30412.30, response time: 3.89ms (95%), errors: 0.00, reconnects: 0.00OLTP test statistics:queries performed:read: 0write: 549248other: 0total: 549248transactions: 0 (0.00 per sec.)read/write requests: 549248 (27459.96 per sec.)other operations: 0 (0.00 per sec.)ignored errors: 0 (0.00 per sec.)reconnects: 0 (0.00 per sec.)General statistics:total time: 20.0018stotal number of events: 549248total time taken by event execution: 998.9108sresponse time:min: 0.26msavg: 1.82msmax: 106.28msapprox. 95 percentile: 4.80msThreads fairness:events (avg/stddev): 10984.9600/156.42execution time (avg/stddev): 19.9782/0.00real 0m20.042suser 0m14.357ssys 0m9.644s

五、测试读

[root@iZt6c63b3qfb3ckcelau81Z bin]# time ./sysbench --mysql-host=10.104.97.191 --mysql-port=3301 --mysql-user=xianjqx_data --mysql-password=xxx你的密码xxx --mysql-db=test --oltp-tables-count=1 --oltp-table-size=200000 --num-threads=50 --max-requests=1000000 --report-interval=1 --max-time=20 --test=/data/application/sysbench-0.5/sysbench/tests/db/select.lua runsysbench 0.5: multi-threaded system evaluation benchmarkRunning the test with following options:Number of threads: 50Report intermediate results every 1 second(s)Random number generator seed is 0 and will be ignoredInitializing worker threads...Threads started![ 1s] threads: 50, tps: 0.00, reads: 48105.42, writes: 0.00, response time: 1.74ms (95%), errors: 0.00, reconnects: 0.00[ 2s] threads: 50, tps: 0.00, reads: 49462.80, writes: 0.00, response time: 1.64ms (95%), errors: 0.00, reconnects: 0.00[ 3s] threads: 50, tps: 0.00, reads: 49870.44, writes: 0.00, response time: 1.67ms (95%), errors: 0.00, reconnects: 0.00[ 4s] threads: 50, tps: 0.00, reads: 49682.75, writes: 0.00, response time: 1.65ms (95%), errors: 0.00, reconnects: 0.00[ 5s] threads: 50, tps: 0.00, reads: 47181.19, writes: 0.00, response time: 1.77ms (95%), errors: 0.00, reconnects: 0.00[ 6s] threads: 50, tps: 0.00, reads: 49236.55, writes: 0.00, response time: 1.65ms (95%), errors: 0.00, reconnects: 0.00[ 7s] threads: 50, tps: 0.00, reads: 49569.46, writes: 0.00, response time: 1.64ms (95%), errors: 0.00, reconnects: 0.00[ 8s] threads: 50, tps: 0.00, reads: 50421.41, writes: 0.00, response time: 1.59ms (95%), errors: 0.00, reconnects: 0.00[ 9s] threads: 50, tps: 0.00, reads: 49966.87, writes: 0.00, response time: 1.61ms (95%), errors: 0.00, reconnects: 0.00[ 10s] threads: 50, tps: 0.00, reads: 49674.86, writes: 0.00, response time: 1.65ms (95%), errors: 0.00, reconnects: 0.00[ 11s] threads: 50, tps: 0.00, reads: 48963.93, writes: 0.00, response time: 1.70ms (95%), errors: 0.00, reconnects: 0.00[ 12s] threads: 50, tps: 0.00, reads: 50391.54, writes: 0.00, response time: 1.61ms (95%), errors: 0.00, reconnects: 0.00[ 13s] threads: 50, tps: 0.00, reads: 49976.12, writes: 0.00, response time: 1.64ms (95%), errors: 0.00, reconnects: 0.00[ 14s] threads: 50, tps: 0.00, reads: 46046.08, writes: 0.00, response time: 1.83ms (95%), errors: 0.00, reconnects: 0.00[ 15s] threads: 50, tps: 0.00, reads: 48998.34, writes: 0.00, response time: 1.69ms (95%), errors: 0.00, reconnects: 0.00[ 16s] threads: 50, tps: 0.00, reads: 49224.01, writes: 0.00, response time: 1.67ms (95%), errors: 0.00, reconnects: 0.00[ 17s] threads: 50, tps: 0.00, reads: 48661.94, writes: 0.00, response time: 1.77ms (95%), errors: 0.00, reconnects: 0.00[ 18s] threads: 50, tps: 0.00, reads: 48576.87, writes: 0.00, response time: 1.73ms (95%), errors: 0.00, reconnects: 0.00[ 19s] threads: 50, tps: 0.00, reads: 45286.54, writes: 0.00, response time: 1.96ms (95%), errors: 0.00, reconnects: 0.00OLTP test statistics:queries performed:read: 976826write: 0other: 0total: 976826transactions: 0 (0.00 per sec.)read/write requests: 976826 (48839.53 per sec.)other operations: 0 (0.00 per sec.)ignored errors: 0 (0.00 per sec.)reconnects: 0 (0.00 per sec.)General statistics:total time: 20.0007stotal number of events: 976826total time taken by event execution: 997.9082sresponse time:min: 0.27msavg: 1.02msmax: 15.07msapprox. 95 percentile: 1.70msThreads fairness:events (avg/stddev): 19536.5200/99.42execution time (avg/stddev): 19.9582/0.00real 0m20.056suser 0m14.841ssys 0m15.401s[root@iZt6c63b3qfb3ckcelau81Z bin]#