ITPub博客

首页 > 数据库 > PostgreSQL > PostgreSQL DBA(172) - PG 13(WAL activity in EXPLAIN)

PostgreSQL DBA(172) - PG 13(WAL activity in EXPLAIN)

原创 PostgreSQL 作者:husthxd 时间:2020-04-14 18:19:26 0 删除 编辑

在PG 13中,explain命令增加了WAL选项,可查看命令执行过程中产生的WAL日志大小,对于写敏感的应用来说,可通过该命令观察WAL日志的统计信息.

新增的WAL选项

[local:/data/run/pg13]:5130 pg13@masterdb=# \help explain
Command:     EXPLAIN
Description: show the execution plan of a statement
Syntax:
EXPLAIN [ ( option [, ...] ) ] statement
EXPLAIN [ ANALYZE ] [ VERBOSE ] statement
where option can be one of:
    ANALYZE [ boolean ]
    VERBOSE [ boolean ]
    COSTS [ boolean ]
    SETTINGS [ boolean ]
    BUFFERS [ boolean ]
    WAL [ boolean ]
    TIMING [ boolean ]
    SUMMARY [ boolean ]
    FORMAT { TEXT | XML | JSON | YAML }
URL: https://www.postgresql.org/docs/devel/sql-explain.html
[local:/data/run/pg13]:5130 pg13@masterdb=#

试用体验
执行checkpoint后运行SQL,WAL日志会写full page

[local:/data/run/pg13]:5130 pg13@masterdb=# checkpoint;
CHECKPOINT
[local:/data/run/pg13]:5130 pg13@masterdb=# explain (analyze,wal) update tmp set id = 1;
                                                      QUERY PLAN                               
-----------------------------------------------------------------------------------------------
-----------------------
 Update on tmp  (cost=0.00..23275.00 rows=1000000 width=10) (actual time=1636.839..1636.839 row
s=0 loops=1)
   WAL:  records=1999840  full page writes=8851  bytes=172345779
   ->  Seq Scan on tmp  (cost=0.00..23275.00 rows=1000000 width=10) (actual time=4.686..100.211
 rows=1000000 loops=1)
 Planning Time: 0.149 ms
 Execution Time: 1636.923 ms
(5 rows)

再次执行SQL,没有写full page,日志大小相应的减少

[local:/data/run/pg13]:5130 pg13@masterdb=# explain (analyze,wal) update tmp set id = 1;
                                                     QUERY PLAN                                
-----------------------------------------------------------------------------------------------
----------------------
 Update on tmp  (cost=0.00..23275.00 rows=1000000 width=10) (actual time=3661.261..3661.261 row
s=0 loops=1)
   WAL:  records=1999719  bytes=131982016
   ->  Seq Scan on tmp  (cost=0.00..23275.00 rows=1000000 width=10) (actual time=3.920..98.921 
rows=1000000 loops=1)
 Planning Time: 0.088 ms
 Execution Time: 3661.310 ms
(5 rows)

节省23%的空间,38KB大小

[local:/data/run/pg13]:5130 pg13@masterdb=# select (172345779-131982016)/172345779::float;
      ?column?       
---------------------
 0.23420221391090756
(1 row)
[local:/data/run/pg13]:5130 pg13@masterdb=# select (172345779-131982016)/1024/1024;
 ?column? 
----------
       38
(1 row)

参考资料
New in pg13: WAL monitoring

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/6906/viewspace-2686124/,如需转载,请注明出处,否则将追究法律责任。

全部评论
ITPUB数据库版块资深版主,对Oracle、PostgreSQL有深入研究。

注册时间:2007-12-28

  • 博文量
    1501
  • 访问量
    3964002