I started a node import of 700,000 address records to a custom content type in Drupal 6. I was only getting in input rate of about 91 nodes/minute, which would take about 128 hours.

Then I switched from MyISAM to InnoDB tables. No significant change.

Then I dropped all the database indexes from the content type table, node, and node_revisions table except for the primary keys. No noticeable change.

To speed it up, I disabled Xdebug and added APC. Then I disabled the following modules to bring the enabled module count down from 58 to 42:

• admin menu
• content copy
• color
• update status
• content form jammer
• generic jammer
• mail logger
• advanced help
• diff
• r4032login
• user import
• rules admin ui
• filter permissions
• log user filter
• bonus views export
• views ui

That changes things. It moved from about 91 nodes/min to 908 nodes/min.

nodes/min
Xdebug
Drop indexes
Disabled modules
Disabled modules w/ APC
Disabled modules w/ APC, no indexes
MyISAM
91
92
883
908
940
InnoDB
90
91
875
836
1003

I noted that the InnoDB import went faster than MyISAM without indexes on the content type, node, node_access, and node_revisions tables. I even took extra rate samples just to make sure that was correct, and it seems to be.

ALTER TABLE <code>content_type_participants ENGINE = InnoDB;ALTER TABLE node ENGINE = InnoDB;ALTER TABLE node_access ENGINE = InnoDB;ALTER TABLE node_revisions ENGINE = InnoDB;ALTER TABLE <code>content_type_participants ENGINE = MyISAM;ALTER TABLE node ENGINE = MyISAM;ALTER TABLE node_access ENGINE = MyISAM;ALTER TABLE node_revisions ENGINE = MyISAM;

Another thing which isn't represented here is that the import clearly slows down as the tables grow in size, so if you're importing several hundred thousand records as nodes, expect that the rate of import will decrease as the import progresses. That fact is probably reflected in the difference between the "Disabled modules" and "Disabled modules w/ APC" figures as my timing in doing rate samples wasn't exactly the same when I did each case.

The summary is clear, though, and Khalid is right again. The single biggest impact you can make to speed up your node imports, or your site in general, is to disable some modules.

Post categories

Drupal