postgres-WAL写放大优化

pg-WAL写放大优化

wal写放大

现场NP数据库插入缓慢,排查发现是因为电子卷宗的数据库IO过高导致的。服务器负载50+

[pg@sdbserver1 bin]$ psql -p 7543 -c "select pg_current_wal_lsn()";pgbench -n -c 100 -j 100 -t 1000 -p 7543 pgbenchdb;psql -p 7543 -c "select pg_current_wal_lsn()"
 pg_current_wal_lsn 
--------------------
 33/B2798088
(1 row)

transaction type: <builtin: TPC-B (sort of)>
scaling factor: 100
query mode: simple
number of clients: 100
number of threads: 100
number of transactions per client: 1000
number of transactions actually processed: 100000/100000
latency average = 288.156 ms
tps = 347.034170 (including connections establishing)
tps = 347.181250 (excluding connections establishing)
 pg_current_wal_lsn 
--------------------
 33/E6C3E530
(1 row)

[pg@sdbserver1 bin]$ psql -p 7543 -c "select pg_wal_lsn_diff('33/E6C3E530','33/B2798088')";
 pg_wal_lsn_diff 
-----------------
       877290664
(1 row)

[pg@sdbserver1 bin]$ ./pg_waldump -p /home/pg/abdata/6.0/abase1/pg_wal -z  -s    33/B2798088   -e     33/E6C3E530
Type                                           N      (%)          Record size      (%)             FPI size      (%)        Combined size      (%)
----                                           -      ---          -----------      ---             --------      ---        -------------      ---
XLOG                                           3 (  0.00)                  318 (  0.00)                    0 (  0.00)                  318 (  0.00)
Transaction                               100012 ( 14.89)              4601928 (  8.44)                    0 (  0.00)              4601928 (  0.53)
Storage                                        0 (  0.00)                    0 (  0.00)                    0 (  0.00)                    0 (  0.00)
CLOG                                           3 (  0.00)                   90 (  0.00)                    0 (  0.00)                   90 (  0.00)
Database                                       0 (  0.00)                    0 (  0.00)                    0 (  0.00)                    0 (  0.00)
Tablespace                                     0 (  0.00)                    0 (  0.00)                    0 (  0.00)                    0 (  0.00)
MultiXact                                      0 (  0.00)                    0 (  0.00)                    0 (  0.00)                    0 (  0.00)
RelMap                                         0 (  0.00)                    0 (  0.00)                    0 (  0.00)                    0 (  0.00)
Standby                                       33 (  0.00)                10081 (  0.02)                    0 (  0.00)                10081 (  0.00)
Heap2                                      90041 ( 13.40)              5875372 ( 10.78)            621669780 ( 75.98)            627545152 ( 71.91)
Heap                                      467950 ( 69.66)             43271462 ( 79.38)            109226984 ( 13.35)            152498446 ( 17.47)
Btree                                      13764 (  2.05)               754237 (  1.38)             87256400 ( 10.67)             88010637 ( 10.09)
Hash                                           0 (  0.00)                    0 (  0.00)                    0 (  0.00)                    0 (  0.00)
Gin                                            0 (  0.00)                    0 (  0.00)                    0 (  0.00)                    0 (  0.00)
Gist                                           0 (  0.00)                    0 (  0.00)                    0 (  0.00)                    0 (  0.00)
Sequence                                       0 (  0.00)                    0 (  0.00)                    0 (  0.00)                    0 (  0.00)
SPGist                                         0 (  0.00)                    0 (  0.00)                    0 (  0.00)                    0 (  0.00)
BRIN                                           0 (  0.00)                    0 (  0.00)                    0 (  0.00)                    0 (  0.00)
CommitTs                                       0 (  0.00)                    0 (  0.00)                    0 (  0.00)                    0 (  0.00)
ReplicationOrigin                              0 (  0.00)                    0 (  0.00)                    0 (  0.00)                    0 (  0.00)
Generic                                        0 (  0.00)                    0 (  0.00)                    0 (  0.00)                    0 (  0.00)
LogicalMessage                                 0 (  0.00)                    0 (  0.00)                    0 (  0.00)                    0 (  0.00)
                                        --------                      --------                      --------                      --------
Total                                     671806                      54513488 [6.25%]             818153164 [93.75%]            872666652 [100%]

FPI:818153164 [93.75%] wal膨胀了 15倍

调优参数

alter system set wal_compression = on;
alter system set checkpoint_completion_target = 0.9;
alter system set min_wal_size = '1GB';
alter system set max_wal_size = '4GB';
alter system set wal_keep_segments = 1000;
alter system set checkpoint_timeout='20min';
[pg@sdbserver1 bin]$ psql -p 7543 -c "select pg_current_wal_lsn()";pgbench -n -c 100 -j 100 -t 1000 -p 7543 pgbenchdb;psql -p 7543 -c "select pg_current_wal_lsn()"
 pg_current_wal_lsn 
--------------------
 34/1A4471C8
(1 row)

transaction type: <builtin: TPC-B (sort of)>
scaling factor: 100
query mode: simple
number of clients: 100
number of threads: 100
number of transactions per client: 1000
number of transactions actually processed: 100000/100000
latency average = 187.921 ms
tps = 532.138425 (including connections establishing)
tps = 532.287100 (excluding connections establishing)
 pg_current_wal_lsn 
--------------------
 34/224AB2E8
(1 row)

[pg@sdbserver1 bin]$ ./pg_waldump -p /home/pg/abdata/6.0/abase1/pg_wal -z  -s    34/1A4471C8 -e    34/224AB2E8
Type                                           N      (%)          Record size      (%)             FPI size      (%)        Combined size      (%)
----                                           -      ---          -----------      ---             --------      ---        -------------      ---
XLOG                                           0 (  0.00)                    0 (  0.00)                    0 (  0.00)                    0 (  0.00)
Transaction                               100010 ( 15.09)              4601620 (  8.51)                    0 (  0.00)              4601620 (  3.48)
Storage                                        0 (  0.00)                    0 (  0.00)                    0 (  0.00)                    0 (  0.00)
CLOG                                           3 (  0.00)                   90 (  0.00)                    0 (  0.00)                   90 (  0.00)
Database                                       0 (  0.00)                    0 (  0.00)                    0 (  0.00)                    0 (  0.00)
Tablespace                                     0 (  0.00)                    0 (  0.00)                    0 (  0.00)                    0 (  0.00)
MultiXact                                      0 (  0.00)                    0 (  0.00)                    0 (  0.00)                    0 (  0.00)
RelMap                                         0 (  0.00)                    0 (  0.00)                    0 (  0.00)                    0 (  0.00)
Standby                                       21 (  0.00)                 6262 (  0.01)                    0 (  0.00)                 6262 (  0.00)
Heap2                                      97983 ( 14.78)              6444115 ( 11.92)             59239465 ( 75.88)             65683580 ( 49.71)
Heap                                      460508 ( 69.47)             42757019 ( 79.10)              4276861 (  5.48)             47033880 ( 35.60)
Btree                                       4350 (  0.66)               244962 (  0.45)             14555606 ( 18.64)             14800568 ( 11.20)
Hash                                           0 (  0.00)                    0 (  0.00)                    0 (  0.00)                    0 (  0.00)
Gin                                            0 (  0.00)                    0 (  0.00)                    0 (  0.00)                    0 (  0.00)
Gist                                           0 (  0.00)                    0 (  0.00)                    0 (  0.00)                    0 (  0.00)
Sequence                                       0 (  0.00)                    0 (  0.00)                    0 (  0.00)                    0 (  0.00)
SPGist                                         0 (  0.00)                    0 (  0.00)                    0 (  0.00)                    0 (  0.00)
BRIN                                           0 (  0.00)                    0 (  0.00)                    0 (  0.00)                    0 (  0.00)
CommitTs                                       0 (  0.00)                    0 (  0.00)                    0 (  0.00)                    0 (  0.00)
ReplicationOrigin                              0 (  0.00)                    0 (  0.00)                    0 (  0.00)                    0 (  0.00)
Generic                                        0 (  0.00)                    0 (  0.00)                    0 (  0.00)                    0 (  0.00)
LogicalMessage                                 0 (  0.00)                    0 (  0.00)                    0 (  0.00)                    0 (  0.00)
                                        --------                      --------                      --------                      --------
Total                                     662875                      54054068 [40.91%]             78071932 [59.09%]            132126000 [100%]

fpi: 78071932 [59.09%]

818153164 :780MB,78071932 :&74MB

posted @ 2021-11-22 10:53  月图灵  阅读(416)  评论(0编辑  收藏  举报