Design high quality and efficient Informix database application procedure

Informix database is a widely used relational database, how to improve its application performance is a topic of concern, especially with the increasing volume of data and application processing in database, its running efficiency problem is especially prominent.

As we all know, the performance optimization of database application system is a highly complex, extremely tedious and extensive work. In the face of complex database application system performance tuning, we often feel the start. In fact, performance tuning is all about the network, hardware, operating system, database parameters and applications. In fact, the performance improvements to network, hardware, operating system, and database parameters all add up to about 40% of the performance of the database application system, and the remaining 60% system performance improvements all come from optimization of the application.

Combining with the work practice, this paper puts forward some optimization measures for the program design and data maintenance of Informix database application system from aspects of the configuration and performance adjustment of Informix online parameters, the way of database table creation, index strategy, query statement (SELECT) optimization, etc.

First, the optimization measures of INFORMIX online parameter setting

The performance adjustment of the parameters of Informix database system directly affects the operation efficiency of Informix database. In determining the system performance tuning, the first should be a complete tuning scheme, first monitoring the system and analysis of the problem, and then according to the results of each adjustment of a parameter, and then further monitor the system to see if there is any change, and then do a near-step adjustment. One important principle is that it's best to adjust only one parameter at a time.

Database system performance is typically associated with four aspects of disk I/O, CPU, shared memory, and network. As for these four aspects in the performance tuning is the weight, but also according to the actual situation of the whole system specific analysis.

1. Disk I/O

The principle of disk adjustment is to reduce the number of read disk, the maximum amount of data per read disk, data distribution uniformity, to prevent bottlenecks. The disk space on INFORMIX online should be in bare device mode (raw device) instead of a processed file space (cooked file spaces), which is much faster and more reliable than the latter. Several online configuration parameters that affect disk I/O:

CKPTINTVL: This parameter specifies the time interval between checkpoints (CheckPoint).

Physfile: The size of the physical log is recommended as 25% of the entire log space. That is: Physical log space size: Logical log space size =1:3

LRUs: This parameter indicates the number of least recently used queues set in the shared memory buffer pool. A more LRU queue will allow more page-cleaner operations and reduce the size of each LRU queue. For a single CPU system, Informix recommends setting the LRUs parameter to a minimum value of 4. For a multiple-CPU system, Informix recommends setting the LRUs to be the larger of the minimum value 4 and Numcpuvps values.

Cleaners: This parameter specifies the number of page cleanup threads that are executed.

Refer to the following table:

Number of disks cleaners value recommended by Informix

Number of <20 disks

20-100 number of disks/2

Number of >100 disks/4 (no more than 128)

2, CPU

Informix Dynamic database server to CPU processing using advanced multiple threads mechanism, that is, the use of VP (Virtual Processer Virtual processor) to manage physical CPU resources, balance CPU load, which has a greater impact on performance is CPUVP. We can think of CPUVP as virtual CPUs that are managed by the Informix Dynamic server on top of the physical CPU, so the physical CPU is transparent to the user.

CPUVP is a multiple-thread process that is executed on the CPU, except that there is no task to do, such as a thread in the ready queue that does not wait for execution, or an operating system that forces CPUVP to relinquish the CPU to another process.

To optimize throughput, set the Numcpuvps to be able to handle the smallest number of tasks to be undertaken, which is less than or equal to the number of CPUs available in the system, and it is recommended that you do not set the number of CPUVP to be greater than the number of available CPUs. Typically for online transaction processing (OLTP) applications: numcpuvps= The actual number of CPUs-1 (for a single CPU system, this parameter should be 1); For online analytical processing (OLAP) applications: numcpuvps= The actual number of CPUs.

If CPUVP cannot withstand the task to be performed, this means that CPU performance needs to be improved to solve the problem. Only the presence of another processor can be used to solve the problem by adding CPUVP. You can also remove other tasks from the system, freeing up CPU cycles for virtual processors, which improves performance.

Increasing the number of CPUVP without increasing CPU resources will only make the problem more serious, resulting in more CPU cycle competition, so that the operating system will have more work to do the balance, the contention for CPU time CPUVP make its burden increased. As a result, context switching increases, which not only consumes time but also uses more CPUs.