Chapter 4. Database Sizing and Optimization
As discussed in Chapter 2, CloudForms Architecture CloudForms 4.6 uses a PostgreSQL 9.5 database as its VMDB back-end, and the performance of this database is critical to the overall smooth-running of the CloudForms installation. This section discusses techniques to ensure that the database is installed and configured correctly, and optimally tuned. The degree of customisation and type of tuning depends on whether CloudForms is running as a Virtual Machine external to the OpenShift Container Platform cluster, or podified within OpenShift Container Platform itself.
4.1. PostgreSQL Running in a Virtual Machine
The following sections decsribe the tuning that can be performed if CloudForms is running as a Virtual Machine external to the OpenShift Container Platform cluster.
4.1.1. Sizing the Database Appliance
The database server or appliance should be sized according to the anticipated scale of the CloudForms deployment. A minimum of 8 GBytes memory is recommended for most installations, but the required number of vCPUs varies with the number of worker processes accessing the database. An initial estimate can be made based on the anticipated idle load of the CFME appliances in the region.
Some earlier investigation into the database load generated by idle CFME appliances is published in Appendix A, Database Appliance CPU Count. To determine the number of CFME appliances required for a region, the total anticipated number of active or "running" nodes, pods and containers should first be established. Using the appliance to OCP object ratios suggested in Chapter 3, Region and Zones, the CFME appliance count can be estimated. For example an OpenShift Container Platform cluster containing 6000 active nodes, pods and containers would need approximately 2 CFME appliances in default configuration to handle a typical workload. If a WebUI zone is to be used, this should typically contain a further 2 CFME appliances.
It can be seen from the table in Appendix A, Database Appliance CPU Count that a region containing 4 idle CFME appliances would need a database server with 2 vCPUs to maintain CPU utilisation at under 20%. Adding the provider(s) and enabling the various server roles on the appliances will increase the database load, and so CPU, memory and I/O utilisation must be monitored. A CPU utilisation of 75-80% should be considered a maximum for the database server, and an indication that any of the following should be increased:
- Real memory
- vCPUs
- The value of shared_buffers (see below)
4.1.2. Configuring the Database Partition
The disk used for the database partition should be presented to the database appliance from the fastest available block storage. Sparsely allocated, thin provisioned or file-backed storage such as NFS are not recommended for optimum performance.
4.1.3. Installation
The first CFME appliance in a region can be configured as a database appliance with or without Rails. The database is created using appliance_console by selecting the following option:
5) Configure Database
After creating a new encryption key and selecting to create an internal database, the following question is asked:
Should this appliance run as a standalone database server? NOTE: * The CFME application will not be running. * This is required when using highly available database deployments. * CAUTION: This is not reversible.
Selecting 'Y' to this option configures the appliance without Rails or the evmserverd application, and allows the server to be configured optimally as a pure PostgreSQL server. This configuration also allows for PostgreSQL high availability to be configured at any time in the future using the following appliance_console option:
6) Configure Database Replication
Although configuring a CFME appliance as a dedicated database instance will result in optimum database performance, the absence of Rails and the evmserverd service and workers means that the server will not appear in the CloudForms WebUI as a CFME appliance in the region.
If it is known that PostgreSQL high availability will never be required (and at the expense of some loss of memory and CPU utilisation), the answer 'N' can be given to the question Should this appliance run as a standalone database server?. In this case the VMDB appliance will be configured to run Rails and the evmserverd service as normal, and will appear as a CloudForms appliance in the region.
It is recommended that this type of VMDB appliance be isolated in its own dedicated zone, and that any unnecessary server roles are disabled.
4.1.3.1. Configuring PostgreSQL
The PostgreSQL configuration file on a newly installed CFME 5.9 appliance is /var/opt/rh/rh-postgresql95/lib/pgsql/data/postgresql.conf. This file contains no CloudForms-specific settings, however it makes reference to a CloudForms/ManageIQ-specific include directory with the line:
include_dir = '/etc/manageiq/postgresql.conf.d'
All CloudForms/ManageIQ-specific customisations are read from files in the directory. The file /etc/manageiq/postgresql.conf.d/01_miq_overrides.conf contains the default CloudForms-specific PostgreSQL settings, however as this file is potentially overwritten on a yum update, any user-custom settings should be added to a new file in the include directory such as /etc/manageiq/postgresql.conf.d/02_user_overrides.conf.
CFME appliances upgraded from a version prior to 5.9 will retain all PostgreSQL configuration in the main /var/opt/rh/rh-postgresql95/lib/pgsql/data/postgresql.conf file as before. This will not make reference to the include directory, and any of the parameters changes described below should be made to this file.
4.1.3.1.2. Max Connections
Each worker process on a CFME appliance in a region opens a connection to the database. There are typically between 20 and 30 worker sessions per appliance in default configuration, and so a region with 20 CFME appliances will open approximately 500 connections. The default value for max_connections in the configuration file is as follows:
max_connections = 1000 # MIQ Value; #max_connections = 100
Although this default value allows for 1000 connections, under certain extreme load conditions CFME appliance workers can be killed but their sessions not terminated. In this situation the number of connections can rise above the expected value.
The number of open connections to the database can be seen using the following psql command:
SELECT datname,application_name,client_addr FROM pg_stat_activity;
The number of outbound database connections from a CFME appliance can be seen using the following bash command:
netstat -tp | grep postgres
It may be necessary to increase the value for max_connections if the default number is being exceeded.
4.1.3.1.3. Log Directory
By default the block device used for the database partition is used for the PGDATA directories and files, and also the postgresql.log log file (this is the text log file, not the database write-ahead log). Moving the log file to a separate partition allows the PGDATA block device to be used exclusively for database I/O, which can improve performance. The default value for log_directory in the configuration file is as follows:
#log_directory = 'pg_log' # directory where log files are written,
# can be absolute or relative to PGDATAThis value creates the log file as /var/opt/rh/rh-postgresql95/lib/pgsql/data/pg_log/postgresql.log. The following commands can be used to setup an alternative directory for the log file such as /var/log/pg_log.
mkdir -p /var/log/pg_log chown postgres:postgres /var/log/pg_log
Change the log_directory line in /var/opt/rh/rh-postgresql95/lib/pgsql/data/postgresql.conf:
log_directory = '/var/log/pg_log'
Restart PostgreSQL:
systemctl restart rh-postgresql95-postgresql.service
The file /etc/logrotate.d/miq_logs.conf should be edited to reflect the new PostgreSQL log directory so that logs will be rotated correctly:
/var/www/miq/vmdb/log/*.log /var/www/miq/vmdb/log/apache/*.log ⏎
/var/log/pg_log/*.log /var/log/tower/*.log {
daily
dateext
missingok
rotate 14
notifempty
compress
copytruncate
prerotate
source /etc/default/evm; /bin/sh ⏎
${APPLIANCE_SOURCE_DIRECTORY}/logrotate_free_space_check.sh $1
endscript
lastaction
/sbin/service httpd reload > /dev/null 2>&1 || true
endscript
}4.1.3.1.4. Huge Pages
For VMDB appliances configured as dedicated database instances, some performance gain can be achieved by creating sufficient kernel huge pages for PostgreSQL and the configured shared_buffers region. The following bash commands allocate 600 huge pages (1.2 GBytes):
sysctl -w vm.nr_hugepages=600 echo "vm.nr_hugepages=600" >> /etc/sysctl.d/rh-postgresql96.conf
The default setting for PostgreSQL 9.5 is to use huge pages if they are available, and so no further PostgreSQL configuration is necessary.
4.1.4. Maintaining Performance
Several of the database tables benefit greatly from regular vacuuming and frequent re-indexing, and database maintenance scripts can be added to cron to perform these functions.[9]
On a CFME 5.9.0 & 5.9.1 appliance these scripts can be installed using the following appliance_console option:
7) Configure Database Maintenance
The scripts perform hourly reindexing of the following tables:
- metrics_00 to metrics_23 (one per hour)
- miq_queue
- miq_workers
The scripts perform weekly or monthly vacuuming of the following tables:
- vms
- binary_blob_parts
- binary_blobs
- customization_specs
- firewall_rules
- hosts
- storages
- miq_schedules
- event_logs
- policy_events
- snapshots
- jobs
- networks
- miq_queue
- miq_request_tasks
- miq_workers
- miq_servers
- miq_searches
- miq_scsi_luns
- miq_scsi_targets
- storage_files
- taggings
- vim_performance_states
- event_streams
From CFME 5.9.2 onwards these scripts are automatically run by the appliance with the database maintenance role configured. No manual configuration from appliance_console is necessary.
4.1.5. Resizing the Database Directory After Installation
It is sometimes the case that a managed virtual infrastructure or cloud grows at a faster rate than anticipated. As a result the CloudForms database mount point may need expanding from its initial size to allow the database to grow further.
The database mount point /var/opt/rh/rh-postgresql95/lib/pgsql is a logical volume formatted as XFS. A new disk can be presented to the database appliance and added to LVM to allow the filesystem to grow.
Some virtual or cloud infrastructures don’t support the 'hot' adding of a new disk to a virtual machine that is powered on. It may be necessary to stop the evmserverd service on all CFME appliances in the region, and shut down the VMDB appliance before adding the new disk.
The following steps illustrate the procedure to add an additional 10 GBytes of storage (a new disk /dev/vdd) to the database mount point:
# label the new disk
parted /dev/vdd mklabel msdos
# partition the disk
parted /dev/vdd mkpart primary 2048s 100%
# create an LVM physical volume
pvcreate /dev/vdd1
Physical volume "/dev/vdd1" successfully created.
# add the new physical volume to the vg_pg volume group
vgextend vg_pg /dev/vdd1
Volume group "vg_pg" successfully extended
# determine the number of free extents in the volume group
vgdisplay vg_pg
--- Volume group ---
VG Name vg_pg
System ID
...
VG Size 19.99 GiB
PE Size 4.00 MiB
Total PE 5118
Alloc PE / Size 2559 / 10.00 GiB
Free PE / Size 2559 / 10.00 GiB
VG UUID IjKZmo-retr-qJ9f-WCdg-gzrc-jbl3-i52mUn
# extend the logical volume by the number of free extents
lvextend -l +2559 /dev/vg_pg/lv_pg
Size of logical volume vg_pg/lv_pg changed from 10.00 GiB ⏎
(2559 extents) to 19.99 GiB (5118 extents).
Logical volume vg_pg/lv_pg successfully resized.
# grow the filesystem to fill the logical volume
xfs_growfs /var/opt/rh/rh-postgresql95/lib/pgsql
meta-data=/dev/mapper/vg_pg-lv_pg isize=256 ...
= sectsz=512 ...
= crc=0 ...
data = bsize=4096 ...
= sunit=0 ...
naming =version 2 bsize=4096 ...
log =internal bsize=4096 ...
= sectsz=512 ...
realtime =none extsz=4096 ...
data blocks changed from 2620416 to 52408324.2. PostgreSQL Running in a Container
The following sections describe the tuning that can be performed if CloudForms is to run podified in OpenShift Container Platform.
4.2.1. Sizing and Configuring the Database Pod
The PostgreSQL pod should be sized according to the anticipated scale of the CloudForms deployment. The following install-time parameters can be overridden if required:
Table 4.1. Install-time PostgreSQL Parameters
| Parameter | Default Value | Description |
|---|---|---|
| POSTGRESQL_CPU_REQ | 500m | Minimum amount of CPU time the PostgreSQL container will need (expressed in millicores) |
| POSTGRESQL_MEM_REQ | 4Gi | Minimum amount of memory the PostgreSQL container will need |
| POSTGRESQL_MEM_LIMIT | 8Gi | Maximum amount of memory the PostgreSQL container can consume |
| DATABASE_VOLUME_CAPACITY | 15Gi | Volume space available for database |
| POSTGRESQL_MAX_CONNECTIONS | 1000 | PostgreSQL maximum number of database connections allowed |
| POSTGRESQL_SHARED_BUFFERS | 1GB | Amount of memory dedicated for PostgreSQL shared memory buffers |
For example if deploying CloudForms to manage larger OpenShift Container Platform installations it may be desirable to increase the size of database pod and the database volume, and this can be done using the following parameters for the oc new-app command:
oc new-app --template=cloudforms \ -p DATABASE_VOLUME_CAPACITY=100Gi,POSTGRESQL_MEM_LIMIT=12Gi,POSTGRESQL_SHARED_BUFFERS=3Gi
4.2.1.1. Sizing the Database Persistent Volume Before Installation
The disk used for the database persistent volume should be presented to the database appliance from the fastest available block storage. Sparsely allocated, thin provisioned or file-backed storage such as NFS are not recommended for optimum performance.
4.2.1.2. Configuring PostgreSQL
The configuration settings shared_buffers and max_connections described in Section 4.1.3.1, “Configuring PostgreSQL” are also relevant for the podified installation of PostgreSQL. Rather than directly editing the PostgreSQL configuration file however, these should either be defined as template parameters (see Table 4.1, “Install-time PostgreSQL Parameters”) when the application is installed, or changed after installation by editing the environment variables for the postgresql deployment (see Figure 4.1, “postgresql Deployment Environment Variables”).
Figure 4.1. postgresql Deployment Environment Variables
4.2.2. Resizing the Database Persistent Volume After Installation
It is sometimes the case that an OpenShift Container Platform cluster grows at a faster rate than anticipated. As a result the CloudForms database persistent volume may need expanding from its initial size to allow the database to grow further.
The database mount point /var/opt/rh/rh-postgresql95/lib/pgsql is a logical volume formatted as XFS. A new disk can be presented to the database appliance and added to LVM to allow the filesystem to grow.
Some virtual or cloud infrastructures don’t support the 'hot' adding of a new disk to a virtual machine that is powered on. It may be necessary to stop the evmserverd service on all CFME appliances in the region, and shut down the VMDB appliance before adding the new disk.
The following steps illustrate the procedure to add an additional 10 GBytes of storage (a new disk /dev/vdd) to the database mount point:
# label the new disk
parted /dev/vdd mklabel msdos
# partition the disk
parted /dev/vdd mkpart primary 2048s 100%
# create an LVM physical volume
pvcreate /dev/vdd1
Physical volume "/dev/vdd1" successfully created.
# add the new physical volume to the vg_pg volume group
vgextend vg_pg /dev/vdd1
Volume group "vg_pg" successfully extended
# determine the number of free extents in the volume group
vgdisplay vg_pg
--- Volume group ---
VG Name vg_pg
System ID
...
VG Size 19.99 GiB
PE Size 4.00 MiB
Total PE 5118
Alloc PE / Size 2559 / 10.00 GiB
Free PE / Size 2559 / 10.00 GiB
VG UUID IjKZmo-retr-qJ9f-WCdg-gzrc-jbl3-i52mUn
# extend the logical volume by the number of free extents
lvextend -l +2559 /dev/vg_pg/lv_pg
Size of logical volume vg_pg/lv_pg changed from 10.00 GiB ⏎
(2559 extents) to 19.99 GiB (5118 extents).
Logical volume vg_pg/lv_pg successfully resized.
# grow the filesystem to fill the logical volume
xfs_growfs /var/opt/rh/rh-postgresql95/lib/pgsql
meta-data=/dev/mapper/vg_pg-lv_pg isize=256 ...
= sectsz=512 ...
= crc=0 ...
data = bsize=4096 ...
= sunit=0 ...
naming =version 2 bsize=4096 ...
log =internal bsize=4096 ...
= sectsz=512 ...
realtime =none extsz=4096 ...
data blocks changed from 2620416 to 5240832
Where did the comment section go?
Red Hat's documentation publication system recently went through an upgrade to enable speedier, more mobile-friendly content. We decided to re-evaluate our commenting platform to ensure that it meets your expectations and serves as an optimal feedback mechanism. During this redesign, we invite your input on providing feedback on Red Hat documentation via the discussion platform.