Good morning.

While our aggregates_versions table is ok (4k lines), I just got a warning this morning regarding a (very) slow query with New Relic

Query SELECT ? AS expressionFROM advagg_aggregates_versions advagg_aggregates_versionsWHERE ( (aggregate_filenames_hash = :db_condition_placeholder_?) AND (aggregate_contents_hash = :db_condition_placeholder_?) ) FOR UPDATE

Duration 38,464 ms

it seems to be caused by "FOR UPDATE"

Call is advagg_insert_aggregate and logs flag MergeQuery::execute.
So I suspect this is

 $return = db_merge('advagg_aggregates')
      ->key(array(
        'aggregate_filenames_hash' => $record['aggregate_filenames_hash'],
        'filename_hash' => $record['filename_hash'],
        ))
      ->insertFields($record)
      ->execute();

is db_merge mandatory?
Thanks.

Comments

mikeytown2’s picture

Are you using Drupal 7.25 or higher?
#937284: DEADLOCK errors on MergeQuery INSERT due to InnoDB gap locking when condition in SELECT ... FOR UPDATE results in 0 rows

If you are then it looks like I need to use RAW sql if the database is MySQL so I can make this query more performant. http://drupal.stackexchange.com/questions/89253/how-to-set-insert-ignore...

mikeytown2’s picture

Guessing you're using 7.34 based off of the other reported issues so I'll work on this in the next couple of days. I used to use lock_acquire and things like that but it resulted in PDO exceptions #2259309: Remove most lock calls by using db_merge instead of drupal_write_record and I thus went for db_merge

JulienThomas’s picture

Good morning.

No, I use an older version of Drupal right now, 7.23.
I will also see on my end if I can review this.

Thanks

mikeytown2’s picture

Title: Slow query (30 sec) on advagg_insert_aggregate » Slow query (30 sec) on advagg_insert_aggregate; core 7.23, merge_query is slow for older versions of Drupal, below 7.25
Status: Active » Postponed (maintainer needs more info)

OK i need to know if this is still an issue with that deadlock patch applied on your end or with an updated core version that includes the patch. I would rather not put in MySQL only code.

mikeytown2’s picture

Status: Postponed (maintainer needs more info) » Closed (works as designed)

Guessing that older versions of Drupal core are to blame here. Closing the issue.