Re: effective_io_concurrency and NVMe devices

On Wed, 20 Apr 2022 at 14:56, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> NVMe devices have a maximum queue length of 64k:

> Should we increase its maximum to 64k? Backpatched? (SATA has a
> maximum queue length of 256.)

I have a machine here with 1 x PCIe 3.0 NVMe SSD and also 1 x PCIe 4.0
NVMe SSD. I ran a few tests to see how different values of
effective_io_concurrency would affect performance. I tried to come up
with a query that did little enough CPU processing to ensure that I/O
was the clear bottleneck.

The test was with a 128GB table on a machine with 64GB of RAM. I
padded the tuples out so there were 4 per page so that the aggregation
didn't have much work to do.

The query I ran was: explain (analyze, buffers, timing off) select
count(p) from r where a = 1;

Here's what I saw:

NVME PCIe 3.0 (Samsung 970 Evo 1TB)
e_i_c query_time_ms
0 88627.221
1 652915.192
5 271536.054
10 141168.986
100 67340.026
1000 70686.596
10000 70027.938
100000 70106.661

Saw a max of 991 MB/sec in iotop

NVME PCIe 4.0 (Samsung 980 Pro 1TB)
e_i_c query_time_ms
0 59306.960
1 956170.704
5 237879.121
10 135004.111
100 55662.030
1000 51513.717
10000 59807.824
100000 53443.291

Saw a max of 1126 MB/sec in iotop

I'm not pretending that this is the best query and table size to show
it, but at least this test shows that there's not much to gain by
prefetching further. I imagine going further than we need to is
likely to have negative consequences due to populating the kernel page
cache with buffers that won't be used for a while. I also imagine
going too far out likely increases the risk that buffers we've
prefetched are evicted before they're used.

This does also highlight that an effective_io_concurrency of 1 (the
default) is pretty terrible in this test. The bitmap contained every
2nd page. I imagine that would break normal page prefetching by the
kernel. If that's true, then it does not explain why e_i_c = 0 was so
fast.

I've attached the test setup that I did. I'm open to modifying the
test and running again if someone has an idea that might show benefits
to larger values for effective_io_concurrency.

David