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
.