By querying the PGXC_BULKLOAD_INFO view on CNs, you can obtain historical statistics information for interconnection, GDS, COPY, and \COPY business executions after they have completed. This view summarizes the historical execution information of import and export business that have already completed on each node of the current cluster (including the interconnection cluster address, import and export business type, maximum, minimum, and total number of rows and bytes written to disk on DNs, etc.), to obtain historical information on import and export business execution and assist in performance troubleshooting.
This view does not record abnormal interruptions of import and export jobs. The data is directly obtained from the system catalog GS_WLM_SESSION_INFO, and the loader_status field is parsed to obtain import and export service information.
System administrator rights are required to access this view.
Column |
Type |
Description |
|---|---|---|
datid |
OID |
OID of the database the backend is connected to. |
dbname |
Text |
Name of the database the backend is connected to. |
schemaname |
Text |
Schema name. |
nodename |
Text |
Name of the CN where the statement is run. |
username |
Text |
Username for connecting to the backend. |
application_name |
Text |
Name of the application that is connected to the backend. |
client_addr |
inet |
IP address of the client connected to the backend. If this column is null, it indicates that the client is connected via a Unix socket on the server machine or that it is an internal process, such as autovacuum. |
client_hostname |
Text |
Host name of the client, which is obtained by reverse DNS lookup of client_addr. This column is only non-null when log_hostname is enabled and IP connection is used. |
client_port |
Integer |
TCP port number used by the client to communicate with the backend. If a Unix socket is used, it is –1. |
query_band |
Text |
Job type, which can be set using the GUC parameter query_band and is a null string by default. |
block_time |
Bigint |
Blocking time before statement execution, including statement parsing and optimization time, in milliseconds. |
start_time |
Timestamp with time zone |
Start time of statement execution. |
finish_time |
Timestamp with time zone |
End time of statement execution. |
status |
Text |
End status of statement execution: finished for normal and aborted for abnormal. The statement status recorded here should be the database server execution status. When the server-side execution is successful and an error occurs when the result set is returned, the statement should be finished. |
queryid |
Bigint |
Internal query ID used for statement execution. |
query |
Text |
Executed statement. |
session_id |
Text |
A session uniquely identified in the database system, in the format of session_start_time.tid.node_name. |
address |
Text |
Server address of the interconnection peer cluster. When not empty, it indicates an interconnection service, and the source cluster will additionally obtain the remote cluster port number. |
direction |
Text |
Type of import and export service, including gds to file, gds from file, gds to pipe, gds from pipe, copy from, and copy to. |
min_done_lines |
json |
Minimum number of rows of a statement across all DNs. |
max_done_lines |
json |
Maximum number of rows of a statement across all DNs. |
total_done_lines |
json |
Total number of rows of a statement across all DNs. |
min_done_bytes |
json |
Minimum number of bytes of a statement across all DNs. |
max_done_bytes |
json |
Maximum number of bytes of a statement across all DNs. |
total_done_bytes |
json |
Total number of bytes of a statement across all DNs. |
- Abnormal interruptions of import and export jobs are not recorded in the view.
- The implementation mechanism of GDS foreign tables and interconnection foreign tables is different. When querying, GDS records the full amount, while interconnection records the actual amount.
- For non-full import and export foreign tables with a limit, due to the special execution plan of limit, the data displayed is collected from one DN, which appears as a maximum value of all and a minimum value of 0.
- If the import and export table is a non-partitioned table:
- When the GDS partitioned table is small, if one DN has finished collecting data and the other DNs have not started collecting data, they will not collect data. Therefore, when the data volume of GDS from non-partitioned tables is small, the minimum value may be 0, but it is not 0 when the table data volume is large.
- When exporting non-partitioned tables from the interconnection source cluster, all DNs will be recorded, and only one DN's data will be collected, so the minimum value is 0.
- When exporting replication tables from the interconnection remote cluster, only one DN will be recorded, so it is equivalent to having only one DN, and the minimum and maximum values are the same.
- Historical monitoring of import and export is implemented by reusing the historical TopSQL function, which follows the precautions, prerequisites, and operation steps of TopSQL. For details, refer to Historical Top SQL.
- Due to the large amount of data recorded by TopSQL, you are advised to query and use it as needed by combining fields such as start_time and finish_time to improve query performance, or to reduce query frequency.
Example
Use the PGXC_BULKLOAD_INFO view to query interconnection import service.
SELECT * FROM PGXC_BULKLOAD_INFO;
datid | dbname | schemaname | nodename | username | application_name | client_addr | client_hostname | client_port | query_band | block_time | start_time | finish_time | status
s | queryid | query
| session_id | address | direction | min_done_lines | max_done_lines | total_done_lines | min_done_by
tes | max_done_bytes | total_done_bytes
-------+----------+----------------+--------------+----------------+------------------+-------------+-----------------+-------------+------------+------------+-------------------------------+-------------------------------+-------
---+-------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------+-----------------------------------------+-------------------+---------------+----------------+----------------+------------------+------------
----+----------------+------------------
16134 | postgres | "$user",public | coordinator1 | interconn_user | gsql | | | -1 | | 0 | 2023-09-25 10:27:47.184696+08 | 2023-09-25 10:27:48.709665+08 | finish
ed | 72339069014639035 | INSERT INTO interconn_user.lineitem_dest SELECT * FROM interconn_user.ft_lineitem_local;
| 1695608841.140482657154648.coordinator1 | 10.90.45.56:63755 | gds from pipe | 19479 | 20971 | 60175 | 3251258
| 3500876 | 10038234
16134 | postgres | "$user",public | coordinator1 | interconn_user | interconnection | 10.90.45.56 | | 47668 | | 0 | 2023-09-25 10:27:47.256095+08 | 2023-09-25 10:27:48.582366+08 | finish
ed | 72339069014639046 | INSERT INTO pg_temp.ft_lineitem_local_72339069014639035_wo SELECT l_orderkey, l_partkey, l_suppkey, l_linenumber, l_quantity, l_extendedprice, l_discount, l_tax, l_returnflag, l_linestatus, l_shipdate, l_c
ommitdate, l_receiptdate, l_shipinstruct, l_shipmode, l_comment FROM public.lineitem; | 1695608867.140482657156768.coordinator1 | 10.90.45.56 | gds to pipe | 19476 | 20934 | 60175 | 3249308
| 3489789 | 10038234
(2 rows)