Increase the table open cache size

The Cloud SQL high-number-of-open-tables recommender helps you detect instances whose number of concurrently opened tables is equal to the value of table_open_cache. It then provides recommendations on how to optimize such instances to improve performance.

This page describes how the high-number-of-open-tables recommender works and how you can use it.

How it works

MySQL is multi-threaded, and clients can simultaneously issue a query on the same table from multiple threads. Thus, MySQL can have independently open tables for each session. The number of simultaneously opened tables is managed by table_open_cache. If the cache is full and additional tables are opened, MySQL closes the least recently used table. If all the tables in cache are currently being used, MySQL extends the cache temporarily and closes the tables as soon as they become unused.

If the number of open tables exceeds thetable_open_cache value, database performance can be adversely affected during high workload. This is because more threads are created and table handlers have to open and close the tables more frequently.

The Cloud SQL high-number-of-open-tables recommender analyzes metrics for the number of opened tables on a Cloud SQL MySQL instance daily. If the number of opened tables increases by 1 every 2 seconds or faster in the preceding 24 hours, and the number of open tables is equal to or more than the table_open_cache value, the recommender advises you to adjust the table_open_cache flag.

Pricing

The Cloud SQL high-number-of-open-tables 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 instance performance recommendations

You can list the 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 performance threshold limits.

Console

To list recommendations about instance performance by 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 Increase table open cache 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 Increase table open cache 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=MYSQL_HIGH_NUMBER_OF_OPEN_TABLES_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.MySqlHighNumberOfTablesBestPractice/recommendations

Replace the following:

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

If the recommender detects instances that have a high-number-of-open-tables, 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 have a high-number-of-open-tables by 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=MYSQL_HIGH_NUMBER_OF_OPEN_TABLES

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 have a high-number-of-open-tables 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.MySqlHighNumberOfOpenTables/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-number-of-open-tables recommender generates to help you improve performance. The subtypes are visible in the gcloud CLI and API results.

Insight Recommendation
Number of open tables is equal to the table_open_cache flag value.
Subtype: MYSQL_HIGH_NUMBER_OF_OPEN_TABLES 		Increase Cloud SQL instance performance by reducing the number of open tables.
Subtype: MYSQL_HIGH_NUMBER_OF_OPEN_TABLES_BEST_PRACTICE

Apply recommendations

Evaluate the recommendations carefully and do any of the following:

  • To examine the instance, click View instance. See Optimize the performance of your instance 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 optimize your instance's performance, do any of the following:

  1. Increase the value of table_open_cache by 500 until the recommendation disappears. The recommendation is updated daily, so after you increase the value of table_open_cache, wait for 24 hours before checking it again.

  2. If the value of open_tables is more than table_open_cache, increase the value of table_open_cache to the value of open_tables.

    For information on how to update a database flag, see Configure database flags.

What's next