sysbench-test

벤치마킹용 DB, USER 생성

create database sysbench;

create user sysbench@'%' identified by 'sysbench';
create user sysbench@'localhost' identified by 'sysbench';
create user sysbench@'127.0.0.1' identified by 'sysbench';

grant all on sysbench.* to sysbench@'%';
grant all on sysbench.* to sysbench@'localhost';
grant all on sysbench.* to sysbench@'127.0.0.1';

sysbench source building

$ wget http://downloads.mysql.com/source/sysbench-0.4.12.10.tar.gz
$ tar xvfz sysbench-0.4.12.10.tar.gz
$ cd sysbench-0.4.12.10
$ ./autogen.sh
$ ./configure --prefix=/engn001/masvc01/sysbench --with-mysql-includes=/engn001/masvc01/GAAID/mysql/include/mysql --with-mysql-libs=/engn001/masvc01/GAAID/mysql/lib
$ make
$ make install
$ .bash_profile에 추가
export PATH=$PATH:/engn001/masvc01/sysbench/bin
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/engn001/masvc01/GAAID/mysql/lib

test 진행

prepare -> run 단계를 거친다.

sysbench --test=oltp --oltp-table-size=10000000 --num-threads=8 --max-requests=10000 --max-time=0 \
--mysql-host=localhost --mysql-user=sysbench --mysql-password=sysbench \
--mysql-db=sysbench --mysql-table-engine=innodb \
--mysql-socket=/engn001/masvc01/GAAID/mysql/mysql.sock \
--mysql-port=3310 \
--oltp-test-mode=complex \
--oltp-read-only=off \
prepare

sysbench --test=oltp --oltp-table-size=10000000 --num-threads=8 --max-requests=10000 --max-time=0 \
--mysql-host=localhost --mysql-user=sysbench --mysql-password=sysbench \
--mysql-db=sysbench --mysql-table-engine=innodb \
--mysql-socket=/engn001/masvc01/GAAID/mysql/mysql.sock \
--mysql-port=3310 \
--oltp-test-mode=complex \
--oltp-read-only=off \
run

sysbench 0.4.12.10:  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:                            140028
        write:                           50010
        other:                           20004
        total:                           210042
    transactions:                        10002  (1039.33 per sec.) <- 초당 트랜잭션 1040건
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 190038 (19747.29 per sec.) <- 초쿼리 실행 19847건
    other operations:                    20004  (2078.66 per sec.)

General statistics:
    total time:                          9.6235s
    total number of events:              10002
    total time taken by event execution: 76.8922
    response time:
         min:                                  4.32ms
         avg:                                  7.69ms
         max:                                 68.28ms
         approx.  95 percentile:              10.72ms

Threads fairness:
    events (avg/stddev):           1250.2500/9.82
    execution time (avg/stddev):   9.6115/0.00