Swapping in Teradata

Swapping in Teradata is bad and has a big impact on performance. We can witness the difference with few performance tweaks.
One among lots that I witnessed recently was fsgcache%. The more the swapping the more the impact on Performance. There should be a balance between Teradata resources and OS resources. The Operating system needs to have room to run its process. We certainly do not want to starve cache in favor of disk reads, but when we swap, we change the whole layout and picture and then all the AMPS will be throttled by a single swap drive. Outside of FSGCACHE are things that the O/S needs room to run.

To eliminate this swapping on a performance critical boxes, it is highly recommended to look into FSGCACHE %. This is pre configured when it is shipped but it doesn’t apply or hold true to all the businesses. If swaps are significantly worse then lowering the value will have a bigger impact.

Things to look:
• Swap on system
• Skew between nodes
• LINUX run queues

How to look swap on a LINUX system

This can be done utilizing the sar utility in LINUX. It is a system activity report.
The samples are taken every five minutes throughout the day, the impact of swap can read and reviewed by running the following commands:

psh sar -W
This will output the entire day's samples whenever it is run. The "psh" part of it runs it across all the nodes. Do not expect to see anything in the Hot Stand By or HSN node, since Teradata is not actively running on HSN.

Options with sar command which needs to be frequently used.

sar -u
%nice - this takes the place of %user that we used to use in MP-RAS
%sys - this is the time spent on system calls
%IO wait - This is all I/O on the system (disk, bynet, etc.).
%cpu idle - This is a good indication of CPU utilization
Like to see %nice to %sys at 60-70%/30-40%
%IO wait can be highly variable since it records all I/O. I don't use it too much.
%cpu idle can certainly run down to 0%, but for extended periods, probably means you are near capacity.

sar -q
runq-sz - is the number of runnable Linux processes waiting to be serviced. This is a good indicator of application activity. Teradata primarily runs AWT processes. Since you have 63 usable AWTs, when the run queue exceeds that, processes wait. It is not bad to wait sometimes, and this queue gets drained
and refilled at a high rate. When I see periods of 100+ in the run queues for sustained periods of time, this will indicate a high degree of concurrent work. This can also be compared between nodes on a system to get a view of overall skew on the machine.

sar -W
pswpin and pswpout - I add these together to get an overall picture of swap. Swap in Teradata is bad. It throttles the AMPS from performing efficiently. FSGCACHE is utilized to tune this. Occasional bursts of this in the 0 - .5 range will not impair operations. Sustained in that range should be tuned.
Ranges from .5 to 5 blocks/sec need tuning to eliminate them. These will impact performance.Ranges above 5 require emergency tuning since their impact on Teradata operation is extreme.These are averages in blocks/sec, so within a 5 minute interval (which is what we sample), this could indicate muchhigher peak values for many seconds at a time, which could severely impact <10sec SLAs for high performance
queries.

And for more information a manual page for sar can be viewed on the system itself.