Analysis of mmm-master drift

Mmm-master drift analysis I. problem description
Online store applications occasionally experience slowness. Check and find that the writer role is drifting before master-backup.
Check mysql-log for no exceptions and no front-end nginx/php or mysql-proxy
Master show processlist500 multiple threads

II. Analysis
1. check the mmm-monitor code to check the mysql status and confirm the drifting conditions.
1). unable to link return "ERROR: Invalid host '$ host'" unless ($ peer_host); account password problems
2). return "UNKNOWN: Too connections when there are Too many connections! "
3). The select now () statement cannot be executed.
4). timeout

2. open the mmm-monitor debug log to confirm the detailed reason for the drift.
# Vim/etc/mysql-mmm/mmm_mon_log_3310.conf
Modify
Log4perl. logger = DEBUG, MMMLog
Log4perl. appender. MMMLog. Threshold = DEBUG
#/Etc/init. d/mysql-mmm-monitor restart 3310

3. wait for reproduction to obtain the cause of drift.
# Grep-n move mmm_mond_3310.log
143932: 10:54:24 INFO Removed role 'writer (192.168.201.10) 'from host 'db2'
10:54:21 DEBUG Received Answer: OK: Status applied successfully! | UP: 7818568.42
10:54:22 ERROR Check 'mysql' on 'db2 'has failed for 10 seconds! Message: ERROR: Connect error (host = 192.168..2 .2: 3310, user = dbslave )! Can't create a new thread (errno 11); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug
10:54:23 DEBUG Listener: Waiting for connection...
10:54:24 FATAL State of host 'db2 'changed from ONLINE to HARD_OFFLINE (ping: OK, mysql: not OK)
10:54:24 INFO Removing all roles from host 'db2 ':
10:54:24 INFO Removed role 'writer (192.168.201.10) 'from host 'db2'
10:54:24 DEBUG Sending command 'set _ STATUS (HARD_OFFLINE,) 'to db2 (192.168..2 .2: 43310)
10:54:24 DEBUG Received Answer: OK: Status applied successfully! | UP: 34710477.06
10:54:24 INFO Orphaned role 'writer (192.168.201.10) 'has been assigned to 'db3'
10:54:24 DEBUG Sending command 'set _ STATUS (ONLINE, reader (192.168.201.11), db3) 'to db216 (192.168.201.216: 43310)
10:54:24 DEBUG Received Answer: OK: Status applied successfully! | UP: 28460505.74

Reason for drift:
Message: ERROR: Connect error (host = 192.168..2 .2: 3310, user = dbslave )! Can't create a new thread (errno 11); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug

4. cause analysis
If you are not out of available memory
Insufficient memory?
The actual memory is sufficient to exclude. What is the maximum number of connections in the system?

Cause analysis:
It has nothing to do with mysql itself
The number of operating system connections is too small. (Centos6 has only 1024 max user processes by default. When mysql process is greater than this value, the problem of Can't create a new thread will occur)

Confirm system restrictions
# Su-s/bin/bash mysql
Bash-4.1 $ ulimit-
Core file size (blocks,-c) 0
Data seg size (kbytes,-d) unlimited
Scheduling priority (-e) 0
File size (blocks,-f) unlimited
Pending signals (-I) 256352
Max locked memory (kbytes,-l) 64
Max memory size (kbytes,-m) unlimited
Open file (-n) 65536
Pipe size (512 bytes,-p) 8
POSIX message queues (bytes,-q) 819200
Real-time priority (-r) 0
Stack size (kbytes,-s) 10240
Cpu time (seconds,-t) unlimited
Max user processes (-u) 1024
Virtual memory (kbytes,-v) unlimited
File locks (-x) unlimited

5. solve the problem
Modify
Test-f/etc/security/limits. d/90-nproc.conf & echo "mysql soft nproc 65536">/etc/security/limits. d/90-nproc.conf
Or:
# Vim/etc/bashrc
# Su-s/bin/bash mysql
Ulimit-u 65536

Confirm
# Su-s/bin/bash mysql
Bash-4.1 $ ulimit-
Core file size (blocks,-c) 0
Data seg size (kbytes,-d) unlimited
Scheduling priority (-e) 0
File size (blocks,-f) unlimited
Pending signals (-I) 256352
Max locked memory (kbytes,-l) 64
Max memory size (kbytes,-m) unlimited
Open file (-n) 65536
Pipe size (512 bytes,-p) 8
POSIX message queues (bytes,-q) 819200
Real-time priority (-r) 0
Stack size (kbytes,-s) 10240
Cpu time (seconds,-t) unlimit ed
Max user processes (-u) 65536
Virtual memory (kbytes,-v) unlimited
File locks (-x) unlimited

Diff: information about changes found
Max user processes (-u) 1024
Max user processes (-u) 65536
This is 64-bit. The 32-bit change is (when mysql soft nproc 65536 is also configured)
Max user processes (-u) 15036

6. move the write role back from backup
Mmm_control @ 3310 move_role writer db2