What is the first thing everyone does when tuning an engine for the first time? Check the buffers and increase them if they haven't been changed from the default setting. We want to minimize I/O because I/O sucks and takes a long time. Putting as much likely to be used again very soon data into cache is a good way to minimize I/O.
Our baseline was 3651 TPM with buffers set to 50000. I'm sure we can improve on that number if we reduce the number of disk reads and writes. Taking advantage of the fact that this is an OLTP benchmark I can configure nearly all of the available memory on the box to buffers (3GB or 1572864 2K buffers) and configure just enough memory to the virtual segment to handle all of the sessions (250 MB).
Running the benchmark again I get the following results
2010-05-07 16:35:07: benchmark - 100 terminals running for 900 seconds
2010-05-07 16:35:07: sql total min max avg num
2010-05-07 16:35:07: dbSelDistOrderId 366.158430 0.000592 3.143735 0.103085 3552
2010-05-07 16:35:07: dbSelDistPymt 1435.361208 0.000605 0.837176 0.037623 38151
2010-05-07 16:35:07: dbSelCustById 2255.096493 0.000784 1.295496 0.059110 38151
2010-05-07 16:35:07: dbSelNewestOrder 595.117024 0.000480 5.193018 0.172648 3447
2010-05-07 16:35:07: dbSelCustData 150.336596 0.000696 0.435675 0.038957 3859
2010-05-07 16:35:07: dbSelOrderLines 69.367385 0.000373 0.499633 0.020124 3447
2010-05-07 16:35:07: dbSelCustByOrder 1348.661282 0.000582 0.652879 0.038489 35040
2010-05-07 16:35:07: dbUpdOOrder 677.845172 0.000256 0.566081 0.019345 35040
2010-05-07 16:35:07: dbUpdDist 763.428072 0.000250 0.676018 0.019180 39803
2010-05-07 16:35:07: dbSelOrder 135.996560 0.000631 0.804294 0.039454 3447
2010-05-07 16:35:07: dbUpdDistPymt 4233.915816 0.000257 8.117577 0.110978 38151
2010-05-07 16:35:07: dbUpdStock 7224.883937 0.000263 0.797545 0.019243 375455
2010-05-07 16:35:07: dbSelCustCount 150.360743 0.000795 1.164771 0.069676 2158
2010-05-07 16:35:07: dbUpdWhsePymt 4263.935629 0.000254 8.083272 0.111765 38151
2010-05-07 16:35:07: dbUpdCustBalanceAndData 71.946746 0.000414 0.333886 0.018644 3859
2010-05-07 16:35:07: dbInsHistPymt 728.578052 0.000265 0.503783 0.019097 38151
2010-05-07 16:35:07: dbUpdCustBalance 666.788779 0.000283 0.656385 0.019444 34292
2010-05-07 16:35:07: dbSelCustByNameOrd 94.072595 0.000762 0.373746 0.046318 2031
2010-05-07 16:35:07: dbSelNewOrder 2017.488472 0.003504 5.246324 0.057577 35040
2010-05-07 16:35:07: dbSelCustWhse 5384.940138 0.000726 6.392744 0.135290 39803
2010-05-07 16:35:07: dbSelCustByIdOrd 82.164412 0.000758 0.852723 0.058026 1416
2010-05-07 16:35:07: dbUpdOrderLine 1070.116667 0.000348 1.189829 0.030540 35040
2010-05-07 16:35:07: dbSelStock 19846.689419 0.000703 1.215853 0.052860 375455
2010-05-07 16:35:07: dbSelOrderLineAmt 1375.268063 0.000681 0.623969 0.039249 35040
2010-05-07 16:35:07: dbSelWhsePymt 1448.440517 0.000599 0.677760 0.037966 38151
2010-05-07 16:35:07: dbInsOrderLine 7268.765215 0.000281 0.920966 0.019360 375455
2010-05-07 16:35:07: dbInsOOrder 764.685358 0.000281 0.520975 0.019212 39803
2010-05-07 16:35:07: dbDelNewOrder 770.737759 0.000263 7.087722 0.021996 35040
2010-05-07 16:35:07: dbSelDist 5383.367946 0.000620 6.788749 0.135250 39803
2010-05-07 16:35:07: dbInsNewOrder 767.917783 0.000262 0.398216 0.019293 39803
2010-05-07 16:35:07: dbUpdCustDelivery 1270.802428 0.000293 1.166629 0.036267 35040
2010-05-07 16:35:07: dbSelCountStock 118.598752 0.006119 1.274016 0.033389 3552
2010-05-07 16:35:07: dbSelItem 14574.856113 0.000633 0.837902 0.038819 375455
2010-05-07 16:35:07: totals 87376.689563 - - - 2240081
2010-05-07 16:35:07:
2010-05-07 16:35:07: time: 901.060969, total transactions: 88584, TPM: 5898.646354, SPM: 149162.891976
Next I think I'll take advantage of my 3 unused dbspaces to spread what I/O I have left around to my idle disks.
No comments:
Post a Comment