- Establish your performance objective.
- Take regular measurements of resource utilization and database activity
- Identify symptoms of performance problem: disproportionate use of Memory, Disk or CPU
- Tune of OS and IDS
- Optimize your chunk & dbspace configuration including placement of log and temporary dbsapce
- Optimize your table placement, extent size and fragmentation
- Improve your indexes
- Optimize your background I/O activities including logging, checkpoints, page cleaning etc
- Schedule backup and other batch jobs during off-peak hours
- Optimize the implementation of your database application.
Measurement of performance
- Throughput
- Response time
- cost per transaction
- Resource utilization
Throughput measures the overall performance of an application. It depends upon the following factor(s)
- The specification of host computer
- The processing overhead in the s/w
- The layout of data on disk
- The degree of parallelism that both h/w and s/w supports
- The type of transaction being processed
Response Time measures the performance of the individual transaction or query. It includes the following sequence of action
- User submit the request
- IDS performs query optimization and retrieves any stored procedures
- Performs Disk I/O
- Performs any background process like page cleaning etc
- Returns the results to application
- Application displays the information to end-user
Response time and throughput is directly related. The response time tends to decrease as the overall throughput increases. However if we allocate the max. resourse to a particular transaction then the response time will be ver less. However the other process are left out with very less resource affecting the throughput.
cost per transaction is the financial measure.
Resource Utilization is the %age of the time for which the resource has been in use as compared to the total time that the component is available for use. We need to measure the utilization of the following resources regularly
- CPU
- Memory
- Disk
Resource Utilization can be calculated by the formula
Expected Service time = (Expected Processing Time/(1 – Utilization of the resource in decimal))
The higher the utilization of a particular resource lower its availability for the process. If for a component, the utilization %age is 70% and the expected processing time it should take is 1sec. then the expected service time is
Expected Service time = (1/(1-0.7)) = 1/0.3 = 3.33sec
CPU Utilization can be calculated using the above formula however high CPU utilization does not necessarily means a bottleneck in the system as long as the throughput of the application is high. But when the throughput of the transaction is low and CPU utilization is high then it is directly related to performance problem because CPU is engaged in other housekeeping activities such as memory management. This can be eliminate by
- Scheduling the big queries during off-peak time
- Restrict the use of unrelated applications
Memory Utilization uses different formula to calculate the paging delay which inturn depends on page scan, paging out etc. When a process/thread does not find any free page in memory then the memory management system has to choose a page, which is least-recently used. The process of locating such pages is called as page scan. These pages are copied to swap space on the disk and the page will be freed from memory so that the waiting process/thread can utilize that page. The process of writing memory pages to swap area is called as paging out. These paging out & in requires CPU utilization and in the scenario where the paging in/out is high has a direct impact on CPU utilization and a time reaches when there is nouseful work is being done by CPU and it is fully engaged in memory management. The following formula is used to calculate the expected paging delay
Expected Paging Delay = (CPU service time/(1 – CPU Utilization in decimal)) * Paging Out Rate * Service Time for Swap device
Disk Utilization depends upon the access time, which is supplied by the Disk Vendor. Informix recommends to add an extra 20% of the access time to calculate the disk utilization. Because of different access time for different Hard Disks, the OS does not reports the disk utilization rather it gives the no. of page transfer per seconds. For an OS which can transfer 10pages per second and the disk access time is 30msec, The disk utilization is
U = Access time for a disk as specified by the vendor * 1.2 * No of Page Transfer per seconds
U = 0.03 * 1.2 * 10 = 0.36
Suppose for a transaction we the system has to do 20 disk scans then the estimated processing time for the transaction can be calculated as
P = 0.03 * 1.2 * 20 = 0.72
So the disk utilization will be
0.72 / ( 1 – 0.36) = 1.13sec
Factors that affects resource utilization
Hardware Resource
OS Configuration
Network issues & Traffic
Database configuration
Dbspace & Table configuration
Application code
Parameters affecting the CPU utilization
| NUM_CPU_VPS | This should be more than ‘1’ is MULTIPROCESSOR is 1. Ideally it should be the total no of physical CPU – 1. When SINGLE_CPU_VP is ‘1’ then NUM_CPU_VPS should always be ‘1’. If it is more than ‘1’ Infromix fails to initialize. |
| SINGLE_CPU_VP | Set ‘1’ if the m/c is of single CPU, otherwise ‘0’ |
| MULTIPROCESSOR | Set ‘1’ if the m/c has more than 1 processor otherwise ‘0’ |
| NOAGE | As the proess is getting older the CPU gives less priority to it. If set to ‘1’ this effect of aging will not appear and priority will be same throughout the life of the thread/process. |
| AFF_NPROCS | No of Physical CPUs to which Online binds the CPU VPs. Online binds CPUS VPS to Physical CPUs in a serial fashion starting from the Physical CPU No specified in AFF_SPROCS. |
| AFF_SPROC | specifies the Physical CPU number from which Online starts binding the CPU VPs. For eg. If the number of physical CPU is 4 and AFF_NPROCS=2, NUMCPUVPS=2,AFF_SPROC=3, then Online binds the CPU VPs to 3rd and 4th physical CPUs. We generally never binds I/O VPs so that these process can run on any physical CPU. |
| NUMAIOVPS | Number if IO VPs. If KAIO is not supported then it should be more than the no of disk. However if KAIO is supported then this should be 1 plus 2 AIO VPS for every buffered chunk file. |
| OPTCOMPIND | directs Online optimizer to choose an appropriate access method. When set to ‘0’ it always goes for existing index (nested-loop) join, when set to ‘1’ and the isolation level is repeatable read then it goes for nested-loop join. When set to ‘2’ (default) the optimizer selects a join method based on cost. |
| MAX_PDQPRIORITY | limit the available resources to %age of PDQ resources which a query can occupy. If set to ‘0’ no PDQ will occur, if set to ‘1’ then only the parallel scans for the fragmented table will occur, if set to 100 the whole resources can be used as PDQ or it can be any integer value between 1 and 100. |
| DS_MAX_QUERIES | limits the max. no of PDQ which can be run concurrently. |
| DS_MAX_SCANS | is the number of parallel scans which Online can start concurrently. |
| NETTYPE |
Parameters affecting the memory Utilization
| RESIDENT PORTION | |
| RESIDENT | when set to ‘1’ the resident portion is never paged out. |
| BUFFERS | The maximum number of shared memory buffer which Online user threads have available for Disk I/O. |
| LOCK | The maximum number of locks which the Online supports |
| LOGBUFF | The size of the each logical Log buffers in shared memory. |
| PHYSBUFF | The size of the each of the physical Log buffers in shared memory. |
| buffer_val = BUFFERS * page_size + Buffers * 254 lock_val = LOCK * 44 logbuff_val = LOGBUFF * 1024 * 3 physbuff_val = PHYSBUFF * 1024 * 2 total resident memory = (buffer_val + lock_val + logbuff_val + physbuff_val + 51200)/1024 in KB | |
| VIRTUAL PORTION | |
| SHMVIRTSIZE | The initial size of the virtual portion of the shared memory. |
| SHMADD | If required, Online adds the the shared memory segment by the size specied in SHMADD. |
| SHMTOTAL | The total shared memory limited to Informix Online. |
| shmvirtsize = fixed overhead + shared structure + (mncs * private structures) fixed overhead = global pool + shared pool after booting | |
| STACKSIZE | indicates the initial stake size of each thread. |
Configuration affecting I/O Activities
Chunk & Dbspace configuration
Placement & mirroring of critical data
Load balancing
Reduction of contention
Ease of backup & Restore
Placement of critical data
Place reserved pages, logical logs and physical logs in 3 different Dbspaces and place these Dbspaces in 3 different disk, which will not only enhance the performance but if these Dbspaces are mirrored, this scheme will reduce the chances of inavailability of Informix Online. If we mirror an read-intensify dbspace we will not face in performance setback however if we mirror a write-intensify dbspace like dbspace containing logical/physical log, we will face slight performance setback.
The parameters related to Disk space configuration are
ROOTNAME
ROOTPATH
ROOTOFFSET
ROOTSIZE
MIRRORNAME
MIRRORPATH
MIRRORPATH
| Logical Log Parameters | |
| LOGSIZE | Size in KB of each logical log file |
| LOGSMAX | Number of Logical Log files. |
| LOGBUFF | No of shared memory logical log buffers |
| Physical Log Parameters | |
| PHYDBS | The Dbspace name where physical Log files are residing |
| PHYSFILE | The number of Physical Log files. |
Parameters that affects background I/O
| Affecting Checkpoints | |||
| CKPTINTVL | specifies the time in seconds after which a checkpoint may occur. Online can skip a checkpoint if it founds that the data is physically consistent. The checkpoint may occur even when the physical log is 75% full. However a long checkpoint interval may increase the time needed for recovery in case of failure. | ||
| LOGSIZE | The size of each logical log in shared memory. | ||
| LOGFILES | Number of logical log files which the Informix Online will create during initialization. | ||
| LOGSMAX | Maximum Number of Logical Log files which an instance can support. | ||
| PHYFILE | specifies the size of the physical log files. | ||
| ONDBSPACEDOWN | |||
| Affecting Logging | |||
| LOGBUFF | Size of each logical log file in shared memory buffer | ||
| PHYSBUFF | |||
| LTXHWM | |||
| LTXEHWM | |||
| LBU_PRESERVE | |||
| Affecting Page Cleaning | |||
| CLEANERS | |||
| LRUS | |||
| LRU_MAX_DIRTY | |||
| LRU_MIN_DIRTY | |||
| RA_PAGES | |||
| RA_THRESHOLD | |||
| Affecting Backup & Recovery | |||
| TAPEBLK | |||
| LTAPEBLK | |||
| TAPEDEV | |||
| LTAPEDEV | |||
| TAPESIZE | |||
| LTAPESIZE | |||
| Factors affecting Fast Recovery | |||
| OFF_RECVRY_THERADS | |||
| ON_RECVRY_THREADS | |||
| Affecting Data Replication | |||
| DRINTERVAL | |||
| DRTIMEOUT | |||
| Affecting Auditing | |||
| ADTERR | |||
| ADTMODE | |||
For the latest version of the project estimation tool, please complete the contact form. I will get in touch with you within 48 to 72 hours, Monday to Friday.