PostgreSQL9.6 Parallel sequential scans 初体验 原作者:francs / 谭峰 创作时间:2016-10-17 12:29:31+08 |
doudou586 发布于2016-10-17 12:29:31 评论: 0 浏览: 10487 顶: 3129 踩: 3153 |
2016 Postgres大象会官方报名通道: 点此报名
Oracle 支持强大的并行功能,创建索引,表分析,数据加载时可以开启并行,这项功能让很多数据库产品垂涎, 作为开源数据库 PostgreSQL 在并行方面也在努力尝试,很早之前 PostgreSQL 几乎不支持任何并行的作业,到了 9.5 版本 PostgreSQL 支持并行的 vacuum,到了 9.6 后, PostgreSQL 支持并行的顺序扫描,这是令人振奋的消息,因为这极大的提升了 PostgreSQL 统计分析SQL的性能,由于硬件环境限制,今天简单体验下,以下实验在笔记本虚拟机上进行。
这个参数配置决定了每个 parallel query 并行操作允许的最大后台进程数,这个值的设置受后台进程数参数 max_worker_processes 限制。
[pg96@db1 ~]$ grep "max_parallel_degree" $PGDATA/postgresql.conf max_parallel_degree = 4 # max number of worker processes per node
[pg96@db1 ~]$ psql francs francs psql (9.6beta1) Type "help" for help. francs=> create table test_big1(id serial, name character varying(64), create_time timestamp(0) without time zone); CREATE TABLE francs=> insert into test_big1(id,name)select n, n||'_test' from generate_series(1,5000000)n; INSERT 0 5000000
francs=> explain analyze select count(*) from test_big1; ------------------------------------------------------------------- Finalize Aggregate (cost=45560.42..45560.43 rows=1 width=8) (actual time=4236.468..4236.469 rows=1 loops=1) -> Gather (cost=45560.00..45560.41 rows=4 width=8) (actual time=4232.517..4232.556 rows=5 loops=1) Workers Planned: 4 Workers Launched: 4 -> Partial Aggregate (cost=44560.00..44560.01 rows=1 width=8) (actual time=4182.972..4182.973 rows=1 loops=5) -> Parallel Seq Scan on test_big1 (cost=0.00..41435.00 rows=1250000 width=0) (actual time=0.034..2450.966 rows=1000000 loops=5) Planning time: 112.309 ms Execution time: 4236.920 ms (8 rows)
备注:执行多次,执行时间大概都在4秒多点,从执行计划中看到走了并行顺序扫描“Parallel Seq Scan on test_big1”,再细看“Workers Launched: 4”,表示开启了四个并行进程。
top - 20:59:28 up 3 days, 19:00, 3 users, load average: 0.49, 0.57, 0.39 Tasks: 185 total, 7 running, 178 sleeping, 0 stopped, 0 zombie Cpu(s): 5.8%us, 26.4%sy, 0.0%ni, 62.9%id, 4.0%wa, 0.2%hi, 0.8%si, 0.0%st Mem: 249112K total, 245312k used, 3800k free, 820k buffers Swap: 1605624k total, 627336k used, 978288k free, 193532k cached PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 4967 pg96 20 0 181m 8836 8072 R 15.6 3.5 0:00.47 postgres: bgworker:parallel worker for PID 4706 4706 pg96 20 0 181m 134m 133m R 13.6 55.2 0:53.77 postgres: francs francs [local] EXPLAIN 4965 pg96 20 0 181m 9712 8948 R 11.9 3.9 0:00.36 postgres: bgworker:parallel worker for PID 4706 4968 pg96 20 0 181m 8768 8004 R 11.3 3.5 0:00.34 postgres: bgworker:parallel worker for PID 4706 4966 pg96 20 0 181m 8632 7868 R 10.9 3.5 0:00.33 postgres: bgworker:parallel worker for PID 4706
备注:图中可看到出现了四个 parallel worker 进程。
测试之前先把 PostgreSQL 9.6 的数据库关了,在确保相等情况下进行测试。
[pg95@db1 ~]$ psql fdb fdb psql (9.5alpha1) Type "help" for help. fdb=> create table test_big1(id serial, name character varying(64),create_time timestamp(0) without time zone); CREATE TABLE fdb=> insert into test_big1(id,name)select n, n||'_test' from generate_series(1,5000000)n; INSERT 0 5000000 fdb=> explain analyze select count(*) from test_big1; QUERY PLAN ------------------------------------------------------ Aggregate (cost=91435.00..91435.01 rows=1 width=0) (actual time=8389.093..8389.094 rows=1 loops=1) -> Seq Scan on test_big1 (cost=0.00..78935.00 rows=5000000 width=0) (actual time=9.958..4781.116 rows=5000000 loops=1) Planning time: 2.436 ms Execution time: 8391.758 ms (4 rows)
备注:多次执行,时间在 8 秒左右。
由于硬件关系原因,本测试只在笔记本虚拟机上测试,在这个全表扫描测试场景下, PostgreSQL 9.6 是 PostgreSQL9.5 性能的两倍,今天仅是初步体验并行扫描,可以预测如果在X86服务器上测试,这个性能倍数会高一些,后续测试并行扫描的其它场景;个人认为 PostgreSQL 对并行顺序扫描的支持,在统计分析性能方面的提升前进了一大步。
2016 Postgres大象会官方报名通道:http://www.huodongxing.com/event/8352217821400
扫描报名