sysbench installation | Different Test Cases

 Install sysbench and Test , get reports..

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

Installation 

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

[root@ip-172-31-62-204 ~]# curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash


[root@ip-172-31-62-204 ~]# yum search sysbench


[root@ip-172-31-62-204 ~]# yum install sysbench


[root@ip-172-31-62-204 ~]# which sysbench

/usr/bin/sysbench

[root@ip-172-31-62-204 ~]#


[root@ip-172-31-62-204 ~]# ls -ltr /usr/share/sysbench

-rwxr-xr-x. 1 root root  2114 Apr 24  2020 select_random_ranges.lua

-rwxr-xr-x. 1 root root  1915 Apr 24  2020 select_random_points.lua

-rwxr-xr-x. 1 root root  1436 Apr 24  2020 oltp_write_only.lua

-rwxr-xr-x. 1 root root  1123 Apr 24  2020 oltp_update_non_index.lua

-rwxr-xr-x. 1 root root  1114 Apr 24  2020 oltp_update_index.lua

-rwxr-xr-x. 1 root root  1820 Apr 24  2020 oltp_read_write.lua

-rwxr-xr-x. 1 root root  1645 Apr 24  2020 oltp_read_only.lua

-rwxr-xr-x. 1 root root  1261 Apr 24  2020 oltp_point_select.lua

-rwxr-xr-x. 1 root root  2411 Apr 24  2020 oltp_insert.lua

-rwxr-xr-x. 1 root root  1286 Apr 24  2020 oltp_delete.lua

-rw-r--r--. 1 root root 14369 Apr 24  2020 oltp_common.lua

-rwxr-xr-x. 1 root root  1448 Apr 24  2020 bulk_insert.lua

drwxr-xr-x. 4 root root    49 Sep 16 14:23 tests

[root@ip-172-31-62-204 ~]#


Pre-requisite

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

--> MariaDB 10.3 / MySQL  should be installed.

--> sysbench should be installed.

--> sysdb database has to be created for testing purpose.


To measure MySQL performance, we first create a test table with the test database: sysdb


[root@ip-172-31-62-204 ~]# mysql -u root -p

Enter password: root

Welcome to the MariaDB monitor.  Commands end with ; or \g.Your MariaDB connection id is 8 Server version: 10.5.9-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


MariaDB [(none)]> show databases;

+---------------------+

| Database            |

+---------------------+

| awsdms_control      |

| repo                |

| information_schema  |

| mysql               |

| percona             |

| performance_schema  |

| replication_rebuild |

| replication_test    |

| swiftmq1            |

| swiftmq2            |

| test                |

+---------------------+

12 rows in set (0.071 sec)


MariaDB [(none)]> create database sysdb;

Query OK, 1 row affected (0.000 sec)


MariaDB [(none)]> show databases;

+---------------------+

| Database            |

+---------------------+

| awsdms_control      |

| repo                |

| information_schema  |

| mysql               |

| percona             |

| performance_schema  |

| replication_rebuild |

| replication_test    |

| swiftmq1            |

| swiftmq2            |

| sysdb               |

| test                |

+---------------------+

12 rows in set (0.001 sec)


MariaDB [(none)]> use sysdb;

Database changed


MariaDB [sysdb]> show tables;

Empty set (0.000 sec)


MariaDB [sysdb]>


Commands to work on

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

Prepare:

This is the command we need to do for preparing the sysbench test tables. Prepare the sysbench testing with the command: prepare


[root@ip-172-31-62-204 sysbench]# pwd

/usr/share/sysbench


[root@ip-172-31-62-204 sysbench]# ls -ltr

-rwxr-xr-x. 1 root root  2114 Apr 24  2020 select_random_ranges.lua

-rwxr-xr-x. 1 root root  1915 Apr 24  2020 select_random_points.lua

-rwxr-xr-x. 1 root root  1436 Apr 24  2020 oltp_write_only.lua

-rwxr-xr-x. 1 root root  1123 Apr 24  2020 oltp_update_non_index.lua

-rwxr-xr-x. 1 root root  1114 Apr 24  2020 oltp_update_index.lua

-rwxr-xr-x. 1 root root  1820 Apr 24  2020 oltp_read_write.lua

-rwxr-xr-x. 1 root root  1645 Apr 24  2020 oltp_read_only.lua

-rwxr-xr-x. 1 root root  1261 Apr 24  2020 oltp_point_select.lua

-rwxr-xr-x. 1 root root  2411 Apr 24  2020 oltp_insert.lua

-rwxr-xr-x. 1 root root  1286 Apr 24  2020 oltp_delete.lua

-rw-r--r--. 1 root root 14369 Apr 24  2020 oltp_common.lua

-rwxr-xr-x. 1 root root  1448 Apr 24  2020 bulk_insert.lua

drwxr-xr-x. 4 root root    49 Sep 16 14:23 tests

[root@ip-172-31-62-204 sysbench]#


[root@ip-172-31-62-204 sysbench]# sysbench oltp_write_only.lua --mysql-host=localhost --mysql-socket=/home/maria/data/mysql.sock --mysql-port=3306 --mysql-user=root --mysql-password=root --mysql-db=sysdb --tables=5 --table_size=1000 prepare


sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)


Creating table 'sbtest1'...

Inserting 1000 records into 'sbtest1'

Creating a secondary index on 'sbtest1'...

Creating table 'sbtest2'...

Inserting 1000 records into 'sbtest2'

Creating a secondary index on 'sbtest2'...

Creating table 'sbtest3'...

Inserting 1000 records into 'sbtest3'

Creating a secondary index on 'sbtest3'...

Creating table 'sbtest4'...

Inserting 1000 records into 'sbtest4'

Creating a secondary index on 'sbtest4'...

Creating table 'sbtest5'...

Inserting 1000 records into 'sbtest5'

Creating a secondary index on 'sbtest5'...

[root@ip-172-31-62-204 sysbench]#


RUN: For oltp write only test

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

Error :-

[root@ip-172-31-62-204 sysbench]# sysbench oltp_write_only.lua --mysql-host=localhost --mysql-socket=/home/maria/data/mysql.sock --mysql-port=3306 --mysql-user=root --mysql-password=root --mysql-db=sysdb --threads=200 --time=10 --tables=5 --report-interval=1 run

sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)


Running the test with following options:

Number of threads: 200

Report intermediate results every 1 second(s)

Initializing random number generator from current time

Initializing worker threads...


FATAL: unable to connect to MySQL server on socket '/home/maria/data/mysql.sock', aborting...

FATAL: error 1040: Too many connections

FATAL: `thread_init' function failed: ./oltp_common.lua:349: connection creation failed

FATAL: unable to connect to MySQL server on socket '/home/maria/data/mysql.sock', aborting...

FATAL: error 1040: Too many connections

FATAL: `thread_init' function failed: ./oltp_common.lua:349: connection creation failed

FATAL: unable to connect to MySQL server on socket '/home/maria/data/mysql.sock', aborting...

FATAL: error 1040: Too many connections


If you get this error , pls increase the max_connections = 200 or 300 from 100 [ By default ]


[root@ip-172-31-62-204 sysbench]# pwd

/usr/share/sysbench


[root@ip-172-31-62-204 sysbench]# sysbench oltp_write_only.lua --mysql-host=localhost --mysql-socket=/home/maria/data/mysql.sock --mysql-port=3306 --mysql-user=root --mysql-password=root --mysql-db=sysdb --threads=200 --time=10 --tables=5 --report-interval=1 run


sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)


Running the test with following options:

Number of threads: 200

Report intermediate results every 1 second(s)

Initializing random number generator from current time

Initializing worker threads...

Threads started!


[ 1s ] thds: 200 tps: 0.00 qps: 198.85 (r/w/o: 0.00/0.00/198.85) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00

[ 2s ] thds: 200 tps: 16.06 qps: 5309.95 (r/w/o: 0.00/26.10/5283.85) lat (ms,95%): 1973.38 err/s: 1284.83 reconn/s: 0.00

[ 3s ] thds: 200 tps: 53.01 qps: 7261.73 (r/w/o: 0.00/83.01/7178.72) lat (ms,95%): 2880.27 err/s: 1696.17 reconn/s: 0.00

[ 4s ] thds: 200 tps: 82.00 qps: 7207.70 (r/w/o: 0.00/154.99/7052.71) lat (ms,95%): 3911.79 err/s: 1688.93 reconn/s: 0.00

[ 5s ] thds: 200 tps: 86.00 qps: 7273.00 (r/w/o: 0.00/142.00/7131.00) lat (ms,95%): 4768.67 err/s: 1691.00 reconn/s: 0.00

[ 6s ] thds: 200 tps: 121.00 qps: 7743.97 (r/w/o: 0.00/218.00/7525.97) lat (ms,95%): 5813.24 err/s: 1754.99 reconn/s: 0.00

[ 7s ] thds: 200 tps: 133.01 qps: 7902.34 (r/w/o: 0.00/340.01/7562.33) lat (ms,95%): 6360.91 err/s: 1800.08 reconn/s: 0.00

[ 8s ] thds: 200 tps: 150.00 qps: 8127.02 (r/w/o: 0.00/387.00/7740.02) lat (ms,95%): 7215.39 err/s: 1780.00 reconn/s: 0.00

[ 9s ] thds: 200 tps: 136.99 qps: 7598.69 (r/w/o: 0.00/360.99/7237.71) lat (ms,95%): 3773.42 err/s: 1698.93 reconn/s: 0.00

[ 10s ] thds: 200 tps: 166.00 qps: 7541.13 (r/w/o: 0.00/440.01/7101.12) lat (ms,95%): 2932.60 err/s: 1644.03 reconn/s: 0.00

SQL statistics:

    queries performed:

        read:                            0

        write:                           2605

        other:                           67959

        total:                           70564

    transactions:                        1144   (107.56 per sec.)

    queries:                             70564  (6634.20 per sec.)

    ignored errors:                      16037  (1507.75 per sec.)

    reconnects:                          0      (0.00 per sec.)


General statistics:

    total time:                          10.6345s

    total number of events:              1144


Latency (ms):

         min:                                    1.29

         avg:                                 1828.26

         max:                                10497.79

         95th percentile:                     5813.24

         sum:                              2091525.37


Threads fairness:

    events (avg/stddev):           5.7200/2.92

    execution time (avg/stddev):   10.4576/0.13


[root@ip-172-31-62-204 sysbench]#


For finding the read benchmark

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

[root@ip-172-31-62-204 sysbench]# sysbench oltp_read_only.lua --mysql-host=localhost --mysql-socket=/home/maria/data/mysql.sock --mysql-port=3306 --mysql-user=root --mysql-password=root --mysql-db=sysdb --threads=250 --tables=5 --time=10 --report-interval=1 --range_selects=off run


sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:

Number of threads: 250

Report intermediate results every 1 second(s)

Initializing random number generator from current time

Initializing worker threads...

Threads started!


[ 1s ] thds: 250 tps: 796.21 qps: 11623.89 (r/w/o: 9809.96/0.00/1813.92) lat (ms,95%): 272.27 err/s: 0.00 reconn/s: 0.00

[ 2s ] thds: 250 tps: 1001.38 qps: 11922.47 (r/w/o: 9910.71/0.00/2011.75) lat (ms,95%): 303.33 err/s: 0.00 reconn/s: 0.00

[ 3s ] thds: 250 tps: 1185.87 qps: 13390.50 (r/w/o: 11005.77/0.00/2384.73) lat (ms,95%): 248.83 err/s: 0.00 reconn/s: 0.00

[ 4s ] thds: 250 tps: 1013.03 qps: 12843.41 (r/w/o: 10813.35/0.00/2030.06) lat (ms,95%): 253.35 err/s: 0.00 reconn/s: 0.00

[ 5s ] thds: 250 tps: 1039.07 qps: 12321.88 (r/w/o: 10257.73/0.00/2064.15) lat (ms,95%): 262.64 err/s: 0.00 reconn/s: 0.00

[ 6s ] thds: 250 tps: 1114.97 qps: 12942.70 (r/w/o: 10710.75/0.00/2231.95) lat (ms,95%): 253.35 err/s: 0.00 reconn/s: 0.00

[ 7s ] thds: 250 tps: 1091.95 qps: 13454.37 (r/w/o: 11266.47/0.00/2187.90) lat (ms,95%): 248.83 err/s: 0.00 reconn/s: 0.00

[ 8s ] thds: 250 tps: 1070.02 qps: 12706.25 (r/w/o: 10567.21/0.00/2139.04) lat (ms,95%): 253.35 err/s: 0.00 reconn/s: 0.00

[ 9s ] thds: 250 tps: 1055.07 qps: 12666.88 (r/w/o: 10550.73/0.00/2116.15) lat (ms,95%): 253.35 err/s: 0.00 reconn/s: 0.00

[ 10s ] thds: 250 tps: 1020.97 qps: 12176.65 (r/w/o: 10139.71/0.00/2036.94) lat (ms,95%): 267.41 err/s: 0.00 reconn/s: 0.00

SQL statistics:

    queries performed:

        read:                            106400

        write:                           0

        other:                           21280

        total:                           127680

    transactions:                        10640  (1039.73 per sec.)

    queries:                             127680 (12476.76 per sec.)

    ignored errors:                      0      (0.00 per sec.)

    reconnects:                          0      (0.00 per sec.)


General statistics:

    total time:                          10.2315s

    total number of events:              10640


Latency (ms):

         min:                                  196.81

         avg:                                  238.25

         max:                                 1319.50

         95th percentile:                      272.27

         sum:                              2534937.58


Threads fairness:

    events (avg/stddev):           42.5600/0.58

    execution time (avg/stddev):   10.1398/0.06


[root@ip-172-31-62-204 sysbench]#


30 min run test

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

Test Case - 1  :- read / write 

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

In order to do this we have to increase max_connections = 1000 in DB level and do this test case.

And also create 100 tables by using this command 

[ sysbench oltp_write_only.lua --mysql-host=localhost --mysql-socket=/home/maria/data/mysql.sock --mysql-port=3306 --mysql-user=root --mysql-password=root --mysql-db=sysdb --tables=1000 --table_size=1000 prepare 


sysbench oltp_write_only.lua --mysql-host=localhost --mysql-socket=/home/maria/data/mysql.sock --mysql-port=3306 --mysql-user=root --mysql-password=root --mysql-db=sysdb --threads=200 --time=10 --tables=100 --report-interval=1 run

]


[root@ip-172-31-62-204 sysbench]# pwd

/usr/share/sysbench


[root@ip-172-31-62-204 sysbench]# sysbench oltp_write_only.lua --mysql-host=localhost --mysql-socket=/home/maria/data/mysql.sock --mysql-port=3306 --mysql-user=root --mysql-password=root --mysql-db=sysdb --db-driver=mysql  --threads=1 --time=10 --tables=2 --report-interval=1 run

sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)


Running the test with following options:

Number of threads: 1

Report intermediate results every 1 second(s)

Initializing random number generator from current time

Initializing worker threads...

Threads started!


[ 1s ] thds: 1 tps: 813.25 qps: 4884.48 (r/w/o: 0.00/2288.07/2596.41) lat (ms,95%): 1.52 err/s: 0.00 reconn/s: 0.00

[ 2s ] thds: 1 tps: 802.21 qps: 4813.24 (r/w/o: 0.00/2640.68/2172.56) lat (ms,95%): 1.52 err/s: 0.00 reconn/s: 0.00

[ 3s ] thds: 1 tps: 711.98 qps: 4271.89 (r/w/o: 0.00/2385.94/1885.95) lat (ms,95%): 1.89 err/s: 0.00 reconn/s: 0.00

[ 4s ] thds: 1 tps: 806.97 qps: 4839.84 (r/w/o: 0.00/2740.91/2098.93) lat (ms,95%): 1.50 err/s: 0.00 reconn/s: 0.00

[ 5s ] thds: 1 tps: 828.01 qps: 4970.08 (r/w/o: 0.00/2795.05/2175.04) lat (ms,95%): 1.47 err/s: 0.00 reconn/s: 0.00

[ 6s ] thds: 1 tps: 780.02 qps: 4680.09 (r/w/o: 0.00/2666.05/2014.04) lat (ms,95%): 1.70 err/s: 0.00 reconn/s: 0.00

[ 7s ] thds: 1 tps: 821.00 qps: 4925.01 (r/w/o: 0.00/2793.00/2132.00) lat (ms,95%): 1.58 err/s: 0.00 reconn/s: 0.00

[ 8s ] thds: 1 tps: 807.00 qps: 4842.99 (r/w/o: 0.00/2764.99/2077.99) lat (ms,95%): 1.52 err/s: 0.00 reconn/s: 0.00

[ 9s ] thds: 1 tps: 805.01 qps: 4830.09 (r/w/o: 0.00/2812.05/2018.04) lat (ms,95%): 1.55 err/s: 0.00 reconn/s: 0.00

[ 10s ] thds: 1 tps: 801.99 qps: 4811.95 (r/w/o: 0.00/2771.97/2039.98) lat (ms,95%): 1.52 err/s: 0.00 reconn/s: 0.00

SQL statistics:

    queries performed:

        read:                            0

        write:                           26663

        other:                           21217

        total:                           47880

    transactions:                        7980   (797.64 per sec.)

    queries:                             47880  (4785.86 per sec.)

    ignored errors:                      0      (0.00 per sec.)

    reconnects:                          0      (0.00 per sec.)


General statistics:

    total time:                          10.0026s

    total number of events:              7980


Latency (ms):

         min:                                    0.91

         avg:                                    1.25

         max:                                   10.48

         95th percentile:                        1.58

         sum:                                 9976.33


Threads fairness:

    events (avg/stddev):           7980.0000/0.00

    execution time (avg/stddev):   9.9763/0.00


[root@ip-172-31-62-204 sysbench]#


Script to Run :- 

nohup sysbench /usr/share/sysbench/oltp_read_write.lua --mysql-host=localhost --mysql-socket=/home/maria/data/mysql.sock --mysql-port=3306 --mysql-user=root --mysql-password=root --mysql-db=sysdb --threads=1000 --time=1800 --tables=100 --report-interval=60 run &


Threads :  1000

Test Duration : 30 Min 


Due to RAM issue in my system , i was using below command to run 30 min ..


Threads : 2

Test Duration : 30 Min

Max_connections = 2 Defined in DB level ..

Tables = 10

I have started at 1.35 PM 


[root@ip-172-31-62-204 sysbench]# pwd

/usr/share/sysbench


[root@ip-172-31-62-204 sysbench]# nohup sysbench /usr/share/sysbench/oltp_read_write.lua --mysql-host=localhost --mysql-socket=/home/maria/data/mysql.sock --mysql-port=3306 --mysql-user=root --mysql-password=root --mysql-db=sysdb --threads=2 --time=1800 --tables=10 --report-interval=60 run &

[1] 3414


[root@ip-172-31-62-204 sysbench]# nohup: ignoring input and appending output to 'nohup.out'


[root@ip-172-31-62-204 sysbench]# tail -f nohup.out

Running the test with following options:

Number of threads: 2

Report intermediate results every 60 second(s)

Initializing random number generator from current time

Initializing worker threads...

Threads started!

[ 60s ] thds: 2 tps: 425.14 qps: 8504.41 (r/w/o: 5953.30/1406.72/1144.39) lat (ms,95%): 5.88 err/s: 0.07 reconn/s: 0.00

[ 120s ] thds: 2 tps: 396.18 qps: 7924.07 (r/w/o: 5546.81/1385.77/991.49) lat (ms,95%): 6.32 err/s: 0.02 reconn/s: 0.00

[ 180s ] thds: 2 tps: 385.40 qps: 7707.87 (r/w/o: 5395.55/1376.75/935.57) lat (ms,95%): 6.43 err/s: 0.00 reconn/s: 0.00

[ 240s ] thds: 2 tps: 377.96 qps: 7559.32 (r/w/o: 5291.53/1374.11/893.68) lat (ms,95%): 6.55 err/s: 0.00 reconn/s: 0.00

[ 300s ] thds: 2 tps: 373.45 qps: 7468.96 (r/w/o: 5228.24/1376.92/863.79) lat (ms,95%): 6.55 err/s: 0.00 reconn/s: 0.00

[ 360s ] thds: 2 tps: 370.25 qps: 7405.04 (r/w/o: 5183.54/1381.46/840.03) lat (ms,95%): 6.55 err/s: 0.00 reconn/s: 0.00

[ 420s ] thds: 2 tps: 368.75 qps: 7374.92 (r/w/o: 5162.34/1389.64/822.94) lat (ms,95%): 6.55 err/s: 0.00 reconn/s: 0.00

[ 480s ] thds: 2 tps: 357.28 qps: 7145.67 (r/w/o: 5001.97/1358.87/784.83) lat (ms,95%): 6.79 err/s: 0.00 reconn/s: 0.00

[ 540s ] thds: 2 tps: 353.77 qps: 7075.43 (r/w/o: 4952.89/1352.00/770.53) lat (ms,95%): 6.91 err/s: 0.00 reconn/s: 0.00

[ 600s ] thds: 2 tps: 362.32 qps: 7246.26 (r/w/o: 5072.29/1392.75/781.22) lat (ms,95%): 6.55 err/s: 0.00 reconn/s: 0.00

[ 660s ] thds: 2 tps: 360.65 qps: 7213.00 (r/w/o: 5049.17/1392.78/771.05) lat (ms,95%): 6.67 err/s: 0.00 reconn/s: 0.00

[ 720s ] thds: 2 tps: 359.47 qps: 7189.60 (r/w/o: 5032.66/1392.39/764.55) lat (ms,95%): 6.67 err/s: 0.00 reconn/s: 0.00

[ 780s ] thds: 2 tps: 352.23 qps: 7044.61 (r/w/o: 4931.26/1369.53/743.82) lat (ms,95%): 6.79 err/s: 0.00 reconn/s: 0.00

[ 840s ] thds: 2 tps: 352.43 qps: 7048.42 (r/w/o: 4933.90/1374.22/740.30) lat (ms,95%): 6.79 err/s: 0.00 reconn/s: 0.00

[ 900s ] thds: 2 tps: 350.08 qps: 7001.65 (r/w/o: 4901.10/1367.90/732.65) lat (ms,95%): 6.91 err/s: 0.00 reconn/s: 0.00

[ 960s ] thds: 2 tps: 351.33 qps: 7026.98 (r/w/o: 4918.90/1375.68/732.40) lat (ms,95%): 6.79 err/s: 0.00 reconn/s: 0.00

[ 1020s ] thds: 2 tps: 342.42 qps: 6848.22 (r/w/o: 4793.84/1343.52/710.87) lat (ms,95%): 7.17 err/s: 0.00 reconn/s: 0.00

[ 1080s ] thds: 2 tps: 350.00 qps: 6999.80 (r/w/o: 4899.81/1374.90/725.08) lat (ms,95%): 6.79 err/s: 0.00 reconn/s: 0.00

[ 1140s ] thds: 2 tps: 351.53 qps: 7030.79 (r/w/o: 4921.60/1382.66/726.53) lat (ms,95%): 6.91 err/s: 0.00 reconn/s: 0.00

[ 1200s ] thds: 2 tps: 355.93 qps: 7118.54 (r/w/o: 4982.93/1402.45/733.15) lat (ms,95%): 6.67 err/s: 0.00 reconn/s: 0.00

[ 1260s ] thds: 2 tps: 345.80 qps: 6916.09 (r/w/o: 4841.30/1363.21/711.58) lat (ms,95%): 7.04 err/s: 0.00 reconn/s: 0.00

[ 1320s ] thds: 2 tps: 346.70 qps: 6934.24 (r/w/o: 4853.89/1368.62/711.72) lat (ms,95%): 6.91 err/s: 0.00 reconn/s: 0.00

[ 1380s ] thds: 2 tps: 348.56 qps: 6971.15 (r/w/o: 4879.89/1375.89/715.36) lat (ms,95%): 7.04 err/s: 0.00 reconn/s: 0.00

[ 1440s ] thds: 2 tps: 350.50 qps: 7009.90 (r/w/o: 4906.90/1384.99/718.02) lat (ms,95%): 6.79 err/s: 0.00 reconn/s: 0.00

[ 1500s ] thds: 2 tps: 350.97 qps: 7019.46 (r/w/o: 4913.65/1387.68/718.12) lat (ms,95%): 6.79 err/s: 0.00 reconn/s: 0.00

[ 1560s ] thds: 2 tps: 353.47 qps: 7069.04 (r/w/o: 4948.24/1398.55/722.25) lat (ms,95%): 6.79 err/s: 0.00 reconn/s: 0.00

[ 1620s ] thds: 2 tps: 353.73 qps: 7074.84 (r/w/o: 4952.44/1400.98/721.42) lat (ms,95%): 6.79 err/s: 0.00 reconn/s: 0.00

[ 1680s ] thds: 2 tps: 347.38 qps: 6947.73 (r/w/o: 4863.44/1375.86/708.43) lat (ms,95%): 7.04 err/s: 0.00 reconn/s: 0.00

[ 1740s ] thds: 2 tps: 351.48 qps: 7029.75 (r/w/o: 4920.84/1392.96/715.95) lat (ms,95%): 6.91 err/s: 0.00 reconn/s: 0.00

[ 1800s ] thds: 2 tps: 354.35 qps: 7086.95 (r/w/o: 4960.79/1405.68/720.48) lat (ms,95%): 6.67 err/s: 0.00 reconn/s: 0.00

SQL statistics:

    queries performed:

        read:                            9071720

        write:                           2485534

        other:                           1402336

        total:                           12959590

    transactions:                        647975 (359.98 per sec.)

    queries:                             12959590 (7199.71 per sec.)

    ignored errors:                      5      (0.00 per sec.)

    reconnects:                          0      (0.00 per sec.)


General statistics:

    total time:                          1800.0130s

    total number of events:              647975


Latency (ms):

         min:                                    2.25

         avg:                                    5.55

         max:                                  166.11

         95th percentile:                        6.79

         sum:                              3597757.92


Threads fairness:

    events (avg/stddev):           323987.5000/10.50

    execution time (avg/stddev):   1798.8790/0.00



Test Case - 2  :- read / write :-  30 min run test

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

Threads = 250 * 4

Total Duraiton = 30 Min


sysbench /usr/share/sysbench/oltp_read_write.lua --mysql-host=localhost --mysql-port=3306  --mysql-socket=/home/maria/data/mysql.sock --mysql-user=root --mysql-password=root --mysql-db=sysdb --threads=250 --time=1800 --tables=100 --report-interval=60 run


--- Above session running 4 times in parallel


Test Case - 3  :- read / write :-  30 min run test

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

Threads = 500 * 3

Total Duraiton = 30 Min


sysbench /usr/share/sysbench/oltp_read_write.lua --mysql-host=localhost --mysql-port=3306 --mysql-socket=/home/maria/data/mysql.sock --mysql-user=root --mysql-password=root --mysql-db=sysdb --threads=500 --time=1800 --tables=100 --report-interval=60 run


Test Case - 4  :- read / write :-  30 min run test

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

Threads = 1000

Total Duraiton = 30 Min


sysbench /usr/share/sysbench/oltp_read_write.lua --mysql-host=localhost --mysql-port=3306 --mysql-socket=/home/maria/data/mysql.sock --mysql-user=root --mysql-password=root --mysql-db=sysdb --threads=1000 --time=1800 --tables=100 --report-interval=60 run


Read benchmark with range_selects=off

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

READONLY with range_selects=off

By default, the range_select is ON in sysbench, turning off might give a change in the report. (with range_selects=off)


sysbench ./oltp_read_only.lua --mysql-host=localhost --mysql-socket= --mysql-port=3306 --mysql-socket=/home/maria/data/mysql.sock  --mysql-user=root --mysql-password=root --mysql-db=sysdb --threads=250 --tables=10 --range_selects=off --report_interval=5 --time=120 run


Here i was reducing the tables and Threads due to RAM issue.


[root@ip-172-31-62-204 sysbench]# pwd

/usr/share/sysbench


[root@ip-172-31-62-204 sysbench]# sysbench ./oltp_read_only.lua --mysql-host=localhost --mysql-socket= --mysql-port=3306 --mysql-socket=/home/maria/data/my   sql.sock  --mysql-user=root --mysql-password=root --mysql-db=sysdb --threads=5 --tables=5 --range_selects=off --report_interval=5 --time=120 run


sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:

Number of threads: 5

Report intermediate results every 5 second(s)

Initializing random number generator from current time

Initializing worker threads...

Threads started!

[ 5s ] thds: 5 tps: 1216.55 qps: 14600.59 (r/w/o: 12166.69/0.00/2433.90) lat (ms,95%): 4.33 err/s: 0.00 reconn/s: 0.00

[ 10s ] thds: 5 tps: 1214.33 qps: 14575.82 (r/w/o: 12146.95/0.00/2428.87) lat (ms,95%): 4.33 err/s: 0.00 reconn/s: 0.00

[ 15s ] thds: 5 tps: 1222.02 qps: 14663.26 (r/w/o: 12219.21/0.00/2444.04) lat (ms,95%): 4.25 err/s: 0.00 reconn/s: 0.00

[ 20s ] thds: 5 tps: 1217.99 qps: 14614.93 (r/w/o: 12179.34/0.00/2435.59) lat (ms,95%): 4.33 err/s: 0.00 reconn/s: 0.00

[ 25s ] thds: 5 tps: 1216.21 qps: 14597.37 (r/w/o: 12164.54/0.00/2432.83) lat (ms,95%): 4.33 err/s: 0.00 reconn/s: 0.00

[ 30s ] thds: 5 tps: 1238.59 qps: 14863.87 (r/w/o: 12386.89/0.00/2476.98) lat (ms,95%): 4.25 err/s: 0.00 reconn/s: 0.00

[ 35s ] thds: 5 tps: 1226.79 qps: 14718.47 (r/w/o: 12264.69/0.00/2453.78) lat (ms,95%): 4.25 err/s: 0.00 reconn/s: 0.00

[ 40s ] thds: 5 tps: 1217.02 qps: 14606.84 (r/w/o: 12172.80/0.00/2434.04) lat (ms,95%): 4.25 err/s: 0.00 reconn/s: 0.00

[ 45s ] thds: 5 tps: 1234.19 qps: 14808.47 (r/w/o: 12340.09/0.00/2468.38) lat (ms,95%): 4.25 err/s: 0.00 reconn/s: 0.00

[ 50s ] thds: 5 tps: 1206.79 qps: 14483.12 (r/w/o: 12069.53/0.00/2413.59) lat (ms,95%): 4.33 err/s: 0.00 reconn/s: 0.00

[ 55s ] thds: 5 tps: 1207.61 qps: 14491.75 (r/w/o: 12076.53/0.00/2415.23) lat (ms,95%): 4.33 err/s: 0.00 reconn/s: 0.00

[ 60s ] thds: 5 tps: 1208.80 qps: 14500.76 (r/w/o: 12083.37/0.00/2417.39) lat (ms,95%): 4.33 err/s: 0.00 reconn/s: 0.00

[ 65s ] thds: 5 tps: 1209.80 qps: 14521.59 (r/w/o: 12101.79/0.00/2419.80) lat (ms,95%): 4.33 err/s: 0.00 reconn/s: 0.00

[ 70s ] thds: 5 tps: 1212.80 qps: 14552.57 (r/w/o: 12126.97/0.00/2425.59) lat (ms,95%): 4.33 err/s: 0.00 reconn/s: 0.00

[ 75s ] thds: 5 tps: 1190.80 qps: 14287.99 (r/w/o: 11906.59/0.00/2381.40) lat (ms,95%): 4.41 err/s: 0.00 reconn/s: 0.00

[ 80s ] thds: 5 tps: 1202.41 qps: 14430.87 (r/w/o: 12025.86/0.00/2405.01) lat (ms,95%): 4.33 err/s: 0.00 reconn/s: 0.00

[ 85s ] thds: 5 tps: 1223.00 qps: 14675.04 (r/w/o: 12229.23/0.00/2445.81) lat (ms,95%): 4.25 err/s: 0.00 reconn/s: 0.00

[ 90s ] thds: 5 tps: 1232.00 qps: 14783.66 (r/w/o: 12319.65/0.00/2464.01) lat (ms,95%): 4.25 err/s: 0.00 reconn/s: 0.00

[ 95s ] thds: 5 tps: 1217.78 qps: 14614.21 (r/w/o: 12178.44/0.00/2435.77) lat (ms,95%): 4.33 err/s: 0.00 reconn/s: 0.00

[ 100s ] thds: 5 tps: 1232.01 qps: 14783.07 (r/w/o: 12319.06/0.00/2464.01) lat (ms,95%): 4.25 err/s: 0.00 reconn/s: 0.00

[ 105s ] thds: 5 tps: 1214.20 qps: 14572.00 (r/w/o: 12143.60/0.00/2428.40) lat (ms,95%): 4.33 err/s: 0.00 reconn/s: 0.00

[ 110s ] thds: 5 tps: 1228.20 qps: 14739.19 (r/w/o: 12282.79/0.00/2456.40) lat (ms,95%): 4.25 err/s: 0.00 reconn/s: 0.00

[ 115s ] thds: 5 tps: 1211.40 qps: 14534.95 (r/w/o: 12112.16/0.00/2422.79) lat (ms,95%): 4.33 err/s: 0.00 reconn/s: 0.00

[ 120s ] thds: 5 tps: 1237.61 qps: 14855.66 (r/w/o: 12380.45/0.00/2475.21) lat (ms,95%): 4.25 err/s: 0.00 reconn/s: 0.00

SQL statistics:

    queries performed:

        read:                            1462020

        write:                           0

        other:                           292404

        total:                           1754424

    transactions:                        146202 (1218.28 per sec.)

    queries:                             1754424 (14619.37 per sec.)

    ignored errors:                      0      (0.00 per sec.)

    reconnects:                          0      (0.00 per sec.)

General statistics:

    total time:                          120.0049s

    total number of events:              146202

Latency (ms):

         min:                                    0.96

         avg:                                    4.10

         max:                                   61.49

         95th percentile:                        4.33

         sum:                               599583.32

Threads fairness:

    events (avg/stddev):           29240.4000/49.01

    execution time (avg/stddev):   119.9167/0.00


[root@ip-172-31-62-204 sysbench]#


CLEANUP

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

cleanup will clear all the tables from sysdb schema, in case if you want to clear out the test and start with the new one.


[root@ip-172-31-62-204 sysbench]# pwd

/usr/share/sysbench


[root@ip-172-31-62-204 sysbench]# sysbench ./oltp_write_only.lua --mysql-host=localhost --mysql-socket=/home/maria/data/mysql.sock --mysql-port=3306 --mysql-user=root --mysql-password=root --mysql-db=sysdb --threads=200 --time=10 --tables=100 --report-interval=1 cleanup

sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)


Dropping table 'sbtest1'...

Dropping table 'sbtest2'...

Dropping table 'sbtest3'...

Dropping table 'sbtest4'...

Dropping table 'sbtest5'...

Dropping table 'sbtest6'...

Dropping table 'sbtest7'...

Dropping table 'sbtest8'...

Dropping table 'sbtest9'...

...

...

Dropping table 'sbtest91'...

Dropping table 'sbtest92'...

Dropping table 'sbtest93'...

Dropping table 'sbtest94'...

Dropping table 'sbtest95'...

Dropping table 'sbtest96'...

Dropping table 'sbtest97'...

Dropping table 'sbtest98'...

Dropping table 'sbtest99'...

Dropping table 'sbtest100'...

[root@ip-172-31-62-204 sysbench]#

Thanks for Visiting my blog....

Comments

Popular posts from this blog

PostgreSQL Database Version 13.4 To MySQL Database Version 8.0.20 Migration by using SQLines Tool

RDS MySQL / MariaDB SSL Connection by using Workbench and command line

Install Mydumper and Myloader Software for Backup of MySQL / MariaDB Databases and Usage of commands