Drupal - How can I upload 1000s of nodes per hour to a live drupal 7 site and avoid deadlocks?
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
.
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?