Re: Wanted MTS information

From: Bill Beaton <beatonb_at_cadvision.com>
Date: 1996/06/30
Message-ID: <4r6bcm$2q9q_at_elmo.cadvision.com>#1/1

In article <4qu33b$elq_at_instasrv.admin>,

        ccisaac_at_mtu.edu (Chuck Isaacson) writes:
>Everything so far has indicated that the multi threaded server is the way
>to go. Why then doesn't everyone use it and why isn't it the default?
>After all it saves memory and there are fewer processes resulting in less
>OS overhead. Is MTS a closely guarded secret?
>
>We run a mixed environment of forms, cobol, c and report writer. Does anyone
>know why we shouldn't be looking at MTS?
>
>Thanks, Chuck ccisaac_at_mtu.edu

MTS is a mixed blessing ... there are transaction mixes that will allow it to really crater a system.

In my primary database, (Solaris 2.4, Oracle 7.2.2.4), with a mix of activities ...

	50,000,000,000 rows selected a day by various queries
	3,000,000 inserts / day,
	14,000,000 updates
	2,500,000 deletes
	(all these numbers are averages for monthly activity), 

I find that MTS can only be used in a very limited sense. As most of the update activity is performed thru large volume batch jobs, they must use dedicated servers, or the MTS threads end up basically hanging in all of the small tasks. In addition, on the query activities, my early observations were that MTS was very beneficial, UNTIL large query activities started. Whenever a large number of large queries (i.e. returning 2 to 3 million rows) were simultaneously active, again the Servers would start ignoring the small jobs. Because our business is delivering data to a large and varied client base, the complaints from users running small queries that should have completed in seconds rather than hours were taken very seriously. We were using up to 20 dispatchers and 40 servers, yet our users would still appear to be locked.

Even opening a TAR with the Canadion support organization finally ended with the recommendation from ORACLE that we simply turn off MTS. Since then, we have had no "lockup" problems. The cost of the extra process memory is very small to us, as opposed to clients dropping our service and going to other data providers.

Bill Received on Sun Jun 30 1996 - 00:00:00 CEST