How can I save 30,000 nodes in less than a day?

Question

Recently I have written a module that reads contents from a file; downloads associated image files; and then saves that to the Drupal 7 as a new node using node_save. The script works fine, but the minute I start the script I have about 16 to 24 hours before it would finish all 30,000 entries. Yea, that's way too long.

What are some suggested design patterns for my module to finish this as fast as possible? Here is the current architecture:

$items = getArrayOfItems();    //reads file and gets an array of items
foreach ($items as item) {
    $node = new stdClass();
    $check_exits = check_title_exits($item[title]);    // note: call to database
    if(!$check_exists) {
        populateNodeObjectFields($item);
    }

    if($images = hasPhotos()) {    //note: must dl up to 4 images from web using CURL
        //create file object and apply to node image field
        $node->image_field = $images;
    }

    //do final custom validations
    validate($node);

    if($node = submit_node($node)) {
        node_save($node);    //note: call to database
    }
}

Everywhere I have a note I believe is a bottleneck. Here are my theoretical thoughts on improving the speed. 1) instead of checking against the database for a title (exists) load all titles into an array and check against the array 2) maybe I can separate the file downloads for a separate process to handle later after the node has been saved? 3) This is the biggest bottleneck. If I am understanding the Drupal API correctly, this opens a connection and saves the node one by one - or more than 30,000 times. How can I just open up one call to the database and save all nodes in one batch?

My original solution was simply to have multiple scripts run simultaneously and be responsible for a certain chunk of the data. 12 scripts for example should have cut it down to 2 hours tops for all the data. The problem with that is I keep getting PDO exceptions - serialization errors. Please see this link where I wrote about it. PDOException: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock;. If I could fix that, I would be happy to continue with that solution.

Thoughts on the best way to approach this?

Answer 1

Inserting 30K nodes in a day is possible and you are also right about the bottlenecks.

1. Avoid unnecessary table scanning. What happen if a title is duplicated? if you just add a couple more words to it to make different then apply this change after the import is done by running a query against the DB showing you only node titles duplicated. See query at the end.

2. The CURL method will be major as well because it depends on your server connection to the server where the images are hosted and everything in between like load balancer, etc. An idea here will be if possible to download the image before the import and have the located already on the server so that it will be a simple move instead of the whole CURL thing.

3. Does the custom validation do more DB table scanning? If that is the case can you prepare the data before running the import. Like if you know the title have to be unique and maybe you have to convert the date to unixtime and so on, then you might be better off creating a temp table where you can hold all the changes done to the data and the last step is just to run the import against that table instead of the original table with no processed data on it.

4. Two more things come to mind when migrating data:

   a. Create a batch method (http://api.drupal.org/api/drupal/includes%21form.inc/group/batch/7)

   b. Use the migrate module (http://drupal.org/project/migrate)

5. Depending on your setup if you can optimize your MySQL to handle the load it will be a big plus so that you have a lot less table or row locking.

Queries:

Duplicate title after import query:

select title, count(*) from node group by title HAVING count(*) > 1;

Preparing node titles to be unique:

INSERT INTO temp_table (title,body,date) VALUES ('title','body',UNIX_TIMESTAMP('2008-12-01 12:00:00')) ON DUPLICATE KEY UPDATE title=title+'Other words';

Answer 2

I think you should have a look at the migrate module. I have used it to create 60k plus nodes via xml in less than an hr. It has a learning curve attached to it but the examples are good and it will import in a clean and proper manner.

Also it has command line integration with drush so you do not run into timeout issues.