The Heatmap Table Function is now available

Extensibility
Extensibility covers the mechanisms by which you, as the user or developer, can extend the functionality of the Teradata Database, for example with the use of User Defined Functions, or UDFs.
Teradata Employee

The Heatmap Table Function is now available

As part of Teradata Database 14.10 Teradata Intelligent Memory (TIM) feature, a table function was developed to provide the heatmap report for a specific AMP for database object(s) for systems in which data temperature collection is enabled[1].     This HeatMap table function is being provided for sites that are interested in looking at the relative temperature of tables, which tables (cylinders) are targeted for the TIM cache and which tables are targeted for what temperature range[2].   This article assumes that the readers have prior working knowledge of Teradata Virtual storage and Teradata Intelligent Memory.   See Appendix. D 'Reference material' for more information on TVS and TIM.      

Preface

This article includes references to Teradata Virtual Storage.  You may not use this feature without the appropriate license.  The fact that this feature may be included in product media or downloads, or described in documentation that you receive, does not authorize you to use it without the appropriate license.

Contact your Teradata sales representative to purchase and enable this optional features.

Very important notice to the users:

DO NOT use the downloadable package on any database releases lower than noted.  The current 1410_heatmap package (V4) is only compatible with below database releases:

           TDBMS_14.10.06.08 and higher

           TDBMS_15.00.04.05 and higher

           TDBMS_15.10.01.01 and higher

The customers currently using the V3 package will need to pick up the V4 package if they are upgrading to above releases.  Otherwise, continue to use the V3 package.

Reference Service Bulletin (ID: SB 815) to download the heatmap UDF V3.  The minimum database releases requirement on V3 is as follows:

           TDBMS_14.10.05.02  

           TDBMS_15.00.03.01  

           TDBMS_15.10.00.01 

            *** Service Bulletin can be accessed from Teradata @https://tays.teradata.com/.

Any lower database releases other than mentioned above may cause instability resulting in a possible database restart.

Older heatmap packages were previously available but removed due to instability.  Although the current provided package has been tested for basic comparability with the database, there are risks associated with using this package.  The package is provided AS-IS and Teradata does not provide support of the UDF.

Requirement(s)

The heatmap table function requires Teradata TDBMS_14.10.05.02/TDBMS_15.00.03.01/TDBMS_15.10.00.01 (or higher) and the system that must have data temperature collection enabled.  TIM is optional.  The users can run the heatmap function with or without TIM enabled on their system as long as the data temperature collection requirements are met.   The data temperature collection requirements are described as follows:

    1. The system is enabled with Temperature Based Block Level Compression (TBBLC)

                                                              AND/OR

    1. The system is enabled with Teradata Virtual Storage(TVS) – ONE_DIMENSIONAL with Metric Collection On

                  See Appendix A for ‘How to check to see if TBBLC is enabled’. 

Data temperature collection

TVS Metric Collection monitors how often data is accessed by the database on a per cylinder basis.  Specifically, TVS monitors customer data block reads and writes, and full cylinder reads.  The cylinder temperature increases as data access on that cylinder increases.  The cylinder temperature decreases over time through the use of a mathematical model.  If a cylinder stops being accessed then its temperature will start to drop, and over time it may fall to zero.  Data temperature values are maintained by the TVS allocator vproc. 

Use cases for the heatmap table function

Although a current snapshot of the heatmap report can help the users answer some of temperature related questions about their tables, heatmap data that has been captured over time will provide even greater value to administrators.   The history data will enable the users to analyze the temperature change of the data at a cylinder level on a given time period very quickly.  Here is the list of some questions the heatmap data is intended to address:

    1. What tables were in the VeryHot temperature range last Tuesday?
    1. How quickly did my month end tables cool down below the HOT range?
    1. For my largest table – how quickly does the temperature of the non-recently-loaded partition (week a month ago and week a year ago) heat up and what temperature did they get?
    1. What tables/partitions cooled down from TIM cache target temperature last night?
    1. What perm data is staying in the HOT area for more than a week but never made into the VH range?
    1. Did my VeryHot loaded partition cool down below VeryHot or Hot yet?
    1. I just loaded a table with QUERY_BAND with TVSTEMPERATURE=VERYHOT.  How do I verify if the table is actually in the TIM Cache?
    1. What tables/cylinders are currently in TIM Cache? 

Use case example

Figure 1 illustrates the simulation of a retail application that queries sales numbers against current day against day last week, last month, and day last quarter .  The horizontal axis represents the internal table partition associated with a certain week (week date range manually added for readability).  The depth axis represents the date after the day’s queries and the heatmap report were completed.  The vertical axis shows the data MAXIMUM(temperature) for any cylinder of that partition with COLD, WARM, HOT, and VERY HOT in different colors.  In this simulation, the table queried is partitioned by week, thus the increasing height of the graph’s surface represents the frequency of access of data from a certain week.

The script used to generate this data was written such that for each day between May 13 and June 27 (a six week period) there were 4,020 queries where:

    1. 1,000 access the current week partition only
    1. 1,000 access the current and the previous week partition only
    1. 1,000 access the current week, the previous week and the same week the previous month partition only
    1. 1,000 access the current week and the same week the previous quarter partition only
    1. 20 access the entire table (all partitions).

A heatmap report was produced each time all 4,020 queries completed and the data was accumulated over for a six week period.    In order to analyze this data, a daily maximum of the temperature column of cylinders per partition was captured, as all partitions in the test table spanned over multiple cylinders.   Then the dates (partition) of the test data were correlated to their internal partition numbers.  That association could be made since all the dates were consecutive in our test data.

The following chart shows that the partition 22 (May 13-19) has the highest height (temperature) among targeted partitions.  It also shows the dramatic increase in temperature over time.  The data saw its temperature increase the most in the first week.  Then it heated up even more the next week since it was scanned with the previous week queries.    It was heated up again about 4 weeks later when this week was scanned as the part of the last month same week queries.

The partition 10 is associated with the previous quarter’s data (Feb 18-22) and experienced the least gain in temperature growth of targeted partitions.   As you can see the temperature had been consistently rising for the partition 10.  But the temperature of the partition 10 gained very little within the same reporting period compared to the partition 22.

Please keep in mind that this test was run in a controlled environment where the TVS metrics collection was set very aggressively.  Each week was condensed in this test to run in an hour of time, and there was no other competing workload other than this one workload.  The test data was kept as loaded (the data was in a steady state during the entire test).   Consequently, the heating up and cooling off effects shown in this chart might not be realistic for a six week run on a real system.

                                                Figure 1:  Graph showing data temperature by partition for a specific table over time 

Installation

This sections provides some direction on installing this heatmap UDF.  Download the attachment.  The 'td1410_heatmap_udf_v1.zip' includes the following five files:

    1. td1410_heatmap.c  - the heatmap ‘C’ source code
    1. td1410_heatmap.bteq – the script to compile(install) UDF ‘td1410_heatmap’ in Syslib
    1. td1410_heatmap_v.bteq – the DDL the view ‘td1410_heatmap_v’
    1. README.docx
    1. td1410_heatmap_history.txt – the script to create and populate a simple heatmap history table(s).    This script was provided as a very simple example to show how to collect the heatmap data over time.

Follow the instructions in README.docx.

The following are some important points regarding the view ‘td1410_heatmap_v’:

    1. The heatmap function requires a numeric (positive integer number including 0) value as an input parameter.   The function determines which specific AMP it is going to run on based on this value when invoked.
    1. The function does not output English description of database and table name.  So if you want the English name displayed on your report, your query will need perform a join to DBC.TVM.TVMID  using either ‘StartTableIdUniq’ or ‘EndTableIdUniq’.

The view ‘td1410_heatmap_v’ takes care of both points.  This view was created so that the heatmap report is always taken from the same AMP and the users do not need to write additional query to get the English name of the objects.  The view uses ‘StartTableIdUniq’ to join to DBC.TVM.TVMID.    In addition the users can specify the name(s) of database object(s) to limit the scope of the heatmap report be generated.

There will be some cases with mixed cylinders where this view will not work.  Please refer to section ‘Key Points’ for more information on this subject.

Contents of heatmap table function output

     FSYS - File System, MI - Master Index, TVS - Teradata Virtual Storage, PDE - Parallel Database Extension 

Heatmap report examples:

Here is the heatmap report showing the temperature property of cylinder id ‘00060002000039F7’.  Note that only one cylinder is selected for ease of illustration.

SELECT * FROM demo.td1410_heatmap_v WHERE DatabaseName = ‘PROD_TBLS’ AND TableName = ‘INVENTORY_DETAIL’  ORDER BY Temperature DESC;

                  Figure 2: Example of a data temperature report showing a specific table and SQL statement used to create the above heatmap report

The above heatmap report indicates the followings:

    1. This cylinder belongs to a primary permanent table – StartTableIdTypeAndIndex = 1024
    1. TIM is not enabled – VeryHotCandidate = BLANK and VeryHotCache = BLANK  and TempVeryHotFloor = 0
    1. This cylinder is not a mixed cylinder – StartTableIdUniq = 0000350E and EndTableIdUniq = 0000350E.  If a mixed cylinder, StartTableIdUniq and EndTableIdUniq would show different values.
    1. This heatmap was taken from AMP 2 – AmpNumber = 2
    1. This table is not partitioned – StartPartition = 0 and EndPartition = 0

Here is another heatmap report showing some parts (cylinders) of INVENTORY_DETAIL table are in TIM cache.  This assumes that TIM has been enabled on this system.

                         Figure 3: Example of cylinders that are in TIM Cache

Figure 4 illustrates a heatmap reporting showing tables of different subtable type.   The Primary FALLBACK subtable of 'DBC.RESUSAGESVPR' and the SECONDARY INDEX subtable of 'DEMO.TEST_TBL1' along with their Primary permanent tables are shown in this example.

                                                              Figure 4: Heatmap report of Primary FALLBACK subtable (2048) and SECONDARY INDEX subtable (1028)

Please note that there are other ways to get heatmap data on your system.  See Appendix D for ‘Other ways to get the heatmap data’. 

Key Points

The heatmap UDF is not a fast path table function.  It is also important to emphasize that it will be executed in non-protected mode.  Please note the ALTER statement in the ‘td1410_heatmap.bteq’ script.

The heatmap table function is designed to be executed on a single AMP as the heatmap for any single AMP should be a good global representation of the all AMPs in the system.  The initial installation of the table function process will require a specific AMP number, specifically in the view creation section.   The typical use cases for the heatmap report are expected to be against historical data that has been accumulated over time on a same AMP.  For that reason, it is strongly suggested that you not change the view once it is created.

The table function is a costly operation as it interfaces with the various subsystems, FSYS, TVS, and PDE.  Daily execution will be adequate.  Run it hourly only if needed for problem research, as temperature does not change that quickly.

The ‘System DBA’ or ‘System Administrator’ is expected to install and maintain the UDF and the view.  There should be very few user IDs that have an authority to access the heatmap view.   The broader level access should be given against the history heatmap table(s) only.

The temperature values and floor/ceiling temperature ranges are local to individual vproc only.  The heatmap report in the next example were run on two AMPs.

          Figure 4: Temperature range report on two different AMPs.    

As you can see the temperature ranges were quite different between two AMPs.  Since the temperature classification (NormalizedTempInfo) is derived from TempWarmFloor and TempWarmCeiling, you would expect that the actual temperature value of what was categorized as ‘HOT’ on one AMP isn’t necessary going to result in the same temperature classification on the other AMP.  Actually this brings up another point.  The same caution needs to be taken when comparing the heatmap reports between different Teradata systems. 

Mixed cylinders (a cylinder with more than one table on it) might not show the true temperature.  If a COLD table is on the same cylinder as a VeryHot table, the COLD table would look VeryHot in the heatmap. 

Since the heatmap view uses ‘StartTableIdUniq’ to join to DBC.TVM.TVMID, only the 1st table on the cylinder will be reported for mixed cylinders.  If rows for a table start in the middle or end of the cylinder and only occupy one cylinder per AMP, the heatmap will not show this object.   The temperature of such small tables is not expected to have significant effects on the overall system wide heatmap so the user should not be concerned about them.   However, if the users must identify those objects, they will need to do this range check (DBC.TVM.TVMID between StartTableId and EndTableId) when joining to DBC.TVM.TVMID.

[1] This table function was developed for automatic testing of the TIM feature.

[2] This information can also be gotten by Ferret command (refer to Appendix B). 

Appendix A. How to check to see if TBBLC is enabled.

 

                                                                Figure 5: Checking for TBBLC

Appendix B. Other approaches to get HeatMap information

 The following section shows some utilities used to display temperature information at the table or cylinder level.   The screen shot of MI from Filer was also included to show the users where the various cylinder attributes (the heatmap columns show the data source as ‘FSYS MI’ in the section ‘Contents of heatmap table function output’) come from.

 

                                                                     Figure 6: Ferret 'showwhere' command display

            Figure 7: tvam -display -tempdetail screen showing the cylinder 0006000200003838 is in TIM Cache

                                                                                              Figure 8: Sample MI information taken from AMP 2 

Appendix C. Errors

When the heatmap table function encounters an unexpected file system or PDE error, the error code and the error message will be logged in /var/log/messages and the AMP fault isolation code should take a snapshot dump without restarting the database.   The field engineer or customers can get some basic information about the issue from just reading the log initially.  Further analysis can be done by looking at the snapshot dump.  The users will receive ‘SELECT failed. [7487] AMP step failure: Please do not resubmit the last request’.

Please note that the users will receive the same error message when the temperature collection requirements are not met.    So when you receive this error for the first time, check your system to make sure that it is meeting the temperature collection requirements.  If the temperature collection has been on for a while and the system is capable of generating a heatmap, then it could be a legitimate error that needed to be analyzed further.   

The following shows the sample snippet of what the customers expect to see in /var/log/messages file when a file system error occurs.

Jun 10 10:21:05 colima Teradata[11839]: INFO: Teradata: 7489 #7489: AMP failure 7487 details follow:

Jun 10 10:21:05 colima Teradata[11839]: On Mon Jun 10 10:21:05 2013 on NODE 001-01, VPROC 2, partition 11, task actmain

Jun 10 10:21:05 colima Teradata[11839]: ErrAmpFailText

Jun 10 10:21:05 colima Teradata[11839]: AMP/Non-AMP failure 7487 details follow:

Jun 10 10:21:05 colima Teradata[11839]: ........................................

Jun 10 10:21:05 colima Teradata[11839]: ........................................

Jun 10 10:21:05 colima Teradata[11839]: 5007: Error detected while generating heatmap segment(s).

Jun 10 10:21:05 colima Teradata[11839]: ........................................

Jun 10 10:21:05 colima Teradata[11839]: ........................................

Jun 10 10:21:07 colima Teradata[11839]: INFO: Teradata: 12123 #Event number 33-12123-00 (severity 10, category 11), occurred on Mon Jun 10 10:21:05 2013 at 001-01 (Vproc 2, partition 11, task 21874) in system colima in Module actmain, version PDE:14.10.01e.05tbimpmon,TDBMS:14.10.01e.05,PDEGPL:14.10.01e.05tbimpmon,TGTW:14.10.01e.05,TDGSS:14.10.01e.05

Jun 10 10:21:07 colima Teradata[11839]:  A task snapshot dump is being taken for event number 7487.

Jun 10 10:21:07 colima Teradata[11839]: PROC/FUN

Jun 10 10:21:07 colima Teradata[11839]: Location             Function                          File:Line

Jun 10 10:21:07 colima Teradata[11839]: -------------------------------------------------------------------------

Jun 10 10:21:07 colima Teradata[11839]: libdbs1.so           suterrcx+0x2236                   suterrc.c:2017

Jun 10 10:21:07 colima Teradata[11839]: libamp4.so           FNC_GetFSysInfo_V+0x619           fncampint.c:5862

Jun 10 10:21:07 colima Teradata[11839]: libudf.so            FNC_GetFSysInfo+0x43              fncvector.c:1510

Jun 10 10:21:07 colima Teradata[11839]: libudf_1002_129.so   td1410_heatmap+0x331              crtn.S:10

Jun 10 10:21:07 colima Teradata[11839]: libudf_1002_129.so   td_td1410_heatmap+0x600           crtn.S:10

Jun 10 10:21:07 colima Teradata[11839]: libevl1.so           udfxnpm+0x141                     udfxnpm.c:958

Jun 10 10:21:07 colima Teradata[11839]: libevl1.so           udfexecute+0x1bf                  udfexecute.c:553

Jun 10 10:21:07 colima Teradata[11839]: libdbs1.so           udfgnxexecute+0x121               udfgnxexecute.c:149

Jun 10 10:21:07 colima Teradata[11839]:                      <no symbol>+0x2aab088840e7        

Jun 10 10:21:07 colima Teradata[11839]: libdbs1.so           evlinterp+0x2ec                   evlinterp.c:3066

Jun 10 10:21:07 colima Teradata[11839]: libstp.so            FncWalkTC+0x22f                   stpret.c:1596

Jun 10 10:21:07 colima Teradata[11839]: libstp.so            FncWalkTbl+0x61a                  stpret.c:4140

Jun 10 10:21:07 colima Teradata[11839]: libstp.so            stpret+0xce5                      stpret.c:6381

Jun 10 10:21:07 colima Teradata[11839]: libstp.so            awtstphd+0xaf8                    awtstphd.c:627

Jun 10 10:21:07 colima Teradata[11839]: libstp.so            awtmain+0x3b4b                    awtmain.c:3292

Jun 10 10:21:07 colima Teradata[11839]: libpde.so            tsknewthread+0x38d                tskport.c:1319 

Appendix D. Reference Material

Donn Holtzmann.  Introduction to Teradata Virtual Storage, Teradata White Paper #541-0005598-B03, November, 2008.

Ron Yellin.  Conquer Complexity with Teradata Virtual Storage, Teradata White Paper #541-0008984-A02, June, 2011.

Phil Benton.  Teradata Intelligent Memory in Teradata 14.10, Teradata Orange Book #541-0009920-A02, July 2013.


  AA  Appendix F. Change History

2014-07-14 - 'TD1410 HeatmapTable Function Installtion Package' has been updated with 'td1410_heatmap_udf_v1_1.zip'  The columns 'thedate' and 'thetime' will not be produced via table function.  Instead these column will be populated with system date and time via td1410_heatmap_v.  Please make sure that you are using the latest package. 

2014-07-14 - 'TD14 HeatmapTable Function Installtion Package' has been updated with 'td14_heatmap_udf_v2.zip'  The columns 'thedate' and 'thetime' will not be produced via table function.  Instead these column will be populated with system date and time via td14_heatmap_v.  Please make sure that you are using the latest package.

2014-10-09 - An error handling is added for filesys returning "Invalid Temperature".  This notes that temperatures have not reached a "mature' state on a system.  This is not a problem.  It will resolve itself over time, as temperatures will eventually mature if meric collection is on.  The following is the message that the users will receive when we hit this condition.                                                               "SELECT Failed. 7504:  in UDF/XSP/UDM SYSLIB.td1410_heatmap: SQLSTATE U0025: Invalid Temperature". 

2015-10-05 - Removed 'TD14 HeatMap Table Function Installation Package' and 'TD1410 HeatMap Table Function Installation Package - V2'.  Uploaded V3 version.

2015-12-09 - Removed V3 and Uploaded V4 version.

 

7 REPLIES

Re: The Heatmap Table Function is now available

There are two V2s listed here, what's the deal with that?

Teradata Employee

Re: The Heatmap Table Function is now available

The customers who are running 14.10.05.02 and higher will need to use V3 package.  For eBay, V3 is the correct package to use.

Re: The Heatmap Table Function is now available

Just so you know, we are running 14.10.4.502.

Teradata Employee

Re: The Heatmap Table Function is now available

Yes, I know.  :-)

Enthusiast

Re: The Heatmap Table Function is now available

Hi jlee,

Good Info. We have already installed the UDF & views and we are trying to generate the data temperature by partition for a specific table over time as mentioned above. But, we are unable to su-diveide the data temperature for each partiion. 

Also, please shed some light on how internal and external partition works and how to map the partition number with the actual table.

For the below output, from the heatmap view, partition starts with 635 and ends with 0. What exactly it means? How to find out the partition range for each table in each AMP?

6/3/2015 100,939.96 139 DBNAME TABLENAME 00-00-37-0F-04-00 00-00-0F-37 1,024 635 F7-BC-4C-CF-00-00-00-01 00-00-37-0F-04-10 00-00-0F-37 1,040 0 8B-4E-67-18 00-01-00-8B-00-00-07-90 65,675 1,936 17.52 WARM WARM     18.43 10.98 0.00 96.10

Thanks,

Dinesh

Re: The Heatmap Table Function is now available

I installed Version 4 of the heatmap udf, it work fine in unproctected mode, but in protected mode get the following error. 

 *** Failure 7803 The UDF/XSP/UDM SYSLIB.td1410_heatmap called FNC_GetFSysInfo in an invalid context.

Re: The Heatmap Table Function is now available

If you do not have TBBLC enabled and want to verify that your system meets the other requirement:

"2. The system is enabled with Teradata Virtual Storage (TVS) - ONE_DIMENSIONAL with Metric Collection On"

then, you can run the following on a system node:

ctl -first "print All;quit" | grep -e "Allocation Method" -e "Metric Collection"