How can a Drupal 7 website handle high volumes of node input? (node_save() function)

We encourage users to post events happening in the community to the community events group on https://www.drupal.org.
Gastonia's picture

I have a project that has been in the works for almost a year. The project is at a point now where it is time to upload content. I have a team of people doing this item by item through the content node forms, and I have a few developers aggregating content and batch saving it in a custom module, which, in order to stay 'Drupally', uses the Drupal node_save() function as well as other typical functions in the API.

At the same time, the site is live and in use by an entire campus of students who also can add content and otherwise interact with the site through their user account.

The problem is the site is crippled by database deadlocks. We get deadlocks so many times a day the site is simply not usable. Here is an example:

PDOException: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction:

I have written more about this here:
http://drupal.stackexchange.com/questions/45617/how-can-i-upload-1000s-o...

Also, I have found a similar bug report in the Drupal issue queue:
http://drupal.org/node/1417866

Again, in this Domain Issue queue, #4 agentrickard notes that this is a problem with node_save() and is part of a much larger issue.
http://drupal.org/node/1707330

I have to agree, this is a huge issue. Is anyone else experiencing this? If so, what are strategies you have used to get around it?

I have reduced the load and done testing over the past couple weeks in every attempt to work within Drupal's capabilities. In my testing I have found that even if my team of just 15 people who are manually entering in nodes one by one through the node forms, this is still a problem.

Is the reality just that Drupal cannot handle a paltry 15 to 25 concurrent connections inputting content without imploding? What are some strategies to scale Drupal to be able to handle 10x to 100x that?

Thanks for the input.

Comments

I can assure you that Drupal

dalin's picture

I can assure you that Drupal can indeed handle much more. What table are the deadlocks happening on?

These inserts shouldn't take more than a millisecond or two, so the chance of your 15 people even having an insert to the same table at the same time is rather low. This suggests that inserts into your database are taking far longer for some reason. It may be that there are some really slow queries that are causing backlogs (check the MySQL slow query log), or it may be that a bit of simple MySQL tuning can go a long way (This will help you get a good start https://tools.percona.com/wizard ).

--


Dave Hansen-Lange
Director of Technical Strategy, Advomatic.com
Pronouns: he/him/his

Hi Gastonia, The same issue

kannan@kiluvai.com's picture

Hi Gastonia,

The same issue is discussed here http://drupal.org/node/937284#comment-6192044 with suggestion which can help you.

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

Actually the issue is with MySQL InnoDB engine row locking. fine tuning your MySQL InnoDB configuration will help you.

One of the suggestion in the above mysql page. is
"Always be prepared to re-issue a transaction if it fails due to deadlock. Deadlocks are not dangerous. Just try again"

Kamalakannan S
Global SoftLab
I love programming

It might help to schedule a

Garrett Albright's picture

It might help to schedule a cron task to do the automated content importing at 2:00 AM or some other low-traffic time, if that content doesn't need to be in the database ASAP.

But this isn't to disagree with dalin that there may be some other problem occurring here.

DB config file helped significantly

Gastonia's picture

Dalin, thank you for that link. That suggested config file was a great starting point. In fact, I increased my scripts to simulate 10, 100, and 500 simultaneous connections and not one single Deadlock PDOException.

Svkannan77, that is a great link. I have a question about the advice there. Most of those have to do with things that are handled by the application and sql query logic, correct? If all I am calling is node_save() how can I rewrite Drupal API sql as needed to implement those solutions?

Thanks so much! The advice is priceless!