数据仓库服务 GAUSSDB(DWS)-统计信息函数:pg_stat_wal_write()

时间:2024-01-26 16:15:21

pg_stat_wal_write()

描述:在CN或者DN上执行,记录当前实例的线程信息,并统计wal日志和数据页导入量及速率,这里以CN为例。

该函数仅8.2.0及以上集群版本支持。

返回值类型:bigint

返回值字段:
  • node_name:实例名。
  • application_name:应用名。
  • query_start:当前正在执行语句的起始时间。
  • datapage_write:当前query导入的数据页总量,单位为bytes。
  • datapage_write_speed:数据页导入速率,单位为bytes/s。
  • wal_write:当前query导入的wal日志总量,单位为bytes。
  • wal_write_speed:wal导入速率,单位为bytes/s。
  • total_datapage_write:当前线程产生数据页总量,单位为bytes。
  • total_wal_write:wal总量,单位为bytes。
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
SELECT * FROM pg_stat_wal_write();
  node_name   | datid |       pid       | lwtid | usesysid |  application_name  | state  |          query_start          |         backend_start         |     query_id      | datapage_write | datapage_write_speed | wal_write | wal_write_speed | total_datapage_write | total_wal_write
--------------+-------+-----------------+-------+----------+--------------------+--------+-------------------------------+-------------------------------+-------------------+----------------+----------------------+-----------+-----------------+----------------------+-----------------
 coordinator1 | 15979 | 140234153498368 | 25353 |       10 | JobScheduler       | active |                               | 2022-11-15 11:57:54.396347+08 |                 0 |              0 |                      |         0 |               0 |                    0 |               0
 coordinator1 | 15979 | 140234132027136 | 25354 |       10 | StatCollector      | idle   |                               |                               |                 0 |              0 |                      |         0 |               0 |                    0 |               0
 coordinator1 | 15979 | 140234090084096 | 25356 |       10 | WDRSnapshot        | idle   | 2022-11-15 11:57:54.388948+08 | 2022-11-15 11:57:54.403055+08 |                 0 |              0 |                    0 |         0 |               0 |                    0 |               0
 coordinator1 | 15979 | 140234027169536 | 25359 |       10 | workload           | active | 2022-11-15 11:57:54.404836+08 | 2022-11-15 11:57:54.389259+08 | 72620543991404472 |              0 |                    0 |   1722112 |               0 |                    0 |               0
 coordinator1 | 15979 | 140234004621056 | 25360 |       10 | CalculateSpaceInfo | active | 2022-11-15 11:57:54.405677+08 | 2022-11-15 11:57:54.389847+08 | 72620543991349266 |              0 |                    0 |    191326 |               0 |                    0 |               0
 coordinator1 | 15979 | 140233987839744 | 25361 |       10 | WorkloadMonitor    | active | 2022-11-15 11:57:54.406263+08 | 2022-11-15 11:57:54.390373+08 | 72620543991404418 |              0 |                    0 |   1183741 |               0 |                    0 |               0
 coordinator1 | 15979 | 140233971058432 | 25362 |       10 | WLMArbiter         | active | 2022-11-15 11:57:54.406367+08 | 2022-11-15 11:57:54.390947+08 |                 0 |              0 |                    0 |         0 |               0 |                    0 |               0
 coordinator1 | 15979 | 140233228666624 | 14415 |       10 | gsql               | active | 2022-11-15 21:19:32.200305+08 | 2022-11-15 21:09:38.916931+08 | 72620543991404422 |              0 |                    0 |     30872 |            1000 |                    0 |               0
 coordinator1 | 15979 | 140233172035328 | 15516 |       10 | gsql               | active | 2022-11-15 21:19:49.7877+08   | 2022-11-15 21:10:13.447312+08 | 72620543991404485 |              0 |                    0 |         0 |               0 |                    0 |               0
 coordinator1 |     0 | 140234245269248 | 25348 |        0 | Background writer  | idle   |                               | 2022-11-15 11:57:54.383651+08 |                 0 |              0 |                      |         0 |               0 |                    0 |               0
 coordinator1 |     0 | 140234291410688 | 25347 |        0 | CheckPointer       | idle   |                               | 2022-11-15 11:57:54.383231+08 |                 0 |              0 |                      |         0 |               0 |                    0 |               0
 coordinator1 |     0 | 140234228487936 | 25349 |        0 | Wal Writer         | idle   |                               | 2022-11-15 11:57:54.384069+08 |                 0 |              0 |                      |         0 |               0 |                    0 |               0
 coordinator1 |     0 | 140234068584192 | 25357 |        0 | TwoPhase Cleaner   | idle   |                               | 2022-11-15 11:57:54.388332+08 |                 0 |              0 |                      |         0 |               0 |                    0 |               0
 coordinator1 |     0 | 140234048657152 | 25358 |        0 | LWLock Monitor     | idle   |                               | 2022-11-15 11:57:54.389239+08 |                 0 |              0 |                      |         0 |               0 |                    0 |               0
 coordinator1 |     0 | 140234194925312 | 25351 |        0 | CBM Writer         | idle   |                               | 2022-11-15 11:57:54.38506+08  |                 0 |              0 |                      |         0 |               0 |                    0 |               0
 coordinator1 |     0 | 140234211706624 | 25350 |        0 | Tick service       | idle   |                               | 2022-11-15 11:57:54.384136+08 |                 0 |              0 |                      |         0 |               0 |                    0 |               0
(16 rows)

行存不带索引批量导入时,数据页copy导入会产生logical newpage相关的xlog日志,在xlog量大于默认值时同样会触发流控。

support.huaweicloud.com/sqlreference-820-dws/dws_06_0065.html