Create indexes or reconfigure join settings
Preview
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 the number of joins without indexes in yourinstance's join buffer. Thisrecommender iscalledCreate indexes or reconfigure join settings.
Every day, this recommender monitors the number of joins without indexes in thejoin buffer. The join buffer is a per-connection memory buffer usedfor storing data during join operations that cannot use indexes. If theaggregated number of joins without indexes is greater than 500 per hour, then itrecommends to create an index or increase the value ofjoin_buffer_size.
For more information on increasing the value ofjoin_buffer_size, seeOther memory consumption.
Pricing
TheCreate indexes or reconfigure join settings 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 applyrecommendations 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_CREATE_INDEX_OR_RECONFIG_JOIN_BUFFER
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 clickCreate indexes orreconfigure join settings. The recommendation panel appears, whichcontains insights and detailed recommendations for the instance.
On theInstances page, clickCreate indexes or reconfigure join settings.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=MYSQL_HIGH_JOINS_WITHOUT_INDEXES
Replace the following:
- PROJECT_ID: your project ID
- LOCATION: a region where your instances are located, such as
us-central1
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 the following:
Create appropriate indexes in your instance to prevent a large number of joinswithout indexes.
If you are not able to create indexes for the joins, increase the value of
join_buffer_sizeuntil the recommendation disappears. The join buffer can beincreased by session or globally. We recommend that you increase the joinbuffer in the session where there are a large number of joins.For more information on increasing the value of
Note: Increasingjoin_buffer_size, seeOther memory consumption.join_buffer_sizerequires more memory. After increasingthis flag, monitor the memory usage.
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-17 UTC.