Workload management is critical to the success of tactical query applications. Improved workload management starts with understanding what is active on the platform and what the resource consumption profiles of your different applications look like.
Recommended Monitoring Activities
- Collect user resource usage detail data.
The DBC.Acctg table is the underlying table for the AMPUsage view, and captures data about user usage of CPU and I/O for each AMP. Heavy resource consumers over time, skewed work, and application usage trends can be identified.
- Collect ResUsage data.
The ResUsage tables report on the aggregate effect of all user requests on system components over time, and can identify bottlenecks and capacity issues.
- Use Database query logging (DBQL).
The Database Query Log records details on queries run, including arrival rates, response times, objects accessed, and SQL statements performed. See Database Administration and Database Query Log Tables and Views for more information about Query Logging.
- Enable canary queries.
Use canary queries with tactical query applications. See the following section for more information about this monitoring technique.
Using Canary Queries
Canary queries are SQL statements that characterize an application. These queries are introduced into the work stream entering your data warehouse to monitor system responsiveness. The frequency at which canary queries are run depends on the site. Canary queries provide a quick health check for tactical applications specifically and for your Teradata system. By monitoring the response times when the canary queries run, you can identify delays or congestion states early.
A user may run canary queries in each active workload, only for tactical query applications, or in workloads that have a defined service level.
Sites may chart canary query behavior during the previous 24 hours, paying attention to outliers. When the response time for a canary query is out of line with expectations, you can try to match that occurrence to system conditions at that time. If canary queries exceed a specified response time threshold, you may want to alert the DBA staff.
Take care to avoid over-scheduling canary queries. If the information your canary queries produce is too large to be easily processed and analyzed, reduce the scope of their execution.