How Increase Database Performance

Hi, here are a few tips that can speed up the server
They worked to me at least

1 Increase shrared memory as much as you can.
2 Take out as may processes you can from the server try to make it dedicated
3 Take out as much as you can from the rootdbs and move it to another dbspace
4 I know that this is stupid but make sure no one uses "into temp" without the "with no log", same thing for create temp table.
5 You may have on or more indexes corrupted, this tends to happen on ids 7.3
6 IMHO The windows version of informix is not like the unix version, based on the fact that the os is slower so it is the dbserver
7 try to gather as much info from onstat it may give you some hints about the problem
8 Another stupid thing, check the queries the may be badly written.
9 At all costs avoid long transactions
10 a DBA i know told me to do a commit each 5000 records, or less of course, long transactions drain memory and sometimes they trash the indexes.
11 run oncheck for the extents and the reserved pages

I don´t really know what else to tell you most of these tips worked for me some others didn´t try them if you can
and let me now if they worked for you

The tunning consist of three elements, Disk, Memory o CPU, you can see where the problem is using system commands and informix utilities (I think onstat -p is very useful).

it's recomendable start with disk (the most slow device), review spaces, extents in your tables, queues (onstat -g ioq), number of VP's AIO, review queries by optimizer (SET EXPLAIN), avoid sequential scan, configure appropiate number of cleaners and checkpoint times, review configuration of LRU's. LRU_MAX_DIRTY and LRU_MIN_DIRTY.

It's Very important to use an adecuate policy of UPDATE STATISTICS

once Disks I/O is OK, you must review memory resources, increase Shared memory until obtain 98% of cache read for OLTP databases

at last review configuration of CPU VP.

you must periodically watch over your system and obtain historical statistics for comparison purposes.

Good Luck!

Hi DRAZCO,

Want to know how stable is IDS server running on WINNT4.0 cluster server. I am running on winnt4.0 and plan to move to winnt4.0 cluster server. Do you have any problem when using NT4.0 Cluster server. How is informix licence count?

Tks /skkoh

Let me summarize exact config of CPU/MEM/DISK/NET

CPU you can use numberofcpus_on_yer_system-1 in onconfig NUMCPUVPS (ie one Informix CPU on 2-cpu NT system) or number of your CPUS on UNIX

MEM,CPU - read ~informix/release/*/*/ONLINE_?.??(.txt) to make MEM resident,enable misc performance enchancements, and !reduce! buffers and locks if informix takes more RAm than you have

DISK - CLEAN values in onconfig for NUMAIOVPS and RA_* -it is essential on most UNIX systems (not sure if it relates to NT)

soctcp protocol is more stable than ipcshm and faster than name/unix/any pipes, so use NETTYPE NET,x,y in onconfig and onsoctcp for connections (X- number of CPU's in system eg 2,Y-maximum users/cpus eg 64 for minimum setup)

Do you have a "cleaners" parameter in "onconfig" or whatever is on this Windows version of IDS? On my AIX box, changing number of cleaners from 1 to 6 respons on very difficult queries is drasticly improoved??? Who know...try this...
(Consult some book for Informix-windows like Informix Performance Tuning, PDF on Informix.com - free for downloading, for some number of cleaners.)
BY
DAMIR