标签
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虚拟机
