Informix performance optimization (1)
Author: CCBZZP
The following is a little experience I use in Informix Database, share it with you, welcome everyone to discuss!
1. Log buffer If you are not afraid to lose a few transactions, it is best to use buffer logs so that you can get better performance. It is best to use non-buffered logs. 2. DSS Server has three objectives: 1>. Total query can maximize the PDQPriority setting limit of the onconfig file to 25% 2>. Each query processing time minimizes the set limit of the PDQPriority setting limit in the onconfig file to 50% 3>. Balance priority Limit the PDQPriority setting in onConfig file to 25%, less than 50%. Optimization of OLTP Server has three objectives: 1>. Update activity maximizes the maximizing the use of buffer logs to maximize the inspection point interval, the cycle minimizes ONCONFIG PDQPriority settings in the file is 0 to increase the physical log length to maximize the write buffer percentage. In fact, the object is also contradictory, the key is how to pay. 2>. Query activity maximizes the maximum buffers can put onconfig files PdqPriority Sets to 0 or 1 Maximize the read buffer percentage 3>. Transaction security Maximize CKPTINTVL Use non-buffered logs to use redundant disks and I / O path to reduce physical log length 4. Simple Query Server has three goals: Maximize Buffers, it is generally> = 40% RAM can set PDQPriority in the onconfig file <25% 5. Memory INFORMIX can be used by memory, how much is it, use Ids7.x as an example: buffer Up to 768000 Pages (OS 3GBW / 4KB) DSS memory up to 1G lock up to 8000000 logical log buffer 3 logsize maximum 2G, Total 6G Physical log buffer 2 Physfiles up to 2G, Total 4G data dictionary buffer is not limited, can be adjusted Parameters DD_HASHSIZE and DD_HASHMAX OnSTAT -G DIC determines if the data dictionary buffer is close to capacity The stored procedure buffer is not limited, and the parameter PC_HASHSIZE and PC_POOLSIZE INSTAT -G PRC determines whether the stored procedure buffer is close to the capacity data distribution buffer can adjust the parameter DS_HASHSIZE and DS_POOLSIZE ONSTAT -G DSC to determine if the data distribution buffer is close to capacity 6. Disk The problem there is more, the better, the better, more drives than large drives, take RAID disk arrays 7. The kernel limit different OS has different kernels, which is adjustable .8. Memory parameters onstat -g seg Determine shared memory allocation And query the memory partition SHMVIRTSIZE to ensure the lowest normal load memory, if there are many messages for dynamic new shared memory in the message log file, you should add the value of this parameter. SHMADD should at least be a 10% shmtotal of Shmvirtsize unless a small system, otherwise To 0 let memory growth .9. Split table and a large number of zones with oncheck -pt and oncheck -pe check tables and their layout on disk generally say more than 33 regional systems, can compress the table to solve 1>. Remove Table Rebuild and Reset Data 2>. Recreate Index 3>. Place the table and index in different dbspace
Welcome everyone to discuss!
to be continued...
