Informix Dynamic Server database Segment Storage (1)

Informix Dynamic Server is also called multipart storage. Its principle is to distribute the records or indexes in a data table in different disk regions according to rules, to store data on multiple disks, we can improve database efficiency by reducing competition for disk I/O. On the contrary, the basic storage principle encourages data in database tables to be stored continuously on a storage device as far as possible. Data is read in batches when Informix Dynamic Server reads data, to improve database efficiency.

Segmented storage improves query efficiency by balancing disk I/O, implementing parallel operations for internal queries, and scanning data on multiple disks in parallel, the improvement of database performance mainly comes from the improvement of I/O concurrency, rather than the improvement of I/O performance. In fact, segmented storage is a method provided for storing tables with large data volumes. You can choose either segmented storage or segmented storage. A good segmented storage tool can improve query efficiency. Otherwise, the query efficiency of the database will not be improved, but will be reduced. In practical applications, the Informix Dynamic Server does not care about which physical disk the data is stored on, but is specific to the corresponding dbspace, because the minimum unit for concurrent query execution of Informix Dynamic Server is dbspace.

Principle of segmented Storage

Segmented storage can be divided into rotation method and expression-based method. The rotation method uses rules defined internally by Informix Dynamic Server to store tables in segments. When creating a table, you can specify the rotation method for data storage. The syntax is as follows:

CREATE TABLE tablename (aa integer …) FRAGMENT BY ROUND ROBIN IN dbspace1,dbspace2, ……dbspaceN。

The expression-based method uses user-defined rules to store tables or indexes in different dbspaces. Expression-based methods can also be divided into range rules and absolute rules. Range Rules use SQL relationships>, <, >=, <=), or logical operations and, or) defines the Data Partition boundary of a table. Generally, one field is used, or two or more fields can be used as needed. The syntax of the range rule is:

CREATE TABLE tablename (a1integer....) FRAGMENT BY EXPRESSION a1 <= 0 IN dbspace1， a1 >= 0 AND a1 <= 100000 IN dbspace2， REMAINDER IN dbspace3;

Absolute rules are defined by Relational operators>, <, >=, <=), and logical operators and, or). Multiple Fields in a table can be used for segmentation. The syntax of the absolute rule is:

CREATE TABLE tablename (a1 integer....) 　FRAGMENT BY EXAMPLE 　a1 = 100000 or a1=200000 IN dbspace1， 　a1 = 300000 or a1=400000 IN dbspace2， 　REMAIDER IN dbspace3;

Since the rotation method uses the rules defined internally by Informix Dynamic Server, it is relatively simple and convenient to use. The expression-based method uses user-defined rules. To improve query efficiency, it is necessary to reduce CPU load and balance disk I/O operations. Therefore, when using expressions to define segmentation rules, you should follow the following principles:

1. To make the expression as simple and clear as possible, avoid using data type conversion in the expression, so that the expression can be parsed as quickly as possible when the informix Dynamic Server writes or reads data;

2. In order to reduce the computational workload of the expression, the most restrictive part should be put in front of the expression;

3. Avoid frequently updated fields in the segment expression so that the data stored in the segment has a relatively fixed position;

4. Analyze the stored data and query statements to clarify the query output results so that frequently accessed data can be evenly distributed across multiple disks.

The goal of the multipart storage design is to provide users with better response time, better concurrency, better and faster backup and recovery, and better availability. However, the management cost of the database is virtually increased in the segment storage, and the database transfer is not convenient. To achieve this goal, you must consider the hardware features of the host when designing the database in segments, such as the number of CPUs, speed, number of disks, number of disk controllers, number of disks connected to each disk controller, and operating system performance. When the hardware features and operating system performance meet the requirements of segmented storage, you can perform segmented storage for tables with large data storage capacity, which improves the performance.

Segmented storage instance

This article uses an HP Server in the test of multipart storage. The server has four low-speed CPUs, 2 GB memory, and the hard disk uses raid 5 technology. The OS is SCO Unix 5.0.6, and the database is Informix 7.31uc5. During the test, the machine is basically idle. The onstat-d command output result is as follows:

Informix Dynamic Server Version 7.31.UC5 -- On-Line -- Up 7 days at 20:44:47 --

307200 Kbytes

Dbspaces

Address number flags fchunk nchunks flags owner name

92c5413c 1 1 1 1 N informix rootdbs

92c54d94 2 1 2 1 N informix phydbs

92c54e50 3 1 3 1 N informix logdbs

92c54f0c 4 2001 4 1 n t informix tmpdbs

92c54fc8 5 1 5 7 N informix datadbs

92c55084 6 1001 12 1 N informix datatest1

92c55140 7 1001 13 1 N informix datatest2

7 active, 2047 maximum

Chunks

Address chk/dbs offset size free bpages flags pathname

92c541f8 1 1 100 250000 146465 PO-/home/informix/DBS/rootdbs

92c54344 2 2 100 150000 4947 PO-/home/informix/DBS/phydbs

92c54420 3 3 100 500000 90347 PO-/home/informix/DBS/logdbs

92c544fc 4 4 100 500000 499947 PO-/home/informix/DBS/tmpdbs

...... Seven chunks used by datadbs are saved

92c54bdc 12 6 50 950000 484235 PO-/home/informix/DBS/data_chunk7

92c54cb8 13 7 50 950000 420499 PO-/home/informix/DBS/data_chunk8

13 active, 2047 maximum