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