This topic provides recommendations for setting up an Oracle database server to store the data warehouse schema for a large installation that can manage thousands of TrueSight Server Automation servers.

The data warehouse schema can become very large based on the number of TrueSight Server Automation servers in your TrueSight Server Automation - Data Warehouse installation and the number of jobs that you run. The following recommendations are for an environment that includes a reports data warehouse that is tens of gigabytes or larger in size, which is often the case with thousands of managed servers. The recommendations take future growth into consideration, accommodating a potential increase in the number of servers and jobs that need to be executed. 

These recommendations are not intended as the "requirements" for all installations. Before installing the product, BMC strongly recommends that you complete a full sizing exercise for the reports warehouse with assistance from BMC to determine specific installation requirements.

CPU

Due to the performance characteristics of data warehouses in general, and demands, such as long running queries, high volume inserts, and parallelization, 4 to 8 CPUs (at 2 GHZ per CPU) per server are recommended.

I/O bandwidth

Deploy all TrueSight Server Automation - Data Warehouse databases on a Storage Area Network (SAN) to ensure the adequate amount of I/O bandwidth that is required for the ETL activities. When deploying a SAN, you must deploy each component on the I/O path to guarantee the required throughput. 

The critical components to deploy are as follows:

  • Disk controllers
  • Disks
  • Host Bus Adapters (HBA)
  • Fiber channel connections
  • Fiber channel switches

In a typical two HBA configuration, each HBA can push 200 MB/s or 400 MB/s when multi-pathing. If you use faster HBAs, you can recalculate with projections from the vendor to obtain the maximum MB/s. If you deploy Oracle Real Application Clusters (RAC), this calculation increases by 50% in a two-node configuration due to the extra I/O path. As servers are added, additional I/O paths are created.

Automated Storage Management

Oracle Automated Storage Management (ASM) should be running on the server. ASM provides the speed of raw devices without the management overhead. Oracle stripes all data across each ASM device in 1 MB chunks and allows for the full I/O bandwidth of your storage infrastructure.

Memory

A minimum of 32 GB of memory is recommended on database servers that are used as reports data warehouses. This recommendation does not mean that TrueSight Server Automation - Data Warehouse requires 32 GB of dedicated memory, but that the database server on which the reports data warehouse is installed should have 32 GB of memory in total.

Reports data warehouse architecture

Use the following recommendations for the reports data warehouse architecture:

  • Redo: Separate redo logs onto separate file systems or ASM disk groups and deploy on RAID 1,0.
  • Data: Separate data from the redo activity in separate file systems or ASM disk groups and deploy data areas on RAID 1,0 or RAID 5. For information about the layout of data storage tablespaces, see Setting up the database, depending upon the database type.

Database memory parameters

The following memory parameters are recommended:

  • SGA: 12 GB
  • PGA: 6 GB

Oracle RAC

BMC recommends the following configuration for Oracle RAC:

  • Set up the public network, as follows:
    • Deploy a fully redundant public network with fail over (the redundancy technology depends on the operating system and vendor).
    • Leverage GigE on redundant switches with default Maximum Transmission Unit (MTU).
  • Set up the private network, as follows:
    • Deploy a fully redundant private network with failover (the redundancy technology depends on the operating system and vendor).
    • Leverage jumbo frames with GigE on redundant, isolated switches with an MTU of 9000 (actual MTU varies by vendor). This setting provides better improvements in the reports warehouse architecture due to the increase in Oracle block size (16, 32).
  • Set the Oracle parameter, parallel_execution_message_size to at least 8 K.
  • Segment the workload through Oracle 10g services on different nodes as needed (ETL, reports, RMAN).
  • Deploy ASM, as recommended earlier in this topic, to increase online redo log and cache fusion performance.
  • Deploy multiple HBAs with multi-pathing on a SAN.

Database parameter configuration

Use the following database parameter configuration:

  • The db_block_size parameter should be set to 16384 (16 K). 
    You need to configure this parameter when you create the database. Do not change the parameter after you create the database.
  • The db_file_multiblock_read_count parameter should be set to 32.
    You can change this parameter value at run time.

Database log file sizing

BMC recommends the following database log file and group sizes. To improve database performance, create new log files that meet the following recommendations and then delete the old log files:

  • The log file size should be 3221225472 (3 GB).
  • The number of log groups should be 10.

You can make these changes at run time by using database log file and group creation commands.

Oracle processes parameter

BMC recommends that the Oracle processes parameter for both the TrueSight Server Automation database and the Oracle database (that is used as the reports data warehouse) be set to 800 (instead of the default of 400) because of high process load.

The ETL execution spawns multiple Oracle processes and makes multiple connections to the reports data warehouse. The actual number of processes that are needed is based on the number of domains (such as Inventory and Audit) for which ETL is being run in parallel.

Upgrade Assistance

The BMC Assisted Migration Offering (AMIGO) program is designed to assist customers with the planning of product upgrades to a newer version – “Success through proper planning”.
Explore AMIGO Program ›
Was this page helpful? Yes No Submitting... Thank you