2

I am writing a module the preferably implements a cron job, and possibly a queue, to run sql queries to delete old nodes and their comment. Approximately 20 Million nodes, and about 40 mil comments. I want to run pure mysql queries and bypass the drupal api (as much as I can), for performance reasons. I have already developed the relevant queries and would like to know what would be the best way I could implement this so it does not user Drupal api? This is on drupal 7 by the way.

1
  • Can you be more specific about the queue part? – Nabil Kadimi Jan 30 '13 at 20:06
3

I would never think running raw SQL queries is a good idea because that can mess up a lot of things such as site maps, search index, caches, etc.

it's just a node_delete() and shouldn't be much slower that deleting a bunch of rows in multiple tables.

Whatever that is, you will need to implement hook_cron and retrive nodes to be deleted from 'node' table.

If you run a node_delete you won't need to delete comments.

You can limit the number of nodes to delete in a single cron run using LIMIT keyword.

Update: You might want to have a look at Delete All module. and there is a patch (which I submitted) to use the Batch API to do the job. From my experience I know even the Batch API can't handle it (or you have to wait several days with the browser open).

You can just use hook_cron to load a bunch of nodes to be deleted, and then delete them in your preferred way (node_delete or raw SQL). Let the cron finish, and in the next run, it will grab some other nodes. Just get your nodes from node table and limit it with a LIMIT.

2
  • Just look at this function that is used by node_delete. This can kill the system. It runs a query to delete a each node. Using mysql I can run batches. – awm Jan 11 '13 at 21:41
  • see the updated reply. – AKS Jan 12 '13 at 1:40
2

I think you should use cron and queue api together with hook_cron_queue_info() hook_cron_queue_info()

Declare queues holding items that need to be run periodically. While there can be only one hook_cron() process running at the same time, there can be any number of processes defined here running. Because of this, long running tasks are much better suited for this API. Items queued in hook_cron() might be processed in the same cron run if there are not many items in the queue, otherwise it might take several requests, which can be run in parallel.

0

VBO, or VBO-like functionality.

VBO allows you to specify "entity load capacity", meaning "the number of entities I can load and process at once". The default is 10, but you can easily set it to 100-200 on a system with a bigger memory limit (a gigabyte for instance). It also allows you to delay execution to cron.

Then, you create a view of 20 million nodes, and use the "select all on all pages" to select them all. VBO will then enqueue them in groups of "entity load capacity" (so each queue item would have 100-200 items, for example), and then on cron each group will be loaded separately, after which entity_delete() will be called for each individual item.

I consider direct db queries in Drupal very unwise. Be very careful if you decide to go that way.

4
  • Trust me this won't work very nice with that large dataset. even though it uses Batch API, it's limited by the number of jobs it can handle. Each batch job has a set of operations - 20m is a large number for that IMO. – AKS Jan 12 '13 at 1:29
  • VBO doesn't use Batch API in a "classic" way, so that shouldn't be true. Let us know how it goes, in any case. – Bojan Zivanovic Jan 13 '13 at 1:16
  • I am having problem deleting one node with this large database. Mysql is going away. It is about 300 GB, views and VBO will break the system. – awm Jan 15 '13 at 23:41
  • 1
    If mysql is going away then you need to increase max_packet_size, that's a common problem with drupal installations of any size. – Bojan Zivanovic Jan 17 '13 at 13:02
0

Below is what I eventualy ended up doing. As I mentioned before, with a database with ~20 million nodes and ~90 million comments, all solutions that relies on the drupal API failed because and took way too much time. For example, invoking path delete was taking too much time for with this query SELECT DISTINCT SUBSTRING_INDEX(source, '/', 1) AS path FROM url_alias; for each node. With this query, deleting 1000 nodes did not even finish (queries often timed out). I had to comment out the function responsible for it to be able to finsh on node. I commented out path_delete in path.inc. This took down the average time for deleting 1000 nodes to aporx~ 200 seconds. Still was not good enough to run on a live db. So I needed to get as close as I could to pure SQL. Here is what I did:

1.Find out all the nodes I am willing to keep and keep their idsin a table.
2. Select all the nodes that I am going to delete based on a criteria and using limits of course. 3. Select all the ids comments of these nodes
4. Delete everything that referecnce them.
The whole operation was then placed in a drush command that takes params and calls the function below.

function _deletes_multiple_from_tables($nids, $cids) {
$transaction = db_transaction();
try {  
// fields
db_delete('field_data_body')
->condition('entity_id', $nids, 'IN')
->condition('entity_type', "node", "=")
->execute(); 


//field_revision_body
db_delete('field_revision_body')
  ->condition('entity_id', $nids, 'IN')
  ->execute();


  if (!empty($cids)) {
    db_delete('field_revision_comment_body')
    ->condition('entity_id', $cids, 'IN')
    ->execute();


    db_delete('field_data_comment_body')
      ->condition('entity_id', $cids, 'IN')
      ->execute();
  }
  // Delete Comments fields



db_delete('field_data_taxonomy_forums')
 ->condition('entity_id', $nids, 'IN')
 ->condition('entity_type', "node", "=")
 ->execute();


  // Delete Comments
db_delete('comment')
  ->condition('nid', $nids, 'IN')
  ->execute();

db_delete('forum')
  ->condition('nid', $nids, 'IN')
  ->execute();

 db_delete('forum')
  ->condition('nid', $nids, 'IN')
  ->execute();

 db_delete('forum_index')
  ->condition('nid', $nids, 'IN')
  ->execute();


 db_delete('node_comment_statistics')
  ->condition('nid', $nids, 'IN')
  ->execute();

db_delete('opengraph_meta')
  ->condition('nid', $nids, 'IN')
  ->execute();

db_delete('print_mail_node_conf')
  ->condition('nid', $nids, 'IN')
  ->execute();
0

First thing i would like to mention is the function entity_delete_multiple or the function node_delete_multiple which they both get a bunch of node/entity ids and delete them.

A very good example exist in OG where the module delete nodes referenced to group once it deleted - it use a cron. In the cron worker the module search 250 nodes which need to be deleted.

Another good place to look at is the Message UI module which update in cron/batch massive amount of nodes. You can rely on the code just to get overview on how this need to be done.

-1

If it were me, I'd create a menu callback function that does a db_query on your nodes. Then write a cron job on your server that calls that URL.

5
  • I'd give you code examples, but formatting on this site is near impossible for me. – vintorg Jan 11 '13 at 20:55
  • This definitely not the way to go. Read about hook_cron. – awm Jan 11 '13 at 21:58
  • I'm familiar with hook_cron. Why is it not the way to go? – vintorg Jan 11 '13 at 22:02
  • 1
    Why would you create a menu call back and hit using the URL when you have a nicely implemented system that run crons for you? Whay if a user, robot, hit that url? My site goes down. – awm Jan 13 '13 at 3:04
  • I think hook_cron isn't a true cron. It only runs if a user hits the site. Also, you just use variable_set and variable_get to flag when you're running. If your fx is called and sees you're running, you just exit. – vintorg Jan 13 '13 at 6:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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