PolarDB for PostgreSQL(Compatible with Oracle):LSN monitoring

select *,
  pg_size_pretty(pg_wal_lsn_diff(WP, AP)) as "WP-AP",
  pg_size_pretty(pg_wal_lsn_diff(AP, CP)) as "AP-CP",
  pg_size_pretty(pg_wal_lsn_diff(WP, CP)) as "WP-CP",
  pg_size_pretty(pg_wal_lsn_diff(WP, redo_lsn)) as "WP-REDO"
from
  (select pg_current_wal_insert_lsn() as WP,
          polar_oldest_apply_lsn() as AP,
          polar_consistent_lsn() as CP,
          redo_lsn
   from pg_control_checkpoint()
  ) as lsn_info;

• The primary node cannot reuse WAL logs, so a large number of WAL logs are generated and storage usage increases. You can query the storage space of the database cluster or of WAL logs to check this problem.
• You cannot query the latest updated data of the primary node from the secondary node.
• If a proxy for read/write splitting is used, the proxy may route all requests to the primary node and read /write splitting fails in effect.
• This results in latency of the consistent LSN and checkpoint and therefore affects the cluster availability.

1. Execute the following statement to query replication slots and check whether the primary/secondary synchronization is interrupted:

   select * from polar_replication_slots;

   Check whether active of a replication slot whose is f. If yes, the primary/secondary synchronization is interrupted. You must further discover the causes for the interrupted primary/secondary synchronization.
   Note Replication slots in PolarDB fall into two types: replica and standby. Replication slot types correspond to node types in a PolarDB cluster. The replica node shares the same data as the primary node. Interrupted synchronization between the primary and replica nodes has a profound impact on the primary node. You must handle the interrupted synchronization in a timely manner. The standby and primary node are in a similar relationship to the traditional primary and secondary nodes in PostgreSQL. Each of them has independent data. The replication slot type is identified by the name displayed in the slot_name field.
2. If primary/secondary synchronization is normal, execute the following statement to check latency of the primary and secondary nodes:

   select pid, usename, application_name, client_addr, state,
          pg_wal_lsn_diff(pg_current_wal_insert_lsn(), sent_lsn) as sent_delay,
          pg_wal_lsn_diff(pg_current_wal_insert_lsn(), write_lsn) as write_delay,
          pg_wal_lsn_diff(pg_current_wal_insert_lsn(), flush_lsn) as flush_delay,
          pg_wal_lsn_diff(pg_current_wal_insert_lsn(), replay_lsn) as replay_delay
   from pg_stat_replication;

   The preceding query results show the following latency types:
   • sent_delay: the latency for the primary node to send logs.
   • write_delay: the latency for the secondary node to write the received logs to the storage.
   • flush_delay: the latency for the secondary node to flush the received logs to the storage.
   • replay_delay: the latency for the secondary node to replay logs.
     Note If the replay_delay value is high, you usually must log on to the host where the secondary node is located and check the running status of the startup process of the secondary node. Further troubleshooting is required depending on the check results.
   Check the secondary node with high latency values. Discover the causes for high latency values.

1. If latency of the consistent LSN coincides with latency of the secondary node, you can first troubleshoot latency of the secondary node, because the latency of the secondary node may cause latency of the consistent LSN.
2. Check whether batch data import or modification is performed. Such operation produces a large amount of dirty data in a short period of time. The database cluster may not be able to write dirty pages to the storage in a timely manner, which results in latency of the consistent LSN. This is a normal case. The latency of the consistent LSN decreases after such operation is complete.
3. Check whether the cluster specifications are high and a shared_buffer value is set. The higher the shared_buffer value, the larger the buffer pool of the database. More dirty pages may be cached because the buffer size is higher. The upper limit for PolarDB flushing depends on disk I/O and parallel flushing efficiency. When the upper limit is reached, the rate of writing dirty pages in the buffer pool to the storage is also at the peak. If dirty pages are generated at a higher speed than the flushing rate, more and more dirty pages are accumulated in the buffer pool. Latency of the consistent LSN gradually increases.
   You can modify the following flushing related parameters to improve flushing efficiency:

   • polar_parallel_bgwriter_workers: the number of parallel flushing processes. Default value: 5.
     Note This parameter defines the number of parallel flushing processes started by default. PolarDB also dynamically starts and stops some parallel flushing processes depending on the primary/secondary latency. The actual number of parallel flushing processes is min (2 * polar_parallel_bgwriter_workers, 16).
   • polar_parallel_bgwriter_delay: the waiting time before each parallel flushing process. Default value: 10ms.

   You can increase the polar_parallel_bgwriter_workers value and decrease the polar_parallel_bgwriter_delay value to improve flushing efficiency.

   Note The large number of flushing processes degrades the overall throughput of the cluster. High I/O consumption by flushing may reduce the efficiency of writing other requests to WAL logs and even the efficiency of SQL execution in the cluster.

• Latency of the consistent LSN will cause checkpoint latency. In this case, you must first troubleshoot the causes for latency of the consistent LSN.
• The causes for latency of the consistent LSN may result in checkpoint latency. For example, batch data modification and flushing inefficiency also cause checkpoint latency.
• The checkpoint execution period is long and many data modifications are performed within the period. You can modify the checkpoint_timeout or max_wal_size value to increase the frequency of checkpoint executions.