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 :-

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

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:

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:

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

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:

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:

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:

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....

Search This Blog

MySQL / MariaDB DBA

sysbench installation | Different Test Cases

Comments

Post a Comment

Popular posts from this blog

MariaDB Galera Cluster Configuration and Validation with Different Types of testings

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

MariaDB Database Multi-instance implementation in single machine