Quick onboard
Deployment
Data Modeling
Connecting
Migration
Query
Operations and Maintenance
Common Maintenance
Partition
Backup and Restore
Expansion
Mirroring
Resource Management
Security
Monitoring
Performance Tuning
Troubleshooting
Reference Guide
Tool guide
Data type
Storage Engine
Executor
Stream
DR (Disaster Recovery)
Configuration
Index
Extension
SQL Reference
This document describes the main components and functions of the matrixmgr extension.
The matrixmgr extension is primarily used for query statistics in YMatrix. After initializing a YMatrix cluster, the system automatically creates the matrixmgr database and installs the matrixmgr extension. Upon successful deployment, the following tables and views appear in the matrixmgr database under the matrixmgr_internal schema:
| View | Description |
|---|---|
| mxstat_execute | This view provides execution details of SQL statements, such as the segment instances where queries were executed, user information, query text, minimum and maximum execution times, and timestamps for each phase of the slowest statement. You can use this view to analyze execution durations across different SQL statements and investigate performance of the slowest queries |
| mxstat_usage | This view provides resource consumption metrics during SQL statement execution |
| mx_query_execute_history | Historical data collected from the mxstat_execute view |
| mx_query_usage_history | Historical data collected from the mxstat_usage view |
| mx_querytext | This view stores the mapping between queryid and querytext. You can use this mapping to retrieve SQL text |
| mx_query_execute_history_with_text | A joined view combining mx_query_execute_history and mx_querytext using queryid |
Connect to the matrixmgr database to access the following views:
This view displays query statistics collected over the last 5 minutes.
| Column | Type | Description |
|---|---|---|
| seg | integer | Segment instance ID where the query plan was generated and dispatched |
| userid | oid | User OID |
| dbid | oid | Database OID |
| queryid | bigint | Query ID generated by the extension to group similar queries |
| nestlevel | integer | Nesting level |
| query | text | Query text |
| calls_begin | bigint | Number of times the query started |
| calls_alive | bigint | Number of queries still running at the time of the last statistics collection |
| calls_end | bigint | Number of queries that completed normally |
| total_time | double precision | Total execution time for this class of queries, in milliseconds |
| min_time | double precision | Minimum execution time for this class of queries, in milliseconds |
| max_time | double precision | Maximum execution time for this class of queries, in milliseconds |
| mean_time | double precision | Average execution time for this class of queries, in milliseconds |
| stddev_time | double precision | Standard deviation of execution time for this class of queries, in milliseconds |
| sample_planid | bigint | Execution plan ID |
| sample_start | timestamp with time zone | Timestamp when the slowest query started |
| sample_parse_done | timestamp with time zone | Timestamp when parsing completed for the slowest query |
| sample_plan_done | timestamp with time zone | Timestamp when planning completed for the slowest query |
| sample_exec_start | timestamp with time zone | Timestamp when execution started for the slowest query |
| sample_exec_end | timestamp with time zone | Timestamp when execution ended for the slowest query |
This view displays resource consumption statistics for queries over the last 5 minutes.
| Column | Type | Description |
|---|---|---|
| seg | integer | Segment node ID where the query executed |
| userid | oid | User OID |
| dbid | oid | Database OID |
| queryid | bigint | Query ID generated by the extension to group similar queries |
| nestlevel | integer | Nesting level |
| rows | bigint | Total number of rows retrieved or affected by the statement |
| shared_blks_hit | bigint | Total number of shared buffer hits by the statement |
| shared_blks_read | bigint | Total number of shared blocks read by the statement |
| shared_blks_dirtied | bigint | Total number of shared blocks dirtied by the statement |
| shared_blks_written | bigint | Total number of shared blocks written by the statement |
| local_blks_hit | bigint | Total number of local buffer hits by the statement |
| local_blks_read | bigint | Total number of local blocks read by the statement |
| local_blks_dirtied | bigint | Total number of local blocks dirtied by the statement |
| local_blks_written | bigint | Total number of local blocks written by the statement |
| temp_blks_read | bigint | Total number of temporary blocks read by the statement |
| temp_blks_written | bigint | Total number of temporary blocks written by the statement |
| blk_read_time | double precision | Total time spent reading blocks, in milliseconds |
| blk_write_time | double precision | Total time spent writing blocks, in milliseconds |
| ru_utime | double precision | User CPU time, in seconds |
| ru_stime | double precision | System CPU time, in seconds |
| ru_maxrss | bigint | Physical memory used (including shared libraries), in KB |
| ru_ixrss | bigint | Integrated shared memory size |
| ru_idrss | bigint | Integrated unshared data size |
| ru_isrss | bigint | Integrated unshared stack size |
| ru_minflt | bigint | Number of minor page faults (handled without I/O) |
| ru_majflt | bigint | Number of major page faults (handled with I/O) |
| ru_nswap | bigint | Number of swap operations |
| ru_inblock | bigint | Number of input operations initiated by the file system |
| ru_oublock | bigint | Number of output operations initiated by the file system |
| ru_msgsnd | bigint | Number of messages sent |
| ru_msgrcv | bigint | Number of messages received |
| ru_nsignals | bigint | Number of signals received |
| ru_nvcsw | bigint | Number of voluntary context switches (process yields CPU) |
| ru_nivcsw | bigint | Number of involuntary context switches (due to time slice expiration or preemption by higher-priority processes) |
mxstat_execute view, once every 5 minutes by default. Its structure matches the mxstat_execute view, with an additional ts_bucket column recording the collection timestamp.mxstat_usage view, once every 5 minutes by default. Its structure matches the mxstat_usage view, with an additional ts_bucket column recording the collection timestamp.queryid and querytext. Like other historical data tables, it stores data periodically so that historical queries can be associated with their SQL text.mx_query_execute_history and mx_querytext on queryid, allowing simultaneous access to historical query statistics and corresponding SQL text.