Friday, May 7, 2010

OLTP Benchmark - Buffers


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
Maxing out buffers increases out TPM by almost 62% to 5899, not bad for a simple config change and engine bounce.

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