We have a client site that currently has a very, very large data set in Drupal. We are working on generating a 'stripped' copy of the database to be used for testing new code by eliminating the bulk of the site content.

To this end, we need to periodically delete 40 million+ nodes and field collection entities from Drupal to strip down a copy of the production database so it can be used more readily on our staging and local development instances.

This is somewhat beyond what VBO or Bulk Delete (Nodes) or other similar contrib solutions can handle in a reasonable time, not to mention that many of the field collections are not on node objects. I suspect that using node_delete_multiple() and entity_delete_multiple() for millions of items may be a non-starter time-wise, even if batched through either the queue API or the batch API.

Is there any way to 'cheat' by TRUNCATING the field_data_* and field_revision_* tables in MySQL to speed the record deletions?

  • For testing purposes, just investigate all tables that connected with Node. Then write script that truncate it. – Nikit Jul 16 '14 at 22:00
  • I think this is a dup, but I handle stuff like this with one-off drush scr scripts instead of Batch API. By not using the API you risk orphaned data in the database as the result of hook_node_delete() and hook_entity_delete() not running. – mpdonadio Jul 16 '14 at 22:48
  • Using this guide as a template and using these functions I think you could get something working for mass deletes. Using drush (command line) or httprl (custom code) you should be able to parallelize the job. – mikeytown2 Jul 16 '14 at 23:48
  • @MPD - Oh, I am more than aware that this isn't best practice. But to delete these all via node_delete_multiple() and entity_delete_multiple() is projecting to take somewhere around a week's time. – BrianV Jul 17 '14 at 15:36
  • @mikeytown2 - thanks, that's helpful info. – BrianV Jul 17 '14 at 15:37

I'm not seeing any simple way to truncate (or truncate from X) a table like this. The tables that would contain field data would be all mixed up and not nicely sorted.

In this case I would;

  • Start with a view that generated a list of node IDs based on your select criteria (content type, status, older than date published, etc)
  • Write a custom module with a drush command
  • Load the node IDs
  • Either node_delete($nid) OR...
  • Examine what node_delete() does and delete * from {table} where nid= $nid

The tables node_delete() may touch will largely be dependant on your content types. node_delete() will do the job, doing it my hand will take more time to prepare but may be faster to run. With the volume of records you're talking about a few milliseconds per delete may save a decent chunk of time.

  • We want to delete all nodes of type X and all field collections of type Y, and neither type has fields shared with content we don't want to delete. So in a sense, I know all the field tables for these fields can be truncated. – BrianV Jul 17 '14 at 15:38
  • As above, using node_delete_multiple() and entity_delete_multiple() via batches or queues is projecting to take about a week to complete, which is far too slow at this point. – BrianV Jul 17 '14 at 15:39

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.