×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

How Increase Database Performance

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.
Replies continue below

Recommended for you

RE: 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

RE: How Increase Database Performance

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!

RE: How Increase Database Performance

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

RE: How Increase Database Performance

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)

RE: How Increase Database Performance

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close