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
A quick list of monitoring activities that offer a foundation for workload management follows:
- Use Teradata Viewpoint to monitor and manage the workload.
- 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 various system components over time, and can identify bottlenecks and capacity issues. See Resource Usage Macros and Tables for details on all ResUsage data and macros.
- Use the Lock Viewer Viewpoint portlet.
Lock Viewer is essential for identifying locking conflicts.
- 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 SQL Data Definition Language for more information about Query Logging.
- Enable canary queries.
You should 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 represent the characteristics of a particular application. These queries are introduced into the work stream entering your data warehouse in order to monitor system responsiveness. Some sites run canary queries once every 1 to 5 minutes, while others run them only every 30 minutes. Canary queries provide a quick health check for tactical applications specifically and for your Teradata Database system in general. By monitoring the response times when the canary queries run, you can identify delays or congestion states early on.
Some users run canary queries in each workload that is active, others only use them for their tactical query applications or in workloads that have a defined service level.
Many sites make charts or graphs of canary query behavior during the previous 24 hours, with particular attention paid to any outliers. When the response time for a canary query is out of line with expectations, you can attempt to match that occurrence to system conditions at that time. It is a common practice for canary queries that exceed a specified response time threshold to send an e-mail alert to 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.
Teradata Viewpoint is designed to automate the submission of canary queries. See the Teradata Viewpoint online HELP to learn how to do this.