sysbench安裝及簡單使用

sysbench安裝：

tar -zxf  sysbench-0.4.12.tar.gz

cd sysbench-0.4.12

./configure --with-mysql-includes=/usr/local/mysql/include/mysql --with-mysql-libs=/usr/local/mysql/lib/mysql
export echo=echo

make

如果make報錯，則執行：

./autogen.sh

然後make && make install即可

有很多時候仍然還有報錯，那麼就需要檢查gcc和libtool包有沒有裝全，使用rpm -qa和yum list看看。

報錯資訊如：

---------------------------------------------------------------------------------------------------------------------------------------------------------

../libtool: line 838: X--tag=CC: command not found

../libtool: line 871: libtool: ignoring unknown tag : command not found

../libtool: line 838: X--mode=link: command not found

../libtool: line 1004: *** Warning: inferring the mode of operation is deprecated.: command not found

../libtool: line 1005: *** Future versions of Libtool will require --mode=MODE be specified.: command not found

../libtool: line 2231: X-g: command not found

../libtool: line 2231: X-O2: command not found

../libtool: line 2231: X-rdynamic: command not found

../libtool: line 1951: X-L/usr/lib/mysql: No such file or directory

../libtool: line 2400: Xsysbench: command not found

Fatal server error:

Server is already active for display 0

        If this server is no longer running, remove /tmp/.X0-lock

        and start again.

../libtool: line 2412: Xsysbench: command not found

../libtool: line 2547: X-lmysqlclient_r: command not found

../libtool: line 2547: X-lz: command not found

../libtool: line 2547: X-lcrypt: command not found

../libtool: line 2547: X-lnsl: command not found

../libtool: line 2547: X-lm: command not found

../libtool: line 2547: X-lmygcc: command not found

../libtool: line 2547: X-lrt: command not found

../libtool: line 2547: X-lm: command not found

../libtool: line 2629: X-L/root/tool/sysbench-0.4.12/sysbench: No such file or directory

../libtool: line 2547: X-lmysqlclient_r: command not found

../libtool: line 2547: X-lz: command not found

../libtool: line 2547: X-lcrypt: command not found

../libtool: line 2547: X-lnsl: command not found

../libtool: line 2547: X-lmygcc: command not found

../libtool: line 2547: X-lrt: command not found

../libtool: line 2547: X-lm: command not found

../libtool: line 2629: X-L/root/tool/sysbench-0.4.12/sysbench: No such file or directory

../libtool: line 2547: X-lmysqlclient_r: command not found

../libtool: line 2547: X-lz: command not found

../libtool: line 2547: X-lcrypt: command not found

../libtool: line 2547: X-lnsl: command not found

../libtool: line 2547: X-lmygcc: command not found

../libtool: line 2547: X-lrt: command not found

../libtool: line 2547: X-lm: command not found

../libtool: line 5162: Xgcc -pthread "" "" -o @OUTPUT@ 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/root/tool/sysbench-0.4.12/sysbench -lmysqlclient_r -lz -lcrypt -lnsl -lmygcc -lrt -lm: No such file or directory

../libtool: line 5163: Xgcc -pthread "" "" -o @OUTPUT@ 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/root/tool/sysbench-0.4.12/sysbench -lmysqlclient_r -lz -lcrypt -lnsl -lmygcc -lrt -lm: No such file or directory

---------------------------------------------------------------------------------------------------------------------------------------------------------

[root@hd-119-186 ~]# rpm -qa|grep gcc

compat-gcc-34-3.4.6-4.1

compat-gcc-34-g77-3.4.6-4.1

gcc-gnat-4.1.2-52.el5

gcc-c++-4.1.2-52.el5

gcc44-c++-4.4.6-3.el5.1

compat-libgcc-296-2.96-138

gcc-gfortran-4.1.2-52.el5

libgcc-4.1.2-52.el5

libgcc-4.1.2-52.el5

gcc-objc-4.1.2-52.el5

gcc-4.1.2-52.el5

gcc44-4.4.6-3.el5.1

gcc44-gfortran-4.4.6-3.el5.1

compat-gcc-34-c++-3.4.6-4.1

gcc-java-4.1.2-52.el5

一定要保證/usr/lib或/usr/lib64下有相應的mysql/libmysqlclient.so檔案或軟連結

[root@root lib64]# pwd

/usr/lib64

[root@root lib64]# ll|grep mysql

lrwxrwxrwx.  1 root root       43 May 28 10:04 libmysqlclient_r.so -> /usr/lib64/mysql/libmysqlclient_r.so.16.0.0

lrwxrwxrwx.  1 root root       43 May 28 10:05 libmysqlclient_r.so.16 -> /usr/lib64/mysql/libmysqlclient_r.so.16.0.0

-rwxr-xr-x.  1 root root  1591560 May 28 10:05 libmysqlclient_r.so.16.0.0

lrwxrwxrwx.  1 root root       41 May 28 10:05 libmysqlclient.so -> /usr/lib64/mysql/libmysqlclient.so.16.0.0

lrwxrwxrwx.  1 root root       41 May 28 10:05 libmysqlclient.so.16 -> /usr/lib64/mysql/libmysqlclient.so.16.0.0

-rwxr-xr-x.  1 root root  1580928 May 28 10:06 libmysqlclient.so.16.0.0

lrwxrwxrwx.  1 root root       37 May 28 09:26 libmysqlclient.so.18 -> /usr/lib64/mysql/libmysqlclient.so.18

lrwxrwxrwx.  1 root root       20 May 28 09:37 libperconaserverclient.so.18 -> libmysqlclient.so.18

準備資料：

sysbench  --test=oltp --debug=off --mysql-table-engine=innodb  --oltp-table-size=20000000 --mysql-user=root --mysql-password=root --mysql-db=test --mysql-socket=/tmp/mysql.sock prepare

進行測試：

sysbench --num-threads=10 --test=oltp --mysql-user=root --mysql-password=root --mysql-table-engine=innodb  --init-rng=on --oltp-table-size=20000000 --max-time=$RT --max-requests=20000 --mysql-db=test --mysql-socket=/tmp/mysql.sock run > sysbench.log

執行緒數為10，測試型別為OLTP，表儲存引擎為innodb的，表總行數200萬行，(每行252bytes)，訪問請求為2萬次，資料庫為test，(準備資料時會建立sbtest表)

結果中的TPS結果：

transactions:                        20001  (908.75 per sec.)

檢視幫助：

[root@hd-119-186 test]# sysbench --help

Usage:

  sysbench [general-options]... --test= [test-options]... command

General options:

  --num-threads=N            number of threads to use [1]

  --max-requests=N           limit for total number of requests [10000]

  --max-time=N               limit for total execution time in seconds [0]

  --forced-shutdown=STRING   amount of time to wait after --max-time before forcing shutdown [off]

  --thread-stack-size=SIZE   size of stack per thread [32K]

  --init-rng=[on|off]        initialize random number generator [off]

  --test=STRING              test to run

  --debug=[on|off]           print more debugging info [off]

  --validate=[on|off]        perform validation checks where possible [off]

  --help=[on|off]            print help and exit

  --version=[on|off]         print version and exit

Compiled-in tests:

  fileio - File I/O test

  cpu - CPU performance test

  memory - Memory functions speed test

  threads - Threads subsystem performance test

  mutex - Mutex performance test

  oltp - OLTP test

Commands: prepare run cleanup help version

See 'sysbench --test= help' for a list of options for each test.

[root@hd-119-186 test]# sysbench --test=oltp --help

Usage:

  sysbench [general-options]... --test= [test-options]... command

General options:

  --num-threads=N            number of threads to use [1]

  --max-requests=N           limit for total number of requests [10000]

  --max-time=N               limit for total execution time in seconds [0]

  --forced-shutdown=STRING   amount of time to wait after --max-time before forcing shutdown [off]

  --thread-stack-size=SIZE   size of stack per thread [32K]

  --init-rng=[on|off]        initialize random number generator [off]

  --test=STRING              test to run

  --debug=[on|off]           print more debugging info [off]

  --validate=[on|off]        perform validation checks where possible [off]

  --help=[on|off]            print help and exit

  --version=[on|off]         print version and exit

Compiled-in tests:

  fileio - File I/O test

  cpu - CPU performance test

  memory - Memory functions speed test

  threads - Threads subsystem performance test

  mutex - Mutex performance test

  oltp - OLTP test

Commands: prepare run cleanup help version

See 'sysbench --test= help' for a list of options for each test.

 
實際測試用法：
sysbench --num-threads=1792 --test=oltp --mysql-user=root --mysql-password=123456 --mysql-table-engine=innodb --oltp-test-mode=complex --oltp-table-size=20000000 --max-time=3600 --max-requests=0 --mysql-db=testdb --mysql-socket=/data/mysql6134/mysql.sock run > 1792_thread.log
執行緒數：1792
錶行數：2000萬
測試時間：3600秒
測試型別：OLTP混合測試


2014.11.19：
今天在做sysbench測試的時候，連線數開到1792時，報錯了：
mysql_stmt_prepare failed! error(1461)Can't create more than max_prepared_stmt_count statements (current value: 16382)

檢視相應引數的值：
mysql> show global variables like '%stmt%';
+----------------------------+----------------------+
| Variable_name              | Value                |
+----------------------------+----------------------+
| binlog_stmt_cache_size     | 32768                |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| max_prepared_stmt_count    | 16382               |
+----------------------------+----------------------+
3 rows in set (0.00 sec)

將該值改大：
set global max_prepared_stmt_count=124000;

再次進行sysbench測試，開1792執行緒，沒有問題。

mysql> show global status like '%stmt%';
+----------------------------+----------+
| Variable_name              | Value    |
+----------------------------+----------+
| Binlog_stmt_cache_disk_use | 0        |
| Binlog_stmt_cache_use      | 0        |
| Com_stmt_close             | 0        |
| Com_stmt_execute           | 14144611 |
| Com_stmt_fetch             | 0        |
| Com_stmt_prepare           | 55752    |
| Com_stmt_reprepare         | 0        |
| Com_stmt_reset             | 0        |
| Com_stmt_send_long_data    | 0        |
| Prepared_stmt_count        | 17920    |
+----------------------------+----------+
10 rows in set (0.00 sec)

修改後即可。


查閱其他資料：
Com_stmt_close             prepare語句關閉的次數
Com_stmt_execute           prepare語句執行的次數
Com_stmt_prepare           prepare語句建立的次數