How Increase Database Performance
How Increase Database Performance
(OP)
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.
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.
RE: How Increase Database Performance
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
RE: How Increase Database Performance
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!
RE: How Increase Database Performance
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
RE: How Increase Database Performance
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)
RE: How Increase Database Performance
(Consult some book for Informix-windows like Informix Performance Tuning, PDF on Informix.com - free for downloading, for some number of cleaners.)
BY
DAMIR