II_QSWITCH

From: bowes@bucket.its.unimelb.EDU.AU (Martin Bowes)
Subject: Re: FW: INGRES DBMS server justice

Hi Manmander,

    II_QSWITCH is an ingres environment variable. It is not a server variable
    available to be set inside cbf. Set it using:
        ingsetenv II_QSWITCH xxx

        where xxx is a time in milliseconds.

    What you wish to set it to is largely a site dependant feature. If you
    have 50+ users per server all clamouring for som activity then I'd suggest
    that setting quantum_size to 1000 is inappropriate and a much lower value
    is in order. The description of this variable and in particular how to set
    values less than 500 ms is very poor. I've always been instructed to set
    both values low and with luck one will work! It seems that all is not well
    in this area!

    Try setting the II_QSWITCH and quantum_size to 100 and see if that helps. 
    In this case you can start a new server up and simply set the old server 
    shut and allow its connections to die off gracefully. All connections to 
    the new server will then function with the new settings.

    Reading between the lines, I'll guess that you are concerned about
    performance and are considering attacking the servers first. I have two
    comments about that:
    1. server tuning is a black art. Tread very warily. Dont change a lot of
        parameters all at once. 
    2. Unless we are talking a major shortfall in say memory assigned to the 
        optimizer (which gives some very strident error messages) then I'd 
        suggest that the bulk of performance gains are not going to be made in
        the server.

    By far the majority of performance improvements are to be gained by tuning
    the databases themselves. By which I mean the appropriateness of the
    structure of any table, its keying given the majority of its usage and the
    nature (interactive/batch) of that usage. Secondary Indicies can be very
    usefull, they can also be bloody appalling in performance if the sec
    index(s) for a table are used only by a few processes. In such a case all
    other activity must carry the burden of the indexes and this can be very
    draining. Overflow chains need to be checked regularly, table maintance and
    keeping high quality statistics are essential tasks. To this end I suggest
    you monitor the queries being executed in your servers and commence by
    focusing on those queries that are the most commonly observed. This is a
    'Monte Carlo' approach, but I have found it very usefull in the vast
    majority of tuning exercises. A script to do this is very easy to write.
    
    After this check the applications, particularly the locking strategies. 
    A poor locking strategy in a highly concurrent environment can wreak
    havoc. You should also check for constant type conversions, these can be
    draining on your CPU resources. Regularly scan the errorlog for deadlocks,
    lock escallations and timeouts. 

    There are several non Ingres System resources you should monitor. Check
    for disk i/o bottlenecks. Check for excessive paging and swapping. Check
    for a CPU problems. 

    A lot of tuning is also a matter of making value judgements for the
    peculiarities of your site. ie What is important for you guys may not be
    important for anyone else. Your call.

    Best of luck,

    Martin Bowes


> At 8:33 AM +1100 11/25/98, Martin Bowes wrote:
> > [re: nasty query sucking a server dry]
> >Hi Andrej,
> >
> >    Have you altered the value of ingres environment variable II_QSWITCH as
> >    well as -quantum in your server config file.
> >
> >    I find setting II_QSWITCH=100 and -quantum 75 helpful.
> 
> There has traditionally been a good bit of mystery surrounding II_QSWITCH
> vs -quantum in 6.4.  Most of the Unix platforms I've experimented with
> only seem to pay attention to -quantum.  It may in fact be that VMS is
> one platform where you have to set both.  In any case, setting both is
> certainly worth trying before you give up on the whole quantum idea.
> 
> 
> Karl R. Schendel, Jr.
> K/B Computer Associates   schendel@kbcomputer.com
> Ingres and Unix Expertise
> 
> President, North American Ingres Users Association
> president@naiua.org
> Join the NAIUA and make your voice heard!
Ingres Q & A
Back to William's Home Page
Email William