mysql sysbench测试安装及命令

42121

一、安装sysbench

1.软件版本sysbench-0.4.12.7

2.编译安装
1）编译配置
# ./configure --with-mysql-includes=/opt/mysql/include/ --with-mysql-libs=/opt/mysql/lib/

2）编译
#make
....
....
/bin/sh ../libtool --tag=CC   --mode=link gcc -pthread -g -O2      -o sysbench sysbench.o sb_timer.o sb_options.o sb_logger.o db_driver.o tests/fileio/libsbfileio.a tests/threads/libsbthreads.a tests/memory/libsbmemory.a tests/cpu/libsbcpu.a tests/oltp/libsboltp.a tests/mutex/libsbmutex.a drivers/mysql/libsbmysql.a -L/opt/mysql/lib/ -lmysqlclient_r   -lrt -lm
libtool: link: gcc -pthread -g -O2 -o sysbench sysbench.o sb_timer.o sb_options.o sb_logger.o db_driver.o  tests/fileio/libsbfileio.a tests/threads/libsbthreads.a tests/memory/libsbmemory.a tests/cpu/libsbcpu.a tests/oltp/libsboltp.a tests/mutex/libsbmutex.a drivers/mysql/libsbmysql.a -L/opt/mysql/lib/ -lmysqlclient_r -lrt -lm -pthread
make[2]: Leaving directory `/home/soft/sysbench-0.4.12.7/sysbench'
make[1]: Leaving directory `/home/soft/sysbench-0.4.12.7/sysbench'
make[1]: Entering directory `/home/soft/sysbench-0.4.12.7'
make[1]: Nothing to be done for `all-am'.
make[1]: Leaving directory `/home/soft/sysbench-0.4.12.7'
[iyunv@mdb01 sysbench-0.4.12.7]#

3）安装
#make install

sysbench默认安装到/usr/loca/bin

4）安装问题：
1.make 错误：
libtool: link: gcc -pthread -g -O2 -o sysbench sysbench.o sb_timer.o sb_options.o sb_logger.o db_driver.o  tests/fileio/libsbfileio.a tests/threads/libsbthreads.a tests/memory/libsbmemory.a tests/cpu/libsbcpu.a tests/oltp/libsboltp.a tests/mutex/libsbmutex.a drivers/mysql/libsbmysql.a -L/opt/mysql/lib/ -lmysqlclient_r -lrt -lm -pthread
/usr/bin/ld: cannot find -lmysqlclient_r
collect2: ld returned 1 exit status
make[2]: *** [sysbench] Error 1
make[2]: Leaving directory `/home/soft/sysbench-0.4.12.7/sysbench'
make[1]: *** [all-recursive] Error 1
make[1]: Leaving directory `/home/soft/sysbench-0.4.12.7/sysbench'
make: *** [all-recursive] Error 1
[iyunv@mdb01 sysbench-0.4.12.7]#

解决办法：

默认查找libmysqlclient_r.so，可是mysql默认为libmysqlclient.so，内容完全一样，做个链接即可

# ln -s libmysqlclient.so.20.0.9 libmysqlclient_r.so


5）设置环境变量
解决sysbench --help无法找到MySQL LIB库的问题
vi /etc/profile
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/opt/mysql/lib


二、测试

1.建立测试库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> create database test
    -> ;
Query OK, 1 row affected (0.18 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.08 sec)

mysql>


开始测试

2.准备测试表
1)预备命令
[iyunv@mdb01 ~]# sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=100000 --mysql-socket=/opt/mysql/mysql.sock --mysql-db=test --mysql-user=root --mysql-host=127.0.0.1 --mysql-password=123456 prepare
sysbench 0.4.13:  multi-threaded system evaluation benchmark

No DB drivers specified, using mysql
Creating table 'sbtest'...
Creating 100000 records in table 'sbtest'...
[iyunv@mdb01 ~]#
2）数据库命令建立的测试表
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| sbtest         |
+----------------+
1 row in set (0.01 sec)

mysql> desc sbtest;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| k     | int(10) unsigned | NO   | MUL | 0       |                |
| c     | char(120)        | NO   |     |         |                |
| pad   | char(60)         | NO   |     |         |                |
+-------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> select count(*) from sbtest;
+----------+
| count(*) |
+----------+
|   100000 |
+----------+
1 row in set (0.05 sec)

mysql>

3.运行测试

[iyunv@mdb01 ~]# sysbench --num-threads=8 --max-requests=10000 --test=oltp --mysql-table-engine=innodb --oltp-table-size=100000 --mysql-socket=/opt/mysql/mysql.sock --mysql-db=test --mysql-user=root --mysql-host=127.0.0.1 --mysql-password=123456 run
sysbench 0.4.13:  multi-threaded system evaluation benchmark

No DB drivers specified, using mysql
Running the test with following options:
Number of threads: 8
Random number generator seed is 0 and will be ignored


Doing OLTP test.
Running mixed OLTP test
Using Special distribution (12 iterations,  1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Maximum number of requests for OLTP test is limited to 10000
Using 1 test tables
Threads started!
Done.


OLTP test statistics:
    queries performed:
        read:                            140098
        write:                           50012
        other:                           20007
        total:                           210117
    transactions:                        10000  (163.72 per sec.)
    deadlocks:                           7      (0.11 per sec.)
    read/write requests:                 190110 (3112.41 per sec.)
    other operations:                    20007  (327.55 per sec.)

Test execution summary:
    total time:                          61.0813s
    total number of events:              10000
    total time taken by event execution: 488.3863
    per-request statistics:
         min:                                 18.94ms
         avg:                                 48.84ms
         max:                                336.99ms
         approx.  95 percentile:              62.45ms

Threads fairness:
    events (avg/stddev):           1250.0000/2.78
    execution time (avg/stddev):   61.0483/0.01

[iyunv@mdb01 ~]#