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-tablesrecommender helps you detect instanceswhose table count is too high and close to the SLA limit. It then provides recommendationson how to optimize such instances and improve instance performance.

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

How it works

If you have 50,000 or more database tables on a single instance, it could resultin the instance becoming unresponsive or unable to perform maintenance operations,and the instance is not covered by the SLA.

The Cloud SQL high-number-of-tables recommender analyzes metrics for the numberof tables on a Cloud SQL MySQL instance. If the number of tables is larger than orequal to 80% of the SLA limit, which allows for 50,000 tables, the instance is consideredto have a high number of tables.

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 Improve Instance Performance recommendations

You can list the Improve 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_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 with high number of tables, it lists them in atable 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 instancesthat have a high number of 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_TABLES

GET https://recommender.googleapis.com/v1beta1/projects/PROJECT-ID/locations/LOCATION/insightTypes/google.cloudsql.instance.PerformanceRecommender.MySqlHighNumberOfTables/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 instanceand 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

• Edit your instance to increase the number of vCPUsto at least 32 cores and the memory size to at least 200 GB.This increases the instance'stable limit from 50,000 to 500,000.

  Caution: This operation can lead to an increase in cost. Additionally, it triggers a restart,which can cause downtime on the instance.

• Drop unnecessary tables:

  DROP TABLETABLE_NAME;

  Replace the following:

  • TABLE_NAME: Name of the table you intend to drop.

• Reduce the number of tables per instance by splitting the database across multiple instances to keep the number of tables in eachinstance within the recommended limits.

• If you cannot immediately reduce the number of tables, you can reduce the likelihoodof your instance being impacted by the high tablecount by setting theinnodb_file_per_table flag to OFF. To turn off the value oftheinnodb_file_per_table flag, seeConfigure a database flag.However, this setting does not bring the instance back into SLA compliance.See theseoperational guidelines.

• Use a general tablespace to create tables or move existing tables to a general tablespace.To learn more, seeMySQL documentation on general tablespaces.

What's next

• Increase the table open cache size
• Monitor disk availability
• Identify idle Cloud SQL instances
• Reduce overprovisioned Cloud SQL instances
• ExploreGoogle Cloud recommenders