Managing your Drupal database revision table.

    See more
    • Updated

    Issue

    The remarkable Node revision functionality in Drupal creates data protection and is beneficial for any organisation. However, editors add, create and update content all the time; this can make node revisions unwieldy, causing a Drupal database volume to fill with items that are out of date and not needed.

    Resolution

    To negate revision tables from becoming excessively bloated, you can delete older revisions. However, to find out the size of your revision tables, two options are open to a developer.

    1. Use a tool like Workbench

    2. Log in via SSH and use `drush sqlc` to access the Mysql prompt. 

    As an example using `drush sqlc`  this is what you may see in your testing. Firstly display all databases to find the schema that will need evaluating.

    mysql> SHOW DATABASES;

    +--------------------+ 
| Database           | 
+--------------------+ 
| information_schema | 
| thedrupaldb        | 
+--------------------+
2 rows in set (0.00 sec)

    Run the command below changing "thedruapaldb" to a schema of your choice.

     
SELECT TABLE_NAME AS "Table Name", 
table_rows AS "Quant of Rows", ROUND( (
data_length + index_length
) /1024, 2 ) AS "Total Size Kb"
FROM information_schema.TABLES
WHERE information_schema.TABLES.table_schema = 'thedrupaldb'

    Find the node revision and field revision tables and review their corresponding sizes. In the example below the sizes are very large.

     
| field_revision_field_archived    | 668596 | 209616.00 | 
| field_revision_field_author      | 0      | 128.00    | 
| field_revision_field_banner_body | 8      | 128.00    | 
... 
| field_revision_field_blog_body   | 4652   | 59520.00  | 
... 
| node_revision.                   | 689476 | 210384.00 |
...

     

    To solve the removal of older content revisions to minimise the size of the table the installation of Node Revision Delete module is what your team can use.  As the module describes itself as:

    The Node Revision Delete module lets you track and prune old revisions of content types.

    However, take care and test the module before moving it into production as once the data is removed from older revisions it cannot be reclaimed in any way.

     

    Acquia Products

    Related articles