0

issue description

MySQL failed to connect, and too many connections expcetion is thrown. But after that, port is open, mysqld failed to respond packets including version and plugins of password,mysql client is blocked and never recover again.

mysql error info

  • version: percona-server-5.7.24-27
  • max_connections=5000
  • MySQL error-log
  • tcpdump while mysql is trying to connect server.
  • strace info ( strace -tt -T -v -f -p 15179 -o output.log)
17264 19:48:42.143662 set_robust_list(0x7fa5a22c39e0, 24 <unfinished ...>
15179 19:48:42.143673 <... clone resumed> child_stack=0x7fa5a22c2f30, flags=CLONE_VM|CLONE_FS|CLONE_FILES|CLONE_SIGHAND|CLONE_THREAD|CLONE_SYSVSEM|CLONE_SETTLS|CLONE_PARENT_SETTID|CLONE_CHILD_CLEARTID, parent_tidptr=0x7fa5a22c39d0, tls=0x7fa5a22c3700, child_tidptr=0x7fa5a22c39d0) = 17264 <0.001116>
17264 19:48:42.144164 <... set_robust_list resumed> ) = 0 <0.000497>
17264 19:48:42.144535 gettid( <unfinished ...>
17264 19:48:42.144879 <... gettid resumed> ) = 17264 <0.000337>
17264 19:48:42.145227 futex(0x1e01e80, FUTEX_WAIT_PRIVATE, 2, NULL <unfinished ...>
  • strace -f -p 15179 > 20minutes.log
9107  18:50:39.688588 futex(0x1e01e80, FUTEX_WAIT_PRIVATE, 2, NULL <unfinished ...>
8926  18:50:39.688703 futex(0x1e01e80, FUTEX_WAIT_PRIVATE, 2, NULL <unfinished ...>
8738  18:50:39.688719 futex(0x1e01e80, FUTEX_WAIT_PRIVATE, 2, NULL <unfinished ...>
8492  18:50:39.688730 futex(0x1e01e80, FUTEX_WAIT_PRIVATE, 2, NULL <unfinished ...>
8203  18:50:39.688742 futex(0x1e01e80, FUTEX_WAIT_PRIVATE, 2, NULL <unfinished ...>
8106  18:50:39.688752 futex(0x1e01e80, FUTEX_WAIT_PRIVATE, 2, NULL <unfinished ...>
7983  18:50:39.688763 futex(0x1e01e80, FUTEX_WAIT_PRIVATE, 2, NULL <unfinished ...>
7753  18:50:39.688774 futex(0x1e01e80, FUTEX_WAIT_PRIVATE, 2, NULL <unfinished ...>
7353  18:50:39.688785 futex(0x1e01e80, FUTEX_WAIT_PRIVATE, 2, NULL <unfinished ...>
7339  18:50:39.688796 futex(0x1e01e80, FUTEX_WAIT_PRIVATE, 2, NULL <unfinished ...>
7255  18:50:39.688806 futex(0x1e01e80, FUTEX_WAIT_PRIVATE, 2, NULL <unfinished ...>
7205  18:50:39.688817 futex(0x1e01e80, FUTEX_WAIT_PRIVATE, 2, NULL <unfinished ...>
7187  18:50:39.688827 futex(0x1e01e80, FUTEX_WAIT_PRIVATE, 2, NULL <unfinished ...>
7157  18:50:39.688836 futex(0x1e01e80, FUTEX_WAIT_PRIVATE, 2, NULL <unfinished ...>
7104  18:50:39.688846 futex(0x1e01e80, FUTEX_WAIT_PRIVATE, 2, NULL <unfinished ...>
7055  18:50:39.688856 futex(0x1e01e80, FUTEX_WAIT_PRIVATE, 2, NULL <unfinished ...>
7028  18:50:39.688871 futex(0x1e01e80, FUTEX_WAIT_PRIVATE, 2, NULL <unfinished ...>
  • lsof -p 15179 > lsof.15179.log, 4410 lines includes "protocol: TCP"
mysqld  15179 mysql 5254u  sock                0,7          0t0 111363206 protocol: TCP
mysqld  15179 mysql 5255u  sock                0,7          0t0 111357243 protocol: TCP
mysqld  15179 mysql 5256u  sock                0,7          0t0 111357244 protocol: TCP
mysqld  15179 mysql 5257u  sock                0,7          0t0 111363207 protocol: TCP
mysqld  15179 mysql 5258u  sock                0,7          0t0 111363208 protocol: TCP
mysqld  15179 mysql 5259u  sock                0,7          0t0 111353396 protocol: TCP
mysqld  15179 mysql 5260u  sock                0,7          0t0 111356603 protocol: TCP
mysqld  15179 mysql 5261u  sock                0,7          0t0 111356604 protocol: TCP
mysqld  15179 mysql 5262u  sock                0,7          0t0 111359747 protocol: TCP
mysqld  15179 mysql 5263u  sock                0,7          0t0 111356606 protocol: TCP
mysqld  15179 mysql 5264u  sock                0,7          0t0 111357250 protocol: TCP
mysqld  15179 mysql 5265u  sock                0,7          0t0 111359748 protocol: TCP
mysqld  15179 mysql 5266u  sock                0,7          0t0 111360201 protocol: TCP
mysqld  15179 mysql 5267u  sock                0,7          0t0 111360202 protocol: TCP
mysqld  15179 mysql 5268u  sock                0,7          0t0 111357251 protocol: TCP
mysqld  15179 mysql 5269u  sock                0,7          0t0 111363211 protocol: TCP
mysqld  15179 mysql 5270u  sock                0,7          0t0 111362371 protocol: TCP
mysqld  15179 mysql 5271u  sock                0,7          0t0 111354590 protocol: TCP
mysqld  15179 mysql 5272u  sock                0,7          0t0 111363212 protocol: TCP
mysqld  15179 mysql 5273u  sock                0,7          0t0 111354591 protocol: TCP
  • netstat includes many close_wait connections;

  • top -Hbp 15179 -n1 | wc -l, 5000+ threads of mysqld

source code analyzing

LOCK_thread_cache is not released as usual. mysql_mutex_lock(&LOCK_thread_cache);, but i don't why.

3
  • I wouldn't jump to the conlusion that is LOCK_thread_cache, especially at the URL mentioned where there is only a small bit of maths before its unlocked again. If you've reached the max_connections why are you expecting more to succeed? If you kill off the number of connections until show processlist is < 5000, can you make new connections?
    – danblack
    Apr 15, 2019 at 4:52
  • @danblack , as i have mentioned in the post, mysql client failed to handshake with mysql server, so there's no "too many connections exception" after some time, mysql server didn't handle the connection in the auth function as usual. client is blocked.
    – Jianqing
    Apr 15, 2019 at 10:31
  • there is one workaround that can change max_connections value; percona.com/blog/2010/03/23/too-many-connections-no-problem. I have change it to 50 000. So it's not the max_connections issue. Maybe it's not LOCK_thread_cache issue, but it might be other lock ,as you can find futex system call , such as "9107 18:50:39.688588 futex(0x1e01e80, FUTEX_WAIT_PRIVATE, 2, NULL <unfinished ...>", and there's no "FUTEX_WAKE_OP_PRIVATE" in 20 minutes' stracing file.
    – Jianqing
    Apr 15, 2019 at 10:38

1 Answer 1

1

@danblack
https://bugs.mysql.com/bug.php?id=91941,
https://bugs.mysql.com/bug.php?id=92108,
It seems it's a known issue. I'm trying to reproduce the issue described in the bug.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.