DEV Community

Cover image for Day 75/100 - Bypassing the Page Cache for ClickHouse® I/O Operations
Kanishga Subramani
Kanishga Subramani

Posted on

Day 75/100 - Bypassing the Page Cache for ClickHouse® I/O Operations

#clickhouse #devops #database #dataengineering

Bypassing the Page Cache for ClickHouse® I/O Operations

Introduction

When discussing database performance, one recommendation often appears:

"Bypass the operating system's page cache."

While this advice can be valuable in specific scenarios, it's frequently presented without explaining what the page cache actually does, why ClickHouse® relies on it by default, or when bypassing it improves performance.

The Linux page cache is designed to accelerate file access by keeping recently read data in memory. For many workloads, this significantly reduces disk I/O and improves query latency. However, for large analytical scans that read vast amounts of data only once, populating the page cache may consume valuable memory without providing any future benefit.

ClickHouse® offers the ability to bypass the Linux page cache using Direct I/O, allowing qualifying reads to access storage directly. This behavior is controlled through the min_bytes_to_use_direct_io setting.

In this article, we'll explore how the Linux page cache works, how ClickHouse® interacts with it, what Direct I/O changes, and when enabling it can improve analytical workloads.

Understanding the Linux Page Cache

Before examining ClickHouse®, it's important to understand the role of the Linux page cache.

Whenever an application performs a standard buffered file read, Linux stores the retrieved data in an in-memory cache known as the page cache.

If the same data is requested again:

  • Linux serves it directly from memory if it already exists in the cache.
  • Otherwise, it reads the data from disk and stores it in the page cache for future access.

This mechanism is completely transparent to applications. Most software doesn't need to implement its own file caching because the operating system automatically manages frequently accessed data.

For many workloads, this dramatically reduces disk access and improves performance.

How ClickHouse® Reads Data by Default

Contrary to a common misconception, ClickHouse® does not bypass the Linux page cache by default.

Instead, ClickHouse performs buffered file reads, allowing Linux to decide what should remain cached.

The default read path looks like this:

          Query
            │
            ▼
      ClickHouse®
            │
     Buffered Read
            │
            ▼
   Linux Page Cache
            │
            ▼
     Storage Device
Enter fullscreen mode Exit fullscreen mode

If another query accesses the same data shortly afterward, Linux may satisfy the request directly from memory instead of reading from storage again.

For workloads involving frequently accessed data, this behavior significantly improves performance.

Why the Page Cache Isn't Always Ideal

Although the page cache is extremely useful, it isn't always the optimal choice.

Imagine a query scanning several hundred gigabytes of historical data exactly once.

Linux loads every accessed page into memory—even if those pages are unlikely to be read again.

This creates two potential problems.

1. Cache Pollution

Large sequential scans can fill the page cache with data that has little chance of being reused.

As a result, frequently accessed pages may be evicted from memory.

This phenomenon is known as cache pollution.

2. Memory Competition

The Linux page cache consumes available RAM.

At the same time, ClickHouse also requires memory for operations such as:

  • Query execution
  • Hash tables
  • Aggregations
  • Sorting
  • JOIN operations
  • Internal caches

Allowing very large scans to populate the page cache reduces the memory available for these operations.

What Does "Bypassing the Page Cache" Mean?

Bypassing the page cache means reading data directly from storage instead of allowing Linux to cache file contents.

On Linux, this is achieved using the O_DIRECT file flag.

Instead of the traditional read path:

Application
     │
     ▼
Page Cache
     │
     ▼
Disk
Enter fullscreen mode Exit fullscreen mode

the data path becomes:

Application
     │
     ▼
Disk
Enter fullscreen mode Exit fullscreen mode

In this mode:

  • File data isn't stored in the Linux page cache.
  • Future reads won't benefit from cached file pages.
  • RAM is preserved for other database operations.

It's important to understand that Direct I/O bypasses the page cache only for the specific file operations that use O_DIRECT.

It does not disable the page cache for the entire operating system.

How ClickHouse® Enables Direct I/O

ClickHouse controls Direct I/O using the following setting:

min_bytes_to_use_direct_io
Enter fullscreen mode Exit fullscreen mode

This setting defines the minimum amount of data a query must read before ClickHouse switches from buffered I/O to Direct I/O.

Example:

SET min_bytes_to_use_direct_io = 104857600;
Enter fullscreen mode Exit fullscreen mode

This configures ClickHouse to use Direct I/O whenever a query is expected to read more than 100 MB of data.

If the setting remains:

0
Enter fullscreen mode Exit fullscreen mode

Direct I/O is disabled, which is also the default behavior.

Why Isn't Direct I/O Enabled by Default?

A common question is:

If Direct I/O reduces memory usage, why doesn't ClickHouse always use it?

The answer is simple.

The Linux page cache is extremely effective for workloads involving repeated access to the same data.

Examples include:

  • BI dashboards
  • Frequently executed reports
  • Hot partitions
  • Interactive analytics
  • Repeated ad-hoc queries

In these situations, Linux serves many reads directly from RAM.

If Direct I/O were always enabled, every query would need to fetch data from storage again, potentially increasing latency.

For this reason, ClickHouse uses buffered I/O by default and allows administrators to enable Direct I/O only when appropriate.

Direct I/O Does Not Disable ClickHouse® Caches

Another common misconception is that enabling Direct I/O disables all caching inside ClickHouse.

It does not.

ClickHouse includes several independent caching mechanisms, including:

  • Mark Cache
  • Uncompressed Block Cache
  • Query Cache (when enabled)

These caches operate independently of the Linux page cache.

Enabling Direct I/O changes only how file data is read from storage.

ClickHouse's internal caches continue functioning normally.

When Direct I/O Makes Sense

Direct I/O is most beneficial for workloads that perform:

  • Very large sequential scans
  • One-time analytical queries
  • Bulk historical analysis
  • Large ETL operations
  • Data migration workloads

It's particularly useful when:

  • Data is unlikely to be read again
  • Cache pollution would reduce performance
  • Predictable memory usage is more important than maximizing cache hits

Many large-scale analytical workloads fall into this category.

When You Should Avoid Direct I/O

Direct I/O isn't a universal optimization.

It may actually reduce performance when:

  • The same data is queried repeatedly
  • The working dataset fits comfortably into memory
  • BI dashboards frequently access hot partitions
  • Storage latency is significantly higher than RAM latency

In these scenarios, the Linux page cache provides substantial performance benefits by serving repeated reads directly from memory.

Choosing Between Buffered I/O and Direct I/O

The right choice depends on your workload.

Buffered I/O Direct I/O
Repeated queries Large sequential scans
Hot datasets One-time scans
Dashboards Historical analytics
High cache reuse Low cache reuse
Faster repeated reads Lower cache pollution
Relies on Linux page cache Bypasses Linux page cache

There is no universally "better" option.

The optimal configuration depends on access patterns, available memory, storage performance, and workload characteristics.

Performance Considerations

Before enabling Direct I/O, consider the following:

  • Measure query latency before and after changes.
  • Monitor memory usage.
  • Observe page cache behavior using Linux monitoring tools.
  • Benchmark representative production workloads.
  • Avoid enabling Direct I/O solely because it's perceived as faster.

Performance tuning should always be guided by real workload measurements rather than general recommendations.

Key Takeaways

  • ClickHouse® uses the Linux page cache by default.
  • Direct I/O is enabled using min_bytes_to_use_direct_io.
  • Direct I/O relies on Linux's O_DIRECT mechanism.
  • Only qualifying reads bypass the page cache.
  • ClickHouse's internal caches continue working with Direct I/O enabled.
  • Direct I/O is best suited for very large sequential scans.
  • Buffered I/O often performs better for frequently accessed datasets.
  • Choosing between the two depends entirely on workload characteristics.

Conclusion

The Linux page cache plays an important role in accelerating file access by keeping frequently used data in memory, and ClickHouse® leverages this behavior by default through buffered I/O. For many analytical workloads—particularly those involving repeated queries, dashboards, or hot datasets—this provides significant performance benefits.

However, workloads that perform large one-time sequential scans can benefit from Direct I/O, which bypasses the page cache using Linux's O_DIRECT mechanism. This helps prevent cache pollution and preserves memory for query execution, aggregations, joins, and ClickHouse's own caching layers.

Ultimately, bypassing the page cache is not a universal performance optimization. It's a workload-specific tuning technique. Understanding your query patterns, memory usage, and storage characteristics is essential before deciding whether buffered I/O or Direct I/O is the better choice for your ClickHouse deployment.

Top comments (0)