Block size OS vs DB

I doubt that it will make much difference.  But that's just an opinion, I haven't actually tried it for a very long time.

One thing to think about is that even if you change the filesystem block the physical disk is still working with 512 byte "sectors".

There are disks with 4k physical sectors these days. I haven't used one but I know they do exist.

And some of these 4k guys "lie" to the bios/os/whatever saying they are 512b when they are not.

In general there would be somewhat random performance loss if the two 4k OS blocks were not contiguous when we go to retrieve the block.

This would require an OS disk reposition to complete the acquisition of the single database block.

If there weren't many of these then the loss to performance would be miniscule.

If there are many of these then you might be sacrificing about 5ms every reposition.

Example of bad scenario.

1)  Reading 100000 db blocks.

2)  80% of these blocks do not reside on contiguous OS blocks.

3)  If normal read speeds are 5ms to read the two contiguous blocks and we have to do 20,000 of those connected block reads that is 100 seconds overall. (20,000 * 5 / 1000)

4)  Now we have to read 80,000 of those non-contiguous blocks so it is 5 ms + 5 ms * 80,000 / 1000 = 800 seconds.

The second 5ms is for the reposition to get to the second block.

If we didn't have the reposition time it would have been 400 seconds.

So there could be some benefit if the file system block size matches the database block size if the underlying blocks we use for the database are not contiguous.

Generally, a database block size that is a multiple of the filesystem blocksize (e.g 8k and 4k) will work rather well. The opposite is NOT true thpugh.

Filesystems can read and write two contiguous blocks to disk in almost the same time as writing one block. Yes, there is a small possibility that two filesystem blocks that make up one database block will NOT be contiguous. This is a rare event and while possible, hardly ever occurs unless the filesystem is 80% or more full mostly of lots of small files.

On the other hand, the difference between 8k and 4k database blocks is not very much. i confess it has been some time ago that i tested that.

agree. my main point was that the system call i/o cost of two adjacent filesystem blocks versus one was negligible.

helpful.  Thanks.

The difference between 4K and 8k will not change the number of index keys in the leaf blocks. Index compression algorithm may slightly change the storage size of these keys but not too much especially for the indexes with many non-unique keys. In other words, this difference will only double the number of the leaf blocks. It will in turn double the number of index keys in the intermediate index blocks and double their total size. Hence the number of the intermediate index blocks will be increased by 4 times. And by 8, 16 etc times for the next intermediate levels. The number of the intermediate blocks in the large indexes used to be 0.1-1% of the leaf blocks. To get new level in the index tree the number of index keys should be increased by the hundreds/thousands times. IMHO, it's rather unlikely that the difference between 4K and 8k will change the number of levels in the index tree. But yes, the larger block size the smaller (slightly) total size of the indexes.

No matter how many levels in the index tree the FOR EACH query creates only one index block request per record. The exception is the first key in the each index block. The number of index levels affects mainly the performance of the FIND FIRST/LAST queries.

It's all very interesting thank you.

So If I understand it's not easy:)
As long as the blocks are contiguous there will not be much difference in disk reposition.
Are the database blocks also contiguous with virtualization?
Does the exmaple of richard also count for SSD(reposition 5ms)?

It's Unlikely the levels of indexes changed when moving from 4K to 8K.
But when it should change there will be a benefit for only FIND FIRST/LAST else it doesn't matter.
The total size of the indexes will be smaller but not much.

What also keeps me busy is the post of ChuiMonster you are working with 8K physical sectors but the logical sectors are 512 bytes...