Once you run the command, you\u2019ll see something like the example mid-test status report below.<\/p>\n\n\n\n

[ 45s ] thds: 40 tps: 1514.26 qps: 18184.14 (r\/w\/o: 15153.62\/0.00\/3030.52) lat (ms,95%): 9.56 err\/s: 0.00 reconn\/s: 0.00\n[ 46s ] thds: 40 tps: 1500.93 qps: 18016.19 (r\/w\/o: 15014.32\/0.00\/3001.86) lat (ms,95%): 10.46 err\/s: 0.00 reconn\/s: 0.00\n[ 47s ] thds: 40 tps: 1445.11 qps: 17348.36 (r\/w\/o: 14458.14\/0.00\/2890.23) lat (ms,95%): 11.24 err\/s: 0.00 reconn\/s: 0.00\n[ 48s ] thds: 40 tps: 1520.69 qps: 18233.34 (r\/w\/o: 15191.95\/0.00\/3041.39) lat (ms,95%): 9.56 err\/s: 0.00 reconn\/s: 0.00\n[ 49s ] thds: 40 tps: 1492.26 qps: 17915.18 (r\/w\/o: 14930.65\/0.00\/2984.53) lat (ms,95%): 10.09 err\/s: 0.00 reconn\/s: 0.00<\/pre>\n\n\n\n
After the benchmark has finished, sysbench will print out the results like the example underneath.<\/p>\n\n\n\n
SQL statistics:\n    queries performed:\n        read:                            4811160\n        write:                           0\n        other:                           962232\n        total:                           5773392\n    transactions:                        481116 (1603.65 per sec.)\n    queries:                             5773392 (19243.77 per sec.)\n    ignored errors:                      0      (0.00 per sec.)\n    reconnects:                          0      (0.00 per sec.)\n\nThroughput:\n    events\/s (eps):                      1603.6474\n    time elapsed:                        300.0136s\n    total number of events:              481116\n\nLatency (ms):\n         min:                                    3.96\n         avg:                                   12.47\n         max:                                  274.15\n         95th percentile:                       22.69\n         sum:                              5998809.01\n\nThreads fairness:\n    events (avg\/stddev):           24055.8000\/111.79\n    execution time (avg\/stddev):   299.9405\/0.00<\/pre>\n\n\n\n
The main points to note from the results are transactions <\/strong>and queries per second<\/strong> as well as the latencies<\/strong> experienced during operations.<\/p>\n\n\n\n
Database queries<\/strong> are requests to access, manipulate or retrieve data from the database. In this test, queries are mainly read operations and the number of queries per second gives an idea of how quickly the database is able to process the queries.<\/p>\n\n\n\n
Database transactions<\/strong> symbolise a unit of work performed within a database management system manipulating the database independent of other transactions. The transactions per second show the average speed at which the database was able to complete transactions.<\/p>\n\n\n\n
Latency<\/strong> indicates how long it took for the database to respond to a query. Sysbench reports the response times in milliseconds and counts the average. However, the number to pay attention to is the 95th percentile. This gives the best representation of the consistency of the response latencies as 95% of all queries were completed within this time.<\/p>\n\n\n\n
Write test<\/h2>\n\n\n\n
Benchmarking your Managed Database for write performance is also an important part of evaluating the service. If your application\u2019s database usage is heavy on the right side, this might be the more important test for you.<\/p>\n\n\n\n
You can run a writing test by using the oltp_write_only<\/tt>\u00a0script with the following command.<\/p>\n\n\n\n
Note that you\u2019ll need to set the database details and credentials as before.<\/p>\n\n\n\n
sysbench oltp_write_only 
\n--mysql-host=hostname<\/tt>.db.upclouddatabases.com 
\n--mysql-user=upadmin --mysql-password=password<\/tt> 
\n--mysql-port=11550 --mysql-db=defaultdb 
\n--threads=40 --tables=40 --table-size=2000000 
\n--events=0 --time=300 --range_selects=off 
\n--delete_inserts=10 --index_updates=10 --non_index_updates=10 
\n--db-ps-mode=disable --report-interval=1 run<\/code><\/p>\n\n\n\n
Once finished, the results might look something like the example below.<\/p>\n\n\n\n
SQL statistics:\n    queries performed:\n        read:                            0\n        write:                           2714840\n        other:                           135742\n        total:                           2850582\n    transactions:                        67871  (226.21 per sec.)\n    queries:                             2850582 (9500.62 per sec.)\n    ignored errors:                      0      (0.00 per sec.)\n    reconnects:                          0      (0.00 per sec.)\n\nThroughput:\n    events\/s (eps):                      226.2052\n    time elapsed:                        300.0418s\n    total number of events:              67871\n\nLatency (ms):\n         min:                                   19.31\n         avg:                                   88.41\n         max:                                  894.92\n         95th percentile:                      158.63\n         sum:                              6000139.50\n\nThreads fairness:\n    events (avg\/stddev):           3393.5500\/20.56\n    execution time (avg\/stddev):   300.0070\/0.01<\/pre>\n\n\n\n
The results include again the transactions<\/strong> and queries per second<\/strong> as well as the latencies<\/strong> experienced during operations. These show how many inserts per second your Managed Database can handle and at what commit latency.<\/p>\n\n\n\n
Mixed read and write test<\/h2>\n\n\n\n
Lastly, if your application does both reads and writes to the database, you can test both simultaneously by setting the weights of each type of query.<\/p>\n\n\n\n
Use the oltp_read_write<\/tt> script along with the parameters below.<\/p>\n\n\n\n
sysbench oltp_read_write 
\n--mysql-host=hostname<\/tt>.db.upclouddatabases.com 
\n--mysql-user=upadmin --mysql-password=password<\/tt> 
\n--mysql-port=11550 --mysql-db=defaultdb 
\n--threads=40 --tables=40 --table-size=2000000 
\n--events=0 --time=300 --range_selects=off 
\n--db-ps-mode=disable --report-interval=1 run<\/code><\/p>\n\n\n\n
On completion, sysbench will print out the results.<\/p>\n\n\n\n
SQL statistics:\n    queries performed:\n        read:                            2567310\n        write:                           1026924\n        other:                           513462\n        total:                           4107696\n    transactions:                        256731 (855.72 per sec.)\n    queries:                             4107696 (13691.44 per sec.)\n    ignored errors:                      0      (0.00 per sec.)\n    reconnects:                          0      (0.00 per sec.)\n\nThroughput:\n    events\/s (eps):                      855.7150\n    time elapsed:                        300.0193s\n    total number of events:              256731\n\nLatency (ms):\n         min:                                    5.46\n         avg:                                   23.37\n         max:                                  741.52\n         95th percentile:                       41.10\n         sum:                              5999340.05\n\nThreads fairness:\n    events (avg\/stddev):           12836.5500\/74.19\n    execution time (avg\/stddev):   299.9670\/0.00<\/pre>\n\n\n\n
The results are similar to the previous runs with the exception of including both read and write queries. The transactions<\/strong> and queries per second<\/strong> as well as the latencies<\/strong> experienced during operations are still the main metrics. Check the ratio of reads and write to see if it\u2019s representative of your use case and adjust the parameters as needed.<\/p>\n\n\n\n
Cleaning up<\/h2>\n\n\n\n
When you are done benchmarking Managed Databases, you can remove any data generated by the test using the sysbench cleanup command. This can also be useful for resetting the database for another round of benchmarks with a different amount of data.<\/p>\n\n\n\n
sysbench oltp_read_only 
\n--mysql-host=hostname<\/tt>.db.upclouddatabases.com 
\n--mysql-user=upadmin --mysql-password=password<\/tt> 
\n--mysql-port=11550 --mysql-db=defaultdb 
\n--tables=40 cleanup<\/code><\/p>\n\n\n\n
Running the above command will have sysbench then connect to the database and drop the tables created by the prepare command or write tests.<\/p>\n\n\n\n
Summary<\/h2>\n\n\n\n
Sysbench is a versatile and dependable system benchmarking tool and it works great for benchmarking Managed Databases. With just a simple setup and a bit of time, you can gauge the performance of your Managed Database cluster.<\/p>\n\n\n\n
These instructions also work on managed databases offered by other cloud providers allowing easy comparison between services. If you\u2019d like to see how AWS RDS and DigitalOcean Databases stack up against UpCloud\u2019s Managed Databases, check out our blog where we put these database services through their paces<\/a>.<\/p>\n","protected":false},"featured_media":24965,"comment_status":"open","ping_status":"closed","template":"","community-category":[124,127],"class_list":["post-24964","tutorial","type-tutorial","status-publish","has-post-thumbnail","hentry","community-category-databases","community-category-managed-services"],"acf":[],"_links":{"self":[{"href":"https:\/\/studiogo.tech\/upcloudold\/wp-json\/wp\/v2\/tutorial\/24964","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/studiogo.tech\/upcloudold\/wp-json\/wp\/v2\/tutorial"}],"about":[{"href":"https:\/\/studiogo.tech\/upcloudold\/wp-json\/wp\/v2\/types\/tutorial"}],"replies":[{"embeddable":true,"href":"https:\/\/studiogo.tech\/upcloudold\/wp-json\/wp\/v2\/comments?post=24964"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/studiogo.tech\/upcloudold\/wp-json\/wp\/v2\/media\/24965"}],"wp:attachment":[{"href":"https:\/\/studiogo.tech\/upcloudold\/wp-json\/wp\/v2\/media?parent=24964"}],"wp:term":[{"taxonomy":"community-category","embeddable":true,"href":"https:\/\/studiogo.tech\/upcloudold\/wp-json\/wp\/v2\/community-category?post=24964"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}