sysbench.md

sysbench result

[root@sim2-p2tk ~]# time sysbench --test=/usr/share/doc/sysbench/tests/db/oltp.lua --mysql-table-engine=innodb --oltp-table-size=250000 --mysql-user=testdb --mysql-password=testpw --mysql-socket=/var/lib/mysql/mysql.sock prepare
sysbench 0.5:  multi-threaded system evaluation benchmark

Creating table 'sbtest1'...
Inserting 250000 records into 'sbtest1'

real    0m11.455s
user    0m3.805s
sys     0m0.025s

[root@sim2-p2tk ~]# sysbench --num-threads=16 --max-requests=500000 --test=/usr/share/doc/sysbench/tests/db/oltp.lua --oltp-table-size=250000 --mysql-socket=/var/lib/mysql/mysql.sock --oltp-read-only --mysql-user=testdb --mysql-password=testpw run
sysbench 0.5:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 16
Random number generator seed is 0 and will be ignored


Threads started!

OLTP test statistics:
    queries performed:
        read:                            7000126
        write:                           2000034
        other:                           1000017
        total:                           10000177
    transactions:                        500008 (881.25 per sec.)
    read/write requests:                 9000160 (15862.56 per sec.)
    other operations:                    1000017 (1762.51 per sec.)
    ignored errors:                      1      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          567.3837s
    total number of events:              500008
    total time taken by event execution: 9076.7644s
    response time:
         min:                                  3.40ms
         avg:                                 18.15ms
         max:                                247.93ms
         approx.  95 percentile:              32.18ms

Threads fairness:
    events (avg/stddev):           31250.5000/581.61
    execution time (avg/stddev):   567.2978/0.01

[root@sim2-p2tk ~]# time sysbench --num-threads=16 --max-requests=5000 --test=/usr/share/doc/sysbench/tests/db/oltp.lua --oltp-table-size=250000 --mysql-socket=/var/lib/mysql/mysql.sock --oltp-test-mode=complex --mysql-user=testdb --mysql-password=testpw run
sysbench 0.5:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 16
Random number generator seed is 0 and will be ignored


Threads started!

OLTP test statistics:
    queries performed:
        read:                            70000
        write:                           20000
        other:                           10000
        total:                           100000
    transactions:                        5000   (870.12 per sec.)
    read/write requests:                 90000  (15662.07 per sec.)
    other operations:                    10000  (1740.23 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          5.7464s
    total number of events:              5000
    total time taken by event execution: 91.5826s
    response time:
         min:                                  3.78ms
         avg:                                 18.32ms
         max:                                 99.20ms
         approx.  95 percentile:              33.71ms

Threads fairness:
    events (avg/stddev):           312.5000/7.58
    execution time (avg/stddev):   5.7239/0.01


real    0m5.788s
user    0m1.999s
sys     0m1.269s

========================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================


[root@sim2-p2tk ~]# time sysbench --test=/usr/share/doc/sysbench/tests/db/oltp.lua --mysql-table-engine=tokudb --oltp-table-size=250000 --mysql-user=testdb --mysql-password=testpw --mysql-socket=/var/lib/mysql/mysql.sock prepare
sysbench 0.5:  multi-threaded system evaluation benchmark

Creating table 'sbtest1'...
Inserting 250000 records into 'sbtest1'

real    0m12.773s
user    0m3.675s
sys     0m0.022s
[root@sim2-p2tk ~]# time sysbench --num-threads=16 --max-requests=500000 --test=/usr/share/doc/sysbench/tests/db/oltp.lua --oltp-table-size=250000 --mysql-socket=/var/lib/mysql/mysql.sock --oltp-read-only --mysql-user=testdb --mysql-password=testpw run
sysbench 0.5:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 16
Random number generator seed is 0 and will be ignored


Threads started!

^[[AOLTP test statistics:
    queries performed:
        read:                            7000056
        write:                           2000016
        other:                           1000008
        total:                           10000080
    transactions:                        500004 (210.73 per sec.)
    read/write requests:                 9000072 (3793.10 per sec.)
    other operations:                    1000008 (421.46 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          2372.7453s
    total number of events:              500004
    total time taken by event execution: 37961.6419s
    response time:
         min:                                  5.23ms
         avg:                                 75.92ms
         max:                                423.21ms
         approx.  95 percentile:              99.41ms

Threads fairness:
    events (avg/stddev):           31250.2500/218.28
    execution time (avg/stddev):   2372.6026/0.01


real    39m32.780s
user    6m43.906s
sys     4m36.130s

[root@sim2-p2tk ~]# time sysbench --num-threads=16 --max-requests=5000 --test=/usr/share/doc/sysbench/tests/db/oltp.lua --oltp-table-size=250000 --mysql-socket=/var/lib/mysql/mysql.sock --oltp-test-mode=complex --mysql-user=testdb --mysql-password=testpw run
sysbench 0.5:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 16
Random number generator seed is 0 and will be ignored


Threads started!

OLTP test statistics:
    queries performed:
        read:                            70000
        write:                           20000
        other:                           10000
        total:                           100000
    transactions:                        5000   (205.52 per sec.)
    read/write requests:                 90000  (3699.29 per sec.)
    other operations:                    10000  (411.03 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          24.3290s
    total number of events:              5000
    total time taken by event execution: 388.8211s
    response time:
         min:                                 23.22ms
         avg:                                 77.76ms
         max:                                136.06ms
         approx.  95 percentile:              98.99ms

Threads fairness:
    events (avg/stddev):           312.5000/3.46
    execution time (avg/stddev):   24.3013/0.01


real    0m24.369s
user    0m4.166s
sys     0m2.933s

Conclusion

I think I will choose xtradb for now. Don't know how to use it properly, if anyone knew about this, I want your help, thanks.

sysbench_install.md

Sysbench Tutorial

echo "create database sbtest;" | mysql -uroot -p
echo "grant all on sbtest.* to testdb@localhost identified by 'testpw';" | mysql -uroot -p
echo "flush privileges;" | mysql -uroot -p

echo "create database sbtest_toku;" | mysql -uroot -p
echo "grant all on sbtest.* to testdb@localhost identified by 'testpw';" | mysql -uroot -p
echo "flush privileges;" | mysql -uroot -p

sysbench --test=/usr/share/doc/sysbench/tests/db/oltp.lua --mysql-table-engine=innodb --oltp-table-size=250000 --mysql-user=testdb --mysql-password=testpw --mysql-socket=/var/lib/mysql/mysql.sock prepare

sysbench --num-threads=16 --max-requests=500000 --test=/usr/share/doc/sysbench/tests/db/oltp.lua --oltp-table-size=250000 --mysql-socket=/var/lib/mysql/mysql.sock --oltp-read-only --mysql-user=testdb --mysql-password=testpw run

sysbench --test=/usr/share/doc/sysbench/tests/db/oltp.lua --mysql-table-engine=tokudb --oltp-table-size=250000 --mysql-user=testdb --mysql-password=testpw --mysql-socket=/var/lib/mysql/mysql.sock prepare

SELECT TABLE_NAME, table_rows, data_length, 
round(((data_length) / 1024 / 1024),2) "Data in MB",
index_length,
round(((index_length) / 1024 / 1024),2) "Index in MB",
round(((data_length + index_length) / 1024 / 1024),2) "Size in MB"
FROM information_schema.TABLES WHERE table_schema = "crm_p2tk_dikdas2"
ORDER BY (data_length + index_length) DESC;