Manage open tables and open table definitions
Preview —Gemini in Databases
This feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of theService Specific Terms. You can process personal data for this feature as outlined in theCloud 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 thelaunch stage descriptions.
This page describes how to manage instances with a high number of open tablesand a high number of open table definitions. Thisrecommender is calledManage open tables.
Every day, this recommender analyzes metrics for the following:
- The number of open tables or open table definitions for an instance as follows:
- If the number of opened tables or opened table definitions increases by 1table every 2 seconds, or faster, during the previous 24 hours.
- If the number of open tables or open table definitions is equal to or morethan the value of
table_open_cacheandtable_definition_cache,respectively.If either of these are true, then the recommender advises you to increase thevalue oftable_open_cacheortable_definition_cache.
For more information on increasing the value oftable_open_cache andtable_definition_cache, seeTable limit.
Pricing
TheManage open tables recommender is in theStandardRecommender pricing tier.
Before you begin
Required roles and permissions
To get the permissions to view and work with insights and recommendations, ensure that you have the requiredIdentity and Access Management (IAM) roles.
| Task | Role |
|---|---|
| View recommendations | recommender.cloudsqlViewer orcloudsql.admin |
| Apply recommendations | cloudsql.editor orcloudsql.admin |
List the recommendations
To list the recommendations, follow these steps:
Console
To list recommendations about instance performance, follow these steps:
- Go to theCloud SQL Instances page.
- On theImprove instance health by investigating issues and acting onrecommendations banner, clickExpand Details .
Alternatively, follow these steps:
Go to theActive Assist. See alsoFind and apply recommendations with the Recommendations.
In theAll recommendations card, clickPerformance .
gcloud
Run thegcloud recommender recommendations list command as follows:
gcloud recommender recommendations list \--project=PROJECT_ID \--location=LOCATION \--recommender=google.cloudsql.instance.PerformanceRecommender \--filter=recommenderSubtype=MYSQL_RECONFIG_OPEN_TABLES
Replace the following:
- PROJECT_ID: your project ID
- LOCATION: a region where your instances are located, such as
us-central1
API
Call therecommendations.list method as follows:
GET https://recommender.googleapis.com/v1/projects/PROJECT_ID/locations/LOCATION/recommenders/google.cloudsql.instance.PerformanceRecommender/recommendations
Replace the following:
- PROJECT_ID: your project ID
- LOCATION: a region where your instances are located, such as
us-central1
View insights and detailed recommendations
To view insights and detailed recommendations, follow these steps:
Console
Do one of the following:
On thePerformance Recommendations page, click thePerformancerecommendations card and then clickManage open tables. Therecommendation panel appears, which contains insights and detailedrecommendations for the instance.
On theInstances page, clickManage open tables.The list of instances displays only those instances for which therecommendation applies.
gcloud
Run thegcloud recommender insights list command as follows:
gcloud recommender insights list \--project=PROJECT_ID \--location=LOCATION \--insight-type=google.cloudsql.instance.PerformanceInsight \--filter=insightSubtype=INSIGHT_SUBTYPE
Replace the following:
- PROJECT_ID: your project ID
- LOCATION: a region where your instances are located, such as
us-central1 - INSIGHT_SUBTYPE: set this parameter to one of the following:
MYSQL_HIGH_NUMBER_OF_OPEN_TABLES: display insights forthe number of open tables for your instanceMYSQL_HIGH_NUMBER_OF_OPEN_TABLE_DEFINITIONS: display insights forthe number of open table definitions for your instance
API
Call theinsights.list method as follows:
GET https://recommender.googleapis.com/v1/projects/PROJECT_ID/locations/LOCATION/insightTypes/google.cloudsql.instance.PerformanceInsight/insights
Replace the following:
- PROJECT_ID: your project ID
- LOCATION: a region where your instances are located, such as
us-central1
Apply the recommendation
To implement this recommendation, do one of the following:
For a high number of open tables, increase the value of
table_open_cacheby1000 until the recommendation disappears, or check the value ofopen_tablesat peak usage, and increase the value oftable_open_cacheto above the valueofopen_tables.For a high number of open table definitions, increase the value of
table_definition_cacheby 500 until the recommendation disappears, or checkthe value ofopen_table_definitionsat peak usage, and increase the valueoftable_definition_cacheto above the value ofopen_table_definitions.
table_open_cache ortable_definition_cache requires morememory. After increasing these flags, monitor the memory usage .This recommendation is updated daily, so after you increase the value of eithertable_open_cache ortable_definition_cache, wait for 24 hours beforechecking the recommendation again. For more information on increasing the valueoftable_open_cache andtable_definition_cache, seeTable limit.
What's next
Except as otherwise noted, the content of this page is licensed under theCreative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.
Last updated 2025-12-16 UTC.