RE: MTS for 5,000 All-at-Once Users?

From: <JApplewhite_at_austin.isd.tenet.edu>
Date: Sat, 15 May 2004 21:15:57 -0500
Message-ID: <OF35529FDA.48F6B0A8-ON86256E96.000A4BCE-86256E96.000C6FC9@austin.isd.tenet.edu>

Riyaj, Responses inline...
I guess, we need more details about the architecture. Is this a 3tier architecture?

JCA - No. It's Client/Server. 5,000 PCs will be running the Client executable and connecting via ODBC to the database.

You say "Upload", does it mean some kind of file upload takes place and another process processes the files?

JCA - The teachers use a Client app to enter all their attendance data into a local file on their PC. When they finish, they hit the Upload button (or something like that) and the Client app connects to the database and does all the Inserts/Updates (I'm not sure which - I need to trace that process to find out.)

Assuming 2tier connections without any connection pooling, MTS might be a way to go. Process creation/death for 5000 connections in 20 minutes might be suboptimal with dedicated server approach.

30 students/5000 users approximately turns to 150000 DML statements within 20 minutes. Approximately 7500 DML statements/minute. I would venture to guess that 4 CPUs_at_3GHz should be able to handle that. You might want to reduce the work during that 20 minutes with variety of approaches such as:

a) preinserting data and updating only the students who are absent 
b) bulk DML statements
c)With increased concurrency on the objects
d)Avoiding log switches during that 20 minutes 
e) Use of bind variables 
f) avoiding space management calls etc.

JCA - Can't do a, b, or e, since this is a COTS app and we can't alter the code. However, d and f are good suggestions. I can certainly make sure the redo logs are big enough and switched before the deluge and that the approximately 200 - 400 tables that will be hit have sufficient empty extents allocated. The rotten design of the app actually works to our advantage in this case. Each School has its own Attendance tables, so there will only be contention among a few dozen Teachers for each table set - not 5,000 Teachers hitting the same table.

JCA - Thanks and TIA for any other suggestions.

Jack C. Applewhite - Database Administrator Austin (Texas) Independent School District 512.414.9715 (wk)
512.935.5929 (pager)

May have come a long way, but we got a long way to go.

B.W. Stevenson Notice: If you use this email nefariously, you are scum - probably criminal scum. Beware. The opinions I express herein are NOT the official policy of AISD, the City of Austin, the State of Texas, or the U.S. Government; but they probably should be.

Riyaj Shamsudeen <rshamsud_at_jcpenney.com> Sent by: oracle-l-bounce_at_freelists.org
05/15/2004 03:42 PM
Please respond to oracle-l

        To:     oracle-l_at_freelists.org
        cc: 
        Subject:        RE: MTS for 5,000 All-at-Once Users?


Hi Jack
                 I guess, we need more details about the architecture. Is

this a
3tier architecture? If yes, how does your middle tier handles the DB connections ? You say "Upload", does it mean some kind of file upload takes place and another process processes the files ?

                 Assuming 2tier connections without any connection

pooling, MTS
might be a way to go. Process creation/death for 5000 connections in 20 minutes might be suboptimal with dedicated server approach.

                 30 students/5000 users approximately turns to 150000 DML

statements within 20 minutes.Approximately 7500 DML statements/minute. I would venture to guess that 4 CPUs_at_3GHz should be able to handle that. You might want to reduce the work during that 20 minutes with variety of approaches such as:a) preinserting data and updating only the students who are absent b) bulk DML statements, c)With increased concurrency on the objects d)Avoiding log switches during that 20 minutes e) Use of bind variables f) avoiding space management calls etc. HTH Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of JApplewhite_at_austin.isd.tenet.edu
Sent: Saturday, May 15, 2004 8:51 AM
To: oracle-l_at_freelists.org
Subject: MTS for 5,000 All-at-Once Users?

Oracle 9.2.0.4 on Linux.
Anyone have experience with using MTS for handling a flood of about 5,000 simultaneous users?
...

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html


-----------------------------------------------------------------

Received on Sat May 15 2004 - 21:13:21 CDT