The Juice is Loose – MySQL Benchmark

Well I am still not happy with a lot of the code, but I have had some people ask me to help test my attempt at a new MySQL benchmark. So I cleaned up the code a bit and pushed what I had to LaunchPad. It’s way to early for this to be ready for prime time, I am using it to stress flash, but as I do i am still finding issues, so use at your own risk. My goal is to make this more generic and allow user customizable SQL files to be loaded in…. something like:

1 | select * from table where a =?  |  rand | a | 1000
2 | select * from table1,table2 where table1.id=table2.id and c = ? and d=? | rand,1-200 | c,d | 10

This would mean execute query #1 , using a random value from a 1000 times in a loop before exiting… and then execute query 2, 10 times using a rand value from c, and a value between 1-200 for d….

But that is a ways off.

For now I support 4 tests which are hard coded in:

mx = mixed wokload of updates/selects I do not insert or delete because I want a uniform distro of values.
ro = read-only io bound test... lots of nasty big queries, full scans, temp tables, etc.
mxio = a mixed IO bound test where I do mass updates and the like
ss = a simple selects test

I need to work on making the order of the queries more random.

You will get output from the test like the following:

Statement type average:

root@bigdbahead:/home/matt/QuickBenchMark/output# cat bench76_stmt_avg.out
Summary||Type||4||count||150||totaltime||9.90888738632202||average||0.0660592492421468||stmt||select count(*) from benchmark_a_lrg where a_int_type1 in (?,?,?,?) 
Summary||Type||7||count||3000||totaltime||8.79797053337097||average||0.00293265684445699||stmt||select a.a_id,a.a_name,a.a_value1,a.a_var3 from benchmark_a_lrg a where a_id=? 
Summary||Type||10||count||150||totaltime||2.02836418151855||average||0.0135224278767904||stmt||select a.a_id,a.a_name,a.a_value1,a.a_var3 from benchmark_a_lrg a where a.a_med_card_i=? 
Summary||Type||14||count||1500||totaltime||4.01659679412842||average||0.00267773119608561||stmt||select a.a_id,a.a_name,a.a_value1,a.a_var3 from benchmark_a_lrg a where a_id=? 
Summary||Type||15||count||150||totaltime||281.347162008286||average||1.8756477467219||stmt||select a.a_id,a.a_name,a.a_value1,a.a_var3 from benchmark_a_lrg a where a_high_skew_i=? and  a_int_type1 in (?,?,?,?) limit 10 
Summary||Type||4||count||160||totaltime||13.6093125343323||average||0.0850582033395767||stmt||select count(*) from benchmark_a_lrg where a_int_type1 in (?,?,?,?) 
Summary||Type||7||count||3200||totaltime||13.9968848228455||average||0.00437402650713921||stmt||select a.a_id,a.a_name,a.a_value1,a.a_var3 from benchmark_a_lrg a where a_id=? 
Summary||Type||10||count||160||totaltime||0.286622762680054||average||0.00179139226675034||stmt||select a.a_id,a.a_name,a.a_value1,a.a_var3 from benchmark_a_lrg a where a.a_med_card_i=? 
Summary||Type||14||count||1600||totaltime||3.89617586135864||average||0.00243510991334915||stmt||select a.a_id,a.a_name,a.a_value1,a.a_var3 from benchmark_a_lrg a where a_id=? 
Summary||Type||15||count||160||totaltime||287.464867830276||average||1.79665542393923||stmt||select a.a_id,a.a_name,a.a_value1,a.a_var3 from benchmark_a_lrg a where a_high_skew_i=? and  a_int_type1 in (?,?,?,?) limit 10 

Statement type details

root@bigdbahead:/home/matt/QuickBenchMark# perl AnalyzeData.pl 76
QNum:      4 ... QCount:    310 ... QTime:   0.075865 ... Max:   0.419020 ... FlatTime:   0.064590  ... Min5%:   0.010076  ... Max5%:   0.326532 
QNum:      7 ... QCount:   6200 ... QTime:   0.003677 ... Max:   0.205884 ... FlatTime:   0.000570  ... Min5%:   0.000174  ... Max5%:   0.062588 
QNum:     10 ... QCount:    310 ... QTime:   0.007468 ... Max:   0.005627 ... FlatTime:   0.001850  ... Min5%:   0.001440  ... Max5%:   0.003310 
QNum:     14 ... QCount:   3100 ... QTime:   0.002553 ... Max:   0.131778 ... FlatTime:   0.000538  ... Min5%:   0.000163  ... Max5%:   0.040112 
QNum:     15 ... QCount:    302 ... QTime:   1.840137 ... Max:  14.276493 ... FlatTime:   1.413010  ... Min5%:   0.007137  ... Max5%:  10.969841 

Total run time of each test

root@bigdbahead:/home/matt/QuickBenchMark/output# cat bench77_total.out
Simple Selects Run Against Table A Finished (0)....  Time:  45.2693591117859
Simple Selects Run Against Table A Finished (1)....  Time:  40.9942080974579
Simple Selects Run Against Table A Finished (2)....  Time:  17.3760769367218
Simple Selects Run Against Table A Finished (3)....  Time:  13.4789490699768
Simple Selects Run Against Table A Finished (4)....  Time:  22.420872926712
Simple Selects Run Against Table A Finished (5)....  Time:  4.02253389358521
Simple Selects Run Against Table A Finished (6)....  Time:  25.0883159637451
Simple Selects Run Against Table A Finished (7)....  Time:  3.53902411460876
Simple Selects Run Against Table A Finished (8)....  Time:  16.60808801651
Simple Selects Run Against Table A Finished (9)....  Time:  25.4266018867493
Simple Selects Run Against Table A Finished (10)....  Time:  14.1644470691681
Simple Selects Run Against Table A Finished (11)....  Time:  28.5027401447296
Simple Selects Run Against Table A Finished (12)....  Time:  20.2571589946747
Simple Selects Run Against Table A Finished (13)....  Time:  24.0040650367737
Simple Selects Run Against Table A Finished (0)....  Time:  30.587256193161
Simple Selects Run Against Table A Finished (1)....  Time:  29.3745720386505
Simple Selects Run Against Table A Finished (2)....  Time:  29.8342020511627
Simple Selects Run Against Table A Finished (3)....  Time:  13.5518918037415
Simple Selects Run Against Table A Finished (4)....  Time:  12.7153739929199
Simple Selects Run Against Table A Finished (5)....  Time:  16.8902640342712
Simple Selects Run Against Table A Finished (6)....  Time:  10.5431311130524
Simple Selects Run Against Table A Finished (7)....  Time:  5.37037992477417
Simple Selects Run Against Table A Finished (8)....  Time:  7.66316485404968
Simple Selects Run Against Table A Finished (9)....  Time:  12.7695250511169
Simple Selects Run Against Table A Finished (10)....  Time:  17.5634911060333
Simple Selects Run Against Table A Finished (11)....  Time:  14.9049520492554
Simple Selects Run Against Table A Finished (12)....  Time:  13.1383061408997
Simple Selects Run Against Table A Finished (13)....  Time:  11.0534348487854
Simple Selects Run Against Table A Finished (14)....  Time:  51.0313611030579
Simple Selects Run Against Table A Finished (15)....  Time:  28.3074588775635

Plus i grab all the io,db,cpu, and other stats I could think of.

Their is a ton of work to, so don’t judge it too harshly!

Grab it here:
https://launchpad.net/juicebenchmark

This entry was posted in benchmark, code, mysql, performance. Bookmark the permalink.

Comments are closed.