Regenerate runs into a scalability wall with large node counts

Well I know why it's happening now, but not sure what it means yet.

My activity table has a field 'actions_id', but in my case all of the existing activities have a value of 0 in that field. Inside the activity_batch_delete() function, it runs a SQL query (:

SELECT aid FROM {activity} WHERE aid > %d AND actions_id = '%s' ORDER BY aid ASC

In my case, %d = 0 and %s = 14. So that SQL fails since activity_id is 0.

I'm guessing that at some point actions_id was introduced and my existing activity doesn't have that represented.

OK, well here's why.
http://drupal.org/node/463854#comment-2972118

Let's assume that it isn't possible in the current state of the code - perhaps it's still a good idea to introduce some better error checking so the batch finishes? Or is this a moot point since very few of us use the older code.

Well, I want to regenerate ALL of my activity, so I just hosed all of the old node activity.

DELETE FROM  `activity` WHERE TYPE =  'node';

I thought that might do it and get me going, but I thought wrong. It's now failing in activity_batch_regenerate(). The $batches array it gets something that looks like this:

[2010-12-19 23:37:52] Array
(
    [14] => Array
        (
            [0] => Array
                (
                    [id] => 1
                    [created] => 1244702271
                    [actor] => 1
                )

            [1] => Array
                (
                    [id] => 66
                    [created] => 1245363323
                    [actor] => 1
                )
           .. and so on

In this case, it takes 14 as the $aid, which seems to not be correct so I'm guessing there is an issue in either what is being passed in to regenerate or something to do with the foreach. Hmm.

EDIT: For what it's worth, this same behavior happens even if I disable the Activity module, uninstall it (no more DB tables), reinstall/re-enable, and then re-add a node activity message and select record. So I don't think it's related to an upgrade.

OK, that wasn't it at all.

The activity_batch_regenerate function starts out with $batch, which in my case includes one array entry for each node on the system. With a node count into the hundreds of thousands, this runs into a scalability wall. It passes this entire list of nodes off to the regenerate function regardless of whether or not the content type is being regenerated. For example, I want to regenerate one content type called 'item'. Even though that's my desire, it's batching on all of the nodes which makes it incredibly slow to regenerate as we fall through to the activity_record_check() function before this is caught.

I'm in the process of rewriting a bit of this checking up-front before the batch process begins. Inside of activity_batch_regenerate, before we set the final $batch, I pull the parameters from the actions table, determine the node type for this $aid, run a SELECT to pull all nodes of that type, and then do a foreach to remove anything that isn't part of this node type. This is done before we batch_set(), so we are only batching up the nodes that need content regenerated. This is a massive savings in time on my site when regenerating activity.

I'll roll up a patch as I get a bit further, but here's what it looks like if you are curious.

function activity_batch_regenerate($batches) {
  $batch_set = FALSE;
  foreach ($batches as $aid => $batch) {
      $trigger_assignment = db_fetch_object(db_query("SELECT hook, op FROM {trigger_assignments} WHERE aid = '%s'", $aid));
      if (!empty($trigger_assignment)) {
        switch ($trigger_assignment->hook) {
          case "nodeapi":
            // Obtain the node type in question
            $parameters = unserialize(db_result(db_query("SELECT a.parameters FROM {actions} a WHERE aid = '%s'", $aid)));
            $types = $parameters['activity_types'];
            // Create a list of NIDs for this node type
            foreach ($types as $type) {
              $result = db_query("SELECT nid FROM {node} WHERE type = '%s'", $type);
              while ($row = db_fetch_array($result)) {
                $nids[] = $row['nid'];
              }
            }
            $nids = array_flip($nids);
            // Remove all nodes from the batch that are not of this node type
            foreach ($batch as $key => $item) {
              if (!array_key_exists($item['id'], $nids)) {
                unset($batch[$key]);
              }
            }
            break;
        }

        $batch_set = TRUE;
        $batch = array(
          'title' => t('Regenerating @description Activity', array('@description' => db_result(db_query("SELECT description from {
          'operations' => array(
            array('activity_batch_delete', array($aid)),
            array('activity_batch_regenerate_step', array($aid, $batch, $trigger_assignment->hook, $trigger_assignment->op)),
          ),
          'file' => drupal_get_path('module', 'activity') . '/activity.admin.inc',
        );

        batch_set($batch);
      }
  }

  if ($batch_set) {
    batch_process('admin/build/activity');
  }
  else {
    return '';
  }
}

Yes, this has solved it. It wasn't actually hanging - it was slowly churning through batches of nodes - 50 at a time. Nothing was being generated because the batches were full of nodes from content types that did not need to be updated in the first place. My 'fix' just reduces the giant array of nodes so it only includes the ones that are part of the content type you want to update. In my case it completes almost immediately now.

I'll roll up a patch today for this.

OK, give this a shot and let me know how it works out for you. It's rolled against 6.x-2.0-alpha1.

It wasn't with my table - I reinstalled activity from scratch including all of its tables.

The core problem is that I have 200,000 nodes, and all of them are being passed in to set_batch() even though I'm only updating a content type with say 1000 total nodes. So the batch runs across 200,000 nodes when it only needs to run over 1000. This patch fixes that so it only passes the 1000 nodes to set_batch in the first place. The result is the messages for those nodes are now generated MUCH faster since we're only batching on the nodes for the content type in question.

This is different than the issue I originally presented, I was off on the wrong path at that point. It wasn't related to upgrading.

I can't quite explain why, but when regenerating comments this was not a problem for me - they completed quickly. It was just the node rebuilds that were problematic. I don't build user actions so I did not test that.

#7 - since you had the same problem, can you give this a go? Test it for both node, comment, and user rebuilds and see what happens (before and after the patch preferably.)