Informix Database Optimization Method

Informix IDS databases are widely used in various industries such as finance, telecommunications, and postal services. It is a multi-threaded relational database server that adopts symmetric multi-processor technology and single-processor architecture, it also has advanced technologies, performance, high reliability and high availability. It provides users with dynamic system management tools to monitor and manage database servers. As the number of databases increases and the number of transactions processed by applications increases, its operation efficiency becomes more prominent. As the hardware environment remains unchanged, the improvement of database performance has become a topic of interest.

The database system performance is usually directly related to four aspects: CPU, shared memory, data storage, and network settings. The following describes how to improve database performance by configuring Informix IDS parameters and monitoring the efficiency of running Informix IDS.

Adjustment and monitoring of virtual processor Parameters

Informix IDS classifies virtual processors into more than 10 categories. Each virtual processor is like a CPU of an operating system that allows multiple processes to serve multiple users, you can also run multiple threads to provide services for Multiple SQL client applications. Among them, the most important virtual processor is the CPU, AIO, and network processor. Among the three servers, the CPU virtual processor cpu vp is the most important. It drives other virtual processors, such as disk I/O virtual processor AIO VP) and all threads in IDS sessions. The function of aio vp is to execute disk I/O when an SQL statement accesses or updates database data. The Network Processor involves the client or user connection of the database server. There are two types of connections: Shared Memory connection and network connection. The following describes the three types of parameters.

1. CPU virtual processor cpu vp) Parameters

NUMCPUVPS: defines the number of CPU VPNs started by Informix IDS. Generally, it cannot exceed the number of system CPUs. for single or Dual CPU computer systems, it is recommended to set NUMCPUVPS to 1 or 2, that is, to use one or two CPU VPNs; for more than four CPUs, we recommend that you set NUMCPUVPS to equal to the total number of processors minus 1.

SINGLE_CPU_VP: multi-CPU VP0) and single-CPU vp1.

MULTIPROCESSOR: defines multiple cpu cidr blocks) or a single CPU VP0 ).

AFF_NPROCS: defines the number of CPUs that can be bound to the cpu vp.

AFF_SPROC: defines the serial number of the first CPU in several consecutive CPUAFF_NRPOCS parameter definitions) connected to the cpu vp.

For example, the hardware platform of an Informix IDS system has four CPUs. If AFF_NPROCS is set to 3, three CPUs can be used to bind CPUVP.) set NUMCPUVPS to 3 and AFF_SPROC to 1, then the three cpuvpns need to be bound to the CPU, starting from 2nd CPUs and binding to the second, third, and fourth CPUs. Set SINGLE_CPU_VP to 0.

2. Disk I/O virtual processor AIO VP) Configuration

NUMAIOVP specifies the number of AIO/KAIO virtual processors on the system. If Informix IDS is stored on bare devices, set it to 2.

In Versions later than Informix IDS9.2, replace NUMCPUVPS, NOAGE, AFF_NPROCS, AFF_SPROC, and NUMAIOVP with VPCLASS parameters. When Informix IDS is online, you can use onmode-p (+/-) # to increase or decrease the virtual processor. # Increase or decrease the number of virtual processors.

3. Network Processor parameter configuration

NETTYPE: defines the connection type of Informix IDS and the number of polling leads that can be connected by connections. If the sqlhosts file supports more than one interface or protocol connection, you must specify an independent NETTYPE parameter for each connection type.

Polling leads can run on two types of VPNs: net vp and CPUVP. For optimal performance, we recommend that you use the NETTYPE table item to allocate only one polling thread to the cpu vp class and distribute the other polling leads to the net vp. The polling thread assigned to any connection type cannot exceed the value of NUMCPUVPS.

The NETTYPE configuration format is as follows: NETTYPE connection_type, poll_threads, c_per_t, vp_class. Connection_type indicates the connection protocol for polling clue allocation. poll_threads indicates the number of polling clues allocated to this connection type. For any connection type, this value cannot exceed the NUMCPUVPS value; c_per_t is the number of connections for each polling thread. You can use the following formula to calculate this value: c_per_t = connections/poll_threads; connections is the maximum number of connections that you want to support for the specified connection type. For Shared Memory connections ipcshm), this value should be doubled to achieve the best performance. vp_class is the VP class that can run polling clues. If only one polling clue is run on the cpu vp, then it is specified as the cpu vp.

In the monitoring of virtual processors, you can use some system commands or database commands. the commonly used database commands are onstat-grea and onstat-g ioq.

The output of onstat-g rea is as follows:

/Usr/informix> onstat-g rea

Informix Dynamic Server Version 9.30.FC5 -- On-Line -- Up 36 days at 00:22:32-

-5352416 Kbytes

Ready threads:

Tid tcb rstcb prty status vp-class name

Onstat-g rea monitors the number of threads in the ready queue. This includes the threads that are ready to run and waiting for resources. Ideally, a few entries are output or no entries are displayed. If the output of a certain VP category continues to grow, you should consider adding VP to the class.

In the output of the onstat-g iog command, the most important column is the len column. The len column value should always be 0 or close to 0. If the value of this column is high and continues to grow, we may need to add another AIO/KAIO virtual processor to reduce disk I/O load.

There are many ways to monitor virtual processors. You can use the Informix IDS query statement to find the virtual processor usage in the system table: you can also use system commands such as sar and top in Unix to monitor CPU usage of the operating system; you can also execute the Informix IDS command onstat-g glo repeatedly within a period of time to monitor the CPU resources occupied by each virtual processor. Determines whether the CPU is idle.

Parameter Adjustment and monitoring of memory usage efficiency

The memory used by Informix IDS is shared by database server threads and other users and virtual processes. Therefore, this part of memory is called shared memory, which reduces disk I/O, provides the fastest way to communicate between processes, and reduces the total memory usage of the database server.

Informix IDS shared memory is divided into four parts: Resident part, virtual processing part, message part, and virtual extended part. The message part is available only when the client and server use the shared memory connection, and the size is small. The virtual extension area is also very small. It contains the thread heaps used for The DataBlade module and other user-defined routines running in the User-Defined virtual processor.

1. Parameters of resident memory

The resident memory can be subdivided into: Shared Memory header, buffer, logical log buffer, physical log buffer, and lock.

The shared memory header contains descriptions of all other structures in the shared memory. It also contains pointers to these structure locations. The shared memory header is created when Informix IDS is initialized and cannot be optimized.

The buffer zone stores the data that Informix IDS reads from dbspace. It is the database object data, such as the table data or index data. The buffer zone occupies the largest portion of the resident memory. All the buffers are organized into a long recently used least-recently-used, LRU) Buffer Queue and managed using the least recent LRU mechanism. The buffer parameter is BUFFERS. This parameter is called the maximum number of shared memory buffers. This parameter has a significant impact on database I/O and transaction processing throughput. However, if you allocate too many buffers, it will affect the memory of the operating system and cause excessive swap Memory Page activity. We recommend that you set it to 20% to 25% of the physical memory.

The logical log buffer is used to store the logical log records from the last backup. The logical log records the modifications made to database data by SQL statements. When initializing Informix IDS, it creates three logical log buffers and operates cyclically to ensure that each logical log record is refreshed to the disk. LOGBUFF defines the number of logical log buffers. The buffer size determines the frequency when it is fully filled, and thus determines the frequency when it must be refreshed to the logical file on the hard disk. We recommend that you set Informix IDS to 16 kb or 32 KB.

The physical log buffer saves the original value of the record to the physical log buffer before Informix IDS modifies or deletes the record. It is used to restore data when the transaction fails, to maintain data consistency. During Informix IDS initialization, two physical log buffers are created and operated cyclically. The parameter corresponding to the physical log buffer is PHYSBUFF.

Locks include the number of available locks. Each user needs a certain number of locks to connect to and perform database operations. In Versions later than Informix IDS9.2, when the user's lock is insufficient, the number of locks can be dynamically allocated. In previous versions, the value is fixed. The parameter corresponding to the lock is LOCKS. Generally, it is set to 2000 to 8000000.

2. Parameters of the shared memory virtual storage area

The shared-memory virtual storage area stores various types of data, including internal tables, large buffers, session data, thread data stacks, and stacks), data distribution cache, Dictionary cache, SPL routine cache, SQL statement cache, sorting pool, and global pool.

Parameters that affect the virtual storage area are SHMADD, shm1_size, and STACKSIZE.

The shm1_size defines the size of the virtual storage area allocated to Informix IDS shared memory. Informix IDS needs to increase the shared memory to the virtual storage area when processing large queries or peak loads. However, the allocation of shared memory needs to increase the transaction processing time. Therefore, when setting the shm1_size value, generally, it can meet the needs of a daily operation.

STACKSIZE indicates the size of the initial stack assigned by the database server for each active thread. If this parameter is configured too small, the thread will not have enough memory to execute its program, and it will interfere with other threads.

SHMADD defines the size of the shared memory increment that Informix IDS automatically adds to the virtual storage area. When you increase the shared memory, you need to use the CPU cycle. The more you increase each time, the less you increase the number of times, and the less memory you leave to other processes. Therefore, a large increase is generally used. However, when the memory load is very heavy, a small increase will make other programs better share memory resources. Therefore, if the actual memory is less than or equal to 256 MB, it is recommended that SHMADD use the default 8192KB; if the memory is between 512 MB and MB, it is set to 16384KB; if the memory is greater than MB, it is set to 32768KB.

You can run the onstat-g seg command to display the number of segments in the shared memory virtual zone of IDS.

During the initialization of Informix IDS, if the defined virtual memory area is too small, other operating system segments will be automatically appended to the virtual area. Too many segments in the virtual memory cause the overall performance of the database to decline. Therefore, during initialization, the size of the virtual memory area is configured large enough to avoid Dynamic Allocation of shared memory segments. In the output of this column, the class column R is the resident memory segment, V is the virtual memory segment, and M is the message memory segment. If more than three virtual memory segments are displayed, you need to increase the value of the SHMVERSIZE parameter in the configuration file.

The onstat-p command is another command used to monitor memory. The two % caches in the output result show the percentage of the read/write cache ratio, which is generally between 80% and 90%. If it is lower than 80%, adjust the BUFFERS parameter value. The ovlock field indicates the number of attempts to use the lock again after IDS uses the maximum number of LOCKS. If the number is non-zero, you may need to increase the value of the LOCKS parameter in the configuration file. The ovbuf field indicates the number of times IDS tried to use the buffer after using the maximum number of buffers. If the number is large, for example, more than 100000, you need to increase the BUFFERS parameter so that you do not have to wait for the buffer zone to access data from the disk. You can also use the Unix System Command vmstat to monitor memory usage.

Memory and I/O parameter adjustment and monitoring

Informix IDS supports two basic data storage devices: bare devices and file systems. We recommend that you use bare devices to store data files. Compared with file systems, bare devices are much faster to access data, and users cannot see bare device files, which is more secure. Using a file system as a data storage device poses a potential risk. When a file system crashes due to some operating system errors and a database transaction is ongoing, the database server will think that the database transaction has been successfully completed, but in fact, the transaction is in the operating system buffer, which will eventually lead to some inconsistency in the database.

Informix IDS accesses data in dbspace, and dbspace contains one or more chunk blocks ). In Versions later than 9.40, the chunk size can exceed 2 GB. In previous versions, the chunk size cannot exceed 2 GB. You can use onsta-d to monitor the usage and status of dbspace and chunk.

Check Points are directly related to I/O parameter adjustment. A checkpoint is used to synchronize pages on a disk with those in the shared memory buffer pool. The checkpoint time includes the checkpoint interval and the checkpoint duration. During the checkpoint, IDS prevents user threads from entering the critical session and stops all transaction activities. Therefore, if the checkpoint lasts for a long time, the user will experience system suspension.

The CKPTINTVL parameter specifies the interval between checkpoints. When the checkpoint interval is reached, the system performs the checkpoint operation.

PHYSFILE specifies the size of the physical log. Once the physical log PHYSFILE) 75% is full, the checkpoint also occurs.

The LRUS parameter indicates the minimum number of recently used LRU queues set in the shared memory buffer pool.

You can use LRUS, LRU_MAX_DIRTY, and LRU_MIN_DIRTY to control the frequency of page refreshes to the disk between full checkpoints. In some cases, by setting these parameters, the number of pages that need to be refreshed and modified at the checkpoint is very small, which can achieve high throughput. If the checkpoint duration is always more than 10 seconds, therefore, you may need to reduce the value of the LRU_MIN_DIRTY and LRU_MAX_DIRTY configuration parameters to obtain a shorter checkpoint duration. You can use the output of onstat-R, onstat-P, and onstat-F commands to determine the parameter value. Generally, LRU_MIN_DIRTY is set to 50, and LRU_MAX_DIRTY is set to 70.

Adjusting Informix IDS parameters to optimize database performance is an aspect of Informix IDS performance optimization, its performance optimization also needs to be comprehensively considered from the network, hardware, operating system, application and other aspects. Its performance optimization is highly complex, they are complex and involve a wide range of comprehensive work, and they are interrelated and affect each other. During the adjustment process, the database running status and system resource usage should be clarified, and the bottleneck of the problem should be determined. Then, optimize the database performance based on the problem. The adjustment of database parameters is an aspect of database optimization, but it just reminds me of the role of others. (