TestOps logo
Try TestOps

Requirements and recommendations for the database

Database in production system

Database must be deployed as standalone solution outside of Allure TestOps deployment. The performance of the database deployed in a pod with PVC or running in a container has worse performance of the database in case of high load generated by the automated tests. Such deployment is acceptable for demo purposes or for PoC stage, but for the production system with automated test results upload, the database must be deployed as standalone cluster with the requirements described further in this article.

Database storage hardware requirements

Database must use SSD as storage HW. Preferably enterprise grade SSDs. Allure TestOps creates considerable workload towards the database when processing test results and querying the data and HDD as storage is not able to handle the workload. In case of HDD usage system performance will degrade with time (after 6-8 months of usage), and at some point in time database responses for the statistics will consume all available connections due to long response time and general system performance alongside with the user experience will suffer.

Issues with Allure TestOps performance running on HDD cannot be resolved by support.

PostgreSQL version requirements

Allure TestOps supports PostgreSQL database only version 15 or higher.

This means that we do not perform any tests during the development and QA stages with any PostgreSQL versions except mandatory one, hence we cannot guarantee Allure TestOps will be able to perform or start with PostgreSQL versions different from the mandatory one.

PostgreSQL parameters recommendations

Allure TestOps heavily uses the databases and objects storage during the processing of the test results of automated tests and generation of the widgets in the projects dashboards, thus there are some recommendations based on our experience when working with highly loaded Allure TestOps instances in our clients' environments.

random page cost

The setting of parameter random_page_cost considerably affects the performance of PostgreSQL database and depends on the type of storage disks used.

Possible values for this parameter:

  • For SSDs, set 1.5.

    For very fast SSDs, such as AWS gp2/gp3 with high IOPS, set 1.2.

    Do not use the default value of 4.0 for SSDs.

  • For HDDs, use the default value of 4.0.

    Avoid using HDDs as database storage whenever possible.

work mem

work_mem parameter depends on the RAM available for the database and max session allowed to the database.

New value of work_mem parameter will require restarting of the database.

Formula for calculating work_mem:

work_mem = (db_available_ram × 0.2) / (num_of_testops_service_instances × connection_pool_size_per_instance)

where:

  • db_available_ram — amount of RAM available to the database;
  • num_of_testops_service_instances — number of testops service instances;
  • connection_pool_size_per_instance — connection pool size per instance (default: 10).

work_mem calculation example

With the parameters:

  • 64 GB of RAM available to the database;
  • 10 replicas of testops service instances;
  • connection pool size is equal to 10,

the work_mem value is calculated as:

work_mem = 64 000 × 0.2 / 100 = 128 MB.

effective io concurrency

effective_io_concurrency is to be set to:

  • 64 in case of SSD;
  • 1 in case of HDD (the default value).

shared_buffers

We recommend to set shared_buffers to at least 50% of available RAM. For better performance, you can set it up to 75% of available RAM — however, make sure to monitor the state of RAM and adjust the parameter towards lower values (e.g., back to 50%) if there is a high competition for RAM resources.

AWS RDS

If you are using AWS RDS, make sure that IOPS is not limited.

For average/huge size customers, AWS RDS has at least 3 000 IOPS that is equal to 1 000 GB gp2 storage.

Creating the database

Allure TestOps uses a single database for both user management and storing all the tests related data.

  1. Sign in as a database administrator.

    # switch to the postgres user on the database server
sudo su postgres
# connect to the database server
psql

    You can use any other way to connect which is more comfortable for you.

  2. Create the database.

    You may consider changing at least the password for the testops user.

    In the example below, testops is both the database username and the database name.

    # create a new database user
CREATE USER testops with encrypted password 'PaSSw0rd';
# create the database
CREATE DATABASE testops TEMPLATE template0 ENCODING 'utf8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8';
# connect to the databse
# grant all privileges to the user testops on the testops database
GRANT ALL PRIVILEGES ON database testops to testops;
\c testops
# grant all privileges on the public schema
GRANT ALL PRIVILEGES ON SCHEMA public TO testops;
\q

Usage of alternative database schema

In the vast majority of the cases, settings described below are not needed, and could lead to unpredictable results and inability of support if performed incorrectly. Please do not change the default schema unless your organisation rules dictate otherwise.

By default Allure TestOps service uses the database schema public. In case of your organisation rules do not allow the usage of public scheme of the database, this behaviour can be overridden.

The database schema needs to be defined before the deployment and needs to remain the same during the system utilisation.

Using the alternative database schema

  1. Before deploying Allure TestOps, add a database schema as defined by your policies.
    • We'll use default as an example.
    • Owner must be the user which is used in TestOps database connection configuration.
    • Database user must have all permissions for the database.
    • Database user should have all the privileges for the created schema (GRANT ALL ON SCHEMA default TO testops;).
  2. Before deploying Allure TestOps, add the following parameter to its configuration for the testops service: ALLURE_DB_SCHEMA=default.

Update the values.yaml file:

  env:
    open:
      ALLURE_DB_SCHEMA: default
<...>

Update the docker-compose.yml file:

services:
  testops:
    <...>
    environment:
    <...>
      ALLURE_DB_SCHEMA: default
      <...>

Update the /opt/testops/conf/testops.conf file by adding the string:

ALLURE_DB_SCHEMA=default

Separating connection pools

This is only relevant for Kubernetes deployments.

By default, Allure TestOps uses a single connection pool for all types of database queries. Its size is determined by the datasources.mainDatasource.appMaxDBConnection setting.

In a high load production environment, you may want not just to increase the total number of connections, but to make sure that longer operations do not take the resources from the faster and more important ones. This can be done by enabling separate uploader pools and analytics pools. The analytics pool may also be configured to use an alternate datasource.

  • The uploader pool, if enabled, will be used when uploading new test results to Allure TestOps.

    To enable the uploader pool:

    1. Set datasources.uploaderDatasource.enabled to true.
    2. Set datasources.uploaderDatasource.appMaxDBConnection to the maximum number of uploader connections.

  • The analytics pool, if enabled, will be used for some of the read-only aggregated data queries. It is a common practice to use a separate database for analytics, with replication configured between the two databases.

    To enable the analytics pool:

    1. Set datasources.analyticsDatasource.enabled to true.
    2. Set datasources.analyticsDatasource.appMaxDBConnection to the maximum number of analytics connections.
    3. Set the PostgreSQL credentials in datasources.analyticsDatasource.* similarly to how you set the main database settings.
© Qameta Software Inc. All rights reserved.