Create Extension
create extension pg_walinspect;
test=# create extension pg_walinspect;
CREATE EXTENSION
Capture Log Sequence Number (lsn) before workload
select now(),pg_current_wal_lsn();
test=# select now(),pg_current_wal_lsn();
now | pg_current_wal_lsn
-------------------------------+--------------------
2022-07-17 10:54:20.764449+00 | 0/1A3E258
(1 row)
Execute some demo workload
drop table if exists account;
create table account (
id int,
name varchar(10),
last_updated timestamptz,
constraint account_pk primary key (id)
);
insert into account values(1,'name1','2021-12-31');
insert into account values(2,'name2','2021-12-31');
insert into account values(3,'name3','2021-12-31');
insert into account values(4,'name4','2021-12-31');
Capture Log Sequence Number (lsn) after workload
test=# select now(),pg_current_wal_lsn();
now | pg_current_wal_lsn
-------------------------------+--------------------
2022-07-17 10:55:08.627989+00 | 0/1A58910
(1 row)
Analyze the workload using "pg_walinspect" extension
select start_lsn, end_lsn, prev_lsn, xid, resource_manager, record_type, record_length, main_data_length, fpi_length, description from pg_get_wal_records_info('0/1A3E258', '0/1A58910');
select start_lsn, end_lsn, prev_lsn, xid, resource_manager, record_type, record_length, main_data_length, fpi_length, description from pg_get_wal_records_info_till_end_of_wal('0/1A58910');
select * from pg_get_wal_stats('0/1A3E258', '0/1A58910', true) where count > 0;
test=# select start_lsn, end_lsn, prev_lsn, xid, resource_manager, record_type, record_length, main_data_length, fpi_length, description from pg_get_wal_records_info('0/1A3E258', '0/1A58910');
start_lsn | end_lsn | prev_lsn | xid | resource_manager | record_type | record_length | main_data_length | fpi_length |
description
-----------+-----------+-----------+-----+------------------+---------------+---------------+------------------+------------+------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------
0/1A3E258 | 0/1A3E290 | 0/1A3DF78 | 0 | Standby | RUNNING_XACTS | 50 | 24 | 0 | nextXid 762 latestCompletedXid 761 oldestRunningXid 762
0/1A3E290 | 0/1A3E2C0 | 0/1A3E258 | 0 | Storage | CREATE | 42 | 16 | 0 | base/16387/16422
0/1A3E2C0 | 0/1A3E398 | 0/1A3E290 | 762 | Heap | INSERT | 211 | 3 | 0 | off 22 flags 0x00
0/1A3E398 | 0/1A3F820 | 0/1A3E2C0 | 762 | Btree | INSERT_LEAF | 5253 | 2 | 5200 | off 258
0/1A3F820 | 0/1A41488 | 0/1A3E398 | 762 | Btree | INSERT_LEAF | 7245 | 2 | 7192 | off 79
0/1A41488 | 0/1A414E0 | 0/1A3F820 | 762 | Heap2 | MULTI_INSERT | 85 | 6 | 0 | 1 tuples flags 0x02
0/1A414E0 | 0/1A43410 | 0/1A41488 | 762 | Btree | INSERT_LEAF | 7957 | 2 | 7904 | off 167
0/1A43410 | 0/1A44EA8 | 0/1A414E0 | 762 | Btree | INSERT_LEAF | 6781 | 2 | 6728 | off 148
0/1A44EA8 | 0/1A44F80 | 0/1A43410 | 762 | Heap | INSERT | 211 | 3 | 0 | off 23 flags 0x00
0/1A44F80 | 0/1A44FC0 | 0/1A44EA8 | 762 | Btree | INSERT_LEAF | 64 | 2 | 0 | off 258
0/1A44FC0 | 0/1A47010 | 0/1A44F80 | 762 | Btree | INSERT_LEAF | 8245 | 2 | 8192 | off 8
0/1A47010 | 0/1A47068 | 0/1A44FC0 | 762 | Heap2 | MULTI_INSERT | 85 | 6 | 0 | 1 tuples flags 0x02
0/1A47068 | 0/1A470B0 | 0/1A47010 | 762 | Btree | INSERT_LEAF | 72 | 2 | 0 | off 167
0/1A470B0 | 0/1A48730 | 0/1A47068 | 762 | Btree | INSERT_LEAF | 5733 | 2 | 5680 | off 112
0/1A48730 | 0/1A48800 | 0/1A470B0 | 762 | Heap | INSERT | 203 | 3 | 0 | off 37 flags 0x00
0/1A48800 | 0/1A49238 | 0/1A48730 | 762 | Btree | INSERT_LEAF | 2613 | 2 | 2560 | off 126
0/1A49238 | 0/1A4AA00 | 0/1A48800 | 762 | Btree | INSERT_LEAF | 6057 | 2 | 6004 | off 10
0/1A4AA00 | 0/1A4C420 | 0/1A49238 | 762 | Btree | INSERT_LEAF | 6657 | 2 | 6604 | off 286
0/1A4C420 | 0/1A4C5D8 | 0/1A4AA00 | 762 | Heap2 | MULTI_INSERT | 440 | 10 | 0 | 3 tuples flags 0x02
0/1A4C5D8 | 0/1A4E400 | 0/1A4C420 | 762 | Btree | INSERT_LEAF | 7693 | 2 | 7640 | off 222
0/1A4E400 | 0/1A4F248 | 0/1A4C5D8 | 762 | Btree | INSERT_LEAF | 3653 | 2 | 3600 | off 178
0/1A4F248 | 0/1A4F290 | 0/1A4E400 | 762 | Btree | INSERT_LEAF | 72 | 2 | 0 | off 223
0/1A4F290 | 0/1A4F2D0 | 0/1A4F248 | 762 | Btree | INSERT_LEAF | 64 | 2 | 0 | off 179
0/1A4F2D0 | 0/1A4F320 | 0/1A4F290 | 762 | Btree | INSERT_LEAF | 80 | 2 | 0 | off 223
0/1A4F320 | 0/1A4F360 | 0/1A4F2D0 | 762 | Btree | INSERT_LEAF | 64 | 2 | 0 | off 180
0/1A4F360 | 0/1A4F6A0 | 0/1A4F320 | 762 | Heap2 | MULTI_INSERT | 830 | 16 | 0 | 6 tuples flags 0x02
0/1A4F6A0 | 0/1A4F6E8 | 0/1A4F360 | 762 | Btree | INSERT_LEAF | 72 | 2 | 0 | off 222
0/1A4F6E8 | 0/1A4F728 | 0/1A4F6A0 | 762 | Btree | INSERT_LEAF | 64 | 2 | 0 | off 178
0/1A4F728 | 0/1A4F770 | 0/1A4F6E8 | 762 | Btree | INSERT_LEAF | 72 | 2 | 0 | off 226
0/1A4F770 | 0/1A4F7B0 | 0/1A4F728 | 762 | Btree | INSERT_LEAF | 64 | 2 | 0 | off 178
0/1A4F7B0 | 0/1A4F7F8 | 0/1A4F770 | 762 | Btree | INSERT_LEAF | 72 | 2 | 0 | off 222
0/1A4F7F8 | 0/1A4F838 | 0/1A4F7B0 | 762 | Btree | INSERT_LEAF | 64 | 2 | 0 | off 178
0/1A4F838 | 0/1A4F880 | 0/1A4F7F8 | 762 | Btree | INSERT_LEAF | 72 | 2 | 0 | off 227
0/1A4F880 | 0/1A4F8C0 | 0/1A4F838 | 762 | Btree | INSERT_LEAF | 64 | 2 | 0 | off 178
0/1A4F8C0 | 0/1A4F908 | 0/1A4F880 | 762 | Btree | INSERT_LEAF | 72 | 2 | 0 | off 222
0/1A4F908 | 0/1A4F948 | 0/1A4F8C0 | 762 | Btree | INSERT_LEAF | 64 | 2 | 0 | off 178
0/1A4F948 | 0/1A4F990 | 0/1A4F908 | 762 | Btree | INSERT_LEAF | 72 | 2 | 0 | off 228
0/1A4F990 | 0/1A4F9D0 | 0/1A4F948 | 762 | Btree | INSERT_LEAF | 64 | 2 | 0 | off 178
0/1A4F9D0 | 0/1A4FA28 | 0/1A4F990 | 762 | Heap2 | MULTI_INSERT | 85 | 6 | 0 | 1 tuples flags 0x02
0/1A4FA28 | 0/1A515F8 | 0/1A4F9D0 | 762 | Btree | INSERT_LEAF | 7093 | 2 | 7040 | off 122
0/1A515F8 | 0/1A51640 | 0/1A4FA28 | 762 | Btree | INSERT_LEAF | 72 | 2 | 0 | off 166
0/1A51640 | 0/1A51670 | 0/1A515F8 | 762 | Standby | LOCK | 42 | 16 | 0 | xid 762 db 16387 rel 16422
0/1A51670 | 0/1A516A0 | 0/1A51640 | 762 | Storage | CREATE | 42 | 16 | 0 | base/16387/16425
0/1A516A0 | 0/1A516D0 | 0/1A51670 | 762 | Standby | LOCK | 42 | 16 | 0 | xid 762 db 16387 rel 16425
0/1A516D0 | 0/1A517A0 | 0/1A516A0 | 762 | Heap | INSERT | 203 | 3 | 0 | off 38 flags 0x00
0/1A517A0 | 0/1A517E0 | 0/1A516D0 | 762 | Btree | INSERT_LEAF | 64 | 2 | 0 | off 127
0/1A517E0 | 0/1A51828 | 0/1A517A0 | 762 | Btree | INSERT_LEAF | 72 | 2 | 0 | off 13
0/1A51828 | 0/1A51868 | 0/1A517E0 | 762 | Btree | INSERT_LEAF | 64 | 2 | 0 | off 287
0/1A51868 | 0/1A51920 | 0/1A51828 | 762 | Heap2 | MULTI_INSERT | 180 | 6 | 0 | 1 tuples flags 0x02
0/1A51920 | 0/1A51960 | 0/1A51868 | 762 | Btree | INSERT_LEAF | 64 | 2 | 0 | off 231
0/1A51960 | 0/1A519A0 | 0/1A51920 | 762 | Btree | INSERT_LEAF | 64 | 2 | 0 | off 187
0/1A519A0 | 0/1A51A68 | 0/1A51960 | 762 | Heap | INSERT | 197 | 3 | 0 | off 52 flags 0x00
0/1A51A68 | 0/1A526E8 | 0/1A519A0 | 762 | Btree | INSERT_LEAF | 3169 | 2 | 3116 | off 115
0/1A526E8 | 0/1A53468 | 0/1A51A68 | 762 | Btree | INSERT_LEAF | 3453 | 2 | 3400 | off 168
0/1A53468 | 0/1A53528 | 0/1A526E8 | 762 | Heap | INSERT | 191 | 3 | 0 | off 21 flags 0x00
0/1A53528 | 0/1A53EB0 | 0/1A53468 | 762 | Btree | INSERT_LEAF | 2433 | 2 | 2380 | off 117
0/1A53EB0 | 0/1A55450 | 0/1A53528 | 762 | Btree | INSERT_LEAF | 5505 | 2 | 5452 | off 3
0/1A55450 | 0/1A56BC8 | 0/1A53EB0 | 762 | Btree | INSERT_LEAF | 5977 | 2 | 5924 | off 117
0/1A56BC8 | 0/1A57550 | 0/1A55450 | 762 | Btree | INSERT_LEAF | 2433 | 2 | 2380 | off 115
0/1A57550 | 0/1A57ED8 | 0/1A56BC8 | 762 | Btree | INSERT_LEAF | 2433 | 2 | 2380 | off 117
0/1A57ED8 | 0/1A57F30 | 0/1A57550 | 762 | Heap2 | MULTI_INSERT | 85 | 6 | 0 | 1 tuples flags 0x02
0/1A57F30 | 0/1A57F78 | 0/1A57ED8 | 762 | Btree | INSERT_LEAF | 72 | 2 | 0 | off 133
0/1A57F78 | 0/1A57FC0 | 0/1A57F30 | 762 | Btree | INSERT_LEAF | 72 | 2 | 0 | off 149
0/1A57FC0 | 0/1A58030 | 0/1A57F78 | 762 | Heap2 | MULTI_INSERT | 85 | 6 | 0 | 1 tuples flags 0x02
0/1A58030 | 0/1A58078 | 0/1A57FC0 | 762 | Btree | INSERT_LEAF | 72 | 2 | 0 | off 123
0/1A58078 | 0/1A580C0 | 0/1A58030 | 762 | Btree | INSERT_LEAF | 72 | 2 | 0 | off 155
0/1A580C0 | 0/1A58150 | 0/1A58078 | 762 | XLOG | FPI | 137 | 0 | 88 |
0/1A58150 | 0/1A58210 | 0/1A580C0 | 762 | Heap | INPLACE | 188 | 2 | 0 | off 37
0/1A58210 | 0/1A582D0 | 0/1A58150 | 762 | Heap | INPLACE | 188 | 2 | 0 | off 38
0/1A582D0 | 0/1A58598 | 0/1A58210 | 762 | Transaction | COMMIT | 709 | 680 | 0 | 2022-07-17 10:54:59.125946+00; inval msgs: catcache 80 catcache 7
9 catcache 80 catcache 79 catcache 55 catcache 54 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache
7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 55 catcache 54 catcache 7 catcache 6 catcache 32 catcache 19 catcache 55 catcache 54 catcache 55 catcache 54 snapshot 2608 re
lcache 16422 relcache 16425 relcache 16422 snapshot 2608 relcache 16422 relcache 16425
0/1A58598 | 0/1A585E8 | 0/1A582D0 | 763 | Heap | INSERT+INIT | 79 | 3 | 0 | off 1 flags 0x00
0/1A585E8 | 0/1A58648 | 0/1A58598 | 763 | Btree | NEWROOT | 90 | 8 | 0 | lev 0
0/1A58648 | 0/1A58688 | 0/1A585E8 | 763 | Btree | INSERT_LEAF | 64 | 2 | 0 | off 1
0/1A58688 | 0/1A586B0 | 0/1A58648 | 763 | Transaction | COMMIT | 34 | 8 | 0 | 2022-07-17 10:54:59.129086+00
0/1A586B0 | 0/1A586E8 | 0/1A58688 | 0 | Standby | RUNNING_XACTS | 54 | 28 | 0 | nextXid 764 latestCompletedXid 762 oldestRunningXid 763; 1 xacts:
763
0/1A586E8 | 0/1A58738 | 0/1A586B0 | 764 | Heap | INSERT | 79 | 3 | 0 | off 2 flags 0x00
0/1A58738 | 0/1A58778 | 0/1A586E8 | 764 | Btree | INSERT_LEAF | 64 | 2 | 0 | off 2
0/1A58778 | 0/1A587A0 | 0/1A58738 | 764 | Transaction | COMMIT | 34 | 8 | 0 | 2022-07-17 10:54:59.131251+00
0/1A587A0 | 0/1A587F0 | 0/1A58778 | 765 | Heap | INSERT | 79 | 3 | 0 | off 3 flags 0x00
0/1A587F0 | 0/1A58830 | 0/1A587A0 | 765 | Btree | INSERT_LEAF | 64 | 2 | 0 | off 3
0/1A58830 | 0/1A58858 | 0/1A587F0 | 765 | Transaction | COMMIT | 34 | 8 | 0 | 2022-07-17 10:54:59.133896+00
0/1A58858 | 0/1A588A8 | 0/1A58830 | 766 | Heap | INSERT | 79 | 3 | 0 | off 4 flags 0x00
0/1A588A8 | 0/1A588E8 | 0/1A58858 | 766 | Btree | INSERT_LEAF | 64 | 2 | 0 | off 4
0/1A588E8 | 0/1A58910 | 0/1A588A8 | 766 | Transaction | COMMIT | 34 | 8 | 0 | 2022-07-17 10:54:59.544526+00
(84 rows)
test=# select start_lsn, end_lsn, prev_lsn, xid, resource_manager, record_type, record_length, main_data_length, fpi_length, description from pg_get_wal_records_info_till_end_of_wal('0/1A58910');
start_lsn | end_lsn | prev_lsn | xid | resource_manager | record_type | record_length | main_data_length | fpi_length | description
-----------+-----------+-----------+-----+------------------+---------------+---------------+------------------+------------+---------------------------------------------------------
0/1A58910 | 0/1A58948 | 0/1A588E8 | 0 | Standby | RUNNING_XACTS | 50 | 24 | 0 | nextXid 767 latestCompletedXid 766 oldestRunningXid 767
(1 row)
test=# select * from pg_get_wal_stats('0/1A3E258', '0/1A58910', true) where count > 0;
resource_manager/record_type | count | count_percentage | record_size | record_size_percentage | fpi_size | fpi_size_percentage | combined_size | combined_size_percentage
------------------------------+-------+------------------+-------------+------------------------+----------+---------------------+---------------+--------------------------
XLOG/FPI | 1 | 1.1904762 | 49 | 0.59596205 | 88 | 0.08847422 | 137 | 0.12722173
Transaction/COMMIT | 5 | 5.952381 | 845 | 10.277305 | 0 | 0 | 845 | 0.78468883
Storage/CREATE | 2 | 2.3809524 | 84 | 1.0216492 | 0 | 0 | 84 | 0.07800457
Standby/LOCK | 2 | 2.3809524 | 84 | 1.0216492 | 0 | 0 | 84 | 0.07800457
Standby/RUNNING_XACTS | 2 | 2.3809524 | 104 | 1.264899 | 0 | 0 | 104 | 0.096577086
Heap2/MULTI_INSERT | 8 | 9.523809 | 1875 | 22.80467 | 0 | 0 | 1875 | 1.7411734
Heap/INSERT | 9 | 10.714286 | 1453 | 17.6721 | 0 | 0 | 1453 | 1.3492934
Heap/INPLACE | 2 | 2.3809524 | 376 | 4.5730968 | 0 | 0 | 376 | 0.3491633
Heap/INSERT+INIT | 1 | 1.1904762 | 79 | 0.96083677 | 0 | 0 | 79 | 0.07336144
Btree/INSERT_LEAF | 51 | 60.714287 | 3183 | 38.713207 | 99376 | 99.91153 | 102559 | 95.23894
Btree/NEWROOT | 1 | 1.1904762 | 90 | 1.0946242 | 0 | 0 | 90 | 0.08357632
(11 rows)
Reference:
No comments:
Post a Comment