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://learn.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
- 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
- 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
- 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.
