Organize tables reasonably in Informix online under Unix

As we all know, the database is composed of tables, in practical use, the table is dynamic, and some tables are growing quickly. When a table in the Dbspace has more than 8 staggered intervals, it will affect its access efficiency, with the further increase in the staggered interval, and even become a bottleneck to access the database, often appear system turbulence phenomenon. It is characterized by slow response time, extended backup time, and Onstat-giof command, Glance (UNIX) command to view disk I/O is often high.

Here are two commands for viewing table allocations: Oncheck-pe and oncheck-pc. The former produces a detailed list of the pages used in each chunk of the dbspace, the latter examines the integrity of the system catalog tables, and summarizes the situation of each table, including creation time, lock category, interval total, interval size, and so on. The latter runs for a slightly longer time. Take a closer look at the results, especially those that are frequently deleted, inserted, and added to the record, which may surprise you. I've seen a table with 460 megabytes, 67 intervals, and another 430 trillion, 57 intervals, which seriously affect the performance of the system. Don't bother with a table that is less than a mega-level.

Usually, a dbspace has multiple chunk, which can be seen with onstat-d. However, not all chunk are reasonably distributed, often some chunk are not used at all (since the initial creation of the database will generally allocate ample space), and some of the chunk is overcrowded, table seriously staggered. To balance I/O and improve access efficiency, you can put a faster-growing table in a separate chunk. Unfortunately, however, Informix-online did not provide such an order. Here's an ingenious way to take a look at CreateTable's simple command, which can be followed by extentsizeextent-size and nextsizenext-size two parameters, which specify the initial interval size of the creation table, which specifies that when the initial interval is filled, The size of the extended interval, in kilobytes. The following statement:

Create table test
(T1 char(10)
T2 char(20)
)
extent size 80000
next size 500

The statement creates a table test with 80 megabytes of initial space, such as 500K of space each time the data is filled.

The space allocation strategy for creating tables in Lnformix is to first find a contiguous, large enough space for the table. Then we can take advantage of this and the problem will be easily solved. Suppose there is a dbspace, it has two chunk, and the second is idle. Let's put some of the faster-growing tables into the second chunk. First step: Back up the database. Step two: Delete the useless records in the faster-growing table and unload it out. Step three: Drop the table that needs to be rebuilt. Step Fourth: Use onstat-d to see how many free pages are in the CHUNK1, set to N. Fifth step: Create a table test, set its extent-size slightly less than n*m,m for each page size, usually 2K, 4K. Step Sixth: Use onstat-d to see if CHUNK2 is still idle, that is, the test table in the previous step is created in Chunk1, otherwise delete the test table and assign a smaller extent-size rebuild test table. Seventh step: Create the table you want to set to TB, please note that set a reasonable extent-size value, it is best to slightly larger than the current TB table of the total space. At this point TB is definitely built in CHUNK2! Because Informix cannot find the right space in the CHUNK1. Finally remember to delete the test table. If your database has only one chunk, then steps fourth and fifth do not. This also helps reduce table interleaving.

In addition, when you create an index on such a table, you can add parameter Fillfactor10 after CreateIndex, which determines that 90% of the space is set when indexing is established, which is good for frequently updated, fast-growing tables, making its indexes contiguous within a certain space, and also helps improve access efficiency. The default value for the system is 90. After creating the table, it is a good idea to close the log before the load data, which helps to shorten the time of the load data.