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

The Cloud SQL high-number-of-open-tablesrecommender helps you detect instanceswhose number of concurrently opened tables is equal to the value oftable_open_cache. It thenprovides recommendations on how to optimize such instances to improve performance.

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

How it works

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

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

The Cloud SQL high-number-of-open-tables recommender analyzes metrics for the numberof opened tables on a Cloud SQL MySQL instance daily. If the number of opened tables increasesby 1 every 2 seconds or faster in the preceding 24 hours, and the number of opentables exceeds thetable_open_cache value, the recommender advises you toadjust thetable_open_cache flag.

The Cloud SQL high-number-of-open-tables recommender is in theStandardRecommender 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 requiredroles.

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

  For more information about roles, seeunderstanding roles andgranting IAM permissions.

• Enable the Recommender API.

  Roles required to enable APIs

  To enable APIs, you need the Service Usage Admin IAM role (roles/serviceusage.serviceUsageAdmin), which contains theserviceusage.services.enable permission.Learn how to grant roles.

  Enable the API

List instance performance recommendations

You can list the instance performance recommendations byusing the Google Cloud console,gcloud CLI, or the Recommender API.

The improve instance performance recommendations are shown only if you have instances thatare nearing performance threshold limits.

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

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

If the recommender detects instances that have a high-number-of-open-tables, it lists them in a tablewith 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 instancesthat have a high-number-of-open-tables by using the Google Cloud console,gcloud CLI, or the Recommender API.

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

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

The following table lists the insight and recommendation that the Cloud SQLhigh-number-of-open-tables recommender generates to help you improve performance.The subtypes are visible in thegcloud CLI and API results.

Apply recommendations

Evaluate the recommendations carefully and do any of the following:

• To examine the instance, clickView instance. SeeOptimize the performance of your instance and follow the recommendations.

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

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

Optimize the performance of your instance

To optimize your instance's performance, do any of the following:

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

2. During peak workload, check the value ofopen_tables. If the value ofopen_tables is more thantable_open_cache, then increase the value oftable_open_cache to the value ofopen_tables.

For information on how MySQL usestable_open_cache,seeHow MySQL Opens and Closes Tables.

For information on how toupdate a database flag, seeConfigure database flags.

What's next

• Manage high number of tables
• Monitor disk availability
• Identify idle Cloud SQL instances
• Reduce overprovisioned Cloud SQL instances
• ExploreGoogle Cloud recommenders