Prevent transaction ID wraparound

MySQL   |  PostgreSQL   |  SQL Server

Preview

This feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of the Service Specific Terms. Pre-GA features are available "as is" and might have limited support. For more information, see the launch stage descriptions.

The Cloud SQL high-transaction-ID-utilization recommender proactively generates recommendations that help you avoid potential transaction ID wraparound for Cloud SQL PostgreSQL instances.

You can apply this recommendation when a Cloud SQL instance is trending toward a transaction ID wraparound problem. This page describes how the Cloud SQL high-transaction-ID-utilization recommender works and how you can use it.

How it works

A transaction ID is assigned when the transaction starts, and it's frozen until the transaction is vacuumed. Transaction ID utilization is the number of unvacuumed transactions (assigned minus frozen) expressed as a fraction of the maximum value of 2 billion. Under the default PostgreSQL settings, with vacuum processes performing optimally and without interruption, most databases experience transaction ID utilization in the region of approximately 10%. Higher transaction ID utilization levels can be observed in busy databases where regular workloads frequently take precedence over vacuum. If the transaction ID utilization trends towards very high values (80% or more), the database might be at risk of transaction ID exhaustion. Transaction ID utilization reaching 100% is termed as transaction ID wraparound. Once the transaction ID utilization percentage reaches 100%, PostgreSQL stops accepting write queries.

The Cloud SQL high-transaction-ID-utilization recommender analyzes transaction ID utilization on a Cloud SQL PostgreSQL instance.

If the transaction ID utilization percentage is more than or equal to 80%, it's recommended to take actions to avoid transaction ID wraparound.

Note: Recommendations are generated daily.

Pricing

The Cloud SQL high-transaction-ID-utilization recommender is in the Standard Recommender pricing tier.

Before you begin

Before you can view recommendations and insights, do the following:

• To get the permissions to view and work with insights and recommendations, ensure that you have the required roles.

  Tasks	Roles
  View recommendations	One of these roles: recommender.cloudsqlViewer or cloudsql.viewer.
  Apply recommendations	One of these roles: recommender.cloudsqlAdmin, cloudsql.editor, or cloudsql.admin.

  For more information about roles, see understanding roles and granting IAM permissions.

• Enable the Recommender API.

  Enable the API

List Improve Instance Performance recommendations

You can list the Improve Instance Performance recommendations by using the Google Cloud console, gcloud CLI, or the Recommender API.

The improve instance performance recommendations are shown only if you have instances that are nearing a transaction ID wraparound.

Console

To list recommendations about instance performance using the Google Cloud console, follow these steps:

1. Go to the Cloud SQL Instances page.

   Go to Cloud SQL Instances

2. Click View all on the Prevent transaction ID wraparound recommendations banner.

Alternatively, follow these steps:

1. Go to the Recommendation Hub. See also Getting started with Recommendation Hub.

   Go to the Recommendation Hub

2. In the Improve Cloud SQL Instance Performance card, click View all.

3. Select the instances with Prevent transaction ID wraparound recommendation.

gcloud CLI

To list Improve Instance Performance recommendations by using gcloud CLI, run the gcloud recommender recommendations list command as follows:

gcloud recommender recommendations list \
--project=PROJECT_ID \
--location=LOCATION \
--recommender=google.cloudsql.instance.PerformanceRecommender \
--filter=recommenderSubtype=POSTGRES_HIGH_TRANSACTION_ID_UTILIZATION_BEST_PRACTICE

Replace the following:

• PROJECT_ID: Your project ID
• LOCATION: A region, such as us-central1

API

To list Improve Instance Performance recommendations by using the Recommendations API, call the recommendations.list method as follows:

GET https://recommender.googleapis.com/v1beta1/projects/PROJECT-ID/locations/LOCATION/recommenders/google.cloudsql.instance.PerformanceRecommender.PostgresHighTransactionIdUtilizationBestPractice/recommendations

Replace the following:

• PROJECT_ID: Your project ID.
• LOCATION: A region, such as us-central1.

If the recommender detects instances with high transaction ID utilization, it lists them in a table with other performance recommendations. Each row shows the instance ID, a brief recommendation, database engine, the location, and the last refresh date.

View insights and detailed recommendations

You can view insights and detailed recommendations about instances that are trending toward a transaction ID wraparound using the Google Cloud console, gcloud CLI, or the Recommender API.

Console

To view insights and detailed recommendations about instances that are close to the performance threshold by using the Google Cloud console, click the recommendation link in the list of instances.

gcloud CLI

To view insights and detailed recommendations about instances that are close to the performance threshold by using gcloud CLI, run the gcloud recommender insights list command as follows:

gcloud recommender insights list \
--project=PROJECT_ID \
--location=LOCATION \
--insight-type=google.cloudsql.instance.PerformanceInsight \
--filter=insightSubtype=POSTGRES_HIGH_TRANSACTION_ID_UTILIZATION

Replace the following:

• PROJECT_ID: Your project ID.
• LOCATION: A region, such as us-central1.

API

To view insights and detailed recommendations about instances that are close to the performance threahold by using the Recommendations API, call the insights.list method as follows:

GET https://recommender.googleapis.com/v1beta1/projects/PROJECT-ID/locations/LOCATION/insightTypes/google.cloudsql.instance.PerformanceRecommender.PostgresHighTransactionIdUtilization/insights

Replace the following:

• PROJECT_ID: Your project ID.
• LOCATION: A region, such as us-central1.

The following table lists the insight and recommendation that the Cloud SQL high-transaction-ID-utilization recommender generates. The subtypes are visible in the gcloud CLI and API results.

Insight	Recommendation
Transaction ID utilization percentage on this instance is high and close to 100%. Subtype: POSTGRES_HIGH_TRANSACTION_ID_UTILIZATION	Avoid potential transaction ID wraparound for Cloud SQL instances. Subtype: POSTGRES_HIGH_TRANSACTION_ID_UTILIZATION_BEST_PRACTICE

Apply recommendations

Evaluate the recommendations carefully and do any of the following:

• To examine the recommendation, click View instance. See Optimize the performance of your instance or Prevent transaction ID wraparound and follow the recommendations.

• To dismiss the recommendation so that it's de-emphasized and appears dimmed, click Dismiss.

• To close the panel without applying or dismissing the recommendation, click Cancel.

Optimize the performance of your instance

To address the transaction ID wraparound problem with the instance, do the following:

1. Find the database and table causing the wraparound.

2. Explore and interpret transaction ID utilization metrics on your database instances.

3. Check for a stuck transaction ID.

4. Remove VACUUM blockers.

5. Check and accelerate VACUUM speed.

For additional information, also see the blog post Using VACUUM to accelerate transaction ID freezing in Cloud SQL for PostgreSQL.

Prevent transaction ID wraparound

Preview — Gemini in Databases

This feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of the Service Specific Terms. You can process personal data for this feature as outlined in the Cloud Data Processing Addendum, subject to the obligations and restrictions described in the agreement under which you access Google Cloud. Pre-GA features are available "as is" and might have limited support. For more information, see the launch stage descriptions.

To prevent transaction ID wraparound for an instance, run the following command:

SELECT * FROM google_vacuum_mgmt.pg_fix_wraparound();

Example output:

postgres=> select * from google_vacuum_mgmt.pg_fix_wraparound();
-[ RECORD 1 ]-----+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
issue_description | Gemini has detected an open prepared transaction on your instance which is blocking vacuum. Monitor the transaction ID utilization and commit or rollback the transaction, as needed.
query             | ROLLBACK PREPARED 'trx_id_pin'; or COMMIT PREPARED 'trx_id_pin';
recommendation    | To commit a prepared transaction, you must be connected as the same user that originally executed the transaction: postgres
insights          | Transaction ID Utilization: 88.49%

What's next

• Monitor disk availability
• Identify idle Cloud SQL instances
• Reduce overprovisioned Cloud SQL instances
• Explore Google Cloud recommenders