Databases Archives | n0derunner

The vertica vioperf tool is used to determine whether the storage you are planning on using is fast enough to feed the vertica database.

The vioperf tool runs on a linux host or VM and can be pointed at any filesystem just like fio or vdbench

Simple execution of vioperf writing to the location /vertica

vioperf --thread-count=8 --duration=120s  /vertica

1	vioperf --thread-count=8 --duration=120s /vertica

Working Set Size

Unlike traditional IO generators vioperf does not allow you to specify the workings size. The amount of data written is simply the IO rate achieved by the storage * runtime. So, fast storage with long run-times will need a lot of capacity otherwise the tool simply fills the partition and crashes!

Measurement and goodness

The primary metric is MB/s Per-Core. The idea is that you give 1 Thread per core in the system, though there is nothing stopping you from using whatever –thread-count value you like.

Although the measure is throughput, the measure (Throughput/Core) does not improve just by giving lots of concurrency. Concurrency is generated purely by the number of threads and since the measure of goodness is Throughput/Core (or per thread) it’s not possible to simply create throughput from concurrency alone.

I guess we assume that each vertica thread is synchronous. Which seems a bit odd.

Throughput compared to FIo

Compared to fio the reported throughput is lower for the same device and same degree of concurrency. Vertica continually writes, and extends the files so there is some filesystem work going on whereas fio is overwriting an existing file.

vertica iostat 64 Threads

Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sdc               0.00   285.00    0.00  169.00     0.00 86528.00  1024.00    10.56   64.11    0.00   64.11   5.89  99.60
sde               0.00   285.00    0.00  159.00     0.00 81408.00  1024.00    21.55  127.55    0.00  127.55   6.29 100.00
md1               0.00     0.00    0.00  605.00     0.00 163840.00   541.62     0.00    0.00    0.00    0.00   0.00   0.00

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

sdc 0.00 285.00 0.00 169.00 0.00 86528.00 1024.00 10.56 64.11 0.00 64.11 5.89 99.60

sde 0.00 285.00 0.00 159.00 0.00 81408.00 1024.00 21.55 127.55 0.00 127.55 6.29 100.00

md1 0.00 0.00 0.00 605.00 0.00 163840.00 541.62 0.00 0.00 0.00 0.00 0.00 0.00

fio iostat iodepth=64

Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sdc               0.00     0.00    0.00  219.00     0.00 112128.00  1024.00    63.50  285.05    0.00  285.05   4.57 100.00
sde               0.00     0.00    0.00  219.00     0.00 112128.00  1024.00     0.45    2.04    0.00    2.04   2.04  44.60
md1               0.00     0.00    0.00  438.00     0.00 224256.00  1024.00     0.00    0.00    0.00    0.00   0.00   0.00

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

sdc 0.00 0.00 0.00 219.00 0.00 112128.00 1024.00 63.50 285.05 0.00 285.05 4.57 100.00

sde 0.00 0.00 0.00 219.00 0.00 112128.00 1024.00 0.45 2.04 0.00 2.04 2.04 44.60

md1 0.00 0.00 0.00 438.00 0.00 224256.00 1024.00 0.00 0.00 0.00 0.00 0.00 0.00

fio 1 thread 1M sequential write

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           3.30    0.03    4.16    9.53    0.00   82.98

Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sda               0.00     0.40    0.00    4.00     0.00    23.50    11.75     0.01    1.70    0.00    1.70   0.20   0.08
dm-1              0.00     0.00    0.00    2.00     0.00     9.50     9.50     0.00    2.00    0.00    2.00   0.20   0.04
nvme0n1           0.00     0.20    0.00 4695.40     0.00 2403432.80  1023.74     1.28    0.27    0.00    0.27   0.18  86.20

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

3.30 0.03 4.16 9.53 0.00 82.98

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

sda 0.00 0.40 0.00 4.00 0.00 23.50 11.75 0.01 1.70 0.00 1.70 0.20 0.08

dm-1 0.00 0.00 0.00 2.00 0.00 9.50 9.50 0.00 2.00 0.00 2.00 0.20 0.04

nvme0n1 0.00 0.20 0.00 4695.40 0.00 2403432.80 1023.74 1.28 0.27 0.00 0.27 0.18 86.20

Vertica IOstat 1 thread

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          11.97    0.03    3.71    1.20    0.00   83.09

Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sda               0.00     0.00    0.00    2.20     0.00    16.80    15.27     0.00    0.64    0.00    0.64   0.64   0.14
nvme0n1           0.00    79.60    2.60  875.20    10.40 308546.40   703.02     0.28    0.32    0.15    0.32   0.19  16.24

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

11.97 0.03 3.71 1.20 0.00 83.09

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

sda 0.00 0.00 0.00 2.20 0.00 16.80 15.27 0.00 0.64 0.00 0.64 0.64 0.14

nvme0n1 0.00 79.60 2.60 875.20 10.40 308546.40 703.02 0.28 0.32 0.15 0.32 0.19 16.24

vioperf with –disable-crc

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           2.46    0.05    6.55    8.09    0.00   82.85

Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sda               0.00     0.00    0.00    0.60     0.00     2.40     8.00     0.00    1.67    0.00    1.67   1.00   0.06
dm-1              0.00     0.00    0.00    0.40     0.00     1.60     8.00     0.00    2.00    0.00    2.00   1.00   0.04
nvme0n1           0.00   158.40    5.20 1786.60    20.80 630392.80   703.67     1.46    0.81    2.85    0.81   0.46  82.44

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

2.46 0.05 6.55 8.09 0.00 82.85

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

sda 0.00 0.00 0.00 0.60 0.00 2.40 8.00 0.00 1.67 0.00 1.67 1.00 0.06

dm-1 0.00 0.00 0.00 0.40 0.00 1.60 8.00 0.00 2.00 0.00 2.00 1.00 0.04

nvme0n1 0.00 158.40 5.20 1786.60 20.80 630392.80 703.67 1.46 0.81 2.85 0.81 0.46 82.44

Effect of removing CPU constraints and maintaining data locality on a running DB instance.

In this video I migrate a Postgres DB running PGbench benchmark. The DB is running on a Host which is CPU constrained. Once the VM is migrated to a less busy host the transaction rate immediately increases from ~15,000 to ~20,000. As the DB continues to run on the new host – the Nutanix storage detects the access patterns and “localizes” the data that the DB is accessing. Over the subsequent minutes the transaction rate increases to ~30,000 TPS.

The variation in the transaction rate is due to the benchmark itself, the transaction rate is not expected to be uniform. Many different queries are executing in parallel, some hitting RAM cache, some hitting storage.

N.B The Postgres DB is totally un-tuned and contains purely default settings. The aim of the experiment was to see how data-locality might affect a running database workload, not to generate the maximum TPS.