Free Oracle Tips   HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

Oracle Tips by Burleson

Test Setup:

Using the standard TPC-C protocol, the utilization of Oracle10g Real Application Clusters with both a solid-state array and a fiber channel attached SATA disk array was tested. Using a 16-warehouse configuration for the TPC-C schema produced a 2.4 gigabyte test database when utilizing the transaction-processing template database provided by the Oracle DBCA utility. The database was created with no sample schemas included. Identical databases where used on the RAID and SSD arrays. The entity-relationship diagram and table descriptions for the TPC-C benchmark are shown in Appendix A.

The systems utilized 2 dual AMD 244 processor 1.7 Ghz Opteron servers with a CPU cache size of 1MB running Red Hat Linux EL with kernel 2.4.21-27.ELsmp. Both servers have 2GB of memory. Each server has one dual ported Qlogic 2342 HBA installed. One port connected to the RamSan and one to the RAID array. The array is configured as a RAID 5 with a segment size of 64k (stripe width).  The stripe is across 5 disks in a 64 Gigabyte total filesystem size. Each disk is a SATA Maxtor MaxLine Plus II with a capacity of 250GB. The disks have a speed of 7200 RPM and an 8MB cache buffer. Server connection to the array is via 2-Gbit Fibre Channel HBA (QL 2342). The servers are connected to the test desktops via the web. There were four client test desktops of various configurations running Windows XP and Window 2000 utilizing the Benchmark Factory agent software to act as test machines controlled by the main Benchmark test director located on the mralaptop2 test machine. The system utilizes the Gigabit Nextreme NIC for the public network and Oracle VIP and the 82557x Pro Ethernet gigabit adapter for the RAC interconnect.  This configuration is shown in Figure 4.1.

The overall disk specifications are in Table 4.1.

The overall RamSan400 Specifications are in Table 4.2.

The database schema (Appendix A) was loaded using the Benchmark Factory tool from Quest software. A 16 warehouse configuration, that was approximately 2.4 gigabytes with indexes, required about 6 hours to build. The schema was then exported and subsequent reloads were performed using the export. The Benchmark Factory was also utilized to perform the TPC-C transaction runs and load generations from 10 to 600 users. The default weighting for transactions, which matches that specified in the TPC-C benchmarkspecification, was utilized. Figure 4.2 shows the main screen from the Benchmark Factory Application.

The use of the Benchmark Factory tool allowed multiple runs of the benchmark without having to perform programming. Essentially the steps performed were:

1.      Set up the number of warehouses required. This is determined empirically to achieve the proper database size.

2.      Build the database schema and load data

3.      Export the database to use for reloading. The build part of the tool requires several hours to build a database so this step is critical.

4.      Run the benchmarks

5.      Drop and Reload the database.

Table 4.3 shows the typical transaction weighting and user load for the tests accomplished. This indicates the approximate frequency at which the specific types of transactions where performed by the benchmark tool.

Due to network issues, configuration issues, and other items beyond control of the test team, over one hundred runs of the benchmark where done in order to complete the testing.

The above book excerpt is from:

Oracle RAC & Tuning with Solid State Disk

Expert Secrets for High Performance Clustered Grid Computing

ISBN 0-9761573-5-7

Donald K. Burleson & Mike Ault

http://www.rampant-books.com/book_2005_2_rac_ssd_tuning.htm