一、安装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'

[root@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

[root@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)预备命令

[root@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'...

[root@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.运行测试


[root@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


[root@mdb01 ~]#