PolarDB:SHOW COLUMNAR OFFSET

Usage notes

The version of your PolarDB-X instance must be 5.4.19-16989811 or later.

Syntax

SHOW COLUMNAR OFFSET

Examples

SHOW COLUMNAR OFFSET;
+--------------------+---------------+-----------+---------------------+-------------------------+-------------+
| TYPE               | BinlogFile    | Position  | TSO                 | TIME                    | LATENCY(ms) |
+--------------------+---------------+-----------+---------------------+-------------------------+-------------+
| CDC                | binlog.000010 | 223718750 | 7185913700627251264 | 2024-04-16 16:15:34.050 | 218         |
| COLUMNAR_LATENCY   | binlog.000010 | 223718418 | 7185913684139442240 | 2024-04-16 16:15:30.119 | 3931        |
| CN_MIN_LATENCY     | binlog.000010 | 223718418 | 7185913684139442240 | 2024-04-16 16:15:30.119 | 3931        |
| CN_MAX_LATENCY     | binlog.000010 | 223714434 | 7185912425433006144 | 2024-04-16 16:10:30.020 | 304030      |
| CN_PURGE_WATERMARK | binlog.000010 | 223713604 | 7185912174181613632 | 2024-04-16 16:09:30.117 | 363933      |
+--------------------+---------------+-----------+---------------------+-------------------------+-------------+

The following table describes the parameters in the returned results.

• In a Change Data Capture (CDC) data row, the BinlogFile and Position parameters indicate the latest checkpoint of the primary instance, the TSO parameter indicates the timestamp of the binary log file checkpoint of data synchronized from CDC nodes to data nodes, the TIME parameter indicates the actual time of the TSO, and the LATENCY parameter indicates the latency elapsed when CDC nodes receive data from multiple data nodes to generate globally consistent binary log files.

• In a COLUMNAR_LATENCY data row, the BinlogFile and Position parameters indicate the binary log file checkpoint to which column store nodes are synchronized, the TSO parameter indicates the timestamp of the synchronization checkpoint, the TIME parameter indicates the actual time when the TSO value takes effect, and the LATENCY parameter indicates the internal calculated by subtracting the actual time of the TSO from the TIME value of CDC nodes.

• A CN_MIN_LATENCY data row records the minimum latency of compute nodes.

• A CN_MAX_LATENCY data row records the maximum latency of compute nodes.

• A CN_PURGE_WATERMARK data row records the watermark purged by compute nodes.

• In most cases, if the BinlogFile and Position values of the CDC data row are close to those of the COLUMNAR_LATENCY data row, column-oriented data is consistent with that of the primary instance.

FAQ

1. Why is no COLUMNAR_LATENCY data row found?

   This indicates that no column store nodes are available, or column store nodes do not synchronize data.

2. Why does the binary log file checkpoint of the CDC data row slightly differ from that of the COLUMNAR data row?

   In idle conditions, if no DML updates exist, CDC nodes consecutively generate 3 TSO events every 30 seconds to ensure the advancement of the binary log file checkpoint. As a result, column store nodes listen for binary log events and commit the first TSO event at regular intervals during data synchronization. This causes slight differences in the binary log file checkpoint between the rows.

3. Can the LATENCY column value of the COLUMNAR_LATENCY data row indicate the latency between column store nodes and the primary instance?

   The LATENCY column value of the COLUMNAR_LATENCY data row indicate the interval between the most recent TSO of CDC nodes and the TSO when binary log events are last committed by column store nodes. The value can be used only for reference. For example, in idle conditions, new binary log events occur every 30 seconds, and LATENCY column values may reach 30 seconds. In practice, no latency occurs because no data is written to binary log files. The binary log events are committed by the column store nodes in batch, which cause a large heartbeat interval.

4. How do I view the latest binary log file checkpoint and the binary log events of a PolarDB-X primary instance?

   You can execute the SHOW BINARY LOGS statement to view binary log files and the SHOW BINLOG EVENTS statement to view specific events in the files.