AWS - Aurora PostgreSQL
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;
AWS IAM permissions
The AWS credentials provided to the agent need at least the following permissions:
rds:DescribeDBInstances
rds:ModifyDBParameterGroup
cloudwatch:GetMetricStatistics
ec2:DescribeInstanceTypes
pi:GetResourceMetrics (if Performance Insights is enabled)
rds:RebootDBInstance (if you want to perform tunign with restart)
Setting up the agent
There are multiple ways to run the DBtune agent, below is an example using Docker container:
docker run --detach \
-e DBT_POSTGRESQL_CONNECTION_URL={connection url to the Aurora database} \
-e DBT_RDS_DATABASE_IDENTIFIER={rds_database_identifier} \
-e DBT_RDS_PARAMETER_GROUP_NAME={parameter_group_name} \
-e DBT_AWS_ACCESS_KEY_ID={aws_access_key_id} \
-e DBT_AWS_SECRET_ACCESS_KEY={aws_secret_access_key} \
-e DBT_AWS_REGION={aws_region} \
-e DBT_DBTUNE_SERVER_URL=https://app.dbtune.com \ # Optional if you use the cloud
-e DBT_DBTUNE_API_KEY=your-api-key \
-e DBT_DBTUNE_DATABASE_ID=your-database-id \
docker.io/dbtuneai/dbtune-agent:latest --aurora
Visit the DBtune agent repository for more information.
Environment variables
Variable | Description |
---|---|
DBT_POSTGRESQL_CONNECTION_URL | Connection URL to the Aurora PostgreSQL database. |
DBT_RDS_DATABASE_IDENTIFIER | Identifier of the Aurora RDS instance you want to optimize. |
DBT_RDS_PARAMETER_GROUP_NAME | Name of the parameter group for the Aurora instance. |
DBT_AWS_ACCESS_KEY_ID | AWS access key ID that DBtune will use to authenticate with AWS services. |
DBT_AWS_SECRET_ACCESS_KEY | AWS secret access key that DBtune will use to authenticate with AWS services. |
DBT_AWS_REGION | AWS region where your database is located. |
DBT_DBTUNE_SERVER_URL | URL of the DBtune server (optional if using the cloud version). |
DBT_DBTUNE_API_KEY | Your DBtune API key for authentication. |
DBT_DBTUNE_DATABASE_ID | Database ID assigned by DBtune for this database instance. |
Parameters tuned
The parameters tuned by DBtune change with respect to the chosen tuning mode. Below are the two tuning modes supported.
Reload-only tuning mode
This tuning mode tunes without restarting the database and using only reloads. This does not cause any downtime and is suitable for critical production environments. The following parameters are tuned in this mode:
work_mem
random_page_cost
seq_page_cost
max_parallel_workers
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; This will likely deliver additional performance. The following parameters are tuned in this mode in addition to the parameters tuned in the reload-only mode:
shared_buffers
max_worker_processes
(will be added soon)
Optimization objectives
Average query runtime (AQR)
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.
Transactions per second (TPS)
Throughput is defined as the number of transactions that the database completes successfully per second.
DBtune computes PostgreSQL's throughput from the xact_commit
metric in the pg_stat_database
statistics table.
Agent monitoring stats (posted every 5 seconds)
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 | Metrics |
---|---|
Database Performance | Query Runtime, Transactions Per Second, Active Connections, Cache Hit Ratio, Wait Events, Autovacuum Count |
System | Database Size, Server Uptime |
Hardware | CPU Utilization, Memory Usage, Freeable Memory |