How can I upload 1000s of nodes per hour to a live drupal 7 site and avoid deadlocks?

Question

Not too long ago I wrote about deadlock here: PDOException: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock;

Despite everything my development team tries to do, we still get errors like this:

PDOException: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction: INSERT INTO {location_instance} (nid, vid, uid, genid, lid) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3, :db_insert_placeholder_4); Array ( [:db_insert_placeholder_0] => 1059 [:db_insert_placeholder_1] => 1059 [:db_insert_placeholder_2] => 0 [:db_insert_placeholder_3] => cck:field_item_location:1059 [:db_insert_placeholder_4] => 1000 ) in location_save_locations() (line 974 of /var/www/website.com/sites/all/modules/location/location.module).

Despite the specific table in that example, we get this error on other tables.

Here is my situation. I have taken a large University project. At any given time there are 50,000 campus residents who use the system daily. In addition to that, I am migrating 100s of 1000s of items of content both manually and through custom module code (migration from the old university data) to this new Drupal 7 site.

This error is killing us, to the point where we are almost ready to scrap the last years worth of work and go with something else if Drupal cannot handle this type of load.

But that is more or less my question - How can Drupal handle this type of load? How can I organize my work flow to be able to handle this much activity? Is this a Drupal issue? A database issue?

Specifically, I am running Ubuntu, LAMP stack 16GB RAM. I am open to any suggestion whether it be Drupal related, database related, server config related, or a different work flow to work within Drupal's capabilities, so feel free to suggest anything if you have experience with this much activity.

Answer 1

I work for Stanford university and have been doing similar things. We constantly have to load over 100,000+ nodes on a regular basis. We have been working on our own custom loading code for 2 years now have been able to speed up the process quite a big using pcntl_fork. The only thing you have to remember is to close all socket connection before invoking the fork. For example you have to close you mysql connection, memcache connection and even mongo connection. Drupal will automatically create new connections when one doesn't exists. As far as the deadlock problem, we were able to fix that issue by putting innodb_locks_unsafe_for_binlog = 1.

Answer 2

The answer is: Configure your MySQL my.cnf file correctly.

After a little over a week of research, I have found that Drupal 7 can indeed handle this much concurrent input traffic.

These Deadlock PDOExceptions were related to the MySQL my.cnf file not being optimized correctly. With help from the Drupal High Performance group and other sources, our team has not had one single Deadlock happen since implementing the new configuration settings for MySQL. We tested our batch scripts to simulate up to 500 current users saving content with no problem. Check out the thread here.

http://groups.drupal.org/node/260938

Specifically, Dalin suggested using a wizard to get a base configuration file based on the server specs and table types. After using this, even without further tweaking, the deadlocks stopped. Here is a link to the wizard if you would like to try it: https://tools.percona.com/wizard

I'll be happy to post the my.cnf file if anyone would find it helpful.

Although the Deadlock problem is no longer an issue, we are now getting this error very frequently:

PDOException: SQLSTATE[42000]: Syntax error or access violation: 
1305 SAVEPOINT savepoint_1 does not exist: ROLLBACK TO SAVEPOINT savepoint_1; 
Array ( ) in file_usage_add() (line 661 of /var/www/website.com/includes/file.inc).

Could this be a mysql configuration issue as well?

Answer 3

http://dev.mysql.com/doc/refman/5.0/en/innodb-deadlocks.html this mysql page suggests what to do when you encounter the deadlock.

By fine tuning your mysql configuration will help you to solve this issue.