GCP - Cloud SQL for PostgreSQL

Google Cloud Platform CloudSQL for 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 your Google Cloud Platform Cloud SQL database

CREATE USER dbtune WITH PASSWORD '{dbtune_password}';

• Grant the necessary permissions to the DBtune user

GRANT pg_monitor TO dbtune;

Google Cloud Platform IAM permissions

The agent is setup to use the Application Default Credentials. The credentials provided to the agent need at least the following permissions:

• cloudsql.databases.get
• cloudsql.databases.update
• cloudsql.instances.get
• cloudsql.instances.list
• cloudsql.instances.update
• monitoring.timeSeries.list

Setting up the agent

There are multiple ways to run the DBtune agent, below is an example using the pre-built binary and environment variables:

  export DBT_POSTGRESQL_CONNECTION_URL={connection url to the Aurora database} \
  export DBT_GCP_PROJECT_ID={gcp_project_id} \
  export DBT_GCP_DATABSE_NAME={gcp_cloudsql_database_name} \
  export DBT_DBTUNE_SERVER_URL=https://app.dbtune.com \
  export DBT_DBTUNE_API_KEY=your-api-key \
  export DBT_DBTUNE_DATABASE_ID=your-database-id \
  dbtune-agent --cloudsql

Visit the DBtune agent repository for more information.

Environment variables

Variable	Description
DBT_POSTGRESQL_CONNECTION_URL	Connection URL to the Aurora PostgreSQL database.
DBT_GCP_PROJECT_ID	Identifier of the Google Cloud Platform where the database you want to optimize can be found.
DBT_GCP_DATABSE_NAME	Name of the Google Cloud Platform
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 server 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:

1. work_mem
2. random_page_cost
3. seq_page_cost
4. checkpoint_completion_target
5. effective_io_concurrency
6. max_parallel_workers_per_gather
7. max_parallel_workers
8. max_wal_size
9. min_wal_size
10. bgwriter_lru_maxpages
11. bgwriter_delay

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 choose 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:

12. shared_buffers
13. max_worker_processes

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)

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	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