1

I recently setup my production MySQL server for replication. To do this I enabled innodb_flush_log_at_trx_commit and sync_binlog and set bind-address. A slave server on the opposite corner of the US is now happily replicating the master.

It's been fine, except that every time the cache is cleared (manually, because a module was enabled/disabled, etc) the site becomes unresponsive for about 5 minutes. It reports the following error (though, sometimes the query changes, the error is the same. This is the most common query to show this):

PDOException: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction: DELETE FROM {semaphore} WHERE (name = :db_condition_placeholder_0) AND (value = :db_condition_placeholder_1) AND (expire <= :db_condition_placeholder_2) ; Array ( [:db_condition_placeholder_0] => variable_init [:db_condition_placeholder_1] => 300377225537241be5ddc00.97685038 [:db_condition_placeholder_2] => 1399996863.3809 ) in lock_may_be_available() (line 181 of /usr/local/www/r/example.com/web/includes/lock.inc).

Clearing the cache now takes much longer. Before it would take 10 seconds at most. Now, it takes 5+ minutes. While I have run into this error occasionally elsewhere (during an hours long batch process with update.php; randomly in other places, though very rare), it's really causing problems when needing to push updates and clear the cache.

Note that the slave server is not actually serving the website at this time; it is only a backup.

mysql  Ver 14.14 Distrib 5.5.14, for Linux (x86_64) using readline 5.1
PHP 5.3.8 (cli) (built: Aug 31 2011 16:27:31)
Drupal 7.8

And the master server's my.cnf:

[mysqld]
datadir = /usr/local/mysql/db
socket  = /usr/local/mysql/mysql.sock
user    = mysql

old_passwords      = 1
key_buffer         = 16M
max_allowed_packet = 16M
thread_stack       = 128K
max_connections    = 1024

log_slow_queries     = 1
slow_query_log_file  = /usr/local/mysql/log/slow.log
long_query_time      = 10
ft_min_word_len      = 2
skip-external-locking

# Query cache configuration
query-cache-type  = 1
query_cache_limit = 2M
query_cache_size  = 16M
thread-cache-size = 8

# InnoDB Buffer Pool
innodb_buffer_pool_size=8G

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links                 = 0
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table
default-storage-engine=InnoDB

# For replication
server-id        = 100
log_bin          = /usr/local/mysql/log/mysql-bin.log
sync_binlog      = 1
expire_logs_days = 10
max_binlog_size  = 100M
bind-address     = example

[mysqld_safe]
socket     = /usr/local/mysql/mysql.sock
log-error  = /var/log/mysqld.log
pid-file   = /var/run/mysqld/mysqld.pid

[client]
socket = /usr/local/mysql/mysql.sock

In my searches, I've seen some suggest enabling inndb_locks_unsafe_for_binlog though the name itself seems to say that it shouldn't be used along with sync_binlog.

2

Follow this and you should be good to go: Fixes for MySQL Deadlocks in D7

  • I've implemented fix for the semaphore table and that seems to have stopped the deadlocks. But clearing the cache still takes a very long time and causes the site to become unresponsive. I'm going to try the "READ COMMITTED" fix next. Thanks for the help so far. Also note that I'm using memcached for the cache backend, not MySQL. – donut May 14 '14 at 20:56
  • Could you do a cache clear with devel's "display query log" checked & "sort by duration"; also check "display page timer" & "display memory usage". This will hopefully give us some insight into why your cache clears are so slow when using memcache. A cachegrind would be ideal but the devel output should be good enough for now. Also noted that semaphore can be in memcache and not the database. check the memcache readme for directions. – mikeytown2 May 14 '14 at 21:35
  • We are still getting deadlocks occasionally and I've tried to enable READ-COMMITTED isolation for transactions. It works in my local dev environment but on production when I add the code from this comment I get a white screen of death and nothing in error logs. – donut Jul 7 '14 at 23:19
  • WSOD usually indicates a syntax error. Slide 76 has a picture of where this goes. – mikeytown2 Jul 7 '14 at 23:50
  • This isn't a syntax error. We have three application servers running the same set of files via an NFS share. I've taken one of those servers out of the pool and am accessing it directly. I have setup up an if statement to only add the READ-COMMITTED isolation setting for the server that I've taken out of the pool, but all servers are using the same settings.php file. – donut Jul 8 '14 at 21:04
0

Following the guide that mikeytown2 linked to in his answer and altering the semaphore table with the following query fixed the deadlocks I was experiencing.

ALTER TABLE semaphore ENGINE = MEMORY;
ALTER TABLE semaphore DROP PRIMARY KEY;
ALTER TABLE semaphore ADD PRIMARY KEY (name, value) USING BTREE;
ALTER TABLE semaphore ADD UNIQUE name (name) USING BTREE;
ALTER TABLE semaphore DROP INDEX value;
ALTER TABLE semaphore ADD INDEX value (value) USING BTREE;
ALTER TABLE semaphore DROP INDEX expire;
ALTER TABLE semaphore ADD INDEX expire (expire) USING BTREE;

But clearing the cache still took around 5 minutes and made the site unresponsive. Then I updated Drupal from 7.8 to 7.28 along with all our modules (most were 2+ years behind) and now clearing the cache takes around 15 to 30 seconds.

  • 2
    Worth noting you can also move the locking mechanism to memcache, so the database sempaphore table won't be used. See: memcache-lock.inc and memcache-lock-code.inc – David Thomas May 28 '14 at 0:02
  • 1
    Some deadlock fixes were put in 7.25 – mikeytown2 May 28 '14 at 0:02
  • I did move the locking mechanism to memcache as @DavidThomas and miketown2 suggested, as well. I haven't been able to detect any improvement from doing so. However, I did it after updating Drupal and running the above query. For now I'm leaving it this way. – donut Jun 10 '14 at 17:08

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.