I would like to find out if data is stored in HOT AMP or in COLD AMP, is there a way to find out that?
Is there a way to move data from Cold Amp to HOt amp?
I am not very sure how do we define hot or cold amp to be honest.
I think you need to liase with you DBA also for this. However, I feel :
there are two dimensions here:
1) how data and/or resources are distributed access the AMPs
in this regard, AMPs are sometimes called HOT/COLD. However, a much more commonly used word for that is "Skew". This depends on data demographics and PI choices.
2) how data and/or resources are distributed inside each AMP
- for data - "multitemperature storage" - if the system has disks of different speeds, as well as in-memory (intelligent memory). This is where HOT/COLD is often used for HOT/COLD data.
- for resources - TASM (dynamic workload management) for resources' distribution across concurrent sessions.
There is no such thing as a hot amp or a cold amp. Teradata's multi-temperature storage has hot and cold storage on every AMP (varies depending on platform family member, platform generation). Within each AMP, the access pattern of each allocation unit is monitored and the allocation unit is placed on the right tier of storage based on its access pattern fully automatically. You do not need to specify the location of the storage or monitor it, it is always automatic. For net new data, especially net new objects, it is possible to give Teradata some guidance via a query band in the load process to provide initial temperature information.
The current distribution of data among hot and cold storage can be seen in Viewpoint TVS portlet.
In our production environment, we refer HOT AMP as:
For any query which is skewed by definition, there are some AMPs which are over-burdened by work. As such, the AMP which is most over-burdened is sometimes called as Hot AMP. This is the AMP where the CPUTime=Max(CPUTime).
In Impact CPU calculation, we use Max(AMPCPUTime) * [HashAMP()+1]. In a way, we multiply the # of AMPs with the CPUTime of the HOT AMP.
Is the above analogy completely wrong ? It might be individual's way of looking at a scenario, yet I wish to know if this is what mostly HOT AMP means ?
Totally different question than what started this thread. The original question was about segregating data based on usage frequency. Sorry that the terminology got overloaded and became confusing...
Impact CPU is a useful indicator of the capacity/throughput impact of a query. A highly skewed query impacts the throughput of the system more than an equal CPU query which is not skewed. ImactCPU calculation is a way of taking that into account.
We do refer to a hot AMP as one which is running higher resource utilization than other AMPs for some period of time - as viewed in the resusage data. Generally a single query does not make a hot AMP by itself, usually it takes several queries/processes that are skewed to the same amps for some period of time. A really extreme impactCPU query can have the effect by itself but that is less common. Multiple- ImpactCPU queries can cancel out their negative impact to some degree if they are skewed to different AMPs - or magnify it if they are cuncurrently skewed to the same AMP. In any case, the hisghest ImpactCPU queries are good candidates for a close look from a query tuning perspective.
The original question here though asked about our hot/cold data allocation and whether we put hot data in some Hot AMPs and Cold data in other Cold AMPs - which is not how it works. Each AMP has an allocation of both hot and cold data and a mechanism to place that data according to it's usage pattern on the appropriate part of the IO system.