AWS - Aurora PostgreSQL

note

Aurora PostgreSQL is currently in closed beta.

Aurora PostgreSQL Agent setup

DBtune offers a database monitoring agent that operates within your network, securely transmitting metric data to DBtune and applying new proposed configurations. Additionally, the agent is available as a ready-to-use Docker image hosted on public repositories.

Required Permissions

Before starting the DBtune agent, you need to make sure that the following prerequisites are met:

• Create DBtune user in Aurora PostgreSQL

CREATE USER dbtune WITH PASSWORD '{dbtune_password}';

• Grant the necessary permissions to the DBtune user

GRANT pg_monitor TO dbtune;

Setting up the agent

docker run --detach \
  -e DBTUNE_USER=dbtune \
  -e DBTUNE_PASSWORD={dbtune_password} \
  -e POSTGRES_DBTUNE_DB_NAME={database_name} \
  -e USER_ID={user_id} \
  -e DB_ID={database_instance_id} \
  -e API_ENDPOINT={aws_api_gateway_endpoint} \
  -e DB_IDENTIFIER={database_identifier} \
  -e AWS_ACCESS_KEY={aws_access_key} \
  -e AWS_SECRET_ACCESS_KEY={aws_secret_access_key} \
  -e AWS_REGION={aws_region} \
  -e DBTUNE_PARAMETER_GROUP_NAME={database_parameter_group_name_for_dbtune} \
  docker.io/dbtuneai/dbtune-aurora-agent:0.1.0-rc.1

Environment variables

Variable	Description
DBTUNE_USER	DBtune database username you will use to log into the aurora database.
DBTUNE_PASSWORD	Password corresponding to the DBtune database user.
POSTGRES_DBTUNE_DB_NAME	Name of the PostgreSQL database that DBtune will optimize.
USER_ID	DBtune account's User ID found on the dashboard.
DB_ID	Database instance ID found on the dashboard.
API_ENDPOINT	Endpoint of the AWS API Gateway that DBtune will use to interact with AWS services.
DB_IDENTIFIER	Identifier of the database you want to optimize with DBtune.
AWS_ACCESS_KEY	Access key that DBtune will use to authenticate with AWS services.
AWS_SECRET_ACCESS_KEY	Secret access key that DBtune will use to authenticate with AWS services.
AWS_REGION	AWS region where your database is located.
DBTUNE_PARAMETER_GROUP_NAME	Name of DBtune-created parameter group for the aurora instance

Parameters tuned

The parameters tuned by DBtune changed in respect to the tuning mode. Below are the two tuning modes we support.

Reload-only tuning mode

This tuning mode tunes without restarting the database and using reload only. This does not cause any downtime and is suitable for production environments. The following parameters are tuned in this mode:

1. work_mem
2. random_page_cost
3. seq_page_cost
4. max_parallel_workers
5. max_parallel_workers_per_gather

Restart tuning mode

Most of our user base prefer reload-only tuning to avoid disruptions in production. If your system can handle restarts gracefully and you want to tune more parameters, you can chose this mode which restarts the database up to 30 times in a span of few hours and provide you with additional performance. The following parameters are tuned in this mode in addition to the parameters tuned in the reload-only mode:

6. shared_buffers
7. max_worker_processes

Optimization Objectives

Average Query Runtime

Average query runtime represents latency, which is measured in milliseconds ms. DBtune computes this using the calls and total_exec_time columns from the pg_stat_statements table.

Throughput

Throughput is defined as the number of transactions that the database completes successfully. DBtune computes PostgreSQL's throughput from the xact_commit metric in the pg_stat_database statistics table.

Agent monitoring stats (posted every second)

info

DBtune only retrieves performance metrics from the database and does not access or transmit any sensitive data, e.g., metadata and the tables data are not transferred.

Below are the data DBtune collects and sends to the DBtune server every second:

Collected data

Category	Subcategory
DB Stats	Throughput
	Query Runtime