PostgreSQL:effective_io_concurrency基准测试

PostgreSQL:effective_io_concurrency基准测试

引言

正如在一篇近期的帖子中所提到的，我会定期检查我心爱的数据库的所有设置，今天我想和大家聊聊一个名为"effective_io_concurrency"的设置。

effective_io_concurrency 告知 PostgreSQL 我们的磁盘有多快（或慢查询）。

它的默认值为1，手册中是这样定义的:

设置 PostgreSQL 期望可以同时执行的并发磁盘 I/O 作的数量。提高此值将增加任何单个 PostgreSQL 会话尝试并行启动的 I/O 作的数量。允许的范围为 1 到 1000，或零以禁用异步 I/O 请求的发出。目前，此设置仅影响位图堆扫描。

对于磁性驱动器，此设置的一个很好的起点是用于数据库的单独驱动器的数量，其中包括用于数据库的 RAID 0 条带或 RAID 1 镜像。（对于 RAID 5，不应计算奇偶校验驱动器。但是，如果数据库经常忙于在并发会话中发出的多个查询，那么较低的值可能足以使磁盘阵列保持忙碌。高于保持磁盘繁忙所需的值只会导致额外的 CPU 开销。SSD 和其他基于内存的存储通常可以处理许多并发请求，因此最佳值可能是数百个。 异步 I/O 依赖于有效的posix_fadvise功能，而某些作系统缺乏此功能。如果该函数不存在，则将此参数设置为零以外的任何值将导致错误。在某些作系统（例如 Solaris）上，该函数存在，但实际上不执行任何作。

effective_io_concurrency 原文：[1]

设置

Linux主机上的PostgreSQL 9.6。硬件配置32个内核。

为了测试位图堆扫描，我创建了一个临时表：

CREATE TABLE test (major int PRIMARY KEY, minor int);

……以及一个多列索引

CREATE INDEX minor_idx ON test (major, minor);

然后我对其进行了填充了数据：

INSERT INTO test VALUES ( generate_series(0,100000000), random()*1000 );

我选择了以下查询:

SELECT * FROM test WHERE major BETWEEN 10 AND 100 OR minor BETWEEN 800 AND 900;

查询计划确认存在位图堆扫描：

 Bitmap Heap Scan on test (cost=1279391.23..2027903.67 rows=10398562 width=12)
   Recheck Cond: (((major >= 10) AND (major <= 100)) OR ((minor >= 800) AND (min
or <= 900)))
   ->  BitmapOr  (cost=1279391.23..1279391.23 rows=10398572 width=0)
         ->  Bitmap Index Scan on minor_idx  (cost=0.00..2.50 rows=93 width=0)
               Index Cond: ((major >= 10) AND (major <= 100))
         ->  Bitmap Index Scan on minor_idx  (cost=0.00..1274189.45 rows=103984
79 width=0)
               Index Cond: ((minor >= 800) AND (minor <= 900))

该测试在一台配备固态硬盘且配置为RAID 10的机器上运行，但我在仅装有一块固态硬盘的机器（6核虚拟机）上也获得了类似的结果。

测试

由于我们正在测试磁盘性能，我会先清理缓存，然后重启PostgreSQL，之后再运行查询。

for i in {1..10} ; do echo 3 > /proc/sys/vm/drop_caches  ; /etc/init.d/postgresql restart  ; sleep 5 ;  psql  -c 'EXPLAIN ANALYZE SELECT * FROM test WHERE major BETWEEN 10 AND 100 OR minor BETWEEN 800 AND 900;' ; done

每组测试都针对不同的effective_io_concurrency值运行。

结果

提高该设置，可使查询速度提升4倍！

以下是在对比测试运行中磁盘受到的影响:

前6个峰值: effective_io_concurrency = 1 最近4个峰值: effective_io_concurrency = 100 此外，IOPS的数量也会受到影响，导致IOPS减少:

数值为20和1000时的图表与上面的图表相似，结果也是如此。

结论

调整数据库的effective_io_cache是非常值得的。

事实再次证明，花在探索、试验和调整上的时间是非常值得的！

原文来自于： PostgreSQL_effective_io_concurrency_benchmarked[2]

引用链接

[1] effective_io_concurrency 原文：: https://www.postgresql.org/docs/current/runtime-config-resource.html [2] PostgreSQL_effective_io_concurrency_benchmarked: https://portavita.github.io/2019-07-19-PostgreSQL_effective_io_concurrency_benchmarked/