What I learn is: PG block size >= ni * VM block size >= nj * host block size

But what happens when the DB in a VM is on an SSD RAID ARRAY? The ARRAY strip size changes the game and puzzle me. What should be the strip size for an ARRAY of SSDs for an OLTP DB?

Is there a relation like : Array strip size >= n * host block size ?

Let start from the base by adding one layer at a time:

i) The recommendation of a PostgreSQL DB running on a physical server is strait.

PG block size (default 8K) should be a multiple of the OS block size : PG block size >= n times OS block size.

ii) When PG runs inside hyper-V (host and VM are Win2019)

I would say that the host and VM block sizes (cluster sizes) should be equal. Can anyone confirm?

iii) RAID strip size

The array is 10 x 8TB SSD in RAID 6 (would prefer RAID10, but RAID 6 chosen for capacity)

https://docs.microsoft.com/en-us/answers/questions/564372/raid-setup-and-drive-formatting-for-hyper-v-vhdx-1.html says to use 64KB stripe size, and 64KB cluster size on file systems of the host and VM, but I am not sure it is right for a PostgreSQL DB (OLTP type)?

Our system is currently configured as follow: PG 8KB, VM 4KB, host 64KB, ARRAY 64KB

  1. My first guess would be to change all block and strip sizes to 8KB. Does it make sense?

PG 8KB, VM 8KB, host 8KB, ARRAY 8KB

  1. But, 8KB strip size may be too small, should I leave it to 64K and set all block sizes to 8K?

PG 8KB, VM 8KB, host 8KB, ARRAY 64KB

  1. Another possibility would be to compile PG with a 16KB block size and set the other block sizes accordingly.

PG 16KB, VM 16KB, host 16KB, ARRAY 16KB or 64KB

BTW we do not have a pure OLTP DB because our large tables are written in large chunks and a significant portion of selects read entire chunks (blocks read should be collocated).

Am I losing my time and should leave the current settings as is? According to https://backupchain.com/i/hyper-v-block-size-for-ntfs-whats-recommended I am.

  • Test with pgbench. Don't use virtualization for top performance. Jan 12 at 7:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.