Informix Issues
A Unix sysadmin asked what Informix DBAs should monitor for performance, capacity planning and availability, listing the OS metrics he already tracks (CPU, load, memory, paging, swap, filesystem). After some joking replies ('users' being the top problem), Madison Pruet gave the substantive advice: first establish a baseline of what's normal, chiefly from 'onstat -p' (read cache percentage, ideally above 98%, and the ratio of sequential scans to total disk accesses, rising scans suggesting a missing index or stale statistics), plus 'onstat -g rea' for ready-queue threads exceeding the number of CPU VPs, indicating a CPU bottleneck. He suggested capturing onstat -p hourly into a database (using onstat -z to zero counters) and profiling over a month. Paul Watson added OS-side tips on watching de/sr paging columns and persistent top processes. No single 'fix' — it's an advice thread.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Greetings Everyone! I'm interested to hear from some Informix Experts (such as yourselves) as to what the top 5 or 10 major dilemas you face with managing Informix. Specifically, I'm interested in how and what you would monitor for performance, capacity (planning), and availability. Any help would be most greatly appreciated! Thanks for your time and have a great New Year's! Best regards, David
1 Users 2 No others David Vano wrote: > > Greetings Everyone! > > I'm interested to hear from some Informix Experts (such as yourselves) > as to what the top 5 or 10 major dilemas you face with managing > Informix. > > Specifically, I'm interested in how and what you would monitor for > performance, capacity (planning), and availability. > > Any help would be most greatly appreciated! > > Thanks for your time and have a great New Year's! > > Best regards, > David -- Paul Watson # Oninit Ltd # You are only young once Tel: +44 1436 672201 # but you can be immature Fax: +44 1436 678693 # for ever www.oninit.com #
In article <3A4E3BC0.30163E85@oninit.com>, Paul Watson <paul@oninit.com> writes >1 Users 2 People asking 'smart' questions 3 No others! >2 No others > > >David Vano wrote: >> >> Greetings Everyone! >> >> I'm interested to hear from some Informix Experts (such as yourselves) >> as to what the top 5 or 10 major dilemas you face with managing >> Informix. >> >> Specifically, I'm interested in how and what you would monitor for >> performance, capacity (planning), and availability. >> >> Any help would be most greatly appreciated! >> >> Thanks for your time and have a great New Year's! >> >> Best regards, >> David > -- Surfer! Send email to: surfer at nevis-view dot demon dot co dot uk "I can resist anything but temptation" - Oscar Wilde ;-)
Okay, I think we all agree that users are the chief cause of all system anomalies. My background is in Unix System Administration, and I've compiled this list for Unix Systems that I concentrate my monitoring efforts on: 1. CPU Utilization 2. CPU Load Average over 1 minute intervals (High load with low CPU Utilization = possible I/O bottleneck) 3. CPU User Time vs. CPU System Time 4. Filesystem Capacity 5. Kernel File Slots in Use 6. Kernel Process slots/Process table utilization 7. Free Memory 8. Number of Memory Pages swapped in from secondary memory 9. Memory paged out to disk. 10. Top processes 11. Total Swap used Would these same items apply to database management? What specifically in the database would clue you in to degradation? "Surfer!" wrote: > In article <3A4E3BC0.30163E85@oninit.com>, Paul Watson <paul@oninit.com> > writes > >1 Users > > 2 People asking 'smart' questions > > 3 No others! > > >2 No others > > > > > >David Vano wrote: > >> > >> Greetings Everyone! > >> > >> I'm interested to hear from some Informix Experts (such as yourselves) > >> as to what the top 5 or 10 major dilemas you face with managing > >> Informix. > >> > >> Specifically, I'm interested in how and what you would monitor for > >> performance, capacity (planning), and availability. > >> > >> Any help would be most greatly appreciated! > >> > >> Thanks for your time and have a great New Year's! > >> > >> Best regards, > >> David > > > > -- > Surfer! Send email to: surfer at > nevis-view dot > demon dot co dot uk > "I can resist anything but temptation" - Oscar Wilde ;-)
David,
First of all my congratulations on seeking information on what to monitor.
Most folks simply use the number of user complaints as their only monitoring
tool. If the users start complaining more than normally then somthing must be
wrong. ;-)
First of all, just as with the OS, you must determine what currently is normal
for your system. It might be that by tweeking some stuff, you can improve your
existing performance, but that is not your initial goal. Your initial goal
should be to develope a base-line on what is normal for your system as it
currently exists. I think that the most important thing is to start with the
informatin in 'onstat -p'. The two most important things from onstat -p is
probably the read cache percentage and the ratio of sequential scans to total
disk accesses. Generally, we like to see the read cache percentage above 98%
(it includes accesses to indexes and dictionary information as well as data
accesses). If the sequential scan starts to increase, then that means that you
might need to add an index of run update statistics.
Also, I would monitor 'onstat -g rea'. This is the number of threads in the
ready queue. If this tends to be greater than the number of cpuvps, then you
have a cpu bottleneck. Again, this might indicate the need of an index.
I would suggest building a database of the onstat -p information - maybe
capturing it once an hour. (You can zero out the statistics via 'onstat -z').
After that, you can put the data into a spreadsheet and develope a profile of
what is normal on your system. After you have done that for a while (maybe a
month), then you might want to publish your findings in this newsgroup for some
advice.
David Vano wrote:
> Okay, I think we all agree that users are the chief cause of all system
> anomalies.
> My background is in Unix System Administration, and I've compiled this list
> for
> Unix Systems that I concentrate my monitoring efforts on:
>
> 1. CPU Utilization
> 2. CPU Load Average over 1 minute intervals (High load with low CPU
> Utilization = possible I/O bottleneck)
> 3. CPU User Time vs. CPU System Time
> 4. Filesystem Capacity
> 5. Kernel File Slots in Use
> 6. Kernel Process slots/Process table utilization
> 7. Free Memory
> 8. Number of Memory Pages swapped in from secondary memory
> 9. Memory paged out to disk.
> 10. Top processes
> 11. Total Swap used
>
> Would these same items apply to database management? What specifically in
> the database
> would clue you in to degradation?
>
> "Surfer!" wrote:
>
> > In article <3A4E3BC0.30163E85@oninit.com>, Paul Watson <paul@oninit.com>
> > writes
> > >1 Users
> >
> > 2 People asking 'smart' questions
> >
> > 3 No others!
> >
> > >2 No others
> > >
> > >
> > >David Vano wrote:
> > >>
> > >> Greetings Everyone!
> > >>
> > >> I'm interested to hear from some Informix Experts (such as yourselves)
> > >> as to what the top 5 or 10 major dilemas you face with managing
> > >> Informix.
> > >>
> > >> Specifically, I'm interested in how and what you would monitor for
> > >> performance, capacity (planning), and availability.
> > >>
> > >> Any help would be most greatly appreciated!
> > >>
> > >> Thanks for your time and have a great New Year's!
> > >>
> > >> Best regards,
> > >> David
> > >
> >
> > --
> > Surfer! Send email to: surfer at
> > nevis-view dot
> > demon dot co dot uk
> > "I can resist anything but temptation" - Oscar Wilde ;-)
In the year of Our Lord Sun, 31 Dec 2000 04:09:31 GMT, Madison Pruet <mpruet@home.com> broke a vow of silence to utter: >First of all my congratulations on seeking information on what to monitor. >Most folks simply use the number of user complaints as their only monitoring >tool. If the users start complaining more than normally then somthing must be >wrong. ;-) Does this imply there's a better way?
David Vano wrote: > > Okay, I think we all agree that users are the chief cause of all system > anomalies. > My background is in Unix System Administration, and I've compiled this list > for > Unix Systems that I concentrate my monitoring efforts on: > > 1. CPU Utilization > 2. CPU Load Average over 1 minute intervals (High load with low CPU > Utilization = possible I/O bottleneck) > 3. CPU User Time vs. CPU System Time > 4. Filesystem Capacity > 5. Kernel File Slots in Use > 6. Kernel Process slots/Process table utilization > 7. Free Memory > 8. Number of Memory Pages swapped in from secondary memory > 9. Memory paged out to disk. I tend to look at the de/sr columns > 10. Top processes I change this to top ten processes that stay in the top 10, there are exceptions such as fsflush which I've seen take out an entire CPU permanently. > 11. Total Swap used > > Would these same items apply to database management? What specifically in > the database would clue you in to degradation? > > "Surfer!" wrote: > > > In article <3A4E3BC0.30163E85@oninit.com>, Paul Watson <paul@oninit.com> > > writes > > >1 Users > > > > 2 People asking 'smart' questions > > > > 3 No others! > > > > >2 No others > > > > > > > > >David Vano wrote: > > >> > > >> Greetings Everyone! > > >> > > >> I'm interested to hear from some Informix Experts (such as yourselves) > > >> as to what the top 5 or 10 major dilemas you face with managing > > >> Informix. > > >> > > >> Specifically, I'm interested in how and what you would monitor for > > >> performance, capacity (planning), and availability. > > >> > > >> Any help would be most greatly appreciated! > > >> > > >> Thanks for your time and have a great New Year's! > > >> > > >> Best regards, > > >> David > > > > > > > -- > > Surfer! Send email to: surfer at > > nevis-view dot > > demon dot co dot uk > > "I can resist anything but temptation" - Oscar Wilde ;-) -- Paul Watson # Oninit Ltd # You are only young once Tel: +44 1436 672201 # but you can be immature Fax: +44 1436 678693 # for ever www.oninit.com #