Skip to main content
Version: Next

gp_stats_collector

The gp_stats_collector module collects query execution metrics in Apache Cloudberry and reports them to an external monitoring agent. It hooks into the executor on the coordinator and on the segments, and for each query it sends lifecycle events (query text, plan, timestamps, user and database) together with system, instrumentation, interconnect, and spill metrics. Events are delivered over a Unix domain socket, or can be written to a log table inside the database.

The module also provides an on-demand runtime query state interface (pg_query_state) that makes every backend of a running query report per-plan-node statistics without waiting for the query to finish.

Build and install​

gp_stats_collector is not built by default. To build it, configure Apache Cloudberry with the --with-gp-stats-collector option:

./configure --with-gp-stats-collector [other options]

When this option is set, configure requires:

  • The protobuf library, version 3.0.0 or later (detected through pkg-config).
  • The protoc compiler in PATH.

The module contains C++ sources that are compiled with -std=c++17.

Enable the module​

  1. Add the library to shared_preload_libraries and restart the cluster. The module allocates shared memory and registers signal handlers at server start, so it must be preloaded:

    gpconfig -c shared_preload_libraries -v 'gp_stats_collector'
    gpstop -ra

    If shared_preload_libraries already lists other libraries, keep them in the value.

  2. Create the extension in the database where you want to use the SQL objects. The extension requires superuser privileges to create:

    CREATE EXTENSION gp_stats_collector;

    The extension creates the gpsc schema. The default extension version is 1.2.

  3. Turn on collection. Collection is off by default:

    SET gpsc.enable TO on;

Collected data​

A query passes through four states, and the module sends one message per state from the dispatcher process (on the coordinator) and from the executor processes (on segments):

submit -> ExecutorStart() -> start -> ExecutorRun() -> ExecutorFinish() -> end -> ExecutorEnd() -> done
DataDescriptionControlled by
Query lifecycleQuery text, normalized query text, timestamps (submit, start, end, done), user and database information.gpsc.enable
EXPLAIN dataThe output of EXPLAIN (TEXT, COSTS, VERBOSE) for the query.gpsc.enable
EXPLAIN ANALYZE dataThe output of EXPLAIN (TEXT, ANALYZE, BUFFERS, TIMING, VERBOSE). Sent only when the execution time reaches gpsc.min_analyze_time.gpsc.enable, gpsc.enable_analyze, gpsc.enable_cdbstats, gpsc.min_analyze_time
Other metricsInstrumentation, system, network, interconnect, and spill metrics.gpsc.enable

Notes on the reported events:

  • Some queries skip the end state. In that case the end statistics are sent with done.
  • If a query fails or is canceled, its statistics are sent with done.
  • Some statistics are computed as the difference between the current value and a snapshot taken at submit.
  • Each process (dispatcher or executor) sends its own statistics.
  • A nested query on the dispatcher becomes a top-level query on the executors.

Configuration parameters​

Collector parameters​

A parameter with the superuser context can be changed only by a superuser. A parameter with the user context can be changed by any role in its session.

ParameterTypeDefaultContextDescription
gpsc.enablebooleanoffsuperuserEnables the metrics collector.
gpsc.uds_pathstring/tmp/gpsc_agent.socksuperuserFile system path of the agent's Unix domain socket.
gpsc.logging_modeenumudssuperuserDestination of collected events. uds sends events to the Unix domain socket. tbl writes events to the log table that is exposed through the gpsc.log view. Only superusers can view this setting.
gpsc.enable_analyzebooleanonsuperuserCollects EXPLAIN ANALYZE data. When it is on, the dispatcher enables buffer, row, and timing instrumentation for the queries it runs.
gpsc.enable_cdbstatsbooleanonsuperuserAdds Cloudberry-specific executor statistics to the EXPLAIN ANALYZE data. Takes effect only when gpsc.enable_analyze is on.
gpsc.min_analyze_timeinteger (ms)10000userMinimum execution time above which the EXPLAIN ANALYZE text is reported. 0 reports it for all queries. -1 turns the feature off.
gpsc.report_nested_queriesbooleanonuserWhen on, both top-level and nested queries are reported from the coordinator, and segments report aggregates. When off, only top-level queries are reported from the coordinator and segments.
gpsc.enable_utilitybooleanoffuserCollects statistics for utility statements.
gpsc.ignored_users_liststringemptysuperuserComma-separated list of roles whose queries are not reported.
gpsc.max_text_sizeinteger (bytes)1048576superuserQuery texts and error messages longer than this size are trimmed. Range: 0 to 2147483647.
gpsc.max_plan_sizeinteger (kB)1024superuserPlan texts longer than this size are trimmed. Range: 0 to 2097151.
note

It is not recommended to set gpsc.max_text_size or gpsc.max_plan_size higher than 1024 kB.

Runtime query state parameters​

These parameters are registered only when the library is loaded through shared_preload_libraries.

ParameterTypeDefaultContextDescription
pg_query_state.enablebooleanonsuperuserEnables the runtime query state module (executor hooks and signal handling).
pg_query_state.enable_timingbooleanonsuperuserCollects timing data in addition to row counts.
pg_query_state.enable_buffersbooleanonsuperuserCollects buffer usage.

SQL objects​

All objects are created in the gpsc schema. USAGE on the schema is granted to PUBLIC.

The gpsc.log and gpsc.stat_messages views are not granted to PUBLIC. All functions keep the default EXECUTE privilege for PUBLIC, and gpsc.truncate_log() and gpsc.stat_messages_reset() do not check the privileges of the caller. Revoke EXECUTE on these functions from PUBLIC if only administrators should run them.

Interconnect statistics are collected only when the cluster uses the UDPIFC interconnect.

gpsc.stat_messages​

A view that shows, for the coordinator and for each segment, counters of the messages that the collector has tried to send. Querying it fails with an error if the library is not loaded through shared_preload_libraries.

ColumnTypeDescription
segidintSegment content ID. -1 is the coordinator.
total_messagesbigintTotal number of messages, including failed ones.
send_failuresbigintNumber of messages that failed to be sent.
connection_failuresbigintNumber of failed connections to the agent socket.
other_errorsbigintNumber of other errors.
max_message_sizeintSize of the largest message.

gpsc.stat_messages_reset()​

gpsc.stat_messages_reset() RETURNS SETOF void

Resets the counters shown in gpsc.stat_messages on the coordinator and on all segments.

gpsc.log​

A view over the log table on the coordinator and on all segments, ordered by tmid, ssid, ccnt. The table is filled when gpsc.logging_mode is tbl. Each row is one reported event.

Rows are inserted in a way that keeps them even if the transaction that ran the query rolls back.

ColumnTypeDescription
query_idbigintQuery ID.
plan_idbigintHash of the normalized plan.
nested_levelbigintNesting level of the query.
slice_idbigintSlice ID.
systemstat_vsize, systemstat_rss, systemstat_vmsizekb, systemstat_vmpeakkbbigintProcess memory figures from /proc/pid/stat and /proc/pid/status.
systemstat_rchar, systemstat_wchar, systemstat_syscr, systemstat_syscw, systemstat_read_bytes, systemstat_write_bytes, systemstat_cancelled_write_bytesbigintProcess I/O counters from /proc/pid/io.
instrumentation_ntuples, instrumentation_nloops, instrumentation_tuplecountbigintTuple and loop counters.
instrumentation_shared_blks_hit, instrumentation_shared_blks_read, instrumentation_shared_blks_dirtied, instrumentation_shared_blks_writtenbigintShared buffer block counters.
instrumentation_local_blks_hit, instrumentation_local_blks_read, instrumentation_local_blks_dirtied, instrumentation_local_blks_writtenbigintLocal buffer block counters.
instrumentation_temp_blks_read, instrumentation_temp_blks_writtenbigintTemporary file block counters.
instrumentation_inherited_callsbigintNumber of nested queries.
instrumentation_sent_total_bytes, instrumentation_sent_tuple_bytes, instrumentation_sent_chunksbigintNetwork data sent.
instrumentation_received_total_bytes, instrumentation_received_tuple_bytes, instrumentation_received_chunksbigintNetwork data received.
interconnect_total_recv_queue_size, interconnect_recv_queue_size_counting_time, interconnect_total_capacity, interconnect_capacity_counting_time, interconnect_total_buffers, interconnect_buffer_counting_time, interconnect_active_connections_num, interconnect_retransmits, interconnect_startup_cached_pkt_num, interconnect_mismatch_num, interconnect_crc_errors, interconnect_snd_pkt_num, interconnect_recv_pkt_num, interconnect_disordered_pkt_num, interconnect_duplicated_pkt_num, interconnect_recv_ack_num, interconnect_status_query_msg_numbigintInterconnect statistics.
spill_totalbytesbigintSpill bytes written.
systemstat_runningtimeseconds, systemstat_usertimeseconds, systemstat_kerneltimesecondsdouble precisionWall clock, user, and kernel time in seconds.
instrumentation_firsttuple, instrumentation_startup, instrumentation_total, instrumentation_blk_read_time, instrumentation_blk_write_time, instrumentation_startup_time, instrumentation_inherited_timedouble precisionTiming figures in seconds.
datetime, submit_time, start_time, end_timetimestamp with time zoneEvent and query timestamps.
tmid, ssid, ccntintQuery key: a time-based ID from gp_gettmid(), the session ID, and the command counter.
dbidintThe dbid of the reporting instance, as in gp_segment_configuration.
segidintSegment index. -1 is the coordinator.
spill_filecountintNumber of spill (temporary) files created.
generatortextPlanner or optimizer that produced the plan.
query_text, template_query_texttextQuery text and normalized query text.
plan_text, template_plan_texttextEXPLAIN text and normalized plan text.
analyze_texttextEXPLAIN ANALYZE text.
user_name, database_name, rsgnametextSession user, database, and resource group.
error_messagetextError message, if the query failed.
query_statustextEvent state, for example QUERY_STATUS_SUBMIT, QUERY_STATUS_START, QUERY_STATUS_END, QUERY_STATUS_DONE.
utilitybooleanWhether the event belongs to a utility statement.

gpsc.truncate_log()​

gpsc.truncate_log() RETURNS SETOF void

Truncates the log table on the coordinator and on all segments.

gpsc.pg_query_state()​

gpsc.pg_query_state(pid int, trace_id bytea) RETURNS SETOF void

Triggers runtime per-node collection for the query that is running on the coordinator backend pid. The request is fanned out to the coordinator backend and to every segment backend that takes part in the query. Each backend walks its plan tree and pushes one batch of per-node statistics to the socket set in gpsc.uds_path. The coordinator also pushes the plan document, rate-limited so that repeated polls of a long query do not resend an unchanged plan. The function returns no rows and does not wait for the results.

  • trace_id must be exactly 16 bytes. The caller generates it, and the receiver uses it as the key of the collection.
  • The function runs on the coordinator only.
  • A backend cannot poll itself.

gpsc.pg_query_state_backends()​

gpsc.pg_query_state_backends(pid int) RETURNS TABLE(segid int, pid int)

Lists the segment backends that take part in the query running on backend pid, so that a collector knows how many batches to expect. A query that runs only on the coordinator is reported as a single row for the coordinator with segid less than 0. The function returns an empty set when the target is not running a query or has pg_query_state.enable turned off.

Internal objects​

The following objects are created by the extension but are not meant to be called directly:

  • gpsc.cbdb_mpp_query_state(gpsc.gp_segment_pid[], bytea) and the composite type gpsc.gp_segment_pid (segid int, pid int): used by gpsc.pg_query_state() to dispatch the request to segments.
  • Functions whose names start with two underscores, such as gpsc.__stat_messages_f_on_master() or gpsc.__test_uds_start_server(text): helpers behind the views and functions above, and helpers for the regression tests.

Privileges​

EXECUTE on gpsc.pg_query_state(), gpsc.pg_query_state_backends(), and gpsc.cbdb_mpp_query_state() is granted to PUBLIC. Access is checked in the server: a caller can poll a backend only if the caller is a superuser or runs under the same role as the target backend. This lets a monitoring agent use a non-superuser role without being able to observe queries of other roles.

Example​

The following example writes events to the log table instead of the socket and then reads them back.

CREATE EXTENSION gp_stats_collector;

-- Helper to sort the events of one query in lifecycle order
CREATE OR REPLACE FUNCTION gpsc_status_order(status text)
RETURNS integer
AS $$
BEGIN
RETURN CASE status
WHEN 'QUERY_STATUS_SUBMIT' THEN 1
WHEN 'QUERY_STATUS_START' THEN 2
WHEN 'QUERY_STATUS_END' THEN 3
WHEN 'QUERY_STATUS_DONE' THEN 4
ELSE 999
END;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

SET gpsc.ignored_users_list TO '';
SET gpsc.enable TO TRUE;
SET gpsc.report_nested_queries TO TRUE;
SET gpsc.enable_utility TO FALSE;

SET gpsc.logging_mode to 'TBL';

SELECT 1;
SELECT COUNT(*) FROM generate_series(1,10);

RESET gpsc.logging_mode;

SELECT segid, query_text, query_status FROM gpsc.log ORDER BY segid, ccnt, gpsc_status_order(query_status) ASC;
segid | query_text | query_status
-------+---------------------------------------------+---------------------
-1 | SELECT 1; | QUERY_STATUS_SUBMIT
-1 | SELECT 1; | QUERY_STATUS_START
-1 | SELECT 1; | QUERY_STATUS_END
-1 | SELECT 1; | QUERY_STATUS_DONE
-1 | SELECT COUNT(*) FROM generate_series(1,10); | QUERY_STATUS_SUBMIT
-1 | SELECT COUNT(*) FROM generate_series(1,10); | QUERY_STATUS_START
-1 | SELECT COUNT(*) FROM generate_series(1,10); | QUERY_STATUS_END
-1 | SELECT COUNT(*) FROM generate_series(1,10); | QUERY_STATUS_DONE
(8 rows)

To clear the log table:

SELECT gpsc.truncate_log();

To poll a running query, generate a 16-byte trace ID and pass the process ID of the coordinator backend that runs the query:

SELECT * FROM gpsc.pg_query_state_backends(12345);
SELECT gpsc.pg_query_state(12345, '\x00112233445566778899aabbccddeeff'::bytea);

Usage notes​

  • Delivery is best-effort. A message that cannot be written to the socket is dropped and is not retried, so a slow or absent agent does not add latency to queries. Use gpsc.stat_messages to check for failures.
  • Runtime query state results are pushed to the agent socket. gpsc.pg_query_state() does not return them to the SQL caller.
  • gpsc.pg_query_state() and gpsc.pg_query_state_backends() report an error if the library is not loaded through shared_preload_libraries, if no backend with the given pid exists, or if the caller is not allowed to poll the target backend. gpsc.pg_query_state_backends() also reports an error if pid belongs to a segment executor process instead of the coordinator backend of the session.
  • The process-level system statistics are read from the /proc file system.