Cloud Platform
Identifying Large Database Tables
Large table sizes can fill database disk volumes, cause downtime, affect data integrity and negatively impact database management tasks. This document will guide you through the steps to identify and troubleshoot large database tables.
At the MySQL prompt, run the following query. You must substitute "exampledb" with your database name. To determine the database name please review Working with databases - Viewing database information.
SELECT
table_schema AS `Database`,
table_name AS `Table`,
round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`
FROM information_schema.TABLES WHERE table_schema = 'exampledb'
ORDER BY (data_length + index_length) DESC
LIMIT 10;In this example, the cache_render table is 2.8 Gigabytes in size:
+------------+------------------------------------------+------------+
| Database | Table | Size in MB |
+------------+------------------------------------------+------------+
| exampledb | cache_render | 2799.59 |
| exampledb | cache_data | 805.53 |
| exampledb | cache_entity | 239.45 |
| exampledb | cache_dynamic_page_cache | 208.03 |
| exampledb | paragraph_revision__field_paragraph_text | 145.41 |
| exampledb | paragraph_revision__field_text | 60.13 |
| exampledb | node_revision__field_paragraphs | 56.50 |
| exampledb | redirect | 48.72 |
| exampledb | paragraphs_item_revision_field_data | 28.58 |
| exampledb | paragraph_revision__field_subheadline | 26.61 |
+------------+------------------------------------------+------------+
10 rows in set (0.00 sec)
Cloud Platform