After enabling MySQL replication getting deadlocks when clearing cache

Question

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.

Answer 1

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

Answer 2

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.