MariaDB가 모든 데이터베이스의 쿼리 처리를 중지합니다.

MariaDB가 모든 데이터베이스의 쿼리 처리를 중지합니다.

우리는 가지고 있다.mariadb server(현재v10.1, 하지만 똑같은 일이 일어났습니다.v10.0너무 큰 것은 없고, 단지 몇 GB의 데이터에 불과합니다.9 databases, 각각의 DB는 서로 다른 응용 프로그램에 대해 논리적으로 연결되어 있지 않습니다.모든 테이블이InnoDB.

때때로(보통)0-2 times일별)server모든 db 관련 쿼리 처리를 중지합니다(select,update,insert,alter, 등)를 모든 데이터베이스에 저장합니다.가장 오래된 쿼리가 도달할 때까지 중단/구축만 수행합니다.500s잠금 시간이 지나면 모든 것이 정상으로 돌아옵니다.이 상황들은 에 등록되어 있지 않습니다."deadlocks"부분의"show engine innodb status;". 그리고 syslog에는 다음과 같은 항목만 표시됩니다.

"mysqld: 150423 12:07:38 [경고] 연결 42 to db: 'ejd_im' 사용자: 'ejabberd' 호스트: 'localhost' (알 수 없는 오류)".

구성:

[mysqld]
skip-external-locking

transaction-isolation = READ-COMMITTED

bind-address            = 0.0.0.0

max_connections         = 30
connect_timeout         = 10
max_allowed_packet      = 16M
thread_cache_size       = 64
sort_buffer_size        = 128M
bulk_insert_buffer_size = 64M

concurrent_insert       = 2
read_buffer_size        = 2M
read_rnd_buffer_size    = 2M
join_buffer_size        = 32M
tmp_table_size          = 64M
max_heap_table_size     = 64M

query_cache_limit       = 4M
query_cache_size        = 64M
query_cache_type        = 1

log_warnings            = 2
slow_query_log          = 1
slow_query_log_file     = /var/log/mysql/mariadb-slow.log
long_query_time         = 2
log_slow_verbosity      = explain #query_plan

log_bin                 = /var/log/mysql/mariadb-bin
log_bin_index           = /var/log/mysql/mariadb-bin.index
binlog_format           = 'ROW'

sync_binlog             = 0
expire_logs_days        = 2
max_binlog_size         = 100M

default_storage_engine  = InnoDB
innodb_log_file_size    = 64M
innodb_buffer_pool_size = 8G
innodb_log_buffer_size  = 8M
innodb_file_per_table   = 1
innodb_open_files       = 1200
innodb_flush_method     = O_DIRECT
innodb_defragment       = 1
innodb_file_format      = Barracuda
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 2
innodb_log_checksum_algorithm = crc32
innodb_use_fallocate    = 1
innodb_autoinc_lock_mode= 2
innodb_ft_sort_pll_degree = 4

또한 가장 오래된 대기 트랜잭션과 관련하여 "엔진 in nodb status;"에서 출력의 일부를 캡처할 수 있었습니다.

---TRANSACTION 71290030, ACTIVE (PREPARED) 411 sec
mysql tables in use 1, locked 1
2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
MySQL thread id 153651, OS thread handle 0x7ff144ada700, query id 1459228 localhost nlb query end
REPLACE INTO apiTimings (`userId`, `lastAccess`, `lastIp`, `source`) VALUES ('909', '1429783180', 'xxx.xxx.xxx.xxx', '1')
Trx #rec lock waits 0 #table lock waits 0
Trx total rec lock wait time 0 SEC
Trx total table lock wait time 0 SEC
TABLE LOCK table `nlb`.`apiTimings` trx id 71290030 lock mode IX lock hold time 411 wait time before grant 0
RECORD LOCKS space id 484 page no 5 n bits 280 index `PRIMARY` of table `nlb`.`apiTimings` trx table locks 1 total table locks 27  trx id 71290030 lock_mode X locks rec but not gap loc
k hold time 411 wait time before grant 0
---TRANSACTION 71290022, ACTIVE (PREPARED) 412 sec
mysql tables in use 1, locked 1
2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
MySQL thread id 153644, OS thread handle 0x7ff1449b6700, query id 1459203 localhost nlb query end
REPLACE INTO apiTimings (`userId`, `lastAccess`, `lastIp`, `source`) VALUES ('963', '1429783179', 'xx.xx.xx.xx', '1')
Trx #rec lock waits 0 #table lock waits 0
Trx total rec lock wait time 0 SEC
Trx total table lock wait time 0 SEC
TABLE LOCK table `nlb`.`apiTimings` trx id 71290022 lock mode IX lock hold time 412 wait time before grant 0
RECORD LOCKS space id 484 page no 5 n bits 280 index `PRIMARY` of table `nlb`.`apiTimings` trx table locks 1 total table locks 27  trx id 71290022 lock_mode X locks rec but not gap lock hold time 412 wait time before grant 0
---TRANSACTION 71290019, ACTIVE 412 sec doing SYNC index
1 lock struct(s), heap size 360, 0 row lock(s), undo log entries 18987
Trx #rec lock waits 0 #table lock waits 0
Trx total rec lock wait time 0 SEC
Trx total table lock wait time 0 SEC
TABLE LOCK table "nlb"."FTS_0000000000000321_0000000000000724_INDEX_1" trx id 71290019 lock mode IX lock hold time 412 wait time before grant 0
---TRANSACTION 71290018, ACTIVE (PREPARED) 412 sec committing
2 lock struct(s), heap size 360, 0 row lock(s), undo log entries 3
MySQL thread id 153643, OS thread handle 0x7ff132d93700, query id 1459197 localhost nlb init
COMMIT
Trx #rec lock waits 0 #table lock waits 0
Trx total rec lock wait time 0 SEC
Trx total table lock wait time 0 SEC
TABLE LOCK table `nlb`.`posts` trx id 71290018 lock mode IX lock hold time 412 wait time before grant 0
TABLE LOCK table `nlb`.`dashboards` trx id 71290018 lock mode IX lock hold time 412 wait time before grant 0

도대체 무슨 일이 일어나고 있는지, 아니면 어떻게 진단해야 하는지 누가 말해줄 수 있나요?모든 데이터베이스에 영향을 미치는 이유는 무엇입니까?

편집: apiTiming 구조:

CREATE TABLE IF NOT EXISTS `apiTimings` (
  `userId` int(11) NOT NULL,
  `lastAccess` bigint(20) NOT NULL,
  `lastIp` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `source` int(11) NOT NULL,
  PRIMARY KEY (`userId`),
  KEY `source` (`source`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

다른show engine innodb status;:

---TRANSACTION 75459395, ACTIVE (PREPARED) 362 sec
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1184, 4 row lock(s), undo log entries 2
MySQL thread id 1077860, OS thread handle 0x7fcd388d1700, query id 10200793 xen xx.xx.xx.xx yyy query end
REPLACE iplock SET ip = 'xx.xx.xx.xx', count = '1', lut = '1430055393'
Trx #rec lock waits 0 #table lock waits 0
Trx total rec lock wait time 0 SEC
Trx total table lock wait time 0 SEC
TABLE LOCK table `main`.`iplock` trx id 75459395 lock mode IX lock hold time 362 wait time before grant 0
RECORD LOCKS space id 677 page no 9 n bits 744 index `ip` of table `main`.`iplock` trx table locks 1 total table locks 4  trx id 75459395 lock_mode X lock hold time 362 wait time before grant 0
RECORD LOCKS space id 677 page no 15 n bits 264 index `PRIMARY` of table `main`.`iplock` trx table locks 1 total table locks 4  trx id 75459395 lock_mode X locks rec but not gap lock hold time 362 wait time before grant 0
RECORD LOCKS space id 677 page no 9 n bits 744 index `ip` of table `main`.`iplock` trx table locks 1 total table locks 4  trx id 75459395 lock_mode X locks gap before rec lock hold time 362 wait time before grant 0
---TRANSACTION 75459393, ACTIVE (PREPARED) 362 sec
1 lock struct(s), heap size 360, 0 row lock(s), undo log entries 1
MySQL thread id 45, OS thread handle 0x7fcade563700, query id 10200790 localhost 127.0.0.1 ejabberd init
commit
Trx #rec lock waits 0 #table lock waits 0
Trx total rec lock wait time 0 SEC
Trx total table lock wait time 0 SEC
TABLE LOCK table `ejd_im`.`spool` trx id 75459393 lock mode IX lock hold time 362 wait time before grant 0
---TRANSACTION 75459390, ACTIVE 362 sec doing SYNC index
1 lock struct(s), heap size 360, 0 row lock(s), undo log entries 18453
Trx #rec lock waits 0 #table lock waits 0
Trx total rec lock wait time 0 SEC
Trx total table lock wait time 0 SEC
TABLE LOCK table "nlb"."FTS_0000000000000321_0000000000000724_INDEX_1" trx id 75459390 lock mode IX lock hold time 362 wait time before grant 0
---TRANSACTION 75459389, ACTIVE (PREPARED) 362 sec committing
2 lock struct(s), heap size 360, 0 row lock(s), undo log entries 3
MySQL thread id 1077859, OS thread handle 0x7fcd355d0700, query id 10200783 localhost nlb init
COMMIT
Trx #rec lock waits 0 #table lock waits 0
Trx total rec lock wait time 0 SEC
Trx total table lock wait time 0 SEC
TABLE LOCK table `nlb`.`posts` trx id 75459389 lock mode IX lock hold time 362 wait time before grant 0
TABLE LOCK table `nlb`.`dashboards` trx id 75459389 lock mode IX lock hold time 362 wait time before grant 0

언급URL : https://stackoverflow.com/questions/29821868/mariadb-stops-processing-queries-from-all-databases