SELECT interval_end,
table_name,
used_bytes
FROM spanner_sys.table_sizes_stats_1hour
WHERE interval_end = (
SELECT MAX(interval_end)
FROM spanner_sys.table_sizes_stats_1hour)
ORDER BY used_bytes DESC
LIMIT 4;
クエリ出力
interval_end
table_name
used_bytes
2022-11-15 13:00:00-07:00
order_item
60495552
2022-11-15 13:00:00-07:00
orders
13350000
2022-11-15 13:00:00-07:00
item_inventory
2094549
2022-11-15 13:00:00-07:00
customer
870000
過去 24 時間の特定のテーブルまたはインデックスのクエリサイズの傾向
次のクエリは、過去 24 時間のテーブルのサイズを返します。
GoogleSQL
SELECT interval_end, used_bytes
FROM spanner_sys.table_sizes_stats_1hour
WHERE interval_end > TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL -24 HOUR)
AND table_name = "table_name"
ORDER BY interval_end DESC;
ここで
table_name は、データベース内の既存のテーブルまたはインデックスである必要があります。
PostgreSQL
SELECT interval_end, used_bytes
FROM spanner_sys.table_sizes_stats_1hour
WHERE interval_end > spanner.timestamptz_subtract(now(), '24 HOUR')
AND table_name = 'table_name'
ORDER BY interval_end DESC;
[[["わかりやすい","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-09-05 UTC。"],[],[],null,["# Table sizes statistics\n\nSpanner provides the following built-in tables:\n\n- `SPANNER_SYS.TABLE_SIZES_STATS_1HOUR`: lists the sizes of your tables and indexes within your databases.\n- `SPANNER_SYS.TABLE_SIZES_STATS_PER_LOCALITY_GROUP_1HOUR`: lists the sizes of your tables and indexes within your databases for each locality group.\n\nThe table size is in bytes. Table sizes include data versions. You can use\ntheses built-in tables to monitor your table and index sizes over time. You can\nalso monitor the sizes of your indexes as you create, delete, and you modify\nthem (as you insert more rows into the index or when you add new columns to it).\nAdditionally, you can also look at the sizes of your change stream tables.\n\nDatabase storage can be monitored with the [Total database storage metric](/spanner/docs/storage-utilization).\nYou can see the breakdown of the database storage with\n`SPANNER_SYS.TABLE_SIZES_STATS_1HOUR` and\n`SPANNER_SYS.TABLE_SIZES_STATS_PER_LOCALITY_GROUP_1HOUR`.\n| **Note:** These built-in tables provide a historical perspective of the sizes of your tables and indexes. It is not for real-time monitoring.\n\nAccess table sizes statistics\n-----------------------------\n\n| **Note:** Spanner Studio (formerly labeled **Query** in the Google Cloud console) supports SQL, DML, and DDL operations in a single editor. For more information, see [Manage your data using the Google Cloud console](/spanner/docs/manage-data-using-console).\n\nSpanner provides the table sizes statistics in the\n`SPANNER_SYS` schema. You can use the following ways to access `SPANNER_SYS` data:\n\n- A database's Spanner Studio page in the Google Cloud console.\n\n- The `gcloud spanner databases execute-sql` command.\n\n- The [`executeSql`](/spanner/docs/reference/rest/v1/projects.instances.databases.sessions/executeSql)\n or the [`executeStreamingSql`](/spanner/docs/reference/rest/v1/projects.instances.databases.sessions/executeStreamingSql)\n method.\n\nThe following single read methods that Spanner provides\ndon't support `SPANNER_SYS`:\n\n- Performing a strong read from a single row or multiple rows in a table.\n- Performing a stale read from a single row or multiple rows in a table.\n- Reading from a single row or multiple rows in a secondary index.\n\nTABLE_SIZES_STATS_1HOUR\n-----------------------\n\n`SPANNER_SYS.TABLE_SIZES_STATS_1HOUR` contains the sizes of all the tables in\nyour database, sorted by `interval_end`. The intervals are based on clock times,\nending on the hour. Internally, every 5 minutes, Spanner collects\ndata from all servers and then makes the data available in the\n`TABLE_SIZES_STATS_1HOUR` table shortly thereafter. The data is then averaged\nper every clock hour. For example, at 11:59:30 AM, `TABLE_SIZES_STATS_1HOUR`\nshows the average table sizes from the interval of 10:00:00 AM - 10:59:59 AM.\n\n### Table schema\n\n### Example queries\n\nThis section includes several example SQL statements that retrieve aggregate\ntable sizes statistics. You can run these SQL statements using the\n[client libraries](/spanner/docs/reference/libraries), the\n[gcloud spanner](/spanner/docs/gcloud-spanner#execute_sql_statements), or the\n[Google Cloud console](/spanner/docs/create-query-database-console#run_a_query).\n\n#### Query 4 largest tables and indexes for the most recent interval\n\nThe following query returns the 4 largest tables and indexes for the most recent\ninterval:\n\n\u003cbr /\u003e\n\n```\n SELECT interval_end,\n table_name,\n used_bytes\n FROM spanner_sys.table_sizes_stats_1hour\n WHERE interval_end = (\n SELECT MAX(interval_end)\n FROM spanner_sys.table_sizes_stats_1hour)\n ORDER BY used_bytes DESC\n LIMIT 4;\n \n```\n\n\u003cbr /\u003e\n\n##### Query output\n\n#### Query size trend for a specific table or index for the last 24 hours\n\nThe following query returns the size of the table over the last 24 hours: \n\n### GoogleSQL\n\n```\nSELECT interval_end, used_bytes\n FROM spanner_sys.table_sizes_stats_1hour\nWHERE interval_end \u003e TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL -24 HOUR)\n AND table_name = \"table_name\"\nORDER BY interval_end DESC;\n```\n\nWhere:\n\n- \u003cvar translate=\"no\"\u003e\u003ccode translate=\"no\" dir=\"ltr\"\u003etable_name\u003c/code\u003e\u003c/var\u003e must be an existing table or index in the database.\n\n### PostgreSQL\n\n```\nSELECT interval_end, used_bytes\n FROM spanner_sys.table_sizes_stats_1hour\nWHERE interval_end \u003e spanner.timestamptz_subtract(now(), '24 HOUR')\n AND table_name = 'table_name'\nORDER BY interval_end DESC;\n```\n\nWhere:\n\n- \u003cvar translate=\"no\"\u003e\u003ccode translate=\"no\" dir=\"ltr\"\u003etable_name\u003c/code\u003e\u003c/var\u003e must be an existing table or index in the database.\n\n##### Query output\n\nTABLE_SIZES_STATS_PER_LOCALITY_GROUP_1HOUR\n------------------------------------------\n\n`SPANNER_SYS.TABLE_SIZES_STATS_PER_LOCALITY_GROUP_1HOUR` contains the sizes of\nall the tables in your database, sorted by `interval_end`, for each locality\ngroup. The intervals are based on clock times, ending on the hour. Internally,\nevery 5 minutes, Spanner collects data from all servers and then\nmakes the data available in the `TABLE_SIZES_STATS_PER_LOCALITY_GROUP_1HOUR`\ntable shortly thereafter. The data is then averaged per every clock hour. For\nexample, at 11:59:30 AM, `TABLE_SIZES_STATS_PER_LOCALITY_GROUP_1HOUR` shows the\naverage table sizes for each locality group from the interval of 10:00:00 AM -\n10:59:59 AM.\n\n### Table schema\n\nData retention\n--------------\n\nAt a minimum, Spanner keeps data for\n`SPANNER_SYS.TABLE_SIZES_STATS_1HOUR` for intervals covering the previous 30\ndays.\n| **Note:** You can't prevent Spanner from collecting table size statistics. To delete the data in the statistics table, you must delete the database associated with the table or wait until Spanner removes the data automatically. The retention period for the table is fixed. If you want to keep statistics for longer periods of time, we recommend that you periodically copy data out of this table.\n\nWhat's next\n-----------\n\n- Learn about other [Introspection tools](/spanner/docs/introspection).\n- Learn about other information Spanner stores for each database in the database's [information schema](/spanner/docs/information-schema) tables.\n- Learn more about [SQL best practices](/spanner/docs/sql-best-practices) for Spanner.\n- Learn more about [Investigating high CPU\n utilization](/spanner/docs/introspection/investigate-cpu-utilization)."]]
