Re: Is a Multithreaded Ingres On the Horizon???

From: Graeme Sargent <graeme_at_pyra.co.uk>
Date: Wed, 11 Aug 1993 23:08:03 GMT
Message-ID: <1993Aug11.230803.13659_at_pyra.co.uk>


In <rule-100893175405_at_17.32.33.227> rule_at_apple.com (Jeff Rule) writes:

>In article <2496st$b3i_at_infochi.com>, rfinkel_at_infochi.com (Richard
>Finkelstein) wrote:
>>
>> Jeffrey Horn (horn_at_schaefer.math.wisc.edu) wrote:
>> : I have been reading a lot about Informix and Oracle developing database
>> : servers that will be able to have multiple threads of control within
>> : a single database server. From what I read, they will be able to
>> : break queries up so that they can be executed in parallel on symmetric
>> : multiprocessing computers and will also allow for distinct queries
>> : to be exectued in parallel.
 

>> : Maybe I missed it at Ingres World (there was so much else worth paying
>> : attention to), but does anyone know if Ingres has any plans along these
>> : lines?
 

>> : Jeff Horn
>> : horn_at_cs.wisc.edu
>>
>>Ingres 6 is already a mult-threaded architecture as is Sybase. Informix is
>>suppose to be working on a similar architecture though I have not heard of
>>any announced dates. Oracle claims that Oracle7 is multi-threaded but it
>>is not in the sense that the database server supports multiple threads.
>>Oracle7 will have a queuing architecture, once SQLNet 2.0 is released. At
>>this point, Oracle7 architecture is the same as 6.0.
>>
 

>I think you are missing the point here. They key phrase is "break queries
>up so that they can be executed in parallel on symmetric multiprocessing
>computers".

I think Jeff Horn is also confusing at least two different concepts which I would choose to label "multi-threaded server" and "Parallel Data Query (PDQ)".

Originally, multi-threaded servers were implemented (under UNIX) using a Single-Process Server architecture (as in early Ingres and Sybase). This was then extended to a primitive Multiprocess Server architecture where multiple multi-threaded servers shared the same database, but users/sessions were dedicated to a particular server (as in Ingres 6.4 and Sybase 4.9):

>Yes, INGRES is multi-threaded at the session level. Multiple Sessions can
>connect to a server and a single process will execute them. However, if 2
>sessions are connected to the same INGRES server that server will only ever
>execute one thread at any one time and the server will only execute on one
>CPU at a time. To qualify the "only one a time comment", an INGRES server
>will do a quantum of work for a thread and then switche to the next
>computable thread and do a quantum of work for it. In effect only one
>thread is ever executing at a time.
 

>If you have a machine with 2 or more CPUs and you start up 2 or more INGRES
>servers on the machine each server can be processing in parallel. However
>if one of the servers has 2 threads ready to compute and the others have
>none, the 2 threads will be processed in serial by that server only taking
>advantage of one of the CPUs. The other CPUs remain idle.
 

>NOTE: INGRES does have other process that operate asynchronously that will
>use the other CPU from time to time, but the two threads connected to the
>same server will never execute in parallel.

The alternative approach was to use single-threaded servers in a Process-Per-User architecture (as in Oracle 6.0 and Informix 5.0).

Oracle7 introduces the option of a more sophisticated Multiprocess Server architecture which is not multi-threaded but provides similar advantages without (potentially) the drawbacks that a multi-threaded architecture typically suffers on an efficient SMP host. As Rich points out they have misnamed this "Multi Threaded Server" and it requires SQL*Net 2.0.

Sybase System 10 offers a similar Multiprocess Server architecture where the servers are themselves (I assume) multi-threaded:

>Sybase has implemented (and is shipping) an architecture where two threads
>connected to a virtual server will be dispatched to individual server
>process (up to one per CPU on the machine). In this way multiple sessions
>can be processed in parallel from the same virtual server. In this
>configuration Sybase never lets a CPU sit idle if there are threads to be
>processed. This is an inprovement over the current INGRES aricetecture.
>However, a large query will never run faster then any single CPU can
>execute it. If your entire environment is to process one very large batch
>job, this approach will not benefit you. What it will help with is anytime
>you have more then one job that needs to be execute at the same time. The
>more CPUs you add the more threads you can execute in parallel.

Informix have been working on a similar Multi-threaded Multiprocess "Virtual" Server architecture (the Informix Dynamic Server) which we should see in Informix 6.0 later this (hopefully) year.

Informix were the first of the big four (I believe) to introduce parallelisation (yeuch!) with the Parallel Sort capability in OnLine 5.0. They have also been working on Parallel Index Build which I don't think made it into 5.01 so I hope to see it in 6.0. (I seem to remember that someone else has Parallel Index Build, but I can't remember who or when!).

Informix and Sequent have been working for some time on true PDQ, which combines Parallel Sort, Parallel Scan and Parallel Join. I expect to see the fruits of this in OnLine 6.0 on Sequent (currently in beta, I believe).

Sybase and NCR have also been working together in this area, presumably leveraging NCR's Teradata inheritance.

Oracle 7.1 is also expected to have some kind of PDQ capability:

>The new Oracle server (not yet shipping) goes one step further. I have not
>been privy to any of their current architecture, so this is purely
>speculation, but this is what I believe you will be seeing from them.
 

>What they are attempting to pull off is to break down a query/tread into
>small sub-tasks that are independent of each other and work on them
>separately. These sub-tasks can be dispatched to separate CPU's in
>parallel and then the results are brought back together to form the final
>result. The truly unique thing about this approach is that it operates in

                    ^^^^^^

Not unique, Jeff. Teradata have been doing it for yonks. Informix and Sybase are also at work in this area, but I don't know about Ingres.

>parallel on sub-tasks. Because this approach breaks a large query up into
>sub-tasks and these sub-tasks can operate on individual CPU's. The more
>CPU's you add the faster the query will run. This lets you scale a machine
>up with out adding a lot of cost, assuming the CPUs are very inexpensive
>(386/486 CPU's for example). This approach is fraught with may perils, so
>I don't hold out to much hope for it real soon, but it sure does look
>promising.
 

>As to what INGRES is working on, I have not heard them addressing this area
>at all. It appears they are in a wait and see mode on the technology. If
>they are working on it, they have not made much noise about it.
 

>-Jeff Rule/Apple Computer/rule_at_apple.com

graeme

--
Disclaimer:	The author's opinions are his own, and not necessarily
		those of Pyramid Technology Ltd. or Pyramid Technology Inc.
---------------------------------------------------------------------------
      -m------- Graeme Sargent                 Voice: +44 (0)252 373035
    ---mmm----- Senior Database Consultant     Fax  : +44 (0)252 373135
  -----mmmmm--- Pyramid Technology Ltd.        Telex: Tell who???
-------mmmmmmm- Farnborough, Hants  GU14 7PL   Email: graeme_at_pyra.co.uk
---------------------------------------------------------------------------
    We have the technology.  The tricky bit is learning how to use it.
Received on Thu Aug 12 1993 - 01:08:03 CEST

Original text of this message