Re: The need for clustered indexes to boost TPC-V performance

Hi Samuel,

The SSDs were used as a cache for the spinning drives. Here is a 30-second iostat sample representative of the whole run:

avg-cpu:  %user   %nice %system %iowait  %steal   %idle

          24.87    0.00   12.54   62.39    0.00    0.20

Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await  svctm  %util

sdd               0.00   137.37 3058.40  106.17 34691.60   974.13    22.54    15.75    4.98   0.32 100.00

sde               0.00   136.07 3063.37  107.70 35267.07   975.07    22.86    15.58    4.92   0.32 100.00

sdf               0.00   135.37 3064.23  109.53 35815.60   979.60    23.19    15.82    4.99   0.32 100.00

sdg               0.00   136.97 3066.57  116.67 35196.53  1014.53    22.75    15.87    4.99   0.31 100.00

sdi               0.00  2011.03    0.00   87.90     0.00  8395.73   191.03     0.13    1.45   1.42  12.51

sdk               0.00   136.63 3066.83  107.53 35805.07   976.67    23.17    16.01    5.04   0.32 100.00

sdm               0.00   138.50 3054.40  111.10 34674.27   998.40    22.54    15.52    4.91   0.32 100.00

sdj               0.00   136.73 3058.70  118.20 35227.20  1019.73    22.82    15.81    4.98   0.31 100.00

sdl               0.00   137.53 3044.97  109.33 34448.00   987.47    22.47    15.78    5.00   0.32 100.00

The data and index tablespaces were striped across the 8 LUNs, and saw an average 5ms response. We can beef up the storage to handle more I/Os so that our utilization doesn’t stay below 40%, but that misses the point: we have an I/O rate twice the commercial database because they used clustered indexes.

I provided more config details in an earlier email.

As for asking for development to game a benchmark, no one is asking for benchmark specials. The question of enhancements in response to benchmark needs is an age old question. We can get into that, but it’s really a different discussion. Let me just expose the flip side of it: are we willing to watch people use other databases to run benchmarks but feel content that no features were developed specifically in response to benchmark results?

I am trying to engage with the community. We can drown the mailing list with details. So I decided to open the discussion with the high level points, and we will give you all the details that you want as we move forward.

Thanks,
Reza