9

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.

  • There is an article about importing large dataset evolvingweb.ca/story/… – kalabro Oct 4 '12 at 7:41
  • Thank you for that. It's very encouraging to see that volumes of data can indeed be imported almost instantaneously. However, what about the issue of individual users posting through their own accounts through the node forms? As I dig and dig more into this problem, the rhetorical questions in my head grow, "Can Drupal handle this much live traffic? If not, then what is the point?" Aside from the imports, we have a team of about 20 who are adding content normally through their accounts. Can Drupal 'node save' really only handle 20 simultaneous users adding data at a time? – blue928 Oct 4 '12 at 23:50
  • We tested our Drupal site with Apache JMeter using MySQL and PostgreSQL. For MySQL our results were about 20 nodes. For PostgreSQL results were much better. – kalabro Oct 5 '12 at 7:39
5

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.

  • are you loading those in in batch with custom code or using some of drupal's API functions like node_save? Or a migration type module? Also is the code you mentioned available for public view? Would be nice to see how pcntl_fork is integrated with drupal to see you you guys have overcome this obstacle. Thanks for the binlog tip! – blue928 Apr 9 '13 at 6:45
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?

  • We're starting to see that error ourselves. Did you ever find an answer to your question? – trimbletodd Oct 10 '13 at 18:06
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.