Re: Why more than one sqlexec process ?

>From: uunet!ix.de!aa (Andreas Ahrens) >Message-Id: <1992May12.082020.29359@ix.de> >Subject: Why more than one sqlexec process ? >Date: 12 May 92 08:20:20 GMT >X-Informix-List-Id: <news.1237> > >I'm a new Informix user (and my boss told me to be the DBA) and there's >one thing about about the database server I just don't understand. If two >different users on the same machine start the demo applications that come >with Informix-4GL (build with i4gl and INFORMIX-SE) there seem to be two >sqlexec processes running in the background. Correct. >I always thought there is only one database server process which handles >all the requests from different users and different application programs >for the same database. It depends on your database system. Sybase works like that. From what you say, Oracle works like that. Informix does not work like that unless you have a copy of Online/MT (Multithreaded), which is unlikely. It will be more generally available with Version 6.00 -- but will probably be restricted to larger machines. That, I hasten to add, is a private guess, not a company statement of position. >And it seems that the application program itself spawns this server process. Correct. >Isn't it possible to start only one background sqlexec process to which the >application programs send their requests ?? No. The application talks to the database engine down a pipe, and the engine talks to the application up a second pipe. Pipes can only be used between two processes with a common parent which set up the pipe (we are talking about nameless pipes, not FIFO files). >How do these two (or more) server processes manage consistency in changing >data in the database. Using locking schemes. There are two main locking schemes, and a number of minor variants, used in SE. A totally different locking scheme is used on OnLine. The main scheme used in SE is kernel locking. This is a supervisory (not mandatory) locking scheme provided by the Unix kernel, normally via the fcntl() system call -- though it is sometimes lockf, flock or locking which is used, though they are all for ancient machines, I believe. Basically, this allows a process to indicate that it has acquired a lock on a record, and any other process which bothers to check will be told that the record has a lock on it. Now, this works fine because only sqlexec processes are accessing the file (aren't they!), so they all check the locking in the same way. The major alternative which is used when a system does not provide kernel locking is CREATLOCK. Here, a .lok file is used to record where the file is locked. This is much less satisfactory as a method of locking, not least because it is very much slower. Also, if a process dies without releasing its locks, records can remain locked indefinitely. OnLine uses locks which it maintains in its shared memory. Access to the locks is controlled by latches; this gets complex so I'll stop there. >And isn't this a waste of processing time and memory having several >servers running on the same database? Yes and no, and more yes with SE than with OnLine. There are a different set of tradeoffs between the two ways of doing it. On the basis of benchmarking performance (e.g. TPC benchmarks), Informix is not too bad. Nevertheless, on a Unisys box (if I remember correctly), a TPC-A benchmark running with tied database engines achieved about 200 tps, but when they used an OLTP system (Tuxedo), they obtained 800 users on the same hardware. So, yes, at large loads, it is better to have fewer servers than there are users. At low loads, it doesn't really matter. >I wonder about this because I formerly worked with ORACLE and there was >only one server process running in the background. We all have our past difficulties, don't we? :-) >Thanks for any hint on this >Andreas Ahrens Since no one else seemed to be responding, Jonathan Leffler (johnl@obelix.informix.com) #include <disclaimer.h>