queryid Metric Configuration
Catalog of SQL Statements Registered for queryid Metrics
queryid metric group allows monitoring the operation of individual SQL operators. For this purpose, metric names are formed using the identifier of a particular operator. The system catalog qhb_queryid is used to store operator texts. When a new operator is added to this table, it is assigned an identifier, and can also be assigned a name. When analyzing metrics, you can quickly find the full text of the SQL statement that the metrics relate to.
The list of metrics for the queryid group is shown in Section queryid.
To manage this directory (especially to prevent it from growing uncontrollably), functions and configuration options are available for user.
You can enable the metric group queryid (it is disabled by default) in the same way as other metric groups:
SELECT metrics_config_enable_group('queryid', false);
A description of this function (and its pair for disabling) is contained in the queryid Metrics table.
But, unlike other metric groups, calling the metrics_config_enable_group function is not enough to start generating metrics when executing SQL statements. You must either enable automatic registration of statements and then enable metrics for at least one of them, or explicitly register at least one SQL statement.
Configuration Parameters for Setting Up the Operation of queryid Metrics
| Parameter Name | Type | Description | Default |
|---|---|---|---|
| queryid_auto_register | bool | Add only explicitly registered queries to the table | false |
| queryid_max_saved | integer | Maximum number of operators and their identifiers stored in the system table qhb_queryid | 1000 |
| queryid_update_threshold | interval | Record access timestamp update resolution | 1min |
The queryid_auto_register parameter enables automatic registration of all operators to be executed. Since there may be many of them, the active flags are set to false. If automatic registration is disabled, then when an operator is explicitly registered (see below), its active flag is set to true. To work with metrics, the active flag of at least one operator must be set to true.
The queryid_max_saved parameter is used to control the autovacuum of the qhb_queryid table. When the number of records reaches the value specified in queryid_max_saved, the vacuum procedure will be called, and it will delete records with the oldest values of the last_used access timestamp. At the same time, named records will not be deleted as long as at least one unnamed record remains.
The queryid_update_threshold parameter is intended to reduce the load on the database in the case of a stream of the same operators. You can specify it in the configuration file, like other time parameters, using ms, s, and min suffixes.
"Virtual" queryid_normalize Parameter
There is another, "virtual", parameter queryid_normalize = true, that is not appear in the configuration file, but can be temporarily, only in the context of one session of connection to the database, changed by calling metrics_queryid_normalize(false) function. After this, the texts of the processed operators will not be subject to the normalization procedure, which, in particular, abstracts the values of the query parameters. Thus, it will be possible to track the operator's work with specific parameter values.
Functions for Working with queryid Metrics
| Name | Parameters | Description |
|---|---|---|
| metrics_queryid_normalize | bool | Enable/disable operator text normalization |
| metrics_queryid_register | string | Register an operator by assigning it an identifier |
| metrics_queryid_enable | string | Allow metric generation for the operator(s) |
| metrics_queryid_disable | string | Disable metric generation for the operator(s) |
| metrics_queryid_disable_all | Disable metric generation for all operators | |
| metrics_queryid_rename | string, string | Assign a new name for the operator |
| metrics_queryid_clear_name | string | Clear operator name |
| metrics_queryid_show | Show current parameter values |
metrics_queryid_normalize(true или false)
The metrics_queryid_normalize function enables or disables normalization of query texts (only in the context of the current backend process). The default is true, which means normalization is performed.
metrics_queryid_register('текст запроса')
The metrics_queryid_register function registers a query (any SQL statement or even an arbitrary string): it inserts a record into the qhb_queryid table immediately in the activated state, but does not execute the query itself. An empty string is not allowed as an argument. If the query is successfully registered, the full set of metrics with the corresponding QueryId is registered. If the query is not built correctly and cannot be executed later, these metrics will never be generated. If the queryid_auto_register=true configuration parameter is set, using this function becomes optional, but all executed SQL statements will be logged, which is often undesirable.
metrics_queryid_enable('id или имя или список')
The metrics_queryid_enable function enables the query associated with the specified QueryId or name to generate metrics. An empty string is not allowed as an argument. It is allowed to pass multiple identifiers or names as a comma-separated list. It is allowed to pass only the first identifier characters, provided that they uniquely identify the only existing QueryId.
metrics_queryid_disable('id или имя или список')
The metrics_queryid_disable function prohibits the query associated with the specified QueryId or name to generate metrics. An empty string is not allowed as an argument. It is allowed to pass multiple identifiers or names as a comma- separated list. It is allowed to pass only the first identifier characters, provided that they uniquely identify the only existing QueryId.
metrics_queryid_disable_all()
The metrics_queryid_disable_all function prohibits all registered queries to generate metrics.
metrics_queryid_rename('id или имя', 'новое имя')
The metrics_queryid_rename function associates a query with a known QueryId or previously set name with a new name. Empty strings are not allowed as arguments. Lists are not allowed as arguments. Existing name or QueryId is not allowed as a new name. If binding is successful a complete set of metrics is registered with a new name.
metrics_queryid_clear_name('id или имя или список')
The metrics_queryid_clear_name function removes the association of existing queries with name. An empty string is not allowed as an argument. It is allowed to transmit several identifiers or names as a comma-separated list. It is allowed to pass only the first identifier characters, provided that they uniquely identify the only existing QueryId. In case of actual deletion, the full set of metrics is re-registered with corresponding QueryId.
metrics_queryid_show();
The metrics_queryid_show function shows the current parameter values.