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

In article <dshapiro-180893123726_at_dshapiro.apple.com> dshapiro_at_apple.com (David Shapiro) writes:
>In article <24qubr$bjr_at_infochi.com>, rfinkel_at_infochi.com (Richard
>Finkelstein) wrote:
>
>> Richard Finkelstein (rfinkel_at_infochi.com) wrote:
>> If there are companies who have successfully implemented
>> Virtual Server on an SMP box (i.e., deployed production applications) I
>> would be interested in hearing about them.
>>
>> -- Rich
>> --
>> Richard Finkelstein Voice: 312-549-8325
>> Performance Computing, Inc. Fax: 312-549-4824
>> Chicago, IL Mail: rfinkel_at_infochi.com
>
>We've been running what is now the 'virtual' server for two years now (both
>the 4.8 and 4.9.x versions have had this capability) under VMS, with few
>problems related to the SMP capabilites. There's no magic here, Sybase
>simply has put almost everything into global memory, instead of the process
>memory, and has set up the timeslicer to divy up any waiting process to any
>of the sql server engine subprocesses. You can set up a virtual server to
>span as many cpu's as available, which means there is one sql server
>process (a server 'engine') per cpu, and as long as you have more than one
>query that needs processing at any time, all sql server cpu processes will
>wake and work on their tasks. I believe only one processes can handle
>client network IO, so return results will go back to the primary sever

Engine 0 is the network engine.

>process. I've noticed though, some tasks, like index creation, and database
>consistency checks, will take advantage of all sever processes, so perhaps
>Sybase has managed to get parallel processing for these tasks. As far as
>implementation, it's as easy as setting up the server in general, except
>one configures - an sp_configure parameter - for n number of cpus.

The engines don't make use of multiple engines for these functions... there is nothing like that in 4.x of Sybase. What you're (probably) seeing is async I/O and the next I/O being handed off the a different engine.

>
>Possible differences with other architectures:
>- One cannot set up the server to have more engines than cpu's. This could

Sure you can! I can easily take a 4 cpu system and let Sybase spawn 10 engines. It's stupid as you indicate but Sybase doesn't restrict it.

>be good or bad. If you have more engines than cpus, the OS scheduler will
>obviously have to swap the processes, assuming all engines need cpu time.
>This means there will be queries stuck in the swapped out engine while the
>other engines work on their tasks. It seems that it would be better as
>Sybase has restricted it, since having the extra engine is only creating
>more work for the OS, and not allowing any more database througput. Other
>database vendors argue that the extra engines allow one to prioritize
>queries as the database scheduler sees them (whereby one sets the OS
>priority of the engines, so some engines get more cpu time than others.)
>This may work under architectures such as Ingres or Oracle, where I believe
>the clients can direct their queries to a specific engine, but this would
>only hinder Sybase's throughput because a low priority engine with a long
>running query would only hold locks longer and reduce througput. Plus, the
>clients cannot pick a specific engine to process their query (hence the
>'virtual' name?)
>
>- Not much parallel query execution. As mentioned above, I think some tasks
>are decomposed, but until sybase rolls in the stuff from the navigation
>server, which currently only runs on an ncr machine, full blown parallel
>query execution is a ways off. Ingres and Oracle may have some query
>decomposition in their servers. This also means that the speed at which
>each query thread completes will necessarily not be improved by the
>addition of server engines.
>
>- No affinity for each sql engine/cpu pair. Generally, SMP applications can
>take advantage of the hardware by keeping the process memory registers and
>pointers on one cpu by creating an affinity between an OS processes and a
>particular cpu, thereby reducing global memory and OS scheduler overhead.
>Sybase does not take advantage of this under VMS (though I think they do
>under the Sequent OS). I'm not sure about Oracle or Ingres, but I doubt
>they do this kind of optimization either.

Nope.. on ptx (sequent) if you affinity Sybase then ALL engines will stay on the SAME processor. This is due to engine 0 forking off all of the other engines. By convention, forked children inherit the same cpu as the parent, if affinitied (on ptx).

One thing that helps on ptx is 'soft affinity'. I typically initiate Sybase with O/S options to 'noage', 'noswap', 'nopff', 'nopreempt' the Sybase processes... this helps to keep the engines high on the process queues. Also, ptx (1.3 and above) will try to keep a process on the same cpu as much as possible (soft affinity) to attempt keeping the process with its cache (cpu cache). This does help... the process can migrate if necessary but tries not to. This is most helpful in a server-only system with no client processes running on the same box.

>
>--
>David Shapiro Internet: dshapiro_at_apple.com
>Apple Computer, Inc AppleLink: SHAPIRO.D
>[the usual disclaimers]

-- 
Michael Perry
Sr. Regional Tech. Support Specialist
Sequent Computer Systems, Inc.
map_at_sequent.com