Using Canary Queries
A canary query a common SQL statement that is run at specific intervals and monitored for data such as response time to characterize the current system workload. Each canary query is fixed to do a consistent amount of work per execution.
You can use Teradata Viewpoint to send canary queries to:
System Canary Queries
Use system canary queries to check for overall system or database hangs, to take some kind of action when response times reach certain thresholds, or when stalled, such as send alert and/or capture system level information.
More than just a check, a system canary query should execute diagnostics that capture the state of the system if performance stalls.
System canary queries are intended specifically to focus on the core system of Teradata Database. They should be short-running (one second), low impact queries on tables that are normally not write locked.
System canary queries are most useful when run frequently. For example, some sites run them every 3 to 5 minutes; other sites find every 5 to 10 minutes adequate.
They should be run on a system node. This will eliminate other factors, such as middle tiers, network connections.
Depending on their makeup, canary queries can add to contention for resources. Use them selectively, where needed, with shorter queries preferable.
Sample System Canary Query
The simplest canary monitor query is the following:
SELECT * from DBC.DBCInfoV;
As the query runs, Teradata Viewpoint can monitor the query, logging start and end times. If the query runs longer than the indicated threshold, an alert and perhaps diagnostic scripts are automatically executed.
Production Canary Queries
Production canary queries may be used to:
Response times are an indicator of system demand. When system demand is high, canary response is high. You can expect all other queries running in the same PG to display similar elongations in response time.
From a user perspective, a sudden deviation in response times would have an immediate impact, since users of consistently short running queries would be the first to notice performance degradation.
Production canary queries have wider uses than system canary queries and can be used in a variety of ways. For example, they:
In using a production query from a non-TPA node location, other things, such as network and middle-tier monitoring, are also covered, but when it stalls, you need to investigate further to determine where the bottleneck is located.
Once the response time for a canary query is stored in a table, it can be summarized for use in tracking trends.