How Increase Database Performance

[DRAZCO]

Hi,
Want to know how to/what steps to do to increase my database performance. My users always complain the system is slow. I think my database have problem cause the CPU of the server goes to 100% when the client's applications consult it . My server using Windows NT Server using IDS 7.31 version with 512MB RAM and Microsoft Cluster.

 

[FluxAnomaly]

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

 

[Gimbert]

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!

 

[skkoh]

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

 

[Guest_imported]

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)

 

[damir]

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