Error 23 how can I prevent it?

[Edward McCouch]

Recently, I had a problem with Error 23; File table overflow on our Oracle Production Database server. After doing some research, I found that I had to increase the maxusers kernal parameter (which increased nfile and nproc) to "fix" the issue.

My questions are:
1. Is there a way that I can monitor the number of open files to prevent this from happening again?

2. How do I determine what is a good "value" for maxusers?

System in question: V2500; HP-UX 11.00; Oracle 8.0.6.
Currently I have maxusers set to 640. It was set to 512.

I am new to the HP-UX admin scene and only took the 1st 2 Admin classes, so any help is greatly appreciated.

Thanx in Advance
-Ed

[harry d brown jr]

Did the new value of "maxusers" or (nproc/nfile) fix the issue?

[Edward McCouch]

Well, we haven't had any problems, but it has only been 5 days. I am not sure if it has fixxed the problem or not. I really dont want to find out on friday at noon that it didn't fix the problem. I am having trouble finding / getting into some of the other forum posts that I have I found on this issue also.

[harry d brown jr]

To "watch" kernel parameters, you should use glanceplus. If you still encounter the problem on Friday, then jack the maxusers up to 1024.

[A. Clay Stephenson]

Hi:

Actually all you need to is sar -v 2 2 and look at the ov (overflow columns). The other thing to consider is that maxusers is not a real tunable, it merely is used in formulae which are used to tune other parameters. On database servers, I typically remove the formulae and set nfile directly.

Regards, Clay

[James R. Ferguson · Accepted solution]

Hi Edward:

One of the easiest ways to monitor 'nfile' and 'nproc' is with 'sar -v 5 20'.

This will report the kernel parameter ceiling, the current value and the number of overflows. In the example I chose, you would get 20-samples, one every 5-seconds. Take a look at the 'sar' man page for more details.

Regards!

...JRF...

[Sanjay_6]

Hi Edward,

I would advise you to set your Kernel parameters to the pretuned kernel parameter set for General OLTP / Database Server System.

To do this,
Sam --> Kernel Configuration --> Configurable Kernel Parameters --> Selct Action from the top bar --> Apply tuned kernel parameters set --> Select General OLTP / Database Server System --> Rebuild Kernel and reboot.

This normally tunes the kernel for the RDBMS like oracle and under most circumstances it should not give you any problem, until a particular application needs a higher kernel parameter ( i know, because one of my application does.)

Hope this helps.

thanks

[Edward McCouch]

Thank you all for your quick responses.

Right now sar is telling me:
14:33:22 text-sz ov proc-sz ov inod-sz ov file-sz ov
14:33:24 N/A N/A 370/5140 0 7121/21228 0 73826/91099 0
14:33:26 N/A N/A 370/5140 0 7186/21228 0 73827/91099 0
14:33:28 N/A N/A 370/5140 0 7259/21228 0 73827/91099 0
14:33:30 N/A N/A 370/5140 0 7307/21228 0 73827/91099 0
14:33:32 N/A N/A 370/5140 0 7374/21228 0 73827/91099 0
14:33:34 N/A N/A 370/5140 0 7439/21228 0 73827/91099 0
14:33:36 N/A N/A 370/5140 0 7482/21228 0 73827/91099 0
14:33:38 N/A N/A 370/5140 0 7507/21228 0 73829/91099 0
14:33:40 N/A N/A 370/5140 0 7475/21228 0 73829/91099 0
14:33:42 N/A N/A 370/5140 0 7383/21228 0 73829/91099 0
14:33:44 N/A N/A 370/5140 0 7411/21228 0 73829/91099 0
14:33:46 N/A N/A 370/5140 0 7429/21228 0 73829/91099 0
14:33:48 N/A N/A 372/5140 0 7443/21228 0 73832/91099 0

It looks like the values for my nproc and ninode are fine if a little high, but the value for the nfile needs to be monitored during the heavy load hours. Am I correct in this reading? I read the man page on sar and matched the values, I just want to make sure that I am doing this correctly.

Unfortunately I am a little nervous in altering the kernal too much (newbie anxiety?) and I really dont have enough experience to comfortably remove parameters or let SAM choose the parameters for me.

thank you again for the help.
-Ed

[Bill Hassell]

Edward writes:

Right now sar is telling me:
14:33:22 text-sz ov proc-sz ov inod-sz ov file-sz ov
14:33:24 N/A N/A 370/5140 0 7121/21228 0 73826/91099 0
14:33:26 N/A N/A 370/5140 0 7186/21228 0 73827/91099 0

Wow! Your parameters are fairly skewed: nproc and nfile are way too high and nfile is pretty scary at 74,000 files and only 370 processes (ie, almost 200 files for every process!)

If this is the busiest time, nproc should be manually dropped to between 600 to 1000. And ninode is way out of whack because you are letting SAM use the formula. ninode is used for HFS filesystems only so it has significantly less use than in the past. You can drop it to 4000 or even less.

I know, everyone will point out that sar is reporting 7,186 entries but ninode is an incore cache and sar cannot determine what is used and what is old (and can be reused).

Note also that SAM's database template has a big mistake in it: timeslice is set to 1 rather than the default of 10. Always set timeslice to 10 unless you havew lots of free time to test and reboot several times.

[Edward McCouch]

Bill,

Now I am confused. Are you saying that the ninode value is too high and the nproc and nfile values are too low? I have no intention of using SAM to calculate the kernal based on a template- again I am not familiar enough with SAM to trust it completely. That sar is from a busy time, but tomorrow promises to be busier (payroll day). Is there something that I should be looking to fix in the extremely near future?

[Thierry Poels_1]

Hi Ed,

I strongly agree with Bill:

1. a nproc setting of 5140, and only needing +/- 340, is way to high. You should bring this down, but respect a reasonable safety margin.

2. the ninode parameters only applies to HFS filesystems. If you're running HP-UX 11, /stand is probably the only HFS filesystem, so ninode can be relatively small, even less than the suggested 4000.

3. a nfile parameter of 91099 is extremely high, but so is its usage of 73827. So you seem to need this high setting. But I really wonder what application is allocation all this file handles.

So my advise: monitor the usage of these parameters (sar -v) for a few days and decrease ninode, nproc (and maybe nfile) if possible. If they are set way too high you'll loose some performance, if nproc or nfile are set too low you're in trouble, so be carefull ;)

good luck,
Thierry.

[Edward McCouch]

We are running SAP 3.1i

How do I go about figuring out the "why" the open files are so high? Is this typical of a SAP system?

*sigh*

Thank you all for your help and advice.

It looks like I need to convince my manager that I need to take a kernal tuning class. Darn. *grin*