| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: ORACLE ON NT SERVER vs. UNIX
In article <35BFA6A0.45694C4F_at_netexplorer.com>,
Paul Nguyen <pnguyen_at_netexplorer.com> wrote:
> Hello everyone,
>
> can anyone tell me if Oracle database run faster on Unix than it does on
> NT server ?
>
> Please help me out.
>
>
Hi,
Oracle on Unix or NT - A few points to take into consideration
6b) Capacity tests: How many users will Oracle7 on Windows NT support? This section is intended to give an orientation of how many users can be supported in one Windows NT server. The tests consisted in observing the evolution of the response time as the number of clients working with the database increases. The main difference with the endurance test is the kind of clients used to load the database. Here, the clients were light clients. These light clients execute a number of selects (most of which use indexes) and updates affecting a reduced number of database records. The selects have been grouped to simulate "logical" transactions (whose duration is measured). The tests were carried out only in one configuration ("Annex V. Capacity Tests Description" contains a detailed explanation of the tests carried out, the clients and the results obtained). Six different types of client were defined each one with a number of different "transactions" (up to a total of 31 different transactions). The different client types try to resemble the interaction of a typical client/server application (with the difference that a client/server application typical keeps the connection to the database open and simulated clients dropped the connection after 5 or 6 transactions and reconnected again). The number of users has been raised from 6 (1 of each type) to 150 (25 of each type). The amount of data in the data base remained constant through all the tests . For each transaction the duration has been recorded and the average value has been computed in each of the different scenarios to be able to compare them . Two parameters have been studied the relative and the absolute evolution of the duration between scenarios. The relative evolution of the duration shows that with 150 users the response time of approximately half of the transactions increases in more than 10 times from the reference value for that transaction. For 60 users, a bit less than half of the transactions increase their duration in more than 3 times. While these results may seem discouraging, a look to the actual duration values may change the opinion. With 6 users, 84% of the transactions were executed in less than 1 second and 87% in less than 2. When the number of users increased to 60, 65% of the transactions were still executed in less than 1 second and 77% in less than 2. For 90 concurrent users, the values are 58% of the transactions in less than 1 second and 68% in less than 2. Finally for 150 users, the values are 45% and 48% respectively. These absolute values can be considered an acceptable response time for a big number of applications. Therefore, it can be estimated that a database server running on a configuration similar to the one used for the tests (4 CPUs Pentium 200MHz, 256MB RAM, 2 x 4 GB + 2 x 9 GB of disk) could support between 60 and 100 concurrent users of an application whose interaction is similar to that simulated during the tests. The above figures are just an indication and they don't mean that for no matter which application that number of concurrent users could be reached in the mentioned configuration without a noticeable degradation of the response time. The number of users that can be supported depends of the type of application and the server hardware configuration. DI-STB can assist the DGs in determining the number of users of a given application that can be supported in a particular configuration.
7) Hardware requirements The hardware required for the server depends on the specific needs of each application and there are multiple possible configurations whose analysis is out of the scope of this document. Therefore, the discussion, here, will be focus on the suitability of the two configurations proposed in the document [snip]. In that document, two configurations are proposed: Configuration 1 (2 CPUs Pentium 200MHz, 256MB RAM, 2 x 4GB + n x 9GB disks) and Configuration 2 (4 CPUs Pentium 200MHz, 512MB RAM, 2 x 4 GB + n x 9 GB disks). These configurations don't correspond with the configurations used for most of the tests. The test in "Capacity tests: How many users will Oracle7 on Windows NT support?" have been done on configuration 1 modified with 2 additional CPUs. The server in that case was more powerful that a configuration 1 but less than a configuration 2. The test under "Endurance tests: Will Oracle7 on Windows NT bear the workload?" have shown that a configuration 2 is twice as powerful as the configuration used for the "Capacity tests". Therefore, the number of clients that can be supported with a configuration 1 server is estimated to be around 50-60. For a configuration 2 server, the number of concurrent users could probably be greater than 100. Summarising, both server configurations are suitable for departmental Oracle databases. If the applications to be supported will never reach a load equivalent to more than 50 of the simulated users, it is feasible to use a configuration 1 server. If the application load may be higher than that generated by 50 simulated users, it is better to use a configuration 2 server. It is worth noticing that if the application does an intensive use of the disks the controller may become a bottleneck. The PCI RAID controllers installed have several SCSI channels and, according to Digital, they support parallel transfers to and from the different channels they control. However, there is only one channel to the host, this channel may become a bottleneck. Therefore, the standard configuration may not be enough in some special cases. Then, additional disks controllers may be required to improve performance.
8) CONCLUSIONS There is a clear effort to adapt Oracle's architecture to the Windows NT particularities. However, there is a number of areas were the integration on Windows NT can be improved. The main areas to improve include: (1) Better integration with Windows NT facilities in the field of security (eg, user authentication by Windows NT, implementation of system roles which prevents access to a schema not authorised for the database user, automatic protection of registry entries). (2) DBA support (eg, integration/simplification of DBA tools, generation of a file structure similar to OFA, etc). (3) Better use of the registry. Despite the areas where integration with Windows NT could be improved, Oracle on Windows NT has demonstrated to be reliable and stable enough to allow its use for departmental applications. This is specially applicable to environments were the migration of Oracle databases to a Windows NT environment may reduce the number of different systems to support (eg, by getting rid of Unix servers).
ANNEX IV. ENDURANCE TESTS DESCRIPTION The objective of these tests were to find out whether Oracle7 on NT will support the load that heavy processes impose on it. A heavy process uses complex non-optimised queries that involve a big amount of data processed for retrieval, insert, update and delete. An outline of the tasks carried out by the heavy client used for the tests is shown in Figure 1. · Insert 1 row in one table (TE) · Insert 10 rows in a table (TD) · Insert 100 rows in a table (TC) · loop 1000 ¨ Select 1 row (TK) ¨ Insert 1 row (TA) ¨ loop variable number of times Þ Select 1 row (TH) Þ Insert 1 row (TB) · Update ~980 rows in one table (TA) · Update ~1890 rows in one table (TB) · Insert 100 rows in one table (TB) · Select using a non optimised query involving three tables (TB, TA, TK) · Select using a non optimised query involving two tables (TA, TK) · Delete all the inserted rows Figure 1- Outline of execution of the client used for the tests As it can be seen, the number of data to deal with depends on the number of clients used in the test since each client will insert the data specific to it. Thus, the test is not a good candidate for measuring the evolution of the response time. As an example, the contents of the data base are as follows after the
conclusion of the first loop (1000 times) for 99 clients. Table Rows TA 98901 TB 543917 TC 10100 TD 1010 TE 101TF 30 TG 10 TH 10 TI 9 TJ 12 TK 200 Figure 2 - Contents of the tables for 99 users The tests were carried out in the following configurations to judge the impact of different configuration parameters in the performance:
ECC1-D1
Computer Prioris ZX 6200 Processor 4 x Pentium Pro 200MHz RAM 512 MB OS Windows NT4 Disk space 1 logical disk (9GB) corresponding to one physical disk(mirrored with RAID 5)
MADRID-D1
Computer Olivetti Strada 7000 Processor 4 x Pentium Pro 200MHz RAM 256 MB OS Windows NT4 Disk space 1 logical disks corresponding to one striped partition over fourphysical disks
MADRID-D2
Computer Olivetti Strada 7000 Processor 4 x Pentium Pro 200MHz RAM 256 MB OS Windows NT4 Disk space 2 logical disks each one corresponding to one striped partitionover two physical disks
MADRID-D4
Computer Olivetti Strada 7000 Processor 4 x Pentium Pro 200MHz RAM 256 MB OS Windows NT4 Disk space 4 logical disks each one corresponding to one physical disks (4 GB + 4 GB + 9 GB + 9 GB) Unix Computer SunSparc Station Processor 1 Sparc RAM 128 MB OS Solaris 2.4 Disk space 4 physical disks (3 used for the database)1,2 GB + 3,2 GB
The tests consisted in executing different numbers of users (1, 10, 25, 50, 60, 75 and 90) in each of the different configurations. During the tests, the evolution of the response time was measured.
Figure 3 - Evolution of duration of client execution Figure 3 shows the evolution of the average duration of the execution time of the clients for each different configuration and for each different platform. The same data
are shown as numbers in Figure 4. Usr#\Conf. ECC1-D1 Madrid-D1 Madrid-D2 Madrid-D4 Unix 1 0:00:18 0:02:44 0:02:10 0:03:30 0:03:00 10 0:01:46 0:07:04 0:07:14 0:09:18 0:17:25 25 0:06:16 0:13:35 0:12:00 0:11:44 0:55:55 50 0:15:25 0:29:08 0:26:51 0:20:27 1:41:15 60 0:00:00 0:36:31 0:37:20 0:26:08 3:04:16 75 0:28:00 0:52:18 0:49:00 0:52:15 0:00:00 99 1:13:17 1:25:19 1:34:27 2:55:280:00:00 Figure 4 - Evolution of duration of client execution There are a few remarks to make about the values shown in the table: · The values 0:00:00 in the column Unix and ECC1-D1 indicate that these tests have not been carried out. In the case of Unix because of lack of resources. · The cells in 1 and 2 in column ECC1-D1 show atypical values because Windows NT stores disk pages in memory and even if the Oracle instance is restarted the pages are immediately available to Oracle and no disk access is necessary. The rest of the tests were carried out rebooting the computer. · The values reported for Unix and for Windows NT can not be compared because of the completely different architectures of the machines (processors, RAM, disks, …) and, therefore, no generalisation can be made from those figures. They are only shown to provide a point of reference for those who know that kind of workstation. The disk configuration (1 stripped partition over 4 different physical disks, 2 stripped partitions over 2 different physical disks or 4 different partitions each one on a different physical disk) doesn't seem to have big impact on the performance of the response time. However, the amount of available RAM has a great impact in the performance. The configuration ECC1-D1 (with double amount of RAM) offered a response time of approximately half the response time of the other Windows NT configurations (Madrid-D1, Madrid-D2 and Madrid-D4). However, we can see that for 99 users the numbers loose the 1:2 relationship and are more similar. This could mean that swapping started at that stage in all configurations and, then, the disk activity becomes the main determinant of the response time. The degradation in response time is not meaningful since the heavy clients resemble more batch processes than interactive clients. Additionally, increasing the number of clients increased the amount of data to deal with because of the way the clients are implemented (as explained above). Therefore, the performance degradation observed in these tests does not represent the perfor-mance degradation that may occur in an application by raising the number of users. While the response time has increased noticeably, the main conclusion to retain is that Oracle continued to behave correctly (without giving errors) while the workload increased provided that it had enough resources. Error messages were obtained for higher number of users because of lack of redolog space. When additional space was allocated to the redolog files the tests executed without problems. A closing remark concerning the simplification of administrative tasks gained on Windows NT. The test instance required a huge SGA to be created. In order to do so, modifications have to be made to the kernel in the Unix environment: first, to allow for more and bigger shared memory segments and, then, to increase the number of processes and semaphores. Once this was set up, the tests could be started only to make the machine crash because there wasn't enough swap space. On Windows NT, only the parameters of the init.ora needed to be set up in order to have the instance running.
ANNEX V. CAPACITY TESTS DESCRIPTION The capacity tests were intended to give an orientation of how many concurrent users can be supported in one Oracle instance running on Windows NT. The differences with the endurance test is the use of light clients to load the database and that the amount of data in the database remained more or less constant during the tests. These light clients execute a number of selects (most of which use indexes) and updates affecting a reduced number of database records. Six different types of clients have been defined. The selects in each client have been grouped to simulate "logical" transactions (whose duration is measured). The different client types try to resemble the interaction of a typical client/server application (with the difference that a client/server application typical keeps the connection to the database open and simulated clients dropped the connection after 5 or 6 transactions and reconnected again). Figure 5 details the transactions present in each type of client and which tables are accessed
during the transaction. Tables Client Transaction TA TB
TC TD TE TF TG TH TI TJ TK C1
cli_1_sel_1 x p x cli_1_sel_2 x
x p x x cli_1_sel_3
x p cli_1_sel_4 x x p cli_1_sel_5
x cli_1_sel_6 x p x
x cli_1_upd_1 x x p C2
cli_2_sel_1 x x p cli_2_sel_2
x x x p cli_2_sel_3 x p x x
cli_2_sel_4 x x p
cli_2_sel_5 x x p C3 cli_3_sel_1
x x p cli_3_sel_2
x x p cli_3_sel_3 x x
x x p cli_3_upd_1 x p C4 cli_4_sel_1 x
x p cli_4_sel_2 x x x p
cli_4_sel_3 x x p cli_4_sel_5 x p
x x cli_4_sel_6 x x
p cli_4_upd_1 x C5 cli_5_sel_1
x x x x p cli_5_sel_2 x
p x x cli_5_sel_3
x x p cli_5_sel_4 x x p cli_5_upd_1
x C6 cli_6_sel_1 x x p
cli_6_sel_2 x p x
cli_6_sel_3 x p cli_6_upd_1 x
p Figure 5 - Tables accessed by transactions of each client
type The tests consisted in observing the evolution of the response time as
the number of clients working with the database increases. Since the amount
of data in the database remains constant, the test is a good candidate for
measuring the evolution of the response time when the number of users
increases. The number of rows of each of the tables corresponds that shown in
Figure 2. Since differences between configurations have been evaluated during
the execution of the endurance tests, the tests were carried out only in the
configuration MADRID-D1. Different number of users of each type have been
used in different scenarios. Figure 6 shows the scenarios that have been used
and the number of user of each type. Scenarios S1, S2, S5 and S9 have been
used to check the independence between the transactions of the different
clients from those of client C1.The rest of the scenarios have been used to
assess the number of concurrent users that could be supported. Clients
Scenario C1 C2 C3 C4 C5 C6 Total S1
1 1 1 1 1 1 6 S2 5 1 1
1 1 1 10 S5 20 1 1 1 1 1
25 S9 40 1 1 1 1 1 45 S10 5
5 5 5 5 5 30 S11 10 10 10 10
10 10 60 S12 15 15 15 15 15 15 90
S14 25 25 25 25 25 25 150 Figure 6 - Different
test scenario used For each transaction the duration has been recorded and
the average value has been computed in each of the different scenarios to be
able to compare them . The average transaction response times in the
different scenario are shown in Figure 7. Average Response Time (in
milliseconds) Users 6 10 20 45 30 60 90
150 Scenario S01 S02 S05 S09 S10 S11 S12 S14
cli_1_sel_1 259,2 194,5 581,6 1249,5 411,0 954,1 1818,4 3111
cli_1_sel_2 13386,7 16338,3 65325,6 117652,0 35741,0 114540,9
198708,4 301842,8 cli_1_sel_3 37,5 34,9 73,3 46,9 48,0
74,1 139,3 213,9 cli_1_sel_4 35,0 36,0 57,6 65,8 37,6
75,3 333,7 564,2 cli_1_sel_5 7585,8 12379,4 51685,3 116178,4
26781,2 96947,1 174157,9 317944,1 cli_1_sel_6 262,5 160,1 353,5
368,4 281,2 1168,2 3826,3 7799,5 cli_1_upd_1 13,3 70,1 90,5
87,0 98,3 68,8 86,3 147,2 cli_2_sel_1 181,9 126,1
333,3 234,4 314,6 1475,7 4465,9 5507,1 cli_2_sel_2 37,5 45,6
60,0 54,4 48,5 54,8 122,4 286 cli_2_sel_3 13,1 13,3
85770,0 166605,6 11430,2 145035,2 208986,5 395297,4 cli_2_sel_4
60445,0 61693,3 64143,3 66310,0 111781,5 308543,3 377826,2
679605 cli_2_sel_5 99,4 96,7 153,3 120,0 114,6 136,2 204,1
564,8 cli_3_sel_1 126,7 122,5 191,9 155,4 183,5 204,9
252,3 690,5 cli_3_sel_2 24,3 27,8 94,8 39,7 29,5 43,1
100,6 122,2 cli_3_sel_3 1998,8 2051,7 2122,4 2181,4 2370,1
3136,6 3945,2 5360,8 cli_3_upd_1 313,9 361,1 431,4 394,1 433,8
495,8 768,6 2797 cli_4_sel_1 579,1 1023,3 1598,0 1252,5
993,1 1367,8 1560,8 7193,2 cli_4_sel_2 133,4 61,3 140,0 57,5
72,2 202,6 237,7 524,5 cli_4_sel_3 95,6 149,3 217,0
169,2 268,8 441,7 810,3 1725,1 cli_4_sel_5 71,9 144,0
45469,0 274,2 27740,2 113999,0 209636,1 430707,3 cli_4_sel_6
8092,5 13469,3 141,0 123710,8 94,9 100,3 1249,8 7840,3
cli_4_upd_1 89,1 90,7 296,0 104,2 120,6 102,9 156,1
188,6 cli_5_sel_1 30,0 28,4 47,3 43,3 36,8 114,6 183,1
166,8 cli_5_sel_2 23,6 16535,2 79010,9 166538,3 34247,9
127011,3 203255,6 372357,7 cli_5_sel_3 147,1 130,8 193,6 182,5
201,8 453,1 995,3 3885,6 cli_5_sel_4 465,4 494,0 751,8
611,7 565,3 1078,5 2235,8 2308,8 cli_5_upd_1 19,3 68,4 50,0
50,0 34,5 99,6 72,9 20,6 cli_6_sel_1 40,5 44,4
78,3 47,2 45,1 58,6 95,4 180,2 cli_6_sel_2 23,9 21,1
48,3 27,2 24,4 34,0 55,5 64,4 cli_6_sel_3 343,7
367,0 77,9 493,0 50,7 47,8 85,8 127,5 cli_6_upd_1 49,8
37,0 538,3 45,7 453,6 589,8 743,5 2622,3 Figure 7 - Average
transaction duration per scenario Two parameters have been studied the
relative and the absolute evolution of the duration between scenarios. The
relative evolution of the duration have been calculated taking the results of
scenario S1 (6 users, one of each kind) as reference value. Figure 8 has been
calculated from the data in Figure 7 and shows for all scenarios the
percentage of transactions whose duration is less than n times the reference
duration. Users Duration 10 20 45 30 60 90
150 <2 times 87% 55% 68% 74% 42% 19% 10% <3 times
90% 71% 74% 81% 52% 35% 19% <10 times 97% 87%
87% 90% 84% 74% 52% Figure 8 - Percentage of transactions
whose duration is less than n times the reference duration per scenario The
relative evolution of the duration shows that with 150 users the response
time of approximately half of the transactions increases in more than 10
times from the reference value for that transaction. For 60 users, a bit less
than half of the transactions (48%) increase their duration in more than 3
times. While these results may seem discouraging, a look to the actual
duration values may lead a more positive opinion. Figure 9 and show for each
scenario the percentage of transactions whose duration is less than 1 and 2
seconds respectively.
Figure 9 - Percentage of transactions whose duration is less than 1 second per scenario
Figure 10 - Percentage of transactions whose duration is less than 2 seconds per scenario With 6 users, 84% of the transactions were executed in less than 1 second and 87% in less than 2. When the number of users increased to 60, 65% of the transactions were still executed in less than 1 second and 77% in less than 2. For 90 concurrent users, the values are 58% of the transactions in less than 1 second and 68% in less than 2. Finally for 150 users, the values are 45% and 48% respectively. These absolute values can be considered an acceptable response time for a big number of applications. Therefore, it can be estimated that a database server running on a configuration similar to the one used for the tests could support between 60 and 100 concurrent users of an application whose interaction is similar to that simulated during the tests. The above figures are just an indication and they don't mean that for no matter which application that number of concurrent users could be reached in the mentioned configuration without a noticeable degradation of the response time. The number of users that can be supported depends of the type of application and the server hardware configuration. DI-STB can assist the DGs in determining the number of users of a given application that can be supported in a particular configuration.
HTH
--
Oliver Willandsen
European Commission
http://europa.eu.int
All remarks are my own and do not necessarily
reflect official European Commission policy
-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Thu Jul 30 1998 - 02:36:13 CDT
 |
 |