AUTOLOCATE
4 months ago
select
tabname::char(20) tab,
(pagreads + pagwrites) io,
pagreads,
pagwrites
from
sysptprof
where
dbsname = "ptpcc" and
tabname not matches ("sys*")
order by
(pagreads + pagwrites) desc;
...
tab io pagreads pagwrites
stock 833785 417350 416435
customer 226990 134711 92279
order_line 75287 42206 33081
stock_pk 45504 45504 0
oorder_uix1 36457 23932 12525
oorder 29367 26773 2594
order_line_pk 27214 11809 15405
oorder_pk 19565 16879 2686
customer_pk 17865 17865 0
new_order_pk 8496 6196 2300
district 4793 4745 48
customer_ix1 3894 3894 0
item 3773 3773 0
new_order 1979 773 1206
history 1522 12 1510
item_pk 815 815 0
warehouse 11 5 6
district_pk 7 7 0
warehouse_pk 1 1 0
Using this information I can place my tables and indexes in the appropriate dbspaces to spread the reads and writes across my disks evenly (giving the RAID10 set with 10 disks more I/Os to do and the RAID10 set with only 6 disks less I/Os to do) and hopefully improve my benchmark TPM.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.2010-05-07 15:11:26: benchmark - 100 terminals running for 900 seconds
2010-05-07 15:31:28: sql total min max avg num
2010-05-07 15:31:28: dbSelDistOrderId 264.492183 0.000589 4.998652 0.120443 2196
2010-05-07 15:31:28: dbSelDistPymt 666.518798 0.000611 0.874549 0.028071 23744
2010-05-07 15:31:28: dbSelCustById 3865.562448 0.000819 2.452900 0.162802 23744
2010-05-07 15:31:28: dbSelNewestOrder 3540.358412 0.000530 13.473251 1.700460 2082
2010-05-07 15:31:28: dbSelCustData 69.895807 0.000703 0.853232 0.030310 2306
2010-05-07 15:31:28: dbSelOrderLines 33.498745 0.000373 0.484876 0.016090 2082
2010-05-07 15:31:28: dbSelOrder 69.810965 0.000608 0.529128 0.033531 2082
2010-05-07 15:31:28: dbSelCustByOrder 1081.806514 0.000592 1.636825 0.049017 22070
2010-05-07 15:31:28: dbUpdOOrder 1071.331497 0.000262 2.102251 0.048542 22070
2010-05-07 15:31:28: dbUpdDist 369.739051 0.000248 0.864587 0.015036 24591
2010-05-07 15:31:28: dbSelCountStock 5653.657284 0.006115 14.663261 2.574525 2196
2010-05-07 15:31:28: dbUpdDistPymt 3211.747694 0.000262 6.936206 0.135266 23744
2010-05-07 15:31:28: dbUpdStock 3607.889070 0.000283 1.362896 0.015618 231006
2010-05-07 15:31:28: dbSelCustCount 207.250488 0.017086 2.808686 0.166466 1245
2010-05-07 15:31:28: dbUpdWhsePymt 2955.763594 0.000259 5.403844 0.124485 23744
2010-05-07 15:31:28: dbUpdCustBalanceAndData 32.769989 0.000403 0.146913 0.014211 2306
2010-05-07 15:31:28: dbInsHistPymt 368.780551 0.000269 1.330070 0.015532 23744
2010-05-07 15:31:28: dbUpdCustBalance 345.092451 0.000297 1.336634 0.016097 21438
2010-05-07 15:31:28: dbSelCustByNameOrd 45.854596 0.000788 0.640742 0.038566 1189
2010-05-07 15:31:28: dbSelNewOrder 1986.016858 0.003527 6.152908 0.089987 22070
2010-05-07 15:31:28: dbSelCustWhse 5416.771947 0.000852 6.570103 0.220275 24591
2010-05-07 15:31:28: dbSelCustByIdOrd 108.330620 0.003462 1.446552 0.121311 893
2010-05-07 15:31:28: dbUpdOrderLine 1515.644348 0.000358 2.189919 0.068674 22070
2010-05-07 15:31:28: dbSelStock 26748.686979 0.000714 5.385313 0.115792 231006
2010-05-07 15:31:28: dbSelOrderLineAmt 688.818336 0.000692 1.372060 0.031211 22070
2010-05-07 15:31:28: dbSelWhsePymt 708.854190 0.000605 0.904277 0.029854 23744
2010-05-07 15:31:28: dbInsOrderLine 4126.250988 0.000295 2.563897 0.017862 231006
2010-05-07 15:31:28: dbInsOOrder 604.548543 0.000291 2.471327 0.024584 24591
2010-05-07 15:31:28: dbDelNewOrder 479.087660 0.000269 8.575327 0.021708 22070
2010-05-07 15:31:28: dbSelDist 3854.715855 0.000630 7.683246 0.156753 24591
2010-05-07 15:31:28: dbInsNewOrder 412.719221 0.000263 2.343799 0.016783 24591
2010-05-07 15:31:28: dbUpdCustDelivery 2324.551898 0.000327 2.426604 0.105326 22070
2010-05-07 15:31:28: dbSelItem 12098.294207 0.000647 1.985179 0.052372 231006
2010-05-07 15:31:28: totals 88535.111786 - - - 1383948
2010-05-07 15:31:28:
2010-05-07 15:31:28: time: 901.863525, total transactions: 54876, TPM: 3650.840630, SPM: 92072.556084
Send your suggestions, questions, complaints, general greetings and cash donations to andrew@informix-dba.com