Cloud SQL 열린 테이블 수가 많음 추천자를 사용하면 동시에 열린 테이블 수가 table_open_cache 값과 동일한 인스턴스를 감지할 수 있습니다. 그런 다음 이러한 인스턴스를 최적화하여 성능을 향상시키는 방법에 대한 권장사항을 제공합니다.
이 페이지에서는 열린 테이블 수가 많음 추천자 작동 방식과 사용 방법을 설명합니다.
작동 방식
MySQL은 멀티 스레드이며 클라이언트는 여러 스레드의 같은 테이블에서 동시에 쿼리를 발행할 수 있습니다. 따라서 MySQL에는 각 세션에 대해 독립적으로 열린 테이블이 있을 수 있습니다.
동시에 열린 테이블 수는 table_open_cache에서 관리됩니다.
캐시가 가득 차고 추가 테이블이 열리면 MySQL은 가장 최근에 사용된 테이블을 닫습니다. 현재 캐시에 있는 모든 테이블을 사용 중인 경우 MySQL은 캐시를 일시적으로 확장하고 사용하지 않는 즉시 테이블을 닫습니다.
열린 테이블 수가 table_open_cache 값을 초과하면 워크로드가 높은 동안 데이터베이스 성능이 저하될 수 있습니다. 더 많은 스레드가 생성되고 테이블 핸들러가 테이블을 더 자주 여닫게 되기 때문입니다.
Cloud SQL 열린 테이블 수가 많음 추천자는 Cloud SQL MySQL 인스턴스의 열린 테이블 수에 대한 측정항목을 매일 분석합니다. 열린 테이블 수가 이전 24시간 동안 2초마다 1씩 증가하고 열린 테이블 수가 table_open_cache 값 이상인 경우 추천자는 table_open_cache 플래그를 조정할 것을 권고합니다.
GET https://recommender.googleapis.com/v1beta1/projects/PROJECT-ID/locations/LOCATION/recommenders/google.cloudsql.instance.PerformanceRecommender.MySqlHighNumberOfTablesBestPractice/recommendations
다음을 바꿉니다.
PROJECT_ID: 프로젝트 ID입니다.
LOCATION: 리전(예: us-central1)
추천자는 열린 테이블 수가 많은 인스턴스를 감지하면 이를 다른 성능 권장사항과 함께 테이블에 나열합니다.
행마다 인스턴스 ID, 간단한 권장사항, 데이터베이스 엔진, 위치, 마지막 새로고침 날짜가 표시됩니다.
통계 및 자세한 권장사항 보기
Google Cloud 콘솔, gcloud CLI 또는 Recommender API를 사용하여 열린 테이블 수가 많은 인스턴스에 대한 통계와 자세한 권장사항을 볼 수 있습니다.
콘솔
Google Cloud 콘솔을 사용하여 성능 기준점에 근접한 인스턴스에 대한 통계와 자세한 권장사항을 보려면 인스턴스 목록에서 권장사항 링크를 클릭합니다.
GET https://recommender.googleapis.com/v1beta1/projects/PROJECT-ID/locations/LOCATION/insightTypes/google.cloudsql.instance.PerformanceRecommender.MySqlHighNumberOfOpenTables/insights
다음을 바꿉니다.
PROJECT_ID: 프로젝트 ID입니다.
LOCATION: 리전(예: us-central1)
다음 표에는 성능 향상을 위해 Cloud SQL 열린 테이블 수가 많은 추천자에 대한 개방형 테이블 추천자가 생성하는 통계 및 권장사항이 나와 있습니다.
하위 유형은 gcloud CLI 및 API 결과에 표시됩니다.
Insight
권장사항
열린 테이블 수는 table_open_cache 플래그 값과 동일합니다.
하위 유형: MYSQL_HIGH_NUMBER_OF_OPEN_TABLES
열린 테이블 수를 줄여 Cloud SQL 인스턴스 성능을 높이세요.
하위 유형: MYSQL_HIGH_NUMBER_OF_OPEN_TABLES_BEST_PRACTICE
[[["이해하기 쉬움","easyToUnderstand","thumb-up"],["문제가 해결됨","solvedMyProblem","thumb-up"],["기타","otherUp","thumb-up"]],[["이해하기 어려움","hardToUnderstand","thumb-down"],["잘못된 정보 또는 샘플 코드","incorrectInformationOrSampleCode","thumb-down"],["필요한 정보/샘플이 없음","missingTheInformationSamplesINeed","thumb-down"],["번역 문제","translationIssue","thumb-down"],["기타","otherDown","thumb-down"]],["최종 업데이트: 2025-08-14(UTC)"],[],[],null,["# Increase the table open cache size\n\n\u003cbr /\u003e\n\nMySQL \\| PostgreSQL \\| SQL Server\n\n\u003cbr /\u003e\n\n\u003cbr /\u003e\n\n|\n| **Preview**\n|\n|\n| This feature is subject to the \"Pre-GA Offerings Terms\" in the General Service Terms section\n| of the [Service Specific Terms](/terms/service-terms#1).\n|\n| Pre-GA features are available \"as is\" and might have limited support.\n|\n| For more information, see the\n| [launch stage descriptions](/products#product-launch-stages).\n\nThe Cloud SQL high-number-of-open-tables [recommender](/recommender/docs/overview) helps you detect instances\nwhose number of concurrently opened tables is equal to the value of `table_open_cache`. It then\nprovides recommendations on how to optimize such instances to improve performance.\n\nThis page describes how the high-number-of-open-tables recommender works and how\nyou can use it.\n\nHow it works\n------------\n\nMySQL is multi-threaded, and clients can simultaneously issue a query on the same table from multiple\nthreads. Thus, MySQL can have independently open tables for each session.\nThe number of simultaneously opened tables is managed by `table_open_cache`.\nIf the cache is full and additional tables are opened, MySQL closes the least recently used\ntable. If all the tables in cache are currently being used, MySQL extends the cache\ntemporarily and closes the tables as soon as they become unused.\n\nIf the number of open tables exceeds the`table_open_cache` value, database performance\ncan be adversely affected during high workload. This is because more threads are created and table handlers\nhave to open and close the tables more frequently.\n\nThe Cloud SQL high-number-of-open-tables recommender analyzes metrics for the number\nof opened tables on a Cloud SQL MySQL instance daily. If the number of opened tables increases\nby 1 every 2 seconds or faster in the preceding 24 hours, and the number of open\ntables is equal to or more than the `table_open_cache` value, the recommender advises you to\nadjust the `table_open_cache` flag.\n| **Note:** Recommendations are generated daily.\n\nPricing\n-------\n\nThe Cloud SQL high-number-of-open-tables recommender is in the *Standard* [Recommender pricing tier](/recommender/pricing).\n\nBefore you begin\n----------------\n\nBefore you can view recommendations and insights, do the following:\n\n- To get the permissions to view and work with insights and recommendations, ensure that you have the required [roles](/sql/docs/mysql/project-access-control#roles).\n\n For more information about roles, see [understanding roles](/iam/docs/understanding-roles) and [granting IAM permissions](/iam/docs/granting-changing-revoking-access).\n-\n\n\n Enable the Recommender API.\n\n\n [Enable the API](https://console.cloud.google.com/flows/enableapi?apiid=recommender.googleapis.com)\n\nList instance performance recommendations\n-----------------------------------------\n\nYou can list the instance performance recommendations by\nusing the Google Cloud console, [gcloud CLI](/sdk/gcloud), or the Recommender API.\n\nThe improve instance performance recommendations are shown only if you have instances that\nare nearing performance threshold limits. \n\n### Console\n\nTo list recommendations about instance performance by using the\nGoogle Cloud console, follow these steps:\n\n1. Go to the **Cloud SQL Instances** page.\n\n [Go to Cloud SQL Instances](https://console.cloud.google.com/sql/instances)\n2. Click **View all** on the Increase table open cache recommendations banner.\n\nAlternatively, follow these steps:\n\n1. Go to the **Recommendation Hub** . See also [Getting started with Recommendation Hub](/recommender/docs/recommendation-hub/identify-configuration-problems).\n\n [Go to the Recommendation Hub](https://console.cloud.google.com/home/recommendations/)\n2. In the **Improve Cloud SQL Instance Performance** card, click **View all**.\n\n3. Select the instances with **Increase table open cache** recommendation.\n\n### gcloud CLI\n\nTo list Improve Instance Performance recommendations by using `gcloud CLI`, run the\n[`gcloud recommender recommendations list`](/sdk/gcloud/reference/recommender/recommendations/list)\ncommand as follows: \n\n```\ngcloud recommender recommendations list \\\n--project=PROJECT_ID \\\n--location=LOCATION \\\n--recommender=google.cloudsql.instance.PerformanceRecommender \\\n--filter=recommenderSubtype=MYSQL_HIGH_NUMBER_OF_OPEN_TABLES_BEST_PRACTICE\n```\n\nReplace the following:\n\n- \u003cvar translate=\"no\"\u003ePROJECT_ID\u003c/var\u003e: Your project ID\n- \u003cvar translate=\"no\"\u003eLOCATION\u003c/var\u003e: A region, such as `us-central1`\n\n### API\n\nTo list Improve Instance Performance recommendations by using the\n[Recommendations API](/recommender/docs/using-api), call the\n[`recommendations.list`](/recommender/docs/reference/rest/v1beta1/projects.locations.recommenders.recommendations/list)\nmethod as follows: \n\n```\nGET https://recommender.googleapis.com/v1beta1/projects/PROJECT-ID/locations/LOCATION/recommenders/google.cloudsql.instance.PerformanceRecommender.MySqlHighNumberOfTablesBestPractice/recommendations\n```\n\nReplace the following:\n\n- \u003cvar translate=\"no\"\u003ePROJECT_ID\u003c/var\u003e: Your project ID.\n- \u003cvar translate=\"no\"\u003eLOCATION\u003c/var\u003e: A region, such as `us-central1`.\n\nIf the recommender detects instances that have a high-number-of-open-tables, it lists them in a table\nwith other performance recommendations.\nEach row shows the instance ID, a brief recommendation, database engine, the location, and the last refresh date.\n\nView insights and detailed recommendations\n------------------------------------------\n\nYou can view insights and detailed recommendations about instances\nthat have a high-number-of-open-tables by using the Google Cloud console,\n`gcloud CLI`, or the Recommender API. \n\n### Console\n\nTo view insights and detailed recommendations about instances that are close to\nthe performance threshold by using the Google Cloud console, click the recommendation\nlink in the list of instances.\n\n### gcloud CLI\n\nTo view insights and detailed recommendations about instances that are close to\nthe performance threshold by using `gcloud CLI`, run the\n[`gcloud recommender insights list`](/sdk/gcloud/reference/recommender/insights/list)\ncommand as follows: \n\n```\ngcloud recommender insights list \\\n--project=PROJECT_ID \\\n--location=LOCATION \\\n--insight-type=google.cloudsql.instance.PerformanceInsight \\\n--filter=insightSubtype=MYSQL_HIGH_NUMBER_OF_OPEN_TABLES\n```\n\nReplace the following:\n\n- \u003cvar translate=\"no\"\u003ePROJECT_ID\u003c/var\u003e: Your project ID.\n- \u003cvar translate=\"no\"\u003eLOCATION\u003c/var\u003e: A region, such as `us-central1`.\n\n### API\n\nTo view insights and detailed recommendations about instances that have a high-number-of-open-tables\nby using the [Recommendations API](/recommender/docs/using-api),\ncall the [`insights.list`](/recommender/docs/reference/rest/v1beta1/projects.locations.insightTypes.insights/list)\nmethod as follows: \n\n```\nGET https://recommender.googleapis.com/v1beta1/projects/PROJECT-ID/locations/LOCATION/insightTypes/google.cloudsql.instance.PerformanceRecommender.MySqlHighNumberOfOpenTables/insights\n```\n\nReplace the following:\n\n- \u003cvar translate=\"no\"\u003ePROJECT_ID\u003c/var\u003e: Your project ID.\n- \u003cvar translate=\"no\"\u003eLOCATION\u003c/var\u003e: A region, such as `us-central1`.\n\nThe following table lists the insight and recommendation that the Cloud SQL\nhigh-number-of-open-tables recommender generates to help you improve performance.\nThe subtypes are visible in the `gcloud CLI` and API results.\n\nApply recommendations\n---------------------\n\nEvaluate the recommendations carefully and do any of the following:\n\n- To examine the instance, click **View instance** . See [Optimize the performance of your instance](#optimize-performance) and follow the recommendations.\n\n- To dismiss the recommendation so that it's de-emphasized and appears dimmed,\n click **Dismiss**.\n\n- To close the panel without applying or dismissing the recommendation, click\n **Cancel**.\n\n### Optimize the performance of your instance\n\nTo optimize your instance's performance, do any of the following:\n\n1. 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.\n\n2. If the value of `open_tables` is more than `table_open_cache`, increase the value of `table_open_cache`\n to the value of `open_tables`.\n\n For information on how to\n update a database flag, see [Configure database flags](/sql/docs/mysql/flags#config).\n | **Note:** Don't set the value of `table_open_cache` to be more than `524288`, which is [the maximum allowed value for this flag](/sql/docs/mysql/flags#table-open-cache).\n\nWhat's next\n-----------\n\n- [Manage high number of tables](/sql/docs/mysql/recommender-high-number-of-tables)\n- [Monitor disk availability](/sql/docs/mysql/using-ood-recommender)\n- [Identify idle Cloud SQL instances](/sql/docs/mysql/recommender-sql-idle)\n- [Reduce overprovisioned Cloud SQL instances](/sql/docs/mysql/recommender-sql-overprovisioned)\n- Explore [Google Cloud recommenders](/recommender/docs/recommenders)"]]
