The size of the Reader’s input queue is set with the environment variable,

SSALDR_RBSIZE=nnn

where

nnn

is the number of records. The default value is 5000.

This parameter is also used to calculate the size of the key generation output queues. They are calculated as

SSALDR_RBSIZE / number_of_key_threads * 8

In order to keep the Key Generation and Writer threads busy, the input queue must be filled as quickly as possible.

When reading from a flat-file, the input queue can be filled very quickly, and in general, the bottleneck is in the Key Generation and/or Writer threads. Since the Writer thread blocks for a short period during sort processing, it is advantageous to have a large input queue (and therefore large key generation output queues), so that key generation can proceed concurrently.

When the Reader’s input queue is filled from records from a database, the Reader thread is usually the bottleneck and the other threads spend time waiting for work.

A thread can wait for two reasons:

To determine how often a thread had to wait, refer to the statistics in the Table Loader log file. When each thread ends, it reports the number of times it had to wait for work. For example,

Reader thread [1] ends. Records In 900000. Waits 960
Keygen thread [3] ends. Processed 450000. Waits 214
Keygen thread [4] ends. Processed 450000. Waits 214
Writer thread [2] Extract ends. IDT out 900000 Waits 448

The thread with the least number of "waits" is the busiest thread (bottleneck). In the example above, the Key Generation threads were the busiest. The Reader thread spent some time waiting for the Key threads to make room in Reader’s output queue. This is typical of a flat-file load.

When reading from a database, it in not uncommon for the Reader thread to report zero waits. That is, it was reading records as fast as the DBMS could deliver them and the other threads were able to keep up with the work load by keeping the input queue in a state where there was always enough room to add the incoming records.

The objective is to make the input queue large enough to keep it from becoming the bottleneck.

If reading from the database and the reader thread reports 0 waits, the reader queue is long enough. If reading from a flat-file, the reader queue must be set large enough so that the key generation threads are the busiest (least waits).