為什麼高并發小事務, unlogged table不比logged table快多少? - commit wal log

标簽

PostgreSQL , unlogged table , logged table , wal writer

https://github.com/digoal/blog/blob/master/201901/20190107_01.md#%E8%83%8C%E6%99%AF 背景

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  
           

https://github.com/digoal/blog/blob/master/201901/20190107_01.md#%E6%80%A7%E8%83%BD%E5%B7%AE%E5%BC%82 性能差異

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虛拟機