为什么高并发小事务, unlogged table不比logged table快多少? - commit wal log-阿里云开发者社区

开发者社区> 数据库> 正文

为什么高并发小事务, unlogged table不比logged table快多少? - commit wal log

简介: 标签 PostgreSQL , unlogged table , logged table , wal writer 背景 unlogged table,这些表的写操作不记录WAL日志。那么这种表的高并发写入一定比logged table快,快很多吗? 实际上一个事务,在事务结束时,也会记录一笔commit或rollback xlog,所以如果是高并发的小事务,commit xlog的

标签

PostgreSQL , unlogged table , logged table , wal writer


背景

unlogged table,这些表的写操作不记录WAL日志。那么这种表的高并发写入一定比logged table快,快很多吗?

实际上一个事务,在事务结束时,也会记录一笔commit或rollback xlog,所以如果是高并发的小事务,commit xlog的量也非常大,从而导致wal writer瓶颈。

观察方法

pg_waldump -b 000000010000017B0000002F|less  

例子

1、commit xlog

rmgr: Transaction len (rec/tot):     34/    34, tx:  775906906, lsn: 17B/24007F38, prev 17B/24007F10, desc: COMMIT 2019-01-07 09:35:34.554660 CST  
rmgr: Transaction len (rec/tot):     34/    34, tx:  775906908, lsn: 17B/24007F60, prev 17B/24007F38, desc: COMMIT 2019-01-07 09:35:34.554672 CST  
rmgr: Transaction len (rec/tot):     34/    34, tx:  775906907, lsn: 17B/24007F88, prev 17B/24007F60, desc: COMMIT 2019-01-07 09:35:34.554674 CST  
rmgr: Transaction len (rec/tot):     34/    34, tx:  775906904, lsn: 17B/24007FB0, prev 17B/24007F88, desc: COMMIT 2019-01-07 09:35:34.554676 CST  
rmgr: Transaction len (rec/tot):     34/    34, tx:  775906909, lsn: 17B/24007FD8, prev 17B/24007FB0, desc: COMMIT 2019-01-07 09:35:34.554676 CST  
rmgr: Transaction len (rec/tot):     34/    34, tx:  775906910, lsn: 17B/24008018, prev 17B/24007FD8, desc: COMMIT 2019-01-07 09:35:34.554677 CST  
rmgr: Transaction len (rec/tot):     34/    34, tx:  775906911, lsn: 17B/24008040, prev 17B/24008018, desc: COMMIT 2019-01-07 09:35:34.554683 CST  
rmgr: Transaction len (rec/tot):     34/    34, tx:  775906912, lsn: 17B/24008068, prev 17B/24008040, desc: COMMIT 2019-01-07 09:35:34.554686 CST  
rmgr: Transaction len (rec/tot):     34/    34, tx:  775906914, lsn: 17B/24008090, prev 17B/24008068, desc: COMMIT 2019-01-07 09:35:34.554688 CST  
rmgr: Transaction len (rec/tot):     34/    34, tx:  775906913, lsn: 17B/240080B8, prev 17B/24008090, desc: COMMIT 2019-01-07 09:35:34.554689 CST  
rmgr: Transaction len (rec/tot):     34/    34, tx:  775906915, lsn: 17B/240080E0, prev 17B/240080B8, desc: COMMIT 2019-01-07 09:35:34.554693 CST  
rmgr: Transaction len (rec/tot):     34/    34, tx:  775906916, lsn: 17B/24008108, prev 17B/240080E0, desc: COMMIT 2019-01-07 09:35:34.554700 CST  

2、table log 和 commit xlog

rmgr: Heap        len (rec/tot):    167/   167, tx:  777196079, lsn: 17B/2F000040, prev 17B/2EFFFF70, desc: INSERT off 18  
        blkref #0: rel 1663/13806/85962 fork main blk 13586  
rmgr: Heap        len (rec/tot):    167/   167, tx:  777196080, lsn: 17B/2F0000E8, prev 17B/2F000040, desc: INSERT off 32  
        blkref #0: rel 1663/13806/85962 fork main blk 13580  
rmgr: Transaction len (rec/tot):     34/    34, tx:  777196077, lsn: 17B/2F000190, prev 17B/2F0000E8, desc: COMMIT 2019-01-07 09:38:41.062496 CST  
rmgr: Heap        len (rec/tot):    167/   167, tx:  777196081, lsn: 17B/2F0001B8, prev 17B/2F000190, desc: INSERT off 26  
        blkref #0: rel 1663/13806/85962 fork main blk 13569  
rmgr: Heap        len (rec/tot):    167/   167, tx:  777196082, lsn: 17B/2F000260, prev 17B/2F0001B8, desc: INSERT off 7  
        blkref #0: rel 1663/13806/85962 fork main blk 13617  
rmgr: Heap        len (rec/tot):    167/   167, tx:  777196083, lsn: 17B/2F000308, prev 17B/2F000260, desc: INSERT off 14  
        blkref #0: rel 1663/13806/85962 fork main blk 13607  
rmgr: Transaction len (rec/tot):     34/    34, tx:  777196078, lsn: 17B/2F0003B0, prev 17B/2F000308, desc: COMMIT 2019-01-07 09:38:41.062509 CST  
rmgr: Transaction len (rec/tot):     34/    34, tx:  777196079, lsn: 17B/2F0003D8, prev 17B/2F0003B0, desc: COMMIT 2019-01-07 09:38:41.062508 CST  
rmgr: Transaction len (rec/tot):     34/    34, tx:  777196076, lsn: 17B/2F000400, prev 17B/2F0003D8, desc: COMMIT 2019-01-07 09:38:41.062511 CST  
rmgr: Transaction len (rec/tot):     34/    34, tx:  777196080, lsn: 17B/2F000428, prev 17B/2F000400, desc: COMMIT 2019-01-07 09:38:41.062511 CST  
rmgr: Heap        len (rec/tot):    167/   167, tx:  777196084, lsn: 17B/2F000450, prev 17B/2F000428, desc: INSERT off 11  
        blkref #0: rel 1663/13806/85962 fork main blk 13613  
rmgr: Heap        len (rec/tot):    167/   167, tx:  777196085, lsn: 17B/2F0004F8, prev 17B/2F000450, desc: INSERT off 20  
        blkref #0: rel 1663/13806/85962 fork main blk 13598  
rmgr: Heap        len (rec/tot):    167/   167, tx:  777196086, lsn: 17B/2F0005A0, prev 17B/2F0004F8, desc: INSERT off 28  
        blkref #0: rel 1663/13806/85962 fork main blk 13575  
rmgr: Transaction len (rec/tot):     34/    34, tx:  777196081, lsn: 17B/2F000648, prev 17B/2F0005A0, desc: COMMIT 2019-01-07 09:38:41.062516 CST  
rmgr: Transaction len (rec/tot):     34/    34, tx:  777196082, lsn: 17B/2F000670, prev 17B/2F000648, desc: COMMIT 2019-01-07 09:38:41.062518 CST  
rmgr: Transaction len (rec/tot):     34/    34, tx:  777196083, lsn: 17B/2F000698, prev 17B/2F000670, desc: COMMIT 2019-01-07 09:38:41.062515 CST  
rmgr: Transaction len (rec/tot):     34/    34, tx:  777196084, lsn: 17B/2F0006C0, prev 17B/2F000698, desc: COMMIT 2019-01-07 09:38:41.062521 CST  
rmgr: Transaction len (rec/tot):     34/    34, tx:  777196086, lsn: 17B/2F0006E8, prev 17B/2F0006C0, desc: COMMIT 2019-01-07 09:38:41.062525 CST  
rmgr: Heap        len (rec/tot):    167/   167, tx:  777196087, lsn: 17B/2F000710, prev 17B/2F0006E8, desc: INSERT off 20  
        blkref #0: rel 1663/13806/85962 fork main blk 13596  
rmgr: Transaction len (rec/tot):     34/    34, tx:  777196085, lsn: 17B/2F0007B8, prev 17B/2F000710, desc: COMMIT 2019-01-07 09:38:41.062534 CST  
rmgr: Heap        len (rec/tot):    167/   167, tx:  777196088, lsn: 17B/2F0007E0, prev 17B/2F0007B8, desc: INSERT off 22  
        blkref #0: rel 1663/13806/85962 fork main blk 13584  
rmgr: Transaction len (rec/tot):     34/    34, tx:  777196087, lsn: 17B/2F000888, prev 17B/2F0007E0, desc: COMMIT 2019-01-07 09:38:41.062541 CST  
rmgr: Heap        len (rec/tot):    167/   167, tx:  777196089, lsn: 17B/2F0008B0, prev 17B/2F000888, desc: INSERT off 5  
        blkref #0: rel 1663/13806/85962 fork main blk 13614  
rmgr: Transaction len (rec/tot):     34/    34, tx:  777196088, lsn: 17B/2F000958, prev 17B/2F0008B0, desc: COMMIT 2019-01-07 09:38:41.062544 CST  
rmgr: Heap        len (rec/tot):    167/   167, tx:  777196091, lsn: 17B/2F000980, prev 17B/2F000958, desc: INSERT off 21  
        blkref #0: rel 1663/13806/85962 fork main blk 13600  
rmgr: Heap        len (rec/tot):    167/   167, tx:  777196090, lsn: 17B/2F000A28, prev 17B/2F000980, desc: INSERT off 38  
        blkref #0: rel 1663/13806/85962 fork main blk 13565  
rmgr: Transaction len (rec/tot):     34/    34, tx:  777196089, lsn: 17B/2F000AD0, prev 17B/2F000A28, desc: COMMIT 2019-01-07 09:38:41.062551 CST  
rmgr: Transaction len (rec/tot):     34/    34, tx:  777196091, lsn: 17B/2F000AF8, prev 17B/2F000AD0, desc: COMMIT 2019-01-07 09:38:41.062552 CST  
rmgr: Heap        len (rec/tot):    167/   167, tx:  777196092, lsn: 17B/2F000B20, prev 17B/2F000AF8, desc: INSERT off 13  
        blkref #0: rel 1663/13806/85962 fork main blk 13589  
rmgr: Heap        len (rec/tot):    167/   167, tx:  777196094, lsn: 17B/2F000BC8, prev 17B/2F000B20, desc: INSERT off 8  
        blkref #0: rel 1663/13806/85962 fork main blk 13611  

性能差异

1、高并发小事务,logged table和unlogged table性能相差无几。

《HTAP数据库 PostgreSQL 场景与性能测试之 39 - (OLTP+OLAP) 含索引多表单点写入》

《HTAP数据库 PostgreSQL 场景与性能测试之 38 - (OLTP+OLAP) 不含索引多表单点写入》

2、批量写入,TPS不高(所以commit xlog占比不高),因此logged table和unlogged table的性能差异会非常大。

《HTAP数据库 PostgreSQL 场景与性能测试之 43 - (OLTP+OLAP) unlogged table 含索引多表批量写入》

《HTAP数据库 PostgreSQL 场景与性能测试之 42 - (OLTP+OLAP) unlogged table 不含索引多表批量写入》

《HTAP数据库 PostgreSQL 场景与性能测试之 41 - (OLTP+OLAP) 含索引多表批量写入》

《HTAP数据库 PostgreSQL 场景与性能测试之 40 - (OLTP+OLAP) 不含索引多表批量写入》

 

免费领取阿里云RDS PostgreSQL实例、ECS虚拟机

版权声明:如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件至:developerteam@list.alibaba-inc.com 进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章