Developing high-quality and efficient Informix database applications (1)

INFORMIX databases are widely used relational databases. How to improve their application performance is a matter of concern. Especially with the increasing volume of data in databases and the increasing volume of transactions processed by applications, its running efficiency is particularly prominent.

As we all know, the performance optimization of database application systems is a highly complex, complex, and comprehensive task. In the face of system performance tuning of complex database applications, we often feel overwhelmed. In fact, performance optimization is nothing more than network, hardware, operating system, database parameters and applications. In fact, the performance improvements obtained by optimizing the network, hardware, operating system, and database parameters only account for about 40% of the database application system performance increases, the remaining 60% system performance improvements all come from application optimization.

This document combines the practices of informix online parameter configuration and performance adjustment, database table creation methods, index policies, and query statements (SELECT) optimization and other aspects of the INFORMIX database application system program design and data maintenance to put forward some optimization measures.

I. Optimization Measures for INFORMIX ONLINE parameter settings

The performance adjustment of INFORMIX database system parameters directly affects the running efficiency of INFORMIX databases. When determining system performance optimization, we should first develop a complete optimization scheme, first monitor the system and analyze the problem, and then adjust a parameter each time based on the analysis results, further monitor the system to check whether the system performance has changed, and then make further adjustments. There is an important principle: it is best to adjust only one parameter at a time.
The database system performance is generally related to four aspects: disk I/O, CPU, shared memory, and network. In terms of performance tuning, we need to analyze the problem based on the actual situation of the entire system.

1. Disk I/O

The principle of disk adjustment is to reduce the number of disk reads, greatly increase the data volume of each disk read, and evenly distribute data to prevent bottlenecks. The disk space of informix online should be raw device instead of cooked file space, which is much faster than the latter, high reliability. Several ONLINE configuration parameters that affect disk I/O:

◆ CKPTINTVL: Specifies the interval between checkpoints.
◆ PHYSFILE: It is recommended that the physical log size be 25% of the total log space. Physical log space: Logical log Space =
◆ LRUS: this parameter indicates the minimum number of recently used queues set in the shared memory buffer pool. Configuring more LRU queues allows more page cleaner operations and reduces the size of each LRU queue. For a single CPU system, we recommend that you set the LRUS parameter to the minimum value of 4 for INFORMIX. For multi-CPU Systems, we recommend that you set LRUS to a value greater than 4 and NUMCPUVPS.
◆ CLEANERS: this parameter specifies the number of page clearing clues executed.

See the following table:
Number of disks recommended CLEANERS values for INFORMIX
<20 Number of disks
20-100 disk count/2
> 100 disks/4 cannot exceed 128)

2. CPU

The INFORMIX Dynamic Database Server uses an advanced multi-process multi-clue mechanism for CPU processing, that is, using virtual processer virtual processor (VP) to manage physical CPU resources and balance CPU loads, cpu vp has a major impact on performance. We can regard the cpu vp as a virtual CPU managed by the INFORMIX dynamic server on top of the physical CPU. Therefore, the physical CPU is transparent to users.
Cpu vp is a multi-clue process, except for no tasks. For example, there is no clue waiting for execution in the ready queue or the operating system forces the cpu vp to give the CPU to other processes, it will always be executed on the CPU.

To optimize throughput, set NUMCPUVPS to the minimum number of tasks that can be processed. This number is less than or equal to the number of CPUs available in the system, we recommend that you do not set the number of vcpus to a greater value than the number of available CPUs. For online transaction processing (OLTP) Applications: NUMCPUVPS = actual number of CPUs-1 for a single CPU system, this parameter should be 1); for Online Analytical Processing (OLAP) applications: NUMCPUVPS = actual number of CPUs.

If the cpu vp cannot afford the task to be executed, it indicates that the CPU performance needs to be improved to solve the problem. Only when another processor exists can the problem be solved by adding the cpu vp. You can also remove other tasks from the system to release the CPU cycle for the virtual processor to improve performance.
Increasing the number of CPU VPNs without adding CPU resources will only cause more serious problems and lead to more CPU cycle competition. In this way, the operating system must have more work to balance, CPU VPNs competing for CPU time increase the burden. As a result, context switching increases, which not only consumes time but also consumes more CPU resources.

3. Shared Memory)

When adjusting the system performance, there are two considerations for memory adjustment:
◆ Is there enough physical memory?
◆ Is the shared memory configuration of informix online reasonable? Is the physical memory fully used?

Informix online Shared Memory is divided into three parts: the resident part, the virtual processing part, and the message part, which are only available when the client and server use the shared memory connection.

You can check whether the system has enough physical memory through the Virtual Processing of INFORMIX shared memory. The current operating system and database system only have a small part of data and process processing in the memory. This small part of data page is called the working set ). When a process workgroup changes or other processes run, the memory required by each process workgroup may exceed the actual memory capacity. At this time, some data pages will be removed from the actual memory, and other data pages will be moved.

Several ONLINE configuration parameters that affect shared memory:

◆ SHMVIRSIZE: Specifies the size of the virtual area allocated to the shared memory of the ONLINE instance. The virtual area of the shared storage stores data related to sessions and requests and other information. Although the shared memory is added to the virtual zone to process large queries or peak loads ONLINE, the allocation of shared memory increases the transaction processing time, we recommend that you set the shm1_size for INFORMIX to provide a virtual interface that meets the general needs of daily operations. Generally, 100 KB is set for 50 users and KB is set for users.

◆ SHMADD: Specifies the size of the shared memory increment automatically added to the virtual zone by ONLINE. There are some compromise factors in determining the value of the hour. CPU usage is required to increase the shared memory: the larger the increase, the less the number of increases, and the less memory left for other processes. Generally, a large increase is used, but when the memory load is very heavy, a small increase makes other programs better share memory resources. INFORMIX has the following suggestions:
SHMADD value recommended for actual memory size
<= 256 MB 8192KB (default)
256-512 MB 16,384 KB
> = 512 MB 32,768 KB

◆ BUFFERS: this parameter can be used for the number of ONLINE data BUFFERS. These buffers reside in the resident area and are used to cache the data pages of the primary database. The more available buffer zones, the more likely the required data pages will be used for the previous request and are already in the memory. This parameter has a significant impact on database I/O and transaction processing throughput. However, allocating excessive buffers will affect the memory system and lead to excessive page activity.
We recommend that you set BUFFERS to 20% to 25% of the physical memory (in MB. The actual BUFFERS unit is page. The page sizes of different operating systems are different and therefore need to be calculated.

◆ LOCKS: this parameter sets the maximum number of LOCKS available at any time. In ONLINE, each lock occupies 44 bytes of the shared memory. When allocating shared memory, consider the resources used by the lock. Generally, the locks can be allocated more in the range of 2000 to 8000000.

◆ LOGBUFF: this parameter is used to specify the number of three shared memories that are respectively retained in the buffer zone for storing logical log records. These buffers store logical log records until they are refreshed to the logical log files on the hard disk. The buffer size determines the frequency when it is fully filled, and thus the frequency when it must be refreshed to the logical file on the hard disk. We recommend that you set INFORMIX to 16 kb or 32 KB. For busy applications, you can set it to 64 KB.

◆ PHYSBUFF: this parameter is specified as the number of two shared memory buffers used to temporarily Save the modified data pages. The buffer size determines the frequency when it is fully filled, and thus the frequency when it is written to physical logs on the hard disk. The minimum value is the size of 1 page. We recommend that you set the size of 16 pages for INFORMIX.

For OLTP and OLAP systems, the configuration and adjustment of informix online Shared Memory are very different.
The OLTP application occupies a large part of the shared memory. Basically, the buffer pool occupies 20% to 25% of the physical memory. For example, if the physical memory of an RS/6000 machine is 1024 MB, the number of buffers should be set to 1024MBX20% at first)/4KB = 52428AIX system with a size of 4 kb per page ); then, increase the number of BUFFER until the number of read caching reaches 95%, write caching reaches 85%, or the system starts to increase the Data removal (Page out ). Note: The high cache speed means that the disk I/O is reduced.
For OLAP systems, a small number of users read a large amount of data. This operation will result in the continuous growth of the shared memory pool, such as the sorting pool and hash function pool. These pools exist in the virtual processing part of the shared memory: when optimizing the OLAP system, the size of the virtual processing part should be set to 75% of the physical memory and set through the shm1_size parameter.

4. Network

One way to avoid network congestion is to reduce the network transmission volume or move some of the transmission volume elsewhere. The following are some measures to reduce the network transmission volume.

1) Configure multiple NICs
By setting related system files that define TCP/IP connections, informix online can communicate among multiple NICs. The following describes how to configure ONLINE in a UNIX system:
◆ Add one to each Nic in the/etc/services file:
Info21 6621/tcp
Info22 6622/tcp
◆ Use different IP addresses to add each Nic of the local machine to the/etc/hosts file:
66.77.9.79 rsibm21
66.77.9.80 rsibm22
◆ Add one to each network card in the $ INFORMIXDIR/etc/sqlhosts file of INFORMIX:
Server21 onsoctcp rsibm21 info21
Server22 onsoctcp rsibm22 info22
◆ In the onconfig file of INFORMIX, set the parameters:
DBSERVERNAME server21
DBSERVERALIASES server22
After the configuration is complete, the application can select a path to the database server by specifying the sqlhosts item name or using the default database server name specified by the Environment Variable INFORMIXSERVER.

2) programming considerations

Below are some programming technologies that can reduce the amount of network transmission:
◆ Use storage routines for tasks with a group of SQL statements because they can reduce the number and length of messages. Maintaining the relative integrity of the database, rather than coding it in the application, can also reduce the amount of network transmission.
◆ Return only the data required by the application and filter all unnecessary rows and columns.
◆ Use the transfer cache when sending and receiving data.
3) Different UNIX vendors provide different levels of control over TCP/IP parameters. The following are parameters that can work on performance:
◆ Number of stream buffers. For high loads on the Client/Server, more buffers can improve performance.
◆ Packet) size. DSS is applicable to a relatively small value. Generally, it is better to use a small value for OLTP.