Teach you easy to understand Informix database tuning method

Informix IDs database is widely used in various industries, such as finance, telecommunications and postal services, it is a multi-threaded relational database server, with symmetrical multiprocessor technology and single processor architecture, with advanced technology, performance and high reliability and high availability. It provides users with dynamic system management tools to monitor and manage database servers. With the increase of the number of databases and the increase of transaction volume, the operation efficiency of the database is especially outstanding. In the case of hardware environment unchanged, the improvement of database performance has always been a topic of concern.

The performance of database system is directly related to four aspects, such as CPU, shared memory, data storage and network setup. The following focuses on improving database performance by configuring the Informix IDs parameters and monitoring Informix IDs operational efficiencies.

Tuning and monitoring of virtual processor parameters

Informix IDs has more than 10 classifications of virtual processors, and each virtual processor, like a CPU in the operating system, allows multiple processes to serve multiple users, and can run multiple threads to service multiple SQL client applications. One of the most important virtual processors is CPU, AIO, network processor Three, in these three kinds of servers, CPU virtual processor (CPU VP) is the most important, it drives other virtual processors, such as disk I/O virtual processor (AIO VP) and the IDS session all threads. The AIO VP function is to perform disk I/O when the SQL statement accesses or updates database data. A network processor involves a client or user connection to a database server. There are two types of connections: a shared memory connection and a network connection. These three types of parameters are described below.

1. Parameters for CPU virtual processor (CPU VP)

Numcpuvps: Defines the number of CPU VP started by Informix IDS. Under normal circumstances can not exceed the number of system CPUs, for single or dual-CPU computer systems, it is recommended to set the Numcpuvps is 1 or 2, even with one or two CPU VP; For more than 4 CPUs, it is recommended to set the Numcpuvps value equal to the total number of processors minus 1.

SINGLE_CPU_VP: Multiple CPU VP (0) and single CPU VP (1) settings are defined.

Multiprocessor: Defines multiple CPU VP (1) or a single CPU VP (0).

Aff_nprocs: Defines the number of CPUs that can be bound to the CPU VP.

Aff_sproc: Defines the serial number of successive CPUs (values defined by the Aff_nrpocs parameter) to connect the ordinal of the first CPU to the CPU VP.

For example, the hardware platform on which an Informix IDs system is located has 4 Cpu,aff_nprocs set to 3 (3 CPUs available for binding CPUVP), Numcpuvps set to 3,aff_sproc set to 1, Then 3 CPUVP need to be bound to the CPU, starting with the 2nd CPU and binding to the 第二、三、四个 CPU. SINGLE_CPU_VP is set to 0.

2. For configuration of disk I/O virtual processor (AIO VP)

NUMAIOVP Specifies the number of Aio/kaio virtual processors on the system, and can be set to 2 if Informix IDs is in bare device storage.

Numcpuvps, Noage, Aff_nprocs, Aff_sproc, NUMAIOVP are replaced with Vpclass parameters in later versions of Informix IDS9.2. When Informix IDs is online, you can use the onmode-p (-+ +) # to increase or decrease the virtual processor. # represents an increase or decrease in the number of virtual processors.