Date Published: February 10, 2022
Identifying Large Database Tables
Issue¶
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.
Resolution¶
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)
Did not find what you were looking for?
If this content did not answer your questions, try searching or contacting our support team for further assistance.