I recently migrated a D6 site with AntiSpam/Akismet to D7, and immediately began noticing crippling performance issues related to comment spam. My site has moderate amounts of traffic, with about 30,000 spam comments caught per month.

We had the configured the "Anti-Spambot Options" configured to provide a 30 second "Delay when spam is detected" and displayed a 503 error page to spammers. We did not select any methods to "Identify spambots."

With those configurations, we received frequent database locking errors, including:
PDOException: SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction: INSERT INTO {field_data_comment_body} (entity_type, entity_id, revision_id, bundle, delta, language, comment_body_value, comment_body_format) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3, :db_insert_placeholder_4, :db_insert_placeholder_5, :db_insert_placeholder_6, :db_insert_placeholder_7); Array ( [:db_insert_placeholder_0] => comment [:db_insert_placeholder_1] => 29837 [:db_insert_placeholder_2] => 29837 [:db_insert_placeholder_3] => comment_node_blog [:db_insert_placeholder_4] => 0 [:db_insert_placeholder_5] => und [:db_insert_placeholder_6] => Hi there colleagues, its fantastic paragraph concerning cultureand entirely explained, keep it up all the time. [:db_insert_placeholder_7] => 1 ) in field_sql_storage_field_storage_write() (line 451 of field_sql_storage/field_sql_storage.module).

and:
PDOException: SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction: UPDATE {node_comment_statistics} SET cid=:db_update_placeholder_0, comment_count=:db_update_placeholder_1, last_comment_timestamp=:db_update_placeholder_2, last_comment_name=:db_update_placeholder_3, last_comment_uid=:db_update_placeholder_4 WHERE (nid = :db_condition_placeholder_0) ; Array ( [:db_update_placeholder_0] => 29824 [:db_update_placeholder_1] => 4 [:db_update_placeholder_2] => 1393297797 [:db_update_placeholder_3] => louis vuitton wallets outlet [:db_update_placeholder_4] => 0 [:db_condition_placeholder_0] => 7812 ) in _comment_update_node_statistics() (line 2469 of comment/comment.module).

According to New Relic, the /comment_reply function was taking anywhere from 20-30 seconds to run. When the site was under heavy load + the comment section was most popular, the server would become cripplingly slow.

Last night, we removed the 30 second delay, setting it to be none, and the performance issues when away:
ss

ss2

Solutions?: It seems that the module could be refactored to not lock tables while displaying the 503 error to spammers. At the very least, we should update the "Delay when spam is detected" description to advise users of well trafficked site to not use that option.

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

kmonty’s picture

Issue summary: View changes
DamienMcKenna’s picture

Issue tags: +Performance

Urk. Thank for putting time into identifying this problem.