Disk data counters (more)

In a previous blog "disk data counter" I did a couple of measurements how to configure the disk data and what we can expect.

Now I have redone the insert test to put as much load as possible with respect to the disk sub system.

1. Insert 5M records a' 4096B (1 threads, batches of five):
6.3.19: 286 sec (3721.12 QPS)

2. Insert 5M records a' 4096B (5 threads, batches of five):
6.3.19: 286 sec (8315.36 QPS)

At 2) the IO sub-system is quite loaded (98.2%) and I get (periodically,when the DiskPageBufferMemory is checkpointed):

"REDO buffers overloaded, consult online manual (increase RedoBuffer) - code=1221" (this does not matter, as i retry my transactions in the application).
I could increase the RedoBuffer (it is set to 32M), but there is no point as if I increase the load, then the disks would not be able to keep up any way. Hence, to get better perf now I need to increase the number of disks I can write to.

Here are some numbers for Read and Writes of 1024B records:

3. Insert 5M records a' 1024B (5 threads, batches of five):
6.3.19: 375 sec (13335 QPS)

4. Insert 10M records a' 1024B (10 threads, batches of five):
6.3.19: 506 sec (19737.2 QPS)

5. Insert 20M records a' 1024B (5 threads, batches of five):
6.3.19: 1100 sec (17035.8 QPS)

6. Insert 20M records a' 1024B (10 threads, batches of five):
6.3.19: 945 sec (21140.7 QPS)

7. Insert 20M records a' 1024B (20 threads, batches of five):
6.3.19: 675 sec (29578.9 QPS)

8. Read 1M random records (1024B) out of 20M (10 threads):
(Each thread does 100K reads)
6.3.19: 816 sec (1230.01 QPS)

9. Read 2M random records (1024B) out of 20M (20 threads):
(Each thread does 100K reads)
6.3.19: 683 sec (2911.67 QPS)

10. Read 3M random records (1024B) out of 20M (30 threads):
(Each thread does 100K reads)
6.3.19: 1273 sec (2350.62 QPS)

The setup was same as in disk data counters.
Next I am going to do some tests on ordered index scans (i.e range scans on an in-memory index) and read data stored on disk.

Use this numbers only for an indication what you can achieve since it may differ for your application.