Percona XtraDB -随机死锁

Question

随机地，我的数据库被完全锁定，查询挂起，它们堆积起来，直到得到“太多的连接”。

下面是我的mysql日志文件，我找不到与错误系统调用有关的任何内容。

Aug 28 14:06:36 db1a mysqld: 2015-08-28 14:06:36 11786 [Note] WSREP: Created page /var/data/mysql/gcache.page.000008 of size 134217728 bytes
Aug 28 14:07:06 db1a mysqld: 2015-08-28 14:07:06 11786 [Note] WSREP: Deleted page /var/data/mysql/gcache.page.000008
Aug 28 16:39:38 db1a mysqld: 2015-08-28 16:39:38 11786 [Note] WSREP: Created page /var/data/mysql/gcache.page.000009 of size 134217728 bytes
Aug 28 16:39:48 db1a mysqld: 2015-08-28 16:39:48 11786 [Note] WSREP: Deleted page /var/data/mysql/gcache.page.000009
Aug 28 19:42:07 db1a mysqld: 2015-08-28 19:42:07 11786 [Note] WSREP: Created page /var/data/mysql/gcache.page.000010 of size 134217728 bytes
Aug 28 19:42:08 db1a mysqld: 2015-08-28 19:42:08 11786 [Note] WSREP: Created page /var/data/mysql/gcache.page.000011 of size 134217728 bytes
Aug 28 19:42:10 db1a mysqld: 2015-08-28 19:42:10 11786 [Warning] WSREP: Failed to report last committed 758795619, -4 (Interrupted system call)
Aug 28 19:42:45 db1a mysqld: 2015-08-28 19:42:45 11786 [Warning] WSREP: Failed to report last committed 758795879, -4 (Interrupted system call)
Aug 28 19:43:07 db1a mysqld: 2015-08-28 19:43:07 11786 [Warning] WSREP: Failed to report last committed 758796011, -4 (Interrupted system call)
Aug 28 19:43:11 db1a mysqld: 2015-08-28 19:43:11 11786 [Warning] WSREP: Failed to report last committed 758796012, -4 (Interrupted system call)
Aug 28 19:43:49 db1a mysqld: 2015-08-28 19:43:49 11786 [Warning] Too many connections
Aug 28 19:43:49 db1a mysqld: 2015-08-28 19:43:49 11786 [Warning] Too many connections
Aug 28 19:43:50 db1a mysqld: 2015-08-28 19:43:50 11786 [Warning] Too many connections
Aug 28 19:43:51 db1a mysqld: 2015-08-28 19:43:51 11786 [Warning] Too many connections

MySQL开始向我的应用程序抛出此错误：

'SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction

版本: 5.6.24-72.2-56-log Percona XtraDB集群，Relationrel72.2，修订版43abf03，WSREP版本25.11，wsrep_25.11

my.cnf

# -- SERVER ---------------------------------------------- #

[mysqld_safe]

pid-file        = /var/data/run/mysqld.pid
socket          = /var/data/run/mysqld.sock
nice            = 0 
flush_caches    = 1 
numa_interleave = 1 
syslog

[mysqld]

user        = mysql
pid-file    = /var/data/run/mysqld.pid
socket      = /var/data/run/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/data/mysql
tmpdir      = /mnt/tmp

lc-messages-dir = /usr/share/mysql
skip-external-locking

default_time_zone = America/New_York

character-set-server    = utf8
collation-server        = utf8_general_ci

transaction-isolation   = READ-COMMITTED

# -- Cluster Settings -------------------------- #

# Path to Galera library
wsrep_provider = /usr/lib/libgalera_smm.so

# Cluster connection URL contains the IPs of node#1, node#2 and node#3
# It should be empty during bootstrap
wsrep_cluster_address=gcomm://10.0.200.8,10.0.210.7
#wsrep_cluster_address=gcomm://

# In order for Galera to work correctly binlog format should be ROW
binlog_format = ROW

# MyISAM storage engine has only experimental support
default_storage_engine = InnoDB

# This changes how InnoDB autoincrement locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode = 2

# We don't trust auto_increment control from galera when nodes are removed and added
# to the cluster, each node has a different offset.
wsrep_auto_increment_control = OFF
auto_increment_increment = 3
auto_increment_offset    = 1

# Node #1 address
wsrep_node_address = 10.0.200.7

# Cluster and node name
wsrep_cluster_name = db1
wsrep_node_name = db1a

# SST method
wsrep_sst_method            = xtrabackup-v2
wsrep_sst_auth              = "db1:XXXXXXXXXXXX"
wsrep_sst_receive_address   = 10.0.200.7
wsrep_sst_donor             = db1b,db1c

wsrep_slave_threads = 4

# ---------------------------------------------- #

# 
# * Timeouts
#
connect_timeout          = 5
lock_wait_timeout        = 3600
interactive_timeout      = 1800
wait_timeout             = 3600

#
# * Buffer
#
key_buffer_size      = 32M
sort_buffer_size     = 4M
read_rnd_buffer_size = 4M
join_buffer_size     = 4M

max_allowed_packet   = 64M
thread_stack         = 512K
thread_cache_size    = 12

table_open_cache     = 4096
open_files_limit     = 65536
max_heap_table_size  = 1G
tmp_table_size       = 1G

myisam-recover       = BACKUP
max_connections      = 500

#
# * Query Cache Configuration
#
# Cache size needs to be set to 0 before start with XtrabDB cluster
# It can ben changed during runtime
# http://www.percona.com/doc/percona-xtradb-cluster/5.6/limitation.html

query_cache_type  = 1
query_cache_limit = 10M
query_cache_size  = 0

#
# * Logging and Replication
#

# It has to be logged to FILE to work with XtraDB Cluster
# http://www.percona.com/doc/percona-xtradb-cluster/5.6/limitation.html
log_output = FILE

#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1

#log_error = /var/log/mysql/error.log

slow_query_log      = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time     = 10
#log-queries-not-using-indexes

#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.

server-id           = 1
log_bin             = /var/data/log/mysql-bin.log
expire_logs_days    = 10
max_binlog_size     = 512M
log_bin_trust_function_creators = 1
log-slave-updates

innodb_buffer_pool_size         = 48G
innodb_buffer_pool_instances    = 10
innodb_log_file_size            = 1G
innodb_log_buffer_size          = 256M
innodb_thread_concurrency       = 0
innodb_file_format              = Barracuda
innodb_flush_method             = O_DIRECT

innodb_lock_wait_timeout        = 60

innodb_read_io_threads          = 64
innodb_write_io_threads         = 32

innodb_ft_enable_stopword       = 0
innodb_ft_min_token_size        = 2

innodb_flush_log_at_trx_commit  = 0

innodb_open_files               = 4096

# NUMA improvement
innodb_buffer_pool_populate     = 1

innodb_file_per_table

#
# * Security
#
ssl-ca    = /etc/ssl/certs/CA.crt
ssl-cert  = /etc/mysql/keys/db1a.crt
ssl-key   = /etc/mysql/keys/db1a.key

Answer 1

我想不是只有你一个人处理这个问题。我们停止了多主复制，而是增强了RAID和XtraBackup，并且没有5.5或5.6的问题。

但是，我已经读到过，一旦您使用了wsrep和群集，这些问题就会悄然出现。不确定他们是否有解决方案，但您最好的选择可能是遵循这条主线：

• https://www.percona.com/forums/questions-discussions/percona-xtradb-cluster/11056-too-many-connections-brings-down-cluster

我希望这有帮助，这篇文章可能有最好的“从Percona工程师口中”命令来诊断您的问题。

要总结Percona工程师的技巧，当然要检查错误日志，并且：

show status like 'wsrep%';

还有文章中提到的一些工具。在我们所有运行DB软件的服务器中，我们有一件事是增加最大的开放文件限制(ulimit -n)等等。

我不确定这是否有帮助，但是Percona的Peter建议在授予特权时显式地设置max_connections。我没有尝试过这样做，但这对于尝试诊断问题所在，或者是否确实与集群相关，可能也是有用的。

mysql> GRANT USAGE ON *.* TO 'batchjob1'@'localhost'
    ->     WITH MAX_USER_CONNECTIONS 10;

• https://www.percona.com/blog/2014/07/29/prevent-mysql-downtime-set-max_用户_联系/

Answer 2

最近，我在Percona XtraDB集群5.6.21-70上做了一些广泛的基准测试，在针对集群运行事务(插入、更新、删除)时，我注意到在32个线程之后出现了退化。然后，我只对一个节点运行测试，32个线程的降级就消失了。不要将应用程序指向集群，而是将其指向一个专用节点。我们使用Linux进行故障转移，并且运行得很好。

Answer 3

这是正常的加雷拉，当交通变得太高。请记住，每个写入都需要在每个其他节点上复制，因此集群上的写入流量乘以N1，其中N是集群中的节点数。如果写入在节点之间均匀分布，过多的负载会导致非常频繁的集群争用，并最终导致事务积压，就像在您的情况下一样。

这只是CAP定理的数学确定性的乘积：https://en.wikipedia.org/wiki/CAP_定理

如果您可以做些什么来降低应用程序对DB的写入量，那么您应该这样做。如果您有任何方法来提高每个节点(以及它们之间的网络)的写入吞吐量，那么您也应该这样做。例如，我们在存储上安装了一个SSD缓存，从而大大减少了事务时间。这缓解了问题一段时间，但随着流量的增加，仍然不足以完全防止集群在负载下发生故障。

目前，我们使用HAProxy将所有的读和写都定向到单个节点。如果该节点失败，HAProxy将选择另一个节点并将所有通信量定向到该节点。在可靠性方面，这仍然比主从配置好，但在性能上也有相应的权衡。一个性能好处是，我们能够将只读通信(如备份、报告和一次性查询)定向到非主节点。这大大减少了这些任务对应用程序一般性能的影响。