开发者社区> 德哥> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

PostgreSQL 异步IO实测

简介:
+关注继续查看

标签

PostgreSQL , effective_io_concurrency , 异步IO


背景

异步IO的目的是充分发挥块设备的吞吐能力,让块设备处于更繁忙的工作状态(一次连续摄取更多的块),而不是等用户进程需要数据时再读取。

如果数据库并发连接(或者活跃会话)足够时,并且块设备处于繁忙状态,那么没有必要开启异步IO,因为开了也没什么用,块设备已经足够的忙了。

目前PostgreSQL的bitmap heap scan支持异步IO,因为bitmap heap scan是按顺序读取堆表的数据块的,对于机械硬盘,bitmap heap scan异步IO效率可以得到充分的发挥。(实际上全表扫描也适合异步IO。)

异步IO的参数effective_io_concurrency,应该如何设置呢?

如果是磁盘阵列,根据表空间所在的块设备进行设置,例如RAID0, RAID10,设置为磁盘个数,而RAID5或者其他RAID,设置为实际的数据盘个数(如,raid5(10)设置为9)。

如果设置为0,表示不使用异步IO。

仅仅当操作系统支持posix时,才能使用异步IO。

effective_io_concurrency (integer)  
  
Sets the number of concurrent disk 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 zero to disable issuance of asynchronous I/O requests. Currently, this setting only affects bitmap heap scans.  
  
For magnetic drives, a good starting point for this setting is the number of separate drives comprising a RAID 0 stripe or RAID 1 mirror being used for the database.   
(For RAID 5 the parity drive should not be counted.)   
However, if the database is often busy with multiple queries issued in concurrent sessions, lower values may be sufficient to keep the disk array busy.   
A value higher than needed to keep the disks busy will only result in extra CPU overhead.   
SSDs and other memory-based storage can often process many concurrent requests, so the best value might be in the hundreds.  
  
Asynchronous I/O depends on an effective posix_fadvise function, which some operating systems lack.   
If the function is not present then setting this parameter to anything but zero will result in an error.   
On some operating systems (e.g., Solaris), the function is present but does not actually do anything.  
  
The default is 1 on supported systems, otherwise 0.   
This value can be overridden for tables in a particular tablespace by setting the tablespace parameter of the same name (see ALTER TABLESPACE).  

那么生产中应该如何设置呢?

我们实际测试一下,如果是SSD,从测试结果看,建议关闭异步IO。(虽然手册中提到如果是内存盘或SSD盘,可以设置更大的值,但是实测效果不理想。)

测试用例

src/test/regress/expected/select_parallel.out

-- test parallel bitmap heap scan.  
set enable_seqscan to off;  
set enable_indexscan to off;  
set enable_hashjoin to off;  
set enable_mergejoin to off;  
set enable_material to off;  
-- test prefetching, if the platform allows it  
DO $$  
BEGIN  
 SET effective_io_concurrency = 50;  
EXCEPTION WHEN invalid_parameter_value THEN  
END $$;  
set work_mem='64kB';  --set small work mem to force lossy pages  
explain (costs off)  
        select count(*) from tenk1, tenk2 where tenk1.hundred > 1 and tenk2.thousand=0;  
                         QUERY PLAN                           
------------------------------------------------------------  
 Aggregate  
   ->  Nested Loop  
         ->  Seq Scan on tenk2  
               Filter: (thousand = 0)  
         ->  Gather  
               Workers Planned: 4  
               ->  Parallel Bitmap Heap Scan on tenk1  
                     Recheck Cond: (hundred > 1)  
                     ->  Bitmap Index Scan on tenk1_hundred  
                           Index Cond: (hundred > 1)  
(10 rows)  
  
select count(*) from tenk1, tenk2 where tenk1.hundred > 1 and tenk2.thousand=0;  
 count   
-------  
 98000  
(1 row)  
  
create table bmscantest (a int, t text);  
insert into bmscantest select r, 'fooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo' FROM generate_series(1,100000) r;  
create index i_bmtest ON bmscantest(a);  
select count(*) from bmscantest where a>1;  
 count   
-------  
 99999  
(1 row)  
  
reset enable_seqscan;  
reset enable_indexscan;  
reset enable_hashjoin;  
reset enable_mergejoin;  
reset enable_material;  
reset effective_io_concurrency;  
reset work_mem;  
drop table bmscantest;  

测试

postgres=# select pg_backend_pid();  
 pg_backend_pid   
----------------  
          42328  
(1 row)  
  
postgres=# set enable_seqscan=off;  
SET  
postgres=# set enable_indexscan=off;  
SET  
postgres=# set work_mem ='64kB';  
SET  
postgres=# set max_parallel_workers_per_gather =0;  
SET  
  
  
postgres=# explain (analyze,verbose,timing,costs,buffers) select count(*) from ptest where id>1000 ;  
                                                                  QUERY PLAN                                                                     
-----------------------------------------------------------------------------------------------------------------------------------------------  
 Aggregate  (cost=338227.11..338227.12 rows=1 width=8) (actual time=2634.805..2634.806 rows=1 loops=1)  
   Output: count(*)  
   Buffers: shared hit=110649  
   ->  Bitmap Heap Scan on public.ptest  (cost=104910.47..313230.01 rows=9998843 width=0) (actual time=418.257..1735.802 rows=9999000 loops=1)  
         Output: id, info  
         Recheck Cond: (ptest.id > 1000)  
         Rows Removed by Index Recheck: 40  
         Heap Blocks: exact=408 lossy=82918  
         Buffers: shared hit=110649  
         ->  Bitmap Index Scan on idx_ptest1  (cost=0.00..102410.76 rows=9998843 width=0) (actual time=418.158..418.158 rows=9999000 loops=1)  
               Index Cond: (ptest.id > 1000)  
               Buffers: shared hit=27323  
 Planning time: 0.123 ms  
 Execution time: 2634.858 ms  
(14 rows)  
  
postgres=# set effective_io_concurrency =0;  
SET  
postgres=# explain (analyze,verbose,timing,costs,buffers) select count(*) from ptest where id>1000 ;  
                                                                  QUERY PLAN                                                                     
-----------------------------------------------------------------------------------------------------------------------------------------------  
 Aggregate  (cost=338227.11..338227.12 rows=1 width=8) (actual time=2619.868..2619.869 rows=1 loops=1)  
   Output: count(*)  
   Buffers: shared hit=110649  
   ->  Bitmap Heap Scan on public.ptest  (cost=104910.47..313230.01 rows=9998843 width=0) (actual time=418.974..1721.578 rows=9999000 loops=1)  
         Output: id, info  
         Recheck Cond: (ptest.id > 1000)  
         Rows Removed by Index Recheck: 40  
         Heap Blocks: exact=408 lossy=82918  
         Buffers: shared hit=110649  
         ->  Bitmap Index Scan on idx_ptest1  (cost=0.00..102410.76 rows=9998843 width=0) (actual time=418.873..418.873 rows=9999000 loops=1)  
               Index Cond: (ptest.id > 1000)  
               Buffers: shared hit=27323  
 Planning time: 0.155 ms  
 Execution time: 2619.930 ms  
(14 rows)  
  
postgres=# set effective_io_concurrency =1000;  
SET  
postgres=# explain (analyze,verbose,timing,costs,buffers) select count(*) from ptest where id>1000 ;  
                                                                  QUERY PLAN                                                                     
-----------------------------------------------------------------------------------------------------------------------------------------------  
 Aggregate  (cost=338227.11..338227.12 rows=1 width=8) (actual time=2661.584..2661.585 rows=1 loops=1)  
   Output: count(*)  
   Buffers: shared hit=110649  
   ->  Bitmap Heap Scan on public.ptest  (cost=104910.47..313230.01 rows=9998843 width=0) (actual time=417.591..1761.522 rows=9999000 loops=1)  
         Output: id, info  
         Recheck Cond: (ptest.id > 1000)  
         Rows Removed by Index Recheck: 40  
         Heap Blocks: exact=408 lossy=82918  
         Buffers: shared hit=110649  
         ->  Bitmap Index Scan on idx_ptest1  (cost=0.00..102410.76 rows=9998843 width=0) (actual time=417.473..417.473 rows=9999000 loops=1)  
               Index Cond: (ptest.id > 1000)  
               Buffers: shared hit=27323  
 Planning time: 0.113 ms  
 Execution time: 2661.632 ms  
(14 rows)  
  
  
postgres=# set max_parallel_workers_per_gather =4;  
SET  
postgres=# set effective_io_concurrency =1000;  
SET  
postgres=# explain (analyze,verbose,timing,costs,buffers) select count(*) from ptest where id>1000 ;  
                                                                            QUERY PLAN                                                                               
-------------------------------------------------------------------------------------------------------------------------------------------------------------------  
 Finalize Aggregate  (cost=226740.55..226740.56 rows=1 width=8) (actual time=1017.452..1017.452 rows=1 loops=1)  
   Output: count(*)  
   Buffers: shared hit=111309  
   ->  Gather  (cost=226740.13..226740.54 rows=4 width=8) (actual time=1017.350..1017.447 rows=5 loops=1)  
         Output: (PARTIAL count(*))  
         Workers Planned: 4  
         Workers Launched: 4  
         Buffers: shared hit=111309  
         ->  Partial Aggregate  (cost=225740.13..225740.14 rows=1 width=8) (actual time=1014.227..1014.227 rows=1 loops=5)  
               Output: PARTIAL count(*)  
               Buffers: shared hit=110649  
               Worker 0: actual time=1013.492..1013.492 rows=1 loops=1  
                 Buffers: shared hit=16355  
               Worker 1: actual time=1013.497..1013.497 rows=1 loops=1  
                 Buffers: shared hit=16269  
               Worker 2: actual time=1013.510..1013.510 rows=1 loops=1  
                 Buffers: shared hit=16333  
               Worker 3: actual time=1013.501..1013.501 rows=1 loops=1  
                 Buffers: shared hit=16268  
               ->  Parallel Bitmap Heap Scan on public.ptest  (cost=104910.47..219490.85 rows=2499711 width=0) (actual time=418.784..840.764 rows=1999800 loops=5)  
                     Recheck Cond: (ptest.id > 1000)  
                     Rows Removed by Index Recheck: 8  
                     Heap Blocks: exact=92 lossy=18009  
                     Buffers: shared hit=110649  
                     Worker 0: actual time=417.217..844.848 rows=1962600 loops=1  
                       Buffers: shared hit=16355  
                     Worker 1: actual time=422.223..844.926 rows=1952280 loops=1  
                       Buffers: shared hit=16269  
                     Worker 2: actual time=417.216..844.028 rows=1959960 loops=1  
                       Buffers: shared hit=16333  
                     Worker 3: actual time=417.216..840.404 rows=1952080 loops=1  
                       Buffers: shared hit=16268  
                     ->  Bitmap Index Scan on idx_ptest1  (cost=0.00..102410.76 rows=9998843 width=0) (actual time=419.867..419.867 rows=9999000 loops=1)  
                           Index Cond: (ptest.id > 1000)  
                           Buffers: shared hit=27323  
 Planning time: 0.121 ms  
 Execution time: 1027.083 ms  
(37 rows)  
postgres=# set effective_io_concurrency =1;  
SET  
postgres=# explain (analyze,verbose,timing,costs,buffers) select count(*) from ptest where id>1000 ;  
                                                                            QUERY PLAN                                                                               
-------------------------------------------------------------------------------------------------------------------------------------------------------------------  
 Finalize Aggregate  (cost=226740.55..226740.56 rows=1 width=8) (actual time=1006.182..1006.182 rows=1 loops=1)  
   Output: count(*)  
   Buffers: shared hit=111309  
   ->  Gather  (cost=226740.13..226740.54 rows=4 width=8) (actual time=1006.073..1006.177 rows=5 loops=1)  
         Output: (PARTIAL count(*))  
         Workers Planned: 4  
         Workers Launched: 4  
         Buffers: shared hit=111309  
         ->  Partial Aggregate  (cost=225740.13..225740.14 rows=1 width=8) (actual time=1003.124..1003.124 rows=1 loops=5)  
               Output: PARTIAL count(*)  
               Buffers: shared hit=110649  
               Worker 0: actual time=1002.447..1002.447 rows=1 loops=1  
                 Buffers: shared hit=16408  
               Worker 1: actual time=1002.441..1002.441 rows=1 loops=1  
                 Buffers: shared hit=16312  
               Worker 2: actual time=1002.446..1002.446 rows=1 loops=1  
                 Buffers: shared hit=16093  
               Worker 3: actual time=1002.436..1002.436 rows=1 loops=1  
                 Buffers: shared hit=16287  
               ->  Parallel Bitmap Heap Scan on public.ptest  (cost=104910.47..219490.85 rows=2499711 width=0) (actual time=413.650..828.524 rows=1999800 loops=5)  
                     Recheck Cond: (ptest.id > 1000)  
                     Rows Removed by Index Recheck: 8  
                     Heap Blocks: exact=85 lossy=18141  
                     Buffers: shared hit=110649  
                     Worker 0: actual time=412.974..833.142 rows=1968960 loops=1  
                       Buffers: shared hit=16408  
                     Worker 1: actual time=412.994..831.530 rows=1957440 loops=1  
                       Buffers: shared hit=16312  
                     Worker 2: actual time=412.980..831.353 rows=1931160 loops=1  
                       Buffers: shared hit=16093  
                     Worker 3: actual time=412.977..828.009 rows=1954440 loops=1  
                       Buffers: shared hit=16287  
                     ->  Bitmap Index Scan on idx_ptest1  (cost=0.00..102410.76 rows=9998843 width=0) (actual time=416.164..416.164 rows=9999000 loops=1)  
                           Index Cond: (ptest.id > 1000)  
                           Buffers: shared hit=27323  
 Planning time: 0.124 ms  
 Execution time: 1015.976 ms  
(37 rows)  
  
postgres=# set effective_io_concurrency =0;  
SET  
postgres=# explain (analyze,verbose,timing,costs,buffers) select count(*) from ptest where id>1000 ;  
                                                                            QUERY PLAN                                                                               
-------------------------------------------------------------------------------------------------------------------------------------------------------------------  
 Finalize Aggregate  (cost=226740.55..226740.56 rows=1 width=8) (actual time=943.288..943.288 rows=1 loops=1)  
   Output: count(*)  
   Buffers: shared hit=111309  
   ->  Gather  (cost=226740.13..226740.54 rows=4 width=8) (actual time=943.170..943.282 rows=5 loops=1)  
         Output: (PARTIAL count(*))  
         Workers Planned: 4  
         Workers Launched: 4  
         Buffers: shared hit=111309  
         ->  Partial Aggregate  (cost=225740.13..225740.14 rows=1 width=8) (actual time=940.060..940.060 rows=1 loops=5)  
               Output: PARTIAL count(*)  
               Buffers: shared hit=110649  
               Worker 0: actual time=939.276..939.276 rows=1 loops=1  
                 Buffers: shared hit=16286  
               Worker 1: actual time=939.387..939.388 rows=1 loops=1  
                 Buffers: shared hit=16303  
               Worker 2: actual time=939.389..939.390 rows=1 loops=1  
                 Buffers: shared hit=16291  
               Worker 3: actual time=939.388..939.389 rows=1 loops=1  
                 Buffers: shared hit=15943  
               ->  Parallel Bitmap Heap Scan on public.ptest  (cost=104910.47..219490.85 rows=2499711 width=0) (actual time=419.160..766.586 rows=1999800 loops=5)  
                     Recheck Cond: (ptest.id > 1000)  
                     Rows Removed by Index Recheck: 8  
                     Heap Blocks: exact=92 lossy=18411  
                     Buffers: shared hit=110649  
                     Worker 0: actual time=418.404..771.100 rows=1954320 loops=1  
                       Buffers: shared hit=16286  
                     Worker 1: actual time=418.487..770.127 rows=1956360 loops=1  
                       Buffers: shared hit=16303  
                     Worker 2: actual time=418.491..770.200 rows=1954920 loops=1  
                       Buffers: shared hit=16291  
                     Worker 3: actual time=418.513..768.309 rows=1913160 loops=1  
                       Buffers: shared hit=15943  
                     ->  Bitmap Index Scan on idx_ptest1  (cost=0.00..102410.76 rows=9998843 width=0) (actual time=421.750..421.750 rows=9999000 loops=1)  
                           Index Cond: (ptest.id > 1000)  
                           Buffers: shared hit=27323  
 Planning time: 0.169 ms  
 Execution time: 952.028 ms  
(37 rows)  

分别使用perf record -ag -p $pid 进行了跟踪。因为这个版本编译时没有打开几个需要的开关(-g -ggdb -fno-omit-frame-pointer),所以看不出什么问题。用户可以自己测试并分析一下。

参考

《PostgreSQL on Linux 最佳部署手册》

《Linux 性能诊断 perf使用指南》

《PostgreSQL 源码性能诊断(perf profiling)指南》

https://www.postgresql.org/docs/devel/static/runtime-config-resource.html

src/backend/executor/nodeBitmapHeapscan.c

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
【实操】数据库RDS迁移实操-DTS服务
帮您快速掌握数据库如何迁移。
1780 0
RDS数据库和ECS自建数据库主从复制,数据同步
设置阿里云RDS和ECS上自建的数据库达成主从分离。
17173 0
MSSQL · 最佳实践 · 实例级别数据库上云RDS SQL Server
摘要 到目前,我们完成了SQL Server备份还原专题系列八篇月报分享:三种常见的数据库备份、备份策略的制定、查找备份链、数据库的三种恢复模式与备份之间的关系、利用文件组实现冷热数据隔离备份方案、如何监控备份还原进度、阿里云RDS SQL自动化迁移上云的一种解决方案以及上个月分享的RDS SDK实现数据库迁移上阿里云,本期我们分享如何将用户线下或者ECS上自建实例级别数据库一键迁移上阿里云RDS SQL Server。
1673 0
阿里云RDS金融数据库(三节点版) - 性能篇
标签 PostgreSQL , MySQL , 三节点版 , 金融数据库 , Raft , 分布式共享存储版 背景 终于到了性能篇,三节点同时满足了企业对数据库的可用性、可靠性的要求,那么性能如何呢? 提到性能测试,我有几点一定要说明一下,很多朋友对性能的理解可能有偏差,那么如何评判性能
4777 0
阿里云RDS金融数据库(三节点版) - 理论篇
标签 PostgreSQL , MySQL , 三节点版 , 金融数据库 , Raft , 分布式共享存储版 背景 《阿里云RDS金融数据库(三节点版) - 背景篇》说明了为什么需要推出金融级数据库的三节点版本,以及三节点引入的一个世界难题 - 拜占庭将军问题。
4938 0
性能测试:自建数据库与RDS性能对比SQL Server案例排查分析
近期经常遇到用户将自建数据库与RDS进行对比,简单的对比结果是自建库比RDS实例查询快。我们这里来看看一个实例,有一家物流公司,刚开始使用RDS SQL Server数据库,发现通过ECS访问RDS实例,执行语句需要60s左右,但是访问ECS本地自建库只需要2-3s。那么RDS是否是真的不如自建数据库呢? 接下来,我们来探讨对比自建库和RDS的正确姿势,如何公平地对比自建库和RDS的性能。对比自建
2974 0
玩转数据库新功能及应用—MySQL for RDS 新功能及应用场景
3月27日至29日,云栖大会于深圳举行。本次深圳带来了“安全&出海”两大主题,在3月28日闭门分享沙龙会场上,游戏行业专家针对“全球同服”、“阿里云数据库新特性”、“大数据运营方案”、“游戏安全保障”进行了深入沟通。
2894 0
性能测试:自建数据库对比RDS中应当注意的地方(适用于MySQL,SQL SERVER,MongoDB)
常常很多用户对比测试自建数据库和RDS的性能差异,其测试结果往往是RDS不如ECS自建,用户往往怀疑难道我花了那么多的钱买的RDS难道还不如自己在ECS上搭建吗?
16250 0
创建RDS到分析型数据库AnalyticDB数据实时同步
数据传输服务DTS支持RDS到分析型数据库AnalyticDB之间的数据实时同步。通过DTS提供的RDS->AnalyticDB实时同步功能,可以将将企业数据集中分析,实现企业内部各种BI、CRM、实时报表系统等。
3302 0
+关注
德哥
公益是一辈子的事, I am digoal, just do it.
文章
问答
来源圈子
更多
让用户数据永远在线,让数据无缝的自由流动
+ 订阅
文章排行榜
最热
最新
相关电子书
更多
PostgresChina2018_权宗亮_基于odyssey连接池实现企业级PostgreSQL数据分布中间件
立即下载
多IO线程优化版
立即下载
PostgresChina2018_曾文旌_阿里云RDS_for_PostgreSQL在PostgreSQL功能和性能改进
立即下载