6.4. Views #
6.4.1. Global Views #
Shardman has a list of global views based on the PostgeSQL local views. The definition of global view columns is the same as in its corresponding local view. Fetching from a global view returns a union of rows from the corresponding local views. The rows are fetched from each of their cluster nodes. Another difference is that the global views have an added column rgid. The rgid value shows the replication group ID of the cluster node from which a row is fetched.
Below is the list of the global views with links to their corresponding local views:
Table 6.2. Global and local views
| Global view | Local view | Description |
|---|---|---|
shardman.gv_stat_activity | pg_stat_activity | One row per server process, showing information related to he current activity of that process. |
shardman.gv_stat_progress_vacuum | pg_stat_progress_vacuum | Whenever VACUUM is running, the pg_stat_progress_vacuum view will contain one row for each backend (including autovacuum worker processes) that is currently vacuuming. |
shardman.gv_stat_replication | pg_stat_replication | One row per WAL sender process, showing statistics about replication to that sender's connected standby server. |
shardman.gv_stat_replication_slots | pg_stat_replication_slots | One row per replication slot, showing statistics about the replication slot's usage. |
shardman.gv_stat_subscription | pg_stat_subscription | One row per subscription for main worker (with null PID if the worker is not running), and additional rows for workers handling the initial data copy of the subscribed tables. |
shardman.gv_stat_ssl | pg_stat_ssl | One row per backend or WAL sender process, showing statistics about SSL usage on this connection. |
shardman.gv_stat_gssapi | pg_stat_gssapi | One row per backend, showing information about GSSAPI usage on this connection. |
shardman.gv_stat_archiver | pg_stat_archiver | One row only, showing statistics about the WAL archiver process's activity. |
shardman.gv_stat_bgwriter | pg_stat_bgwriter | One row only, showing statistics about the background writer process's activity. |
shardman.gv_stat_wal | pg_stat_wal | One row only, showing statistics about WAL activity. |
shardman.gv_stat_database | pg_stat_database | One row per database, showing database-wide statistics about query cancels due to conflict with recovery on standby servers. |
shardman.gv_stat_database_conflicts | pg_stat_database_conflicts | One row per database, showing database-wide statistics about query cancels occurring due to conflicts with recovery on standby servers. This view will only contain information on standby servers, since conflicts do not occur on primary servers. |
shardman.gv_stat_all_tables | pg_stat_all_tables | One row for each table in the current database, showing statistics about accesses to that specific table. |
shardman.gv_stat_all_indexes | pg_stat_all_indexes | One row for each index in the current database, showing statistics about accesses to that specific index. |
shardman.gv_statio_all_tables | pg_statio_all_tables | One row for each table in the current database, showing statistics about I/O on that specific table. |
shardman.gv_statio_all_indexes | pg_statio_all_indexes | One row for each index in the current database, showing statistics about I/O on that specific index. |
shardman.gv_statio_all_sequences | pg_statio_all_sequences | One row for each sequence in the current database, showing statistics about I/O on that specific sequence. |
shardman.gv_stat_user_functions | pg_stat_user_functions | One row for each tracked function, showing statistics about executions of that function. |
shardman.gv_stat_slru | pg_stat_slru | One row per SLRU, showing statistics of operations. |
shardman.gv_locks | pg_locks | This view provides access to information about the locks held by active processes within the database server. |
shardman.gv_shmem_allocations | pg_shmem_allocations | This view shows allocations made from the server's main shared memory segment. |
shardman.gv_backend_memory_contexts | pg_backend_memory_contexts | This view displays all the memory contexts of the server process attached to the current session. |
shardman.gv_stat_csn | shardman.pg_stat_csn | One row showing statistics on delays that take place during import of CSN snapshots. |
shardman.gv_stat_monitor | shardman.pg_stat_monitor | One row showing metrics of the Shardman monitor. |
shardman.gv_stat_netusage | shardman.pg_stat_net_usage | One row showing the cumulative network traffic between Shardman cluster nodes. |
shardman.gv_stat_xact_time | shardman.pg_stat_xact_time | One row showing statistics for the time spent on a transaction. |
shardman.gv_silk_routes | shardman.silk_routes | This view displays the current snapshot of the multiplexer routing table. |
shardman.gv_silk_connects | shardman.silk_connects | This view displays the current list of multiplexer connects. |
shardman.gv_silk_backends | shardman.silk_backends | This view displays the current list of processes of two kinds: backends that serve client connections and silkworm multiplexer workers, which interact with the multiplexer. |
shardman.gv_silk_pending_jobs | shardman.silk_pending_jobs | This view displays the current list of routes in the queue of multiplexer jobs that are not assigned to workers yet. |
shardman.gv_silk_routing | shardman.silk_routing | This view displays the results of the shardman.silk_routing function. |
shardman.gv_stats_sdm_statements | pgpro_stats_sdm_statements | This view allows accessing the aggregated statistics for the distributed queries. This view can only be created if Shardman is installed for the database that has pgpro_stats. The pgpro_stats must be created on all the cluster nodes for the global view to work. |
shardman.gv_lock_graph | shardman.lock_graph | This view displays a graph of locks between processes on Shardman cluster nodes including external locks. This view is based on the pg_locks and pg_prepared_xacts system views and on the pg_stat_activity view of the Statistics Collector. |
shardman.gv_stat_foreign_bytes | shardman.pg_stat_foreign_stat_bytes | This view shows the amount of statistics for foreign relations transferred over the network between Shardman cluster nodes. |
6.4.2. shardman.pg_stat_csn #
The shardman.pg_stat_csn view has one row showing statistics on delays that take place during import of CSN snapshots. These delays occur because system clocks on Shardman cluster nodes may be out of sync. The delays negatively impact the performance by increasing the query latency. The shardman.pg_stat_csn view allows tracking these delays. The view data is based on The Statistics Collector. The columns of the view are shown in Table 6.3.
Table 6.3. shardman.pg_stat_csn Columns
| Name | Type | Description |
|---|---|---|
csn_snapshots_imported | bigint | Total number of imported CSN snapshots |
csn_total_import_delay | interval | Total duration of all delays in importing CSN snapshots, in microseconds |
csn_max_shift | bigint | Maximum registered snapshot CSN shift that caused a delay |
local_oldest_csn | bigint | CSN of the oldest transaction on the current node |
local_oldest_xid | xid | XID of the oldest transaction on the current node |
indoubt_threshold_incidents | bigint | Total number of transactions that exceeded the 10 seconds limit in the inDoubt state. |
CSNXidMap_head_csn | bigint | Most recent CSN in the CSNSnapshotXidMap |
CSNXidMap_head_xid | xid | XID corresponding to the most recent CSN in the CSNSnapshotXidMap |
CSNXidMap_tail_csn | bigint | Oldest CSN in the CSNSnapshotXidMap |
CSNXidMap_tail_xid | xid | XID corresponding to the oldest CSN in the CSNSnapshotXidMap |
stats_reset | timestamp with time zone | Time at which these statistics were last reset |
CSNXidMap_last_trim | timestamp with time zone | Shows the last time when the shardman.trim_csnxid_map() function was called. |
To reset CSN-related statistics, call the pg_stat_reset_shared function with the only text argument equal to csn.
Note
Shardman functionality related to CSN snapshots is work in progress. So anticipate changes to the corresponding views in future releases.
6.4.3. shardman.pg_stat_xact_time #
The shardman.pg_stat_xact_time view shows statistics for the time spent on a transaction. The columns of the view are shown in Table 6.4.
Table 6.4. shardman.pg_stat_xact_time Columns
| Name | Type | Description |
|---|---|---|
overall_committed_xact_time | bigint | Overall time spent for the committed transactions |
overall_aborted_xact_time | bigint | Overall time spent for the aborted transactions |
overall_commit_time | bigint | Overall time spent for the committing transactions |
local_commit_time | bigint | Overall time spent for writing to WAL for all the committed transactions |
global_commit_time | bigint | Overall time spent for the distributed queries sending messages about transaction statuses for all the committed transactions |
overall_abort_time | bigint | Overall time spent for aborting transactions |
local_abort_time | bigint | Overall time spent for writing to WAL for all the aborted transactions |
global_abort_time | bigint | Overall time spent for the distributed queries sending messages about transaction statuses for all the aborted transactions |
stats_reset | timestamp with time zone | Time at which these statistics were last reset |
6.4.4. shardman.oldest_csn #
The shardman.oldest_csn view has one row showing tuple csn, xid, and rgid containing CSN and XID of the oldest transaction in the cluster along with transaction's replication group number.
6.4.5. shardman.pg_stat_monitor #
The shardman.pg_stat_monitor view has one row showing metrics of the Shardman monitor. The view data is based on the Statistics Collector. The columns of the view are shown in Table 6.5.
Table 6.5. shardman.pg_stat_monitor Columns
| Name | Type | Description |
|---|---|---|
resolved_deadlocks | bigint | Number of resolved distributed deadlocks |
aborted_xacts | bigint | Number of aborted outdated prepared transactions |
committed_xacts | bigint | Number of committed outdated prepared transactions |
errors | bigint | Number of Shardman monitor errors |
stats_reset | timestamp with time zone | Time at which these statistics were last reset |
6.4.6. shardman.pg_stat_netusage #
The shardman.pg_stat_netusage view has one row showing the cumulative network traffic between Shardman cluster nodes. The view data is based on the Statistics Collector. The columns of the view are shown in Table 6.6.
Table 6.6. shardman.pg_stat_netusage Columns
| Name | Type | Description |
|---|---|---|
netusage_recv_bytes | numeric | Total number of bytes received from other nodes through the network by each Shardman cluster node |
netusage_sent_bytes | numeric | Total number of bytes sent to other nodes through the network by each Shardman cluster node |
stats_reset | timestamp with time zone | Time at which these statistics were last reset |
6.4.7. shardman.pg_stat_foreign_stat_bytes #
The shardman.pg_stat_foreign_stat_bytes view shows the amount of statistics for foreign relations transferred over the network between Shardman cluster nodes. The view data is based on The Statistics Collector. The columns of the view are shown in Table 6.7.
Table 6.7. shardman.pg_stat_foreign_stat_bytes Columns
| Name | Type | Description |
|---|---|---|
foreign_stat_recv_bytes | bigint | Total number of bytes of the statistics for the foreign relations received from other nodes through the network by this node |
stats_reset | timestamp with time zone | Time at which these statistics were last reset |
6.4.8. Multiplexor Diagnostics Views #
Views in this section provide various information related to Silk multiplexing. See Section 7.4 for details of silkroad multiplexing process.
6.4.8.1. shardman.silk_routes #
The shardman.silk_routes view displays the current snapshot of the multiplexer routing table. The columns of the view are shown in Table 6.8.
Table 6.8. shardman.silk_routes Columns
| Name | Type | Description |
|---|---|---|
hashvalue | integer | Internal unique route identifier. Can be used to join with other Silk diagnostics views. |
origin_ip | inet | IP address of the source node, which generated this route |
origin_port | int2 | External TCP connection port of the source node, which generated this route |
channel_id | integer | Route sequential number within the node that generated this route. channel_id is unique for the pair origin_ip + origin_port. This pair is a unique node identifier within the Shardman cluster and hence the origin_ip + origin_port + channel_id tuple is a unique route identifier within the Shardman cluster. |
from_cn | integer | Connect index in the shardman.silk_connects view for incoming routes, that is, not generated by this node, and -1 for routes generated by this node. |
backend_id | integer | ID of the local process that is currently using this route: either the ID of the backend that generated this route or the ID of the silkworm worker assigned to this route. Equals -1 for queued incoming routes that have not been assigned a worker yet. |
pending_queue_bytes | bigint | Size of the queue of delayed messages (awaiting a free worker) for this route, in bytes. This value is only meaningful for incoming routes of each node that are not assigned to a worker yet. |
pending_queue_messages | bigint | Number of messages in the queue of delayed messages (awaiting a free worker) for this route. This value is only meaningful for incoming routes of each node that are not assigned to a worker yet. |
connects | integer[] | List of indexes of connects that are currently using this route. |
6.4.8.2. shardman.silk_connects #
The shardman.silk_connects view displays the current list of multiplexer connects. The columns of the view are shown in Table 6.9.
Table 6.9. shardman.silk_connects Columns
| Name | Type | Description |
|---|---|---|
cn_index | integer | Unique connect index |
reg_ip | inet | “Registration” IP address of the node with which the connection is established. See Notes for details. |
reg_port | int2 | “Registration” TCP port of the node with which the connection is established. See Notes for details. |
read_ev_active | boolean | true if the multiplexer is ready to receive data to the incoming queue. See Notes for details. |
write_ev_active | boolean | true if the multiplexer filled the queue of non-sent messages and is waiting for it to get free. See Notes for details. |
is_outgoing | boolean | true if the connection is outgoing, that is, created by connect, and false for incoming connects, that is, created by accept. Only used during the handshaking. |
state | text | Current state of the connect: connected — if the connection is established, in progress — if the client has already connected, but handshaking has not happened yet, free — if the client has already disconnected, but the connect structure for the disconnected client has not been destroyed yet. |
pending_queue_bytes | bigint | Size of the queue of non-sent messages for this connect, in bytes |
pending_queue_messages | bigint | Number of messages in the queue of non-sent messages for this connect |
blocked_by_backend | integer | ID of the backend that blocked this connect |
blocks_backends | integer[] | List of IDs of backends that are blocked by this connect |
routes | integer[] | List of unique IDs of routes that use this connect |
6.4.8.3. shardman.silk_backends #
The shardman.silk_backends view displays the current list of processes of two kinds: backends that serve client connections and silkworm multiplexer workers, which interact with the multiplexer. The columns of the view are shown in Table 6.10.
Table 6.10. shardman.silk_backends Columns
| Name | Type | Description |
|---|---|---|
backend_id | integer | Unique backend/worker identifier |
pid | integer | OS process ID |
attached | boolean | Value is true if backend is attached to multiplexer, false otherwis |
read_ev_active | boolean | true if the backend/worker is ready to receive data to the incoming queue. See Notes for details. |
write_ev_active | boolean | true if the backend/worker filled the queue of non-sent messages and is waiting for it to get free. See Notes for details. |
is_worker | boolean | true if this process is a silkworm multiplexer worker and false otherwise |
pending_queue_bytes | bigint | Size of the queue of messages being sent to this backend/worker, in bytes |
pending_queue_messages | bigint | Number of messages in the queue of messages being sent to this backend/worker |
blocked_by_connect | integer | Index of the connect that blocks this backend/worker |
blocks_connects | integer[] | List of indexes of connects that are blocked by this backend/worker |
routes | integer[] | List of unique IDs of routes that are used by this backend/worker |
in_queue_used | bigint | Number of queued data bytes in the incoming queue in the shared memory between the backend and multiplexer |
out_queue_used | bigint | Number of queued data bytes in the outgoing queue in the shared memory between the backend and multiplexer |
6.4.8.4. shardman.silk_routing #
The shardman.silk_routing view displays the results of the shardman.silk_routing function. Table 6.11.
Table 6.11. shardman.silk_routing Columns
| Name | Type | Description |
|---|---|---|
hashvalue | integer | Internal unique route identifier |
origin_ip | inet | IP address of the node that generated this route |
origin_port | int2 | External TCP connection port of the source node that generated this route |
channel_id | integer | Route sequential number within the node that generated this route |
is_reply | bool | Index of the connect from which a message was received that caused generation of this route |
pending_queue_bytes | bigint | Pending queue size, in bytes |
pending_queue_messages | bigint | Number of pending queue messages |
backend_id | integer | ID of the local process that is currently using this route: either the ID of the backend that generated this route or the ID of the silkworm worker assigned to this route. Equals -1 for queued incoming routes that have not been assigned a worker yet. |
backend_pid | integer | Returns the process ID of the server process attached to the current session |
attached | boolean | Value is true if backend is attached to multiplexer, false otherwis |
backend_rd_active | boolean | true if the backend/worker is ready to receive data to the incoming queue. See Notes for details. |
backend_wr_active | boolean | true if the backend/worker filled the queue of non-sent messages and is waiting for it to get free. See Notes for details. |
is_worker | boolean | true if this process is a silkworm multiplexer worker and false otherwise |
backend_blocked_by_cn | integer | Index of the connect that blocks this backend/worker |
blocks_connects | integer[] | List of indexes of connects that are blocked by this backend/worker |
in_queue_used | bigint | Number of queued data bytes in the incoming queue in the shared memory between the backend and multiplexer |
out_queue_used | bigint | Number of queued data bytes in the outgoing queue in the shared memory between the backend and multiplexer |
connect_id | integer | Unique connect index |
reg_ip | inet | “Registration” IP address of the node with which the connection is established |
reg_port | int2 | “Registration” TCP port of the node with which the connection is established |
connect_rd_active | boolean | true if the multiplexer is ready to receive data to the incoming queue |
connect_wr_active | boolean | true if the multiplexer filled the queue of non-sent messages and is waiting for it to get free |
connect_is_outgoing | boolean | true if the connection is outgoing, that is, created by connect, and false for incoming connects, that is, created by accept. Only used during the handshaking. |
connect_state | text | Current state of the connect: connected — if the connection is established, in progress — if the client has already connected, but handshaking has not happened yet, free — if the client has already disconnected, but the connect structure for the disconnected client has not been destroyed yet |
connect_outgoing_queue_bytes | bigint | Size of the queue of non-sent messages for this connect, in bytes |
connect_outgoing_queue_messages | bigint | Number of messages in the queue of non-sent messages for this connect |
connect_blocked_by_bk | integer | ID of the backend that blocked this connect |
blocks_backends | integer[] | List of IDs of backends that are blocked by this connect |
6.4.8.5. shardman.silk_pending_jobs #
The shardman.silk_pending_jobs view displays the current list of routes in the queue of delayed multiplexer jobs, that is, jobs that are not assigned to workers yet. The columns of the view are shown in Table 6.12.
Table 6.12. shardman.silk_pending_jobs Columns
| Name | Type | Description |
|---|---|---|
hashvalue | integer | Internal unique route identifier |
origin_ip | inet | IP address of the node that generated this route |
origin_port | int2 | TCP connection port of the node that generated this route |
channel_id | integer | Route sequential number within the node that generated this route |
query | text | The first queued message |
pending_queue_bytes | bigint | Pending queue size, in bytes |
pending_queue_messages | bigint | Number of pending queue messages |
6.4.8.6. shardman.silk_statinfo #
The shardman.silk_statinfo view displays the current multiplexer state information. The columns of the view are shown in Table 6.13.
Table 6.13. shardman.silk_statinfo Columns
| Name | Type | Description |
|---|---|---|
pid | integer | silkroad process ID |
started_at | timestamp with time zone | Time when the silkroad backend was started. |
transferred_bytes | json | JSON object of key value pairs, where the key is the name of the message type, and the value is total number of bytes sent for the message types with at least one message sent |
transferred_pkts | json | JSON object of key value pairs, where the key is the name of the message type, and the value is the total number of sent messages for the message types with at least one message sent |
transferred_max | json | JSON object of key value pairs, where the key is the name of the message type, and the value is the maximum size of a message for the message types with at least one message sent |
memcxt_dpg_allocated | bigint | The mem_allocated value of the process in DPGMemoryContext |
memcxt_top_allocated | bigint | The mem_allocated value of the process in TopMemoryContext |
read_efd_max | bigint | Maximum reading time of the eventfd since reset |
write_efd_max | bigint | Maximum writing time of the eventfd since reset |
read_efd_total | bigint | Total reading time of the eventfd since reset |
write_efd_total | bigint | Total writing time of the eventfd since reset |
read_efd_count | bigint | Total number of reading events of the eventfd since reset |
write_efd_count | bigint | Total number of writing events of the eventfd since reset |
sort_time_max | bigint | Maximum time of sorting operations with the silk_flow_control enabled (any value other than none) |
sort_time_total | bigint | Total time of sorting operations with the silk_flow_control enabled (any value other than none) |
sort_time_count | bigint | Total number of the sorting operations with the silk_flow_control enabled (any value other than none) |
Note that read_efd_max, write_efd_max, read_efd_total, write_efd_total, read_efd_count, write_efd_count, sort_time_max, sort_time_total, and sort_time_count are only calculated if the shardman.silk_track_time configuration parameter is enabled.
6.4.8.7. shardman.silk_state #
The shardman.silk_state view displays the current silkroad process state. The columns of the view are shown in Table 6.14.
Table 6.14. shardman.silk_state Columns
| Name | Type | Description |
|---|---|---|
state | text | State of the silkroad process |
6.4.8.8. Notes #
reg_ip and reg_port values are not actual network addresses, but the addresses by which the multiplexer accesses the node. They are determined during a handshake between multiplexer nodes and are equal to the corresponding parameters of an appropriate server in the pg_foreign_server table.
All the read_ev_active values are true and all the write_ev_active values are false when the multiplexer is in the idle state.