Using custom SQL queries

The PATROL for PostgreSQL includes custom SQL queries that you can configure to monitor the condition of a PostgreSQL database.

These KM metrics generate events that indicate the:

You can use the events generated by these attributes to determine how efficiently queries to the PostgreSQL database are handled.

For example, you can sample the number of locks in the database by entering the following query: SELECT COUNT(*) FROM pg_locks;

By using this query, you can set a threshold for the Value attribute to identify an increase in the number of locks in the system.

Related topics

Using

Configuring-the-PostgreSQL-KM-in-TrueSight-or-Central-Monitoring-Administration

Configuring-PostgreSQL-KM-database-filtering-query-and-debug-options

Number of records

Set a threshold for this attribute to get a notification if the query returns a high number of records (rows) that exceed the threshold. By default, this attribute does not generate an alarm or warning event. For more information about this attribute, see the Number of records attribute page.

For example, you can use this attribute of the PostgreSQL Custom Query monitor type to monitor how many transactions are being executed by a specific user.

In the TrueSight console, You add a custom query to the environment configuration through the Custom SQL queries panel.

Custom SQL Query panel sample

Number of running queries.png

The complete query in this example is:

SELECT usename, query FROM pg_stat_activity WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%' AND usename ILIKE 'postgres'

To monitor other users than postgres, update the WHERE clause.

For more information about setting an alarm or warning event for this attribute, or changing the default attribute range, see Defining a monitoring policy.

Number of records graph

Number of records.png

Suggested action

A high number of return rows can indicate an over-utilized resource. A generated event indicates that you might want to update the number of rows returned by the query to avoid alarms or warning events.

Query status

Set a threshold for this attribute to get a notification if the query was successful or unsuccessful. By default, this attribute will not generate an alarm or warning event. For more information about this attribute, see the Query status attribute page.

Set a threshold for this attribute to get a notification if the query execution was successful or unsuccessful. By default, this attribute will not generate an Alarm or a Warning events. For more information about this attribute,

For more information about setting an alarm or warning event for this attribute, or changing the default attribute range, see Defining a monitoring policy.

Query status graph

Query status.png


    • ====

Suggested action

If an SQL query fails because of a bad SQL query definition, revise your query and correct the configuration in the CMA screen.

Response time

Set a threshold for this attribute to monitor the response time, in milliseconds, required by a query to retrieve data. By default, this attribute will not generate an alarm or warning event. For more information about this attribute, see the Response time attribute page.

For more information about setting an alarm or warning event for this attribute, or changing the default attribute range, see Defining a monitoring policy.

Response time graph

Response time.png


    • ====

Suggested action

A high response time value can indicate delays occurring in the PostgreSQL database, check your database performance.

Value

Use this attribute to retrieve data from a selected cell. The data must be a numeric value that is stored in first cell of the result and the type of the cell must be a numeric, or text, data type. By default, this attribute will not generate an alarm or warning event. For more information about this attribute, see the Value attribute page.

For example, you can use this attribute of the PostgreSQL Custom Query monitor type to sample if a specific user is causing an excessive number of disk reads during a cache sequence.

In the TrueSight console, you add a custom query to the environment configuration through the Custom SQL queries panel, see the Number of recordssection.

The complete query to sample if a specific user is causing an excessive number of disk reads is:

SELECT sum(idx_blks_read) as idx_read FROM pg_statio_user_indexes WHERE relname ILIKE 'postgres';

For more information about setting an alarm or warning event for this attribute, or changing the default attribute range, see Defining a monitoring policy.

Value graph

Value.png


    • ====

Event generated by the KM

You can see the event in the TrueSight console in Monitoring > Events.

Value event.png

Suggested action

If the retrieved value does not meet the single cell requirement, this parameter will not return a value. You need to update the selection to a single, specific cell that holds a number value.


 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*