Performance Hints

In general, the performance of the HPL depends on the underlying hardware resources: CPU, memory, disks, tapes, controllers, and so on. Any of these resources could be a bottleneck, depending on the speed of the resource, the load on the resource, and the particular nature of the load or unload.

For example, load and unload jobs that perform no conversions consume minimal CPU resources. These jobs are thus likely to be limited by device or controller bandwidth. On the other hand, ASCII loads and unloads are CPU intensive because of the overhead of conversion to and from ASCII. This section discusses some topics that you should consider when you try to improve performance.

Choose an Efficient Format

When you load data to or unload data from a non-Informix source, you can use fixed or delimited format in an appropriate code set such as ASCII or EBCDIC. In general, ASCII loads and unloads are the fastest. If you are using onpload for a machine or schema reorganization, choose the no-conversion format.

Delimited and fixed ASCII formats are comparable in behavior except when VARCHAR data is present. If the schema contains VARCHAR data and the length of the VARCHAR data varies greatly, you might want to choose delimited format.

Ensure Enough Converter Threads and VPs

As mentioned earlier, loads and unloads other than raw and fast-format ones are likely to be CPU intensive due to conversion overhead. In such cases, conversion speed is likely to determine the load or unload speed. It is thus important to use sufficient conversion resources (that is, enough converter threads and VPs).

The number of converter threads that is required for a device depends on the relative speeds of the device and the CPU as well as the data types in the table being loaded or unloaded. CHAR and VARCHAR formats are the cheapest to convert. INT, DATE, SMFLOAT, and FLOAT are more expensive. DECIMAL and MONEY are among the most expensive formats to convert.

The PLCONFIG file specifies the number of converter threads per device. You can override this value on the onpload command line with the -M option.

The number of converter VPs should be based on the conversion intensity of the load or unload and the number of physical CPUs on the computer. If the load or unload is expected to be convert intensive, you might want to specify the number of convert VPs to be the number of physical CPUs (or one fewer) to take advantage of all of the available CPUs. You can set the number of converter VPs in the onpload configuration file.

The database server and onpload client VPs might both be competing for the same physical CPU resources. To reduce contention, run only the number of VPs that are necessary on both the database server and onpload sides. However, if the number of database server VPs is already specified, you might have a choice only in the number of onpload VPs. In this case, the suggestions in the previous paragraph apply.

Too few converter threads and VPs can make conversion a bottleneck. On the other hand, too many converter threads can waste time in scheduling threads in and out of the VPs. In general, more than ten converter threads per VP is too many.

Ensure Enough Memory

To maximize available memory and scan resources, HPL automatically sets the zPDQPRIORITY environment variable to 100, if it is not already set. If the PDQPRIORITY environment variable is set, HPL uses that value.

If the PDQPRIORITY environment variable is set to 0 or if the PDQPRIORITY environment variable is disabled on the database server, then HPL cannot unload multiple devices.

Ensure Enough Buffers of Adequate Size

The onpload utility lets you set the size and number of buffers in the plconfig configuration file. For adequate performance, you should provide at least two (preferably three) AIO and stream buffers per converter thread. The size of AIO buffers should be at least as large as the device block size, and the size of the stream buffers should be large (32 or 64 kilobytes), as the following table shows.

Increase the Commit Interval

In the deluxe modes, the commit interval specifies the number of records that should be loaded before the transaction is committed (see Figure 111). Low values (frequent commits) degrade performance and high values improve performance. If you increase the commit interval, you might need to increase the size of your logical-log buffers.

While larger commit intervals can speed up loads, larger commit intervals require larger logical-log space and increase the checkpoint time. These side effects impact other system users during onpload operations.