0

This topic has already been discussed here: Understanding block sizes But I have few more things to add for my use case.

Generally, most database systems use a default block size of 8 KB, though some allow it to be modified. On the other hand, modern operating systems often use a 4 KB block size for file systems. This discrepancy can result in multiple physical I/O requests to fill a single database page.

A smaller file system block size benefits random reads, such as index lookups, while larger block sizes are advantageous for sequential scans and heap fetches. Considering these points, I have a few questions:

  1. Is there a common practice to align the database block size with the file system block size for OLTP?
  2. In a clustered system (e.g., SQL Server Availability Groups or PostgreSQL streaming replication) with a primary and one or more secondaries, is it acceptable to have different file system block sizes, or is this something that should always be avoided?
  3. For analytical databases or columnar tables, is it beneficial to use a larger block size?
Improve this question
2
  • This was important during the days of rotating hard drives but less on SSDs. What problem are you trying to solve? Commented Dec 11, 2024 at 17:58
  • @FrankHeikens I am not solving a problem, but trying to see if anything there to improve the whole thing. The reason you say it's not matter with SSDs is because it handles random read efficiently? Commented Dec 12, 2024 at 1:37

1 Answer 1

Reset to default
1

Is there a common practice to align the database block size with the file system block size for OLTP?

Not really, no. The best filesystem block size will depend on too many other factors such as the stripe sizes (or similar based on technology), location (you may not actually know what remote storage is using under the covers), drivers, filesystem, and then the actual workload of the environment which is the most important part.

It doesn't help, for example, to se an 8k block size for an 8k allocation unit for a database system only to have most/all of the actual IO be done in 256 KB units. The graph for block size vs IOPs is generally inversely proportional, meaning that as block size increases the amount of IOPs decreases while overall throughput increases.

In a clustered system (e.g., SQL Server Availability Groups or PostgreSQL streaming replication) with a primary and one or more secondaries, is it acceptable to have different file system block sizes, or is this something that should always be avoided?

Ideally everything should be the same carbon-copies of each other. You can run it without being exactly the same, but depending on your OS/DBMS this may cause unforeseen performance issues. For example, if the system is doing physical replication at a block level but a block on one system is 512 bytes but a block on another system is 16kb (note this is at the hardware level). Having different filesystem block sizes generally works, with the same caveats.

For analytical databases or columnar tables, is it beneficial to use a larger block size?

It depends on the overall IO profile that is created by use of the features. Different features work in different ways. You're better off getting an ETW trace fo the IO profile for your DBMS to understand what IO sizes and types (synchronous/async) it prefers for that feature/workload.

Improve this answer
3
  • Yeah got it, I never seen someone doing this before as well. But there is people who recommends as modern system's support larger block sizes. What you think about it's usage with HUGE PAGES or THB? It's really not recommended to have HUGE PAGES in most cases but just wanted to know if they work together in a better way. Commented Dec 12, 2024 at 1:43
  • That has to do with the OS and hardware, which are both required to support them, and don't have to do with the various block or cache sizes. Two vastly different things. Commented Dec 12, 2024 at 14:07
  • Partition alignment matching the raid stripe was a valid concern back in the day (windows 2003) that could cause >10% penalty on writes with Raid 5. Performance differences related to allocation unit size are miniscule in comparison, and can effectively be ignored. Commented Dec 12, 2024 at 16:51

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.