分析PostgreSQL DBA中新增的WAL选项

这篇文章主要讲解了"分析PostgreSQL DBA中新增的WAL选项"，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习"分析PostgreSQL DBA中新增的WAL选项"吧！

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

新增的WAL选项

[local:/data/run/pg13]:5130 pg13@masterdb=# \help explainCommand:     EXPLAINDescription: show the execution plan of a statementSyntax:EXPLAIN [ ( option [, ...] ) ] statementEXPLAIN [ ANALYZE ] [ VERBOSE ] statementwhere 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 rows=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 rows=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)

感谢各位的阅读，以上就是"分析PostgreSQL DBA中新增的WAL选项"的内容了，经过本文的学习后，相信大家对分析PostgreSQL DBA中新增的WAL选项这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是，小编将为大家推送更多相关知识点的文章，欢迎关注！