Database requirements

TrueSight Capacity Optimization supports Oracle and PostgreSQL databases. BMC recommends that you install the TrueSight Capacity Optimization components and the database on separate servers.

You must validate these databases for your environment by performing an appropriate sizing exercise. Use the following information to understand the requirements for these databases.

Oracle database requirements

This section explains the requirements for the TrueSight Capacity Optimization database server that uses an Oracle database:

  • Operating system: Any 64-bit operating system supported by the Oracle database.
  • Oracle Database versions: The following versions are currently supported:
VersionEditions
18c (18.1.0.0.0)
  • Standard Edition 2 (SE2)
  • Enterprise Edition (EE)
12c R2
(12.2.0.1)
  • Standard Edition 2 (SE2)
  • Enterprise Edition (EE)
12c R1
(12.11)
  • Standard Edition One (SE1)
  • Standard Edition (SE)
  • Enterprise Edition (EE)
  • Express Edition (XE) (11.2 only)
11g R2 and later
(11.2)

1 - Oracle version 12.1.0.2.0 does not work as expected (caused by Oracle bug 20508819). You can perform one of the following tasks as a workaround:

  • Add Oracle instance init parameter _optimizer_aggr_groupby_elim=false to use Oracle version 12.1.0.2.0.
  • Use Oracle version 12.1.0.1.0 or 12.2.0.1.

Notes

  • The Database Server supports both the multi-tenancy options for Oracle 12c R1 and later: Container Database (CDB) and Pluggable Database (PDB).
  • You must use Oracle 12c R1 and later with CentOS 7.x, Red Hat Enterprise Linux 7.x, and Oracle Enterprise Linux 7.x because Oracle 11g R2 is not supported on these operating systems.
  • The Oracle Advanced Compression (OAC) feature is not recommended for use with a TrueSight Capacity Optimization Database Server. Activating this feature can have a significant impact on performance in terms of ETL execution time and data warehouse (DWH) data processing time.
  • The Oracle Spatial that is installed on a TrueSight Capacity Optimization database instance affects the database CPU performance when using the truncate command on the temporary tables and drop tables. Therefore, BMC recommends avoiding the installation of Oracle Spatial on a TrueSight Capacity Optimization database instance. If you need Oracle Spatial for any other purposes, contact Oracle for the possible workarounds to improve the CPU performance.

Configuration requirements for Oracle database

Consider the following configuration requirements for Oracle database:

  • Character set: AL32UTF8 — As part of Internationalization support and Unicode standard alignment, use this character set to prevent issues related to wrong character encoding during the integration of heterogeneous data sources.
  • Oracle Partitioning: Install and enable Oracle Partitioning on the Database Server, especially for very large databases to ensure maximum performance.
  • Oracle Java Virtual Machine (JVM): Install and enable Oracle JVM on the Database Server.

Configuration recommendations: Review the following Oracle configuration recommendations. These recommendations are based on technical considerations. Refer to the latest Oracle documents for details about any licensing restrictions.

Configuration itemRecommendation
Schema

TrueSight Capacity Optimization database schema uses four tablespaces: two for capacity data and two for indexes. For better performance, BMC recommends that the two data tablespaces be allocated on a different physical device than the two index tablespaces.

The default schema owner user is BCO_OWN. This user must have permissions on the four tablespaces and also on a temporary tablespace. 

The schema uses global temporary tables that can reach considerable size.

Allocate a minimum of 3 GB for the temporary tablespace and 3 GB for the UNDO tablespace.

Memory Management

Use automatic memory management. Configure only the limit, and Oracle will manage the SGA and PGA.

Set the memory limit at 80 percent of the total memory.

Listener configuration

By default, the Oracle instance sets the listener port to 1521. You can also set a different port according to Oracle guidelines.

Set the max sessions parameter to 300. This is the minimum requirement.

For very large deployments involving many Application Servers and ETL engines, you must increase the max sessions parameter to avoid saturation.

Block size

The default block size should be used. BMC recommends you to set a block size between 8 KB and 32 KB.

Archive logging

BMC recommends running the database in archive logging (ARCHIVELOG) mode and using Oracle best practices:

  • Perform periodic full backups (for example, weekly).
  • Perform incremental backups (for example, daily).
  • Perform continuous archive log backups (for example, every 6 hours).

The size and frequency needed for the archive logs depends linearly on the size of the TrueSight Capacity Optimization database in terms of daily population load. As a rough estimation, provide 2.5 GB/hour for every 10 million rows loaded per day. For example, provide 5 GB/hour for a TrueSight Capacity Optimization database sized for 20 million rows per day.

Archive logging allows you to do a full backup without shutting down TrueSight Capacity Optimization. Daily shutdown for backup can cause problems to application tasks and services: no task can be executed in the database maintenance window, so you would have to reconfigure OOTB tasks. If the database is shut down, TrueSight Capacity Optimization will log several exceptions and send the administrator an email, so you would also have to stop TrueSight Capacity Optimization components before turning off the database.

REDO logs

Allocate at least 4 GB for the REDO logs. You must allocate enough archive space to accommodate the REDO logs for the backup period. For example, if backup is taken every six hours, the archive space will need to be at least 24 GB.

Recommended number of groups and file size: Six groups of REDO logs, with redo file size of 512MB.

Auto space advisorIf you schedule this task, it is recommended that you schedule it for execution on a weekly basis.
Gather database statisticsIf you schedule this task, it is recommended that you schedule it for execution after every 2 or 3 days.

Oracle client requirements

Computers or VMs that host the Application Server or ETL Engine components must be running a supported Oracle client with the following packages, components, and configurations:

Oracle Client install options1

Oracle Client configurations

  • Oracle Net
  • Oracle Database Utilities
  • Oracle JDBC/THIN Interfaces
  • SQL*Plus
  • SQLLDR
  • Oracle Call Interfaces

1 These options are available only while performing a Custom installation of the Oracle Client.

  • Oracle Client 12.1.x and 12.2.x, 64-bit

  • Oracle Client 18.x, 64-bit

Note

  • TrueSight Capacity Optimization does not support the Oracle Instant Client.
  • 11.x Oracle clients are not supported. If you are using any of the 11.x Oracle clients, you must upgrade the client to 12.1 or later versions.

PostgreSQL database requirements

This section explains the requirements for the TrueSight Capacity Optimization database server that uses a PostgreSQL database:

  • Operating system: Any 32- or 64-bit operating system supported by the PostgreSQL Database Server

  • PostgreSQL Database versions: Versions 11.x, 10.x, 9.6, 9.5, 9.4 and 9.3.6 are currently supported. BMC recommends you to install PostgreSQL version 10.0 or later for improved performance.
    To migrate the data from PostgreSQL 9.x to 10.x or 11.x, see Migrating the data from PostgreSQL 9.x to 10.x and later.

    Note

    The current compatibility offering for the PostgreSQL database supports large environments handling up to 80 million records per day.

  • Configuration recommendations: Review the following PostgreSQL configuration recommendations.

Configuration itemRecommendation
Users, Roles
  • The first installation of TrueSight Capacity Optimization Application Server will automatically create the users and roles needed by the application. The installer will request the credentials for the PostgreSQL privileged user (usually named postgres). 
  • Default names are provided for users and roles but they can be customized during installation.
  • The default schema owner user is BCO_OWN.
Schema and Tablespaces
  • TrueSight Capacity Optimization database schema uses four tablespaces: two for capacity data and two for indexes. For better performance, BMC recommends that the two data tablespaces be allocated on a different physical device than the two index tablespaces.
  • Default names are provided for tablespaces (BCO_DATA, BCO_DATA_IDX, BCO, BCO_IDX) but they can be customized during installation.
  • Tablespaces are automatically created during installation of the first TrueSight Capacity Optimization Application Server. Before launching the installation, a proper directory structure must be prepared on Database Server to host tablespaces. 
    • For example, a parent directory like "/var/lib/pgsql/data" can be used to host tablespace files (or any other directory on the Database Server machine). Inside this parent folder, a folder for each tablespace must be created before launching the installation. By default the structure could be as follows (the names are case sensitive)
      • /var/lib/pgsql/data
        • /BCO_DATA
        • /BCO_DATA_IDX
        • /BCO
        • /BCO_IDX
    • The folders created above must be accessible and owned by the OS user that runs the PostgreSQL process. The directories must be empty. The Capacity Optimization installer will request the location of the above tablespaces folder.
Client Authentication
  • Make sure all TrueSight Capacity Optimization server machines (excluding Remote ETL Servers) can access the Database Server by allowing access in pg_hba.conf
  • Recommended configuration in pg_hba.conf is to use MD5 encryption for passwords.
Instance parameters

BMC recommends adding the following parameters to the configuration of the Database Server in postgresql.conf or equivalent file:

listen_addresses = '*'
max_connections = 300
default_statistics_target = 1000
constraint_exclusion = on
wal_buffers = 16MB
min_wal_size = 4GB
max_wal_size = 8GB
random_page_cost = 1.1
effective_io_concurrency = 200
autovacuum_vacuum_scale_factor = 0.01
autovacuum_analyze_scale_factor = 0.05
autovacuum_vacuum_cost_limit = 2000

checkpoint_timeout = 15min
checkpoint_completion_target = 0.9
log_min_messages = fatal
log_min_error_statement = fatal

#following parameters should be tuned according to actual memory available to Database server machine
#following and example of configuration for 16GB RAM server

maintenance_work_mem = 1512MB
effective_cache_size = 9GB
work_mem = 6MB
shared_buffers = 3GB

#for PG versions >= 10, max workers have to be configured. This is a sample configuration for a 16 CPU configuration
max_worker_processes = 16
max_parallel_workers_per_gather = 8
max_parallel_workers = 16

Note: For PostgreSQL 9.4 and earlier, add checkpoint_segments = 32 parameter instead of min_wal_size = 4GB and max_wal_size = 8GB parameters.

Some recommendations for improving the PostgreSQL database performance:

  • Use PGTune to configure the PostgreSQL database. For more information, see  PGTune .
  • Ensure that the database is managed by official PostgreSQL database administrators.

For information about deploying TrueSight Capacity Optimization in a PostgreSQL environment, see  Deploying TrueSight Capacity Optimization in a PostgreSQL environment .

For information about the backup recommendations for a PostgreSQL database, see the  PostgreSQL documentation.

Running the database on a virtual machine

The TrueSight Capacity Optimization database can be set up by using a virtual machine, instead of a physical machine.

  • Ensure that resources are available to the TrueSight Capacity Optimization database, similar to mission critical workloads hosted on virtual machines. The choice of hypervisor and its configuration determines the levels of contention with other virtual machines sharing the CPU, memory, and other resources.
  • For TrueSight Capacity Optimization, I/O throughput is a key sizing parameter, as explained in Sizing considerations. Configure the hypervisor layer to ensure that the required I/O throughput is available.
Was this page helpful? Yes No Submitting... Thank you

Comments

  1. Nacho Capdepon

    What are the recommendations for BACKUP for postgres DB??

    I can see the recommendation for Oracle. But if we use postgres????

    Cheers

    Oct 08, 2019 03:15
    1. Shweta Patil

      Thank you for your comment, Nacho. 

      After discussing with the SMEs, we have added an external link to the PostgreSQL documentation for the backup recommendations.

      They also suggested that customer database administrators should use third-party backup tools and procedures according to their requirements. 

      Aug 18, 2020 05:14
  2. Ajay Ranbhise

    We want below information to be added on above page:

    We don't support pluggable DB in TSCO as CO DB for Oracle.

    Devinder Lamba As discussed can you confirm,so this can be updated.

     

     

    May 22, 2020 05:49
    1. Shweta Patil

      Thanks, Ajay. Pluggable databases are supported in TSCO for Oracle 12c R1 and later versions. Closing the comment. 

      Confirmed with the SME. 

      Jun 01, 2020 11:11