ReaderMX.
PostgreSQL码农集散地

PostgreSQL 18 preview - 调整 BAS_BULKREAD 算法提升并发IO性能

Pdigoal约 6 分钟

PostgreSQL 18 preview - 调整 BAS_BULKREAD 算法适配 并发IO请求 和 合并IO大小

PostgreSQL 18 调整 BAS_BULKREAD 算法适配 并发IO请求 和 IO合并上限, 以使得PostgreSQL能够更好地利用现代存储设备的性能，同时避免了过度分配内存带来的性能问题。

原来BAS_BULKREAD是简单地增大固定值, 太小无法利用并发IO提升性能, 太大则可能预读已缓存的页面浪费存储带宽.

调整后 BAS_BULKREAD 取决于 io_combine_limit , effective_io_concurrency.

除此以外, BAS_BULKREAD上限还与shared buffer有关GetPinLimit() * (BLCKSZ / 1024).  https://doxygen.postgresql.org/bufmgr_8c_source.html#l00218

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=15f0cb26b530b6725a37391738cfc62d4745c49b

Increase BAS_BULKREAD based on effective_io_concurrency master github/master  
author Andres Freund <[email protected]>   
Tue, 8 Apr 2025 06:41:03 +0000 (02:41 -0400)  
committer Andres Freund <[email protected]>   
Tue, 8 Apr 2025 06:41:03 +0000 (02:41 -0400)  
commit 15f0cb26b530b6725a37391738cfc62d4745c49b  
tree 68c3efd7b463403275b89c8984c7b214d560003f tree  
parent dcf7e1697ba75ce7883ad7c6bc26ba24422eb892 commit | diff  
Increase BAS_BULKREAD based on effective_io_concurrency  
Before, BAS_BULKREAD was always of size 256kB. With the default  
io_combine_limit of 16, that only allowed 1-2 IOs to be in flight -  
insufficient even on very low latency storage.  
We don't just want to increase the size to a much larger hardcoded value, as  
very large rings (10s of MBs of of buffers), appear to have negative  
performance effects when reading in data that the OS has cached (but not when  
actually needing to do IO).  
To address this, increase the size of BAS_BULKREAD to allow for  
io_combine_limit * effective_io_concurrency buffers getting read in. To  
prevent the ring being much larger than useful, limit the increased size with  
GetPinLimit().  
The formula outlined above keeps the ring size to sizes for which we have not  
observed performance regressions, unless very large effective_io_concurrency  
values are used together with large shared_buffers setting.  
Reviewed-by: Thomas Munro <[email protected]>  
Discussion: https://postgr.es/m/lqwghabtu2ak4wknzycufqjm5ijnxhb4k73vzphlt2a3wsemcd@gtftg44kdim6  
Discussion: https://postgr.es/m/uvrtrknj4kdytuboidbhwclo4gxhswwcpgadptsjvjqcluzmah@brqs62irg4dt  

相关代码

src/backend/storage/buffer/freelist.c

case BAS_BULKREAD:  
-           ring_size_kb = 256;  
-           break;  
+           {  
+               int         ring_max_kb;  
+  
+               /*  
+                * The ring always needs to be large enough to allow some  
+                * separation in time between providing a buffer to the user  
+                * of the strategy and that buffer being reused. Otherwise the  
+                * user's pin will prevent reuse of the buffer, even without  
+                * concurrent activity.  
+                *  
+                * We also need to ensure the ring always is large enough for  
+                * SYNC_SCAN_REPORT_INTERVAL, as noted above.  
+                *  
+                * Thus we start out a minimal size and increase the size  
+                * further if appropriate.  
+                */  
+               ring_size_kb = 256;  
+  
+               /*  
+                * There's no point in a larger ring if we won't be allowed to  
+                * pin sufficiently many buffers.  But we never limit to less  
+                * than the minimal size above.  
+                */  
+               ring_max_kb = GetPinLimit() * (BLCKSZ / 1024);  
+               ring_max_kb = Max(ring_size_kb, ring_max_kb);  
+  
+               /*  
+                * We would like the ring to additionally have space for the  
+                * the configured degree of IO concurrency. While being read  
+                * in, buffers can obviously not yet be reused.  
+                *  
+                * Each IO can be up to io_combine_limit blocks large, and we  
+                * want to start up to effective_io_concurrency IOs.  
+                *  
+                * Note that effective_io_concurrency may be 0, which disables  
+                * AIO.  
+                */  
+               ring_size_kb += (BLCKSZ / 1024) *  
+                   io_combine_limit * effective_io_concurrency;  
+  
+               if (ring_size_kb > ring_max_kb)  
+                   ring_size_kb = ring_max_kb;  
+               break;  
+           }  

相关参数

effective_io_concurrency (integer)

Sets the number of concurrent storage I/O operations that PostgreSQL expects can be executed simultaneously. Raising this value will increase the number of I/O operations that any individual PostgreSQL session attempts to initiate in parallel. The allowed range is 1 to 1000, or 0 to disable issuance of asynchronous I/O requests. The default is 16.

Higher values will have the most impact on higher latency storage where queries otherwise experience noticeable I/O stalls and on devices with high IOPs. Unnecessarily high values may increase I/O latency for all queries on the system

On systems with prefetch advice support, effective_io_concurrency also controls the prefetch distance.

This value can be overridden for tables in a particular tablespace by setting the tablespace parameter of the same name (see ALTER TABLESPACE).

maintenance_io_concurrency (integer)

Similar to effective_io_concurrency, but used for maintenance work that is done on behalf of many client sessions.

The default is 16. This value can be overridden for tables in a particular tablespace by setting the tablespace parameter of the same name (see ALTER TABLESPACE).

io_max_combine_limit (integer)

Controls the largest I/O size in operations that combine I/O, and silently limits the user-settable parameter io_combine_limit. This parameter can only be set in the postgresql.conf file or on the server command line. The maximum possible size depends on the operating system and block size, but is typically 1MB on Unix and 128kB on Windows. The default is 128kB.

io_combine_limit (integer)

Controls the largest I/O size in operations that combine I/O. If set higher than the io_max_combine_limit parameter, the lower value will silently be used instead, so both may need to be raised to increase the I/O size. The maximum possible size depends on the operating system and block size, but is typically 1MB on Unix and 128kB on Windows. The default is 128kB.

AI 解读

PostgreSQL补丁解读：基于effective_io_concurrency调整BAS_BULKREAD大小

这个补丁修改了PostgreSQL中BAS_BULKREAD缓冲环(ring buffer)大小的计算方式，使其能够更好地适应不同的I/O并发需求。

问题背景

  1. 原有问题：

  • BAS_BULKREAD缓冲环大小固定为256KB
  • 默认io_combine_limit=16时，只能支持1-2个并发I/O操作
  • 即使在低延迟存储设备上，这样的并发能力也显得不足

  • 潜在风险：

    • 简单地增大固定值会导致缓冲环过大（几十MB）
    • 当操作系统已缓存数据时，过大的缓冲环会对性能产生负面影响

    解决方案

    1. 动态计算缓冲环大小：

    • 保持256KB的基础大小
    • 额外增加空间以支持配置的I/O并发度
    • 计算公式：基础大小 + (块大小/1024) * io_combine_limit * effective_io_concurrency

  • 安全限制：

    • 使用GetPinLimit()获取最大允许的缓冲环大小
    • 最终大小不超过这个限制值
    • 确保不会小于基础大小256KB

    代码实现细节

    1. 基础大小：

      ring_size_kb = 256;  

    2. 计算最大允许大小：

      ring_max_kb = GetPinLimit() * (BLCKSZ / 1024);  
      ring_max_kb = Max(ring_size_kb, ring_max_kb);  

    3. 增加I/O并发所需空间：

      ring_size_kb += (BLCKSZ / 1024) * io_combine_limit * effective_io_concurrency;  

    4. 应用上限限制：

      if (ring_size_kb > ring_max_kb)  
          ring_size_kb = ring_max_kb;  

    设计考虑

    1. 最小缓冲区保障：

    • 确保有足够空间让用户使用缓冲区和缓冲区重用之间有时间间隔
    • 满足SYNC_SCAN_REPORT_INTERVAL的需求

  • 并发支持：

    • 每个I/O操作最多包含io_combine_limit个块
    • 支持启动最多effective_io_concurrency个并发I/O
    • 当effective_io_concurrency=0时禁用异步I/O

  • 性能平衡：

    • 避免过大缓冲环带来的性能下降
    • 只在同时使用大effective_io_concurrency和大shared_buffers时才可能增大缓冲环

    这个改进使得PostgreSQL能够更好地利用现代存储设备的性能，同时避免了过度分配内存带来的性能问题。

    KEEP THE THREAD GOING

    更多来源文章