You've got 2 obvious problems here.

1) From your original post you are getting lock overflows. You said these
overflows were associated with checkpoints. Not so, the online log you sent
show the lock overflow occurring 2 minutes before the checkpoint. And it
wouldn't make any sense for the 2 to be associated anyway.
17:20:41 Lock table overflow - user id 6, session id 1363
17:22:18 Checkpoint Completed: duration was 0 seconds.
17:22:18 Checkpoint loguniq 1155, logpos 0xd11018

A lock table over flow is caused when some user attempts to execute some SQL
in a single transaction and that SQL requires more locks than are
configured. Not to hard to do since you only have 20000 locks. Example: a
user excutes a delete on a table which will delete 10,000 rows. The table
has 4 indexes on. A lock on each data row and each index item is 50,000
locks (row level locking assumed). You/ve exceeded your lock table config.
Up the number of locks. The overhead for increasing locks is minimal, only
the space they use in virtual shared memory which is several bytes per lock.
So configure lots of locks.

2) You have no where near enough BUFFERS allocated. Up your number of
BUFFERS to some reasonable number. I saw suggestions in this thread ranging
from 50,00 to 250,000. Pick a number somewhere in the middle and then keep
an eye on the %cached read and write in onstat -p. Recommendations are for
read to be over 95% and write over 85%. Tune buffers till you hit those
percentages.

Be carefull of one thing when you increase buffers. It can make your
checkpoints a lot longer and that really makes for angry users. So watch
your checkpoints. If they increase significantly you'll need to tune your
LRU parameters.

Regards,
Bill Dare





> -----Original Message-----
> From: Bill Hamilton [SMTP:bham@finsco.com]
> Sent: Thursday, August 21, 2003 10:58 AM
> To: John Carlson; informix-list@iiug.org
> Subject: Re: lock table overflow
>
> The customer's server has 1GB of ram.
> Unfortuately, it also is the web server, so IIS is running on it.
>
> I upped the buffers to 400 and the locks to 40000.
> The service reported:
> 08:47:27 Onconfig parameter RA_PAGES modified from 4 to 8.
> 08:47:27 Onconfig parameter RA_THRESHOLD modified from 2 to 4.
>
> I don't know how far I should push it.
> >
> > Only 200 BUFFERS seems a bit too few . . .. how much physical memory
> > is on your server?
> >
> > Any way to see what process is hitting the ovrlock condition??
> >
>
> sending to informix-list
sending to informix-list