Thread

Any time we have a busy story we see a LOT of database lockups, and I had this nagging feeling that something was actually updating the database on every page *view*—We do have a comment form right on our article pages. Is it possible that exp_security_hashes is being updated every time someone views an article with that comment form? And will disabling “Process form data in Secure Mode” remedy that?

Would there be any other tables/features that would cause database UPDATES for each page load?

Is it possible that exp_security_hashes is being updated every time someone views an article with that comment form?

Yes. Every time a secure form is shown a hash is created and stored in the database.

And will disabling “Process form data in Secure Mode” remedy that?

Yes, that should work. Alternatively, don’t show forms on regular single entry pages, use a separate template.

Would there be any other tables/features that would cause database UPDATES for each page load?

Some of the user, referrer and other tracking can have that effect, too.

In general this seems like it would have a negative performance impact and, indeed, it does. When we start serving several thousand pages per hour (or sometimes a LOT more), this really breaks down.

I’m surprised this isn’t mentioned in the Performance Tuning section. Is there a way to get a list of all the things that do this so we can be sure to NOT do any database writes for simple page loads? It seems crazy to me to write to the DB every time someone simply reads a page. Seems to me all of that should be coming out of a cache anyway, and not even *reading* from the DB, let alone writing.

Dave,

The db write is mentioned on the Security and Session Preferences. The pros of doing this write is that it is designed to deter automated spam attacks as well as multiple accidental submissions. It is up to you the user to see fit if this is something you need or not.

Are you referring to the Handling Extreme Traffic with ExpressionEngine section when you refer to the Performance Tuning section?

Here is another document you may find helpful

The db write is mentioned on the Security and Session Preferences.

Indeed, it is, but it’s mentioned as a “query” which we always interpreted as a “read”—as you know, writes are much more costly (and lock-ly! 😉) than reads. Appreciate the clarification.

The pros of doing this write is that it is designed to deter automated spam attacks as well as multiple accidental submissions. It is up to you the user to see fit if this is something you need or not.

For anyone following-up on this thread, it’s worth noting that implementing this is really a trade-off in deciding which type of attack against wish one wishes to guard. With the setting on it protects against the duplicate posting attack but leaves you wide open to a (very) simple DOS attack whereby one user can simply request many copies of your article pages in parallel. All those write operations lock up your database and crush your site. Turning it off fixes that but opens you up to a (much more difficult to perform) comment-submission attack. For us, we’ll choose to keep it off and make our would-be attackers work a little harder. 😉

Are you referring to the Handling Extreme Traffic with ExpressionEngine section when you refer to the Performance Tuning section?

I appreciate the link. I’m not sure we’d seen that one before or not. in Admin > System Preferences > Tracking Preferences the only thing we have enabled is “Enable Section Entry View Tracking?” but that’s only being used on specific pages where we want it to trigger a DB write.

Here is another document you may find helpful

Oh that’s likely VERY handy. Thanks!

So, does that answer your questions? Any remaining issues?

Thanks, I think we’re on the right track now. I still don’t like that even a page “in the cache” has 50+ SQL queries associated with its load, but hopefully EE2 will be a little more efficient in that regard.

Dave, no problems.Feel free to start a new thread if you have any more questions.