TPump maximum pack factor has been increased from 600 to 2430

Tools
Tools covers the tools and utilities you use to work with Teradata and its supporting ecosystem. You'll find information on everything from the Teradata Eclipse plug-in to load/extract tools.
Teradata Employee

TPump maximum pack factor has been increased from 600 to 2430

In TPump 13.00.00.02 and higher releases the maximum pack factor has been increased from 600 to 2430.

TPump users use "PACK <statements>" in the "BEGIN LOAD" command to specify the number of data records to be packed into one request, where PACK is a TPump keyword and “statements” actually refers to the number of data records to be packed.

Packing improves network/channel efficiency by reducing the number of sends and receives between the application and the Teradata Database.

To utilize the new pack factor of 2430 developers may use one of the following methods:

  • Simply use PACKMAXIMUM in "BEGIN LOAD" command, or
  • Explicitly specify PACK 2430 in "BEGIN LOAD" command.

Here is a brief summary of the benefit of this feature enhancement, and possible negative performance impact as a result of AWT consumption/exhaustion.

1. Feature benefits

Increasing the packing factor may improve network/channel efficiency by reducing the number of handshakes (sends and receives) between the client application and the Teradata Database. Increasing the maximum pack factor for empty tables, this enhancement could help TPump to double faster; for populated tables, this feature enhancement could improve both throughput and efficiency in terms of rows/ampcpusecond, although the system was I/O bound in this scenario. Here are some statistics from Bob Hahn:

type ses-pack rows pack elp mb r/s r/s/n r/amp cpusec reqs clientcpu ampcpu ampio
tpump 1-600 1000000 600 180 100 5561 1390 5901 1667 2.9055 169.46 1693181 1
tpump 16-600 1000000 600 26 100 39085 9771 3601 1667 3.102 277.7 1803722 16
tpump 32-600 1000000 600 30 100 32893 8223 3447 1667 3.321 290.12 1886059 32
tpump 64-600 1000000 600 33 100 30536 7634 3254 1667 3.789 307.35 1986059 64
tpump 1-2430 1000000 2430 75 100 13399 3350 12497 412 2.684 80.02 809766 1
tpump 16-2430 1000000 2430 14 100 72228 18057 7551 412 2.79 132.44 846145 16
tpump 32-2430 1000000 2430 14 100 72495 18124 6801 412 2.99 47.04 909644 32
tpump 64-2430 1000000 2430 14 100 69798 17450 6156 412 3.2455 162.45 1009790 64

populated--12516 blocks, 90M rows

type ses-pack rows pack elp mb r/s r/s/n r/amp cpusec reqs clientcpu ampcpu ampio
tpump 1-600 500000 600 258 50 1936 484 4093 833 1.517769 22.15 1752443 1
tpump 16-600 500000 600 79 50 6289 1572 3494 833 1.620754 143.12 1790132 16
tpump 32-600 500000 600 72 50 6978 1572 3494 833 1.77573 146.92 1789901 32
tpump 64-600 500000 600 72 50 6988 1747 3291 833 2.073685 151.94 1790425 64
tpump 1-2430 500000 2430 193 50 2596 649 5247 206 1.431783 95.29 1755596 1
tpump 16-2430 500000 2430 72 50 6963 1741 4381 206 1.438782 114.14 1780706 16
tpump 32-2430 500000 2430 70 50 7100 1775 4131 206 1.541766 121.03 1779529 32
tpump 64-2430 500000 2430 62 50 8000 2000 3861 206 1.534767 129.5 1785922 64

With the increased maximum pack factor of 2430, performance gain could be observed when Array Support is turned on in TPump.

2. Possible performance impact due to AWT consumption/exhaustion:

For PA requests, the PE dispatches every record in the pack into a step, up to 1 step per amp, and each step uses one AWT; for non-PA requests, the PE dispatcher respects the parallel step limit of 20. High AWT consumption can imply significant performance impact.

So with PAs the AWT consumption can be as high as sessions * pack. With non-PA it can be no higher than sessions * 20. There are usually 50 ‘new work’ AWTs per node so high pack + PA especially with multiple concurrent TPumps can have a bad impact.

The following experiment was done with 10102012  rows and 130560 blksize.

parms jobstart rqsts runelp rowspsec r/s/n maxpcpu avgpcpu maxpelp maxacpu avgacpu maxiocount avgiocount maxsteps avgsteps
32-288 17:43:43.8 35107 104.9 96283 10698 0.02 0.003 0.03 0.156 0.066 1554 1029 206 183
32-576 17:45:55.5 17569 102.8 98239 10916 0.024 0.005 0.05 0.232 0.125 2684 1947 269 250
32-864 17:47:58.0 11723 102 99000 11000 0.024 0.006 0.13 0.316 0.175 3618 2854 286 274
32-1152 17:49:56.8 8800 93.52 108019 12002 0.02 0.008 0.06 0.34 0.217 4927 3802 289 283
32-1440 17:51:45.7 7046 448.6 22519 2502 0.024 0.007 0.12 0.356 0.206 5607 4477 289 286
32-1728 17:59:29.2 5877 377.1 26785 2976 0.02 0.009 0.05 0.384 0.237 6796 5484 289 287
32-2016 18:06:02.2 5042 666.1 15166 1685 0.02 0.42 0.01 0.44 0.27 7277 6155 289 287
32-2304 18:17:22.6 4416 567 17815 1980 0.02 0.011 0.22 0.476 0.302 8810 7155 289 287
64-288 18:27:04.2 35134 83.89 120419 13380 0.04 0.004 0.22 0.16 0.078 1371 1036 204 183
64-576 18:28:45.3 17598 88.85 113697 12633 0.056 0.005 0.17 0.236 0.134 2480 1914 267 249
64-864 18:30:29.5 11754 79.17 127598 14178 0.028 0.007 0.17 0.296 0.184 3771 2825 285 273
64-1152 18:32:06.3 8831 80.4 125646 13961 0.036 0.008 0.33 0.368 0.227 4777 3763 289 282
64-1440 18:33:42.9 7077 470.5 21470 2386 0.016 0.007 0.08 0.34 0.206 5458 4424 289 285
64-1728 18:41:48.9 5908 384.5 26271 2919 0.02 0.009 0.19 0.388 0.239 6711 5416 289 286
64-2016 18:48:28.9 5073 665.5 15180 1687 0.02 0.01 0.06 0.428 0.27 7392 6104 289 286
64-2304 18:59:49.8 4447 580.1 17415 1935 0.02 0.011 0.06 0.508 0.304 8790 7086 289 285
96-288 19:09:46.1 35157 95.5 105780 11753 0.064 0.003 0.11 0.176 0.077 1384 1033 204 183
96-576 19:11:37.8 17624 87.53 115412 12824 0.028 0.006 0.16 0.252 0.135 2462 1908 268 249
96-864 19:13:21.3 11783 78.94 127970 14219 0.028 0.007 0.17 0.312 0.188 3611 2808 286 273
96-1152 19:14:57.5 8861 80.67 125226 13914 0.032 0.009 0.34 0.368 0.232 4798 3745 289 281
96-1440 19:16:34.6 7108 481.8 20966 2330 0.016 0.007 0.05 0.372 0.206 5439 4404 289 284
96-1728 19:24:51.6 5939 397.3 25424 2825 0.02 0.009 0.11 0.384 0.237 6447 5372 289 284
96-2016 19:31:45.0 5104 679.3 14872 1652 0.024 0.01 0.05 0.436 0.269 7109 6045 289 284
96-2304 19:43:21.0 4478 591.1 17090 1899 0.02 0.011 0.07 0.48 0.298 8474 7019 289 284

NOTE:

TPump will still automatically lower the PACK factor in the case of unusable pack factor (i.e., above the limit). For production or other repetitive runs, the user should optimize the PACK factor accordingly. This briefing primarily introduces the enhancement of TPump maximum pack factor; in any rate, it is not always the case that higher the pack factor, the better the performance. There could be possible negative performance impact of AWT consumption/exhaustion with elevated packing factor.

Special thanks go to Bob Hahn for sharing his discovery and thought.

Tags (2)
3 REPLIES

Re: TPump maximum pack factor has been increased from 600 to 2430

Very informative, thanks
Teradata Employee

Re: TPump maximum pack factor has been increased from 600 to 2430

Good info!

Re: TPump maximum pack factor has been increased from 600 to 2430

very good work!!