This topic describes the requirements for the TrueSight Capacity Optimization database and the two database technologies. Refer to the following sections for details:
TrueSight Capacity Optimization supports two different database technologies:
You must validate these options for your environment by performing an appropriate sizing exercise. For more information, see Planning. The Running the database on a virtual machine section provides recommendations for using a virtualized server.
Oracle database requirements
The following software is required 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 Server: The following versions are currently supported:
|11g R2 and later|
1 - Oracle version 188.8.131.52.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 184.108.40.206.0.
- Use Oracle version 220.127.116.11.0 or 18.104.22.168.
- The Database Server supports both the Oracle 12c R1 multi-tenancy options: Container Database (CDB) and Pluggable Database (PDB).
- You must use Oracle 12c R1 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.
- SQL*PLUS and SQL*LDR do not work on Oracle client 11g R2 running on an AMD processor platform. To fix this issue, you need to deploy
after you install the Oracle Client.
This issue is not a TrueSight Capacity Optimization issue, but a known issue in the Oracle 11gR2 client.
BMC strongly recommends use of the following software for the TrueSight Capacity Optimization Database Server:
- 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 datasources.
- 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.
- Oracle Database version: Use Oracle database 11gR2 version 22.214.171.124.0 or later.
The schema uses global temporary tables that can reach considerable size.
Space allocation recommendations
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.
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 Capacity Optimization servers (that is, more than five ETL engines), you must increase the max sessions parameter to avoid saturation.
The default block size should be used.
Set a block size between 8 KB and 32 KB.
BMC recommends running the database in archive logging (ARCHIVELOG) mode and using Oracle best practices:
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.
|Auto space advisor||If you schedule this task, it is recommended that you schedule it for execution on a weekly basis.|
|Gather database statistics||If you schedule this task, it is recommended that you schedule it for execution after every 2 or 3 days.|
These recommendations are based on technical considerations. Refer to the latest Oracle documents for details about any licensing restrictions.
PostgreSQL database requirements
The following software is required for the TrueSight Capacity Optimization Database Server:
Operating system: Any 32- or 64-bit operating system supported by the PostgreSQL Database Server
PostgreSQL Database Server: Versions 9.6, 9.5, 9.4 and 9.3.6 are currently supported.
Install PostgreSQL version 9.4 or later for improved performance.
- The current compatibility offering for the PostgreSQL database supports large environments handling up to 80 million records per day.
- The EnterpriseDB PostgreSQL database is not supported.
|Schema and Tablespaces|
maintenance_work_mem = 512MB
effective_cache_size = 5GB
work_mem = 48MB
shared_buffers = 2GB
Note: For PostgreSQL 9.5, 9.4, and 9.3.6, add
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.