Ingres quantum

At 02:29 PM 3/17/98 +0100, alessandro.forcina@dianos.it wrote:

>I have a simple question about how does a dbms manage its internal threads.
>I've been told that if I don't explicitly set the parameters "quantum" or
>"noquantum" in the rundbms.opt, by default the server will assign 1000 ms 
>to each of its own threads. Is it true ?

Essentially yes, presuming you're running 6.4, 1.2, or a non-OS-thread
platform with 2.0.  I forget if the default is 1000 or 500, I think it's
1000 though.

>My aim is to avoid a single heavy query to stall the dbms server (and the
>other users). Which is the relation between the dbms quantum slice and 
>the OS's one?

None.

What you want to do is reduce -quantum.  Depending on your job mix you might
set it as low as 75.  It doesn't seem to be too sensitive to the exact value,
just don't set it to something ridiculous like 10.

The OS sees 6.4/1.2 as one schedulable process.  So the entire server gets
an OS time slice (assuming it has enough work to do).  What the server
does internally is no concern of the OS's.  Internally, Ingres will try
to give each session "quantum" milliseconds, unless something happens to
make it context switch sooner (blocking for I/O or a lock).  The
default quantum of a second heavily favors big CPU-grinder queries, which
is why I always set quantum a lot lower in 6.4 and 1.2.

It might take several OS time slices to make up one Ingres session time
slice.  Or not.  Ingres has no real way of noticing, and it doesn't
really matter, since Ingres is doing its own time accounting for its
internal sessions.

Karl R. Schendel, Jr.
K/B Computer Associates   schendel@kbcomputer.com
Ingres and Unix Expertise





Under Ingres 6 in Solaris environments and SunOS/DBE environments, I
understand the following to be true:
II_QSWITCH is used to apply the default QUANTUM of the DBMS only when the
quantum of a session was set to a value less than 500. The logic being that
if you set the quantum to a value less than 500 and II_QSWITCH isn't set
then the DBMS would assign a quantum of 500 to the session, regardless of
the desired quatum that the session was requesting. ie) The default value
for the DBMS session quatum is 500 unless II_QSWITCH is set and then
II_QSWITCH would be the default QUANTUM. ie) In the past quantum's of less
than 500, would cause the DBMS to work very hard at just switching session
context's and never getting anything done. Once CPU's got over the 50Mhz
speed , quantum values less than 500 have been plausible options for DBMS
server tunning.

When to consider applying II_QSWITCH.
1) On DBMS's that require very fast response times.
2) Data entry DBMS's
3) On DBMS's that don't have to service query's that require large sorts.

When to consider applying a large QUANTUM:
1) Reporting DBMS's
2) Large batch jobs
3) On DBMS's that require very large SORT's query's.

I've seen some reports forcing their quantum's to values of 10000. Trying to
kill a session on a Ingres 6 DBMS with a value of 10000 could take up to 10
minutes, if at all.

The quantum and II_QSWITCH session relationship in a DBMS have a direct
dependency on the size of QEF sort memory. If the sort memory size in the
QEF of a DBMS is to large then II_QSWITCH and the quantum size of a session
have very little effect on the performance of session and context switching.
So if you want highly responsive response times, have a small sort memory
footprint in the QEF and set II_QSWITCH to a value suitable to your CPU
speed. A suck it and see approach is appropriate because only a DBA knows
the client base and the query profile of their Ingres Installations.
Therefore it's up to the DBA or Ingres Installation Manager to decide and
agree with their clients on suitable quatum values/response times, for each
database, ingres installation, etc

If you want large query's to finish 10-20% faster, or better depending on
you memory/processor subsystem configuration then use a large value for
II_QSWITCH or quantums.

General murphy laws of Ingres DBMS nature:
Highly analytical databases will tend to favour high QUANTUM sessions, in a
multiple DBMS configuration.
Data entry databases will tend to need to low value quatums and small QEF
sort memory footprints.
Financial implementations will tend to require BOTH. So it's up the DBA to
ensure that data entry financial clients connect to DBMS's with low quantums
with small QEF sort memory footprints and that financial client reports
connect to DBMS's with high quantums.

IngresII challenges the QUANTUM debate with a whole new threading mechanism.
However the same QEF memory dependency appears to be valid. ie) Setting the
QEF memory sort size to the maximum size that you DBMS will let you, will
surely cripple a DBMS to servicing large query's alone, while other sessions
on the same DBMS just hang waiting for the SORT to end. However the whole
idea of the quantum parameter is to assist these sessions form appearing to
be hanging in the DBMS.

I hope the observations described above assist in your DBMS performance
testing and I stress that there is no definitive answer or DBMS server value
that can be applied to every Ingres Installation in the world. Exceptions to
that rule (at the moment anyway) is always ensure your DBMS quatum is a
value greater than 1.

Chris Ocello
DBA
Ingres Q & A
Back to William's Home Page
Email William