{"id":4320,"date":"2016-08-21T15:47:56","date_gmt":"2016-08-21T14:47:56","guid":{"rendered":"https:\/\/stevepedwards.today\/DebianAdmin\/?p=4320"},"modified":"2016-08-21T15:47:56","modified_gmt":"2016-08-21T14:47:56","slug":"sysbench-simulated-database-testing","status":"publish","type":"post","link":"https:\/\/stevepedwards.today\/DebianAdmin\/sysbench-simulated-database-testing\/","title":{"rendered":"Sysbench – Simulated Database Testing"},"content":{"rendered":"
<\/div>\n

<\/path><\/svg><\/i> \"Loading\"<\/p>\n

<\/div>\n

man sysbench<\/span><\/p>\n

oltp<\/span>
\n This test mode was written to benchmark a real database performance. At the prepare<\/span>
\n stage the following table is created in the specified database (sbtest by default):<\/span><\/p>\n

Example usage: (THESE DON'T WORK AS IS - A DB NEEDS CREATING FIRST - SEE BELOW)<\/strong><\/span><\/span><\/p>\n

$ sysbench --test=oltp --mysql-table-type=myisam --oltp-table-size=1000000 --mysql-socket=\/tmp\/mysql.sock prepare<\/span><\/strong>
\n $ sysbench --num-threads=16 --max-requests=100000 --test=oltp --oltp-table-size=1000000 --mysql-socket=\/tmp\/mysql.sock --oltp-read-only run<\/span><\/strong><\/p>\n

The first command will create a MyISAM table 'sbtest' in a database 'sbtest' on a MySQL<\/span>
\n server using \/tmp\/mysql.sock socket, then fill this table with 1M records. The second<\/span>
\n command will run the actual benchmark with 16 client threads, limiting the total number<\/span>
\n of request by 100,000.<\/span><\/p>\n

--mysql-db<\/span><\/strong>
\n MySQL database name. Note SysBench will not automatically create this database. You\u00a0<\/span><\/strong>should create it manually and grant the appropriate privileges to a user which will\u00a0<\/span><\/strong>be used to access the test table. (default: sbtest)<\/span><\/strong><\/p>\n

AUTHOR<\/span>
\n Alexey Kopytov <kaamos@users.sourceforge.net><\/span>
\n Author.<\/span><\/p>\n

First you have to install mysql:<\/p>\n

sudo apt-get install mysql-server-5.5<\/span><\/p>\n

Now create a db and grant all privileges to a user. Log in to mysql as root:<\/p>\n

su root<\/span><\/p>\n

mysql -u root -p<\/span><\/p>\n

mysql> show databases ;<\/span><\/span>
\n+------+<\/span>
\n| Database |<\/span>
\n+------+<\/span>
\n| information_schema |<\/span>
\n| mysql |<\/span>
\n| performance_schema |<\/span>
\n+------+<\/span>
\n4 rows in set (0.06 sec)<\/span><\/p>\n

create database sbtest ;<\/span><\/p>\n

mysql> show databases ;<\/span><\/span>
\n+------+<\/span>
\n| Database |<\/span>
\n+------+<\/span>
\n| information_schema |<\/span>
\n| mysql |<\/span>
\n| performance_schema |<\/span>
\n| sbtest |<\/span>
\n+------+<\/span>
\n4 rows in set (0.00 sec)<\/span><\/p>\n

grant all privileges on *.* to 'root'@'localhost' ;<\/span><\/p>\n

mysql> quit<\/span><\/span>
\nBye<\/span><\/p>\n

As the first and second example lines above do not work in Mint, this is no longer a DB \"simulation\" but tests a real DB you had to create above. I used my 32bit slowest PC so just 3 for comparison. This DB test may be useful if looking to transfer a DB to another server and get an idea how well it will perform compared the the old one before you move it etc...<\/p>\n

localhost stevee #<\/span><\/strong> sysbench --test=oltp --mysql-user=root --mysql-password=xxxxxx --mysql-db=sbtest --oltp-table-size=1000000 prepare<\/span>
\nsysbench 0.4.12: multi-threaded system evaluation benchmark<\/span><\/p>\n

No DB drivers specified, using mysql<\/span>
\nCreating table 'sbtest'...<\/span>
\nCreating 1000000 records in table 'sbtest'...<\/span><\/p>\n

sysbench --num-threads=16 --max-requests=100000 --test=oltp --mysql-user=root --mysql-password=xxxxx --mysql-db=sbtest --oltp-table-size=1000000 --oltp-read-only run<\/span><\/p>\n

sysbench 0.4.12: multi-threaded system evaluation benchmark<\/span><\/p>\n

No DB drivers specified, using mysql<\/span>
\n Running the test with following options:<\/span>
\n Number of threads: 16<\/span><\/p>\n

Doing OLTP test.<\/span>
\nRunning mixed OLTP test<\/span>
\nDoing read-only test<\/span>
\nUsing Special distribution (12 iterations, 1 pct of values are returned in 75 pct cases)<\/span>
\nUsing \"BEGIN\" for starting transactions<\/span>
\nUsing auto_inc on the id column<\/span>
\nMaximum number of requests for OLTP test is limited to 100000<\/span>
\nThreads started!<\/span><\/p>\n

OLTP test statistics:<\/span>
\n queries performed:<\/span>
\n read: 1400000<\/span>
\n write: 0<\/span>
\n other: 200000<\/span>
\n total: 1600000<\/span>
\n transactions: 100000 (183.14 per sec.)<\/span>
\n deadlocks: 0 (0.00 per sec.)<\/span>
\n read\/write requests: 1400000 (2563.96 per sec.)<\/strong><\/span>
\n other operations: 200000 (366.28 per sec.<\/strong>)<\/span><\/p>\n

Test execution summary:<\/span>
\n total time: 546.0298s<\/strong><\/span>
\n total number of events: 100000<\/span>
\n total time taken by event execution: 8735.0764<\/span>
\n per-request statistics:<\/span>
\n min: 3.09ms<\/span>
\n avg: 87.35ms<\/span>
\n max: 456.36ms<\/span>
\n approx. 95 percentile: 123.10ms<\/span><\/p>\n

Threads fairness:<\/span>
\n events (avg\/stddev): 6250.0000\/24.90<\/span>
\n execution time (avg\/stddev): 545.9423\/0.02<\/span><\/p>\n

Comparison 1, the HP64:<\/p>\n

No DB drivers specified, using mysql<\/span>
\nRunning the test with following options:<\/span>
\nNumber of threads: 16<\/span><\/p>\n

Doing OLTP test.<\/span>
\nRunning mixed OLTP test<\/span>
\nDoing read-only test<\/span>
\nUsing Special distribution (12 iterations, 1 pct of values are returned in 75 pct cases)<\/span>
\nUsing \"BEGIN\" for starting transactions<\/span>
\nUsing auto_inc on the id column<\/span>
\nMaximum number of requests for OLTP test is limited to 100000<\/span>
\nThreads started!<\/span>
\nDone.<\/span><\/p>\n

OLTP test statistics:<\/span>
\n queries performed:<\/span>
\n read: 1400000<\/span>
\n write: 0<\/span>
\n other: 200000<\/span>
\n total: 1600000<\/span>
\n transactions: 100000 (431.29 per sec.)<\/span>
\n deadlocks: 0 (0.00 per sec.)<\/span>
\n read\/write requests: 1400000 (6038.00 per sec.)<\/strong><\/span>
\n other operations: 200000 (862.57 per sec.)<\/strong><\/span><\/p>\n

Test execution summary:<\/span>
\n total time: 231.8647s<\/strong><\/span>
\n total number of events: 100000<\/span>
\n total time taken by event execution: 3708.6049<\/span>
\n per-request statistics:<\/span>
\n min: 3.19ms<\/span>
\n avg: 37.09ms<\/span>
\n max: 131.28ms<\/span>
\n approx. 95 percentile: 49.82ms<\/span><\/p>\n

Threads fairness:<\/span>
\n events (avg\/stddev): 6250.0000\/66.88<\/span>
\n execution time (avg\/stddev): 231.7878\/0.01<\/span><\/p>\n

Comparison 2, the Dell64:<\/p>\n

Maximum number of requests for OLTP test is limited to 100000<\/span>
\nThreads started!<\/span>
\nDone.<\/span><\/p>\n

OLTP test statistics:<\/span>
\n queries performed:<\/span>
\n read: 1400000<\/span>
\n write: 0<\/span>
\n other: 200000<\/span>
\n total: 1600000<\/span>
\n transactions: 100000 (589.12 per sec.)<\/span>
\n deadlocks: 0 (0.00 per sec.)<\/span>
\n read\/write requests: 1400000 (8247.70 per sec<\/strong>.)<\/span>
\n other operations: 200000 (1178.24 per sec.)<\/span><\/p>\n

Test execution summary:<\/span>
\n total time: 169.7443s<\/strong><\/span>
\n total number of events: 100000<\/span>
\n total time taken by event execution: 2714.8378<\/span>
\n per-request statistics:<\/span>
\n min: 1.76ms<\/span>
\n avg: 27.15ms<\/span>
\n max: 107.29ms<\/span>
\n approx. 95 percentile: 38.89ms<\/span><\/p>\n

Threads fairness:<\/span>
\n events (avg\/stddev): 6250.0000\/59.86<\/span>
\n execution time (avg\/stddev): 169.6774\/0.00<\/span><\/p>\n

Comparison 3, the Intel HP64:<\/p>\n

OLTP test statistics:<\/span>
\n queries performed:<\/span>
\n read: 1400000<\/span>
\n write: 0<\/span>
\n other: 200000<\/span>
\n total: 1600000<\/span>
\n transactions: 100000 (272.75 per sec.)<\/span>
\n deadlocks: 0 (0.00 per sec.)<\/span>
\n read\/write requests: 1400000 (3818.54<\/strong> per sec.)<\/span>
\n other operations: 200000 (545.51 per sec.)<\/span><\/p>\n

Test execution summary:<\/span>
\n total time: 366.6323s<\/strong><\/span>
\n total number of events: 100000<\/span>
\n total time taken by event execution: 5865.1829<\/span>
\n per-request statistics:<\/span>
\n min: 1.49ms<\/span>
\n avg: 58.65ms<\/span>
\n max: 209.58ms<\/span>
\n approx. 95 percentile: 94.70ms<\/span><\/p>\n

Threads fairness:<\/span>
\n events (avg\/stddev): 6250.0000\/77.06<\/span>
\n execution time (avg\/stddev): 366.5739\/0.01<\/span><\/p>\n\n\n\n\n\n\n<\/colgroup>\n\n\n\n\n\n
<\/td>\nHP Intel Dual 64 2GB<\/strong><\/td>\nDell 64 6GB<\/strong><\/td>\nHP Pav 64 4GB<\/strong><\/td>\nHP Pav 32 1GB<\/strong><\/td>\n<\/tr>\n
\u00a0read\/write requests:<\/strong><\/td>\n\u00a03818.54 per sec<\/span><\/strong><\/td>\n\u00a08247.70 per sec<\/strong><\/span><\/td>\n6038.00 per sec<\/strong><\/span><\/td>\n\u00a02563.96 per sec<\/strong><\/span><\/td>\n<\/tr>\n
total time:<\/strong><\/td>\n\u00a0366.6323s<\/span><\/strong><\/td>\n\u00a0169.7443s<\/strong><\/span><\/td>\n\u00a0231.8647s<\/strong><\/span><\/td>\n\u00a0546.0298s<\/strong><\/span><\/td>\n<\/tr>\n
<\/td>\n<\/td>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n","protected":false},"excerpt":{"rendered":"
<\/div>\n

<\/path><\/svg><\/i> \"Loading\"<\/p>\n

<\/div>\n

man sysbench oltp This test mode was written to benchmark a real database performance. At the prepare stage the following table is created in the specified database (sbtest by default): Example usage: (THESE DON'T WORK AS IS - A DB NEEDS CREATING FIRST - SEE BELOW) $ sysbench --test=oltp --mysql-table-type=myisam --oltp-table-size=1000000 --mysql-socket=\/tmp\/mysql.sock prepare $ sysbench ...\u00a0 Sysbench – Simulated Database Testing<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[],"class_list":["post-4320","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"a3_pvc":{"activated":true,"total_views":1,"today_views":0},"_links":{"self":[{"href":"https:\/\/stevepedwards.today\/DebianAdmin\/wp-json\/wp\/v2\/posts\/4320","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/stevepedwards.today\/DebianAdmin\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/stevepedwards.today\/DebianAdmin\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/stevepedwards.today\/DebianAdmin\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/stevepedwards.today\/DebianAdmin\/wp-json\/wp\/v2\/comments?post=4320"}],"version-history":[{"count":0,"href":"https:\/\/stevepedwards.today\/DebianAdmin\/wp-json\/wp\/v2\/posts\/4320\/revisions"}],"wp:attachment":[{"href":"https:\/\/stevepedwards.today\/DebianAdmin\/wp-json\/wp\/v2\/media?parent=4320"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/stevepedwards.today\/DebianAdmin\/wp-json\/wp\/v2\/categories?post=4320"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/stevepedwards.today\/DebianAdmin\/wp-json\/wp\/v2\/tags?post=4320"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}