Moments in time
Track down system issues with Teradata Viewpoint Rewind.
Much like the flux capacitor in Doc Brown’s DeLorean in the 1985 movie “Back to the Future,”
a cutting-edge tool in the Teradata systems management appliance allows IT and business users to travel back in time. And like the main character in the movie, DBAs are given the power—through time travel, so to speak—to find, investigate and fix issues on the fly. The innovative tool is Rewind, an out-of-the-box feature of Teradata Viewpoint.
A new display mechanism for Teradata tools and utilities, the Teradata Viewpoint portal enables intuitive system management through portlets. Users can engage the Rewind feature to view vital system information, providing DBAs the capability to review activities that occurred in the past.
Some management tools used by both IT and business users include system health indicators, in-flight query progress, canary query performance and disk space usage. With this information at hand, IT gains insight essential to managing the Teradata system, while business users can more easily and effectively plan and execute their work without the need to call the help desk with questions.
Click to the past
While the Teradata Viewpoint appliance is rack-mounted in a Teradata platform, it operates with minimal impact completely outside the data warehouse. At configurable intervals, the application collects session and performance data from the Teradata Performance Monitor Application Programming Interface (PMAPI) and Database Query Log (DBQL) and stores it on a cache database.
With this ongoing operation, Teradata Viewpoint is, in effect, taking intermittent photos of the Teradata environment and associated activity. The cache database, where these photos are stored, is fed to each Web-based application, or portlet, that the end user consumes. From there, DBAs can use the Rewind function to browse through previous system snapshots in order to visually analyze or debug past occurrences.
A click of the mouse enables the DBA to rewind one or many portlets to a specific time or date. The portal can be rewound by the second, minute, hour or day. A wide array of system information will be displayed on one screen, giving the DBA a high-level, holistic view of the Teradata environment. From that screen, the DBA can access a rewound portlet and derive important information, like SQL statements or query execution plans.
This innovative function is simple to use. The Rewind button, which looks similar to the same on a DVD player, is located at the top of the Teradata Viewpoint portal. To activate Rewind, the user clicks the button; a time-slider will pop open and stretch across the top of the portal page. The user can either toggle the time-slider or enter the exact date and time. The portal page will refresh to that moment.
Limiting which portlets are engaged for this function is a user option. For example, a DBA might want to keep the Filtered Queries portlet as current as possible to watch for a certain set of queries or loads. By clicking “Disable from Rewind” on the menu bar of the Filtered Queries portlet, that portlet will stay current and continue to refresh while the other portlets move back in time.
Besides being a functional tool, Rewind enhances current capabilities within Teradata Viewpoint. For example, when the Filtered Queries portlet is rewound, a list of which queries were running at that time is provided. However, the user can also review an individual query’s explain plan to see the exact step that was executing at that particular time.
Similarly, the user can rewind the System Health portlet to see which threshold caused the system to be degraded or changed to a critical status.
Click to enlarge
Case in point
Sam, a DBA, arrives at work and is greeted with a system error message. During the previous night’s data operations, a key production system had two restarts. To find out what happened, Sam uses Rewind to toggle back several portlets in time to when the first restart occurred. (See figure, part A.)
As Sam investigates which jobs were processing around the time of the restart, he notices a query that had been running for 22 hours. He uses the drill-down feature to scrutinize the SQL.
Sam then fast-forwards to the next restart and notices that the same job had been running again, this time for almost three hours. By drilling down, he recognizes that the SQL is also the same. He now has an idea of what’s causing the problem and will look for that query and the associated system behavior as the day goes on.
Later that afternoon, the system starts to slow down abnormally. Sam checks Filtered Queries and notices that the same suspect query has been running for four hours. The System Health portlet displays elevated thresholds of AMP CPU skew, AMP I/O skew and Max Spool by AMP, which signifies that the job is sucking up most of the spool space. (See figure, part B.) Another look at the SQL confirms it is the same query, so Sam kills it and the system is quickly running within its parameters.
As portrayed in this scenario, Sam harnessed the power of Rewind to quickly investigate the issue and find the suspected cause. Then, through the portlets, he identified the problem as it resurfaced in real time and immediately took action.
The chance to rewind
In “Back to the Future,” the main character relies on the DeLorean to carry him into the past or project him forward in time. In essence, innovative DBAs are combining the drill-down features of Teradata Viewpoint and the analytic capabilities of Rewind for the same purpose. They understand that Rewind can help them investigate system issues quickly and easily, providing visual monitoring of a system of any size for past and current performance issues.
This instant, hands-on system management will keep the data warehouse environment running smoothly and efficiently now and in the future.