Mysql – App server takes a long time to connect to MySQL Server

connectionMySQLremote

I have a strange problem that I couldn't figured out and would like some expert advice to handle this thing.

  1. My database is running on a seperate machine
  2. My application server tries to connect to login and it keeps on waiting for response
  3. New connection Thread is created by MySQL
  4. My application server keeps on waiting for login but does not get any response
  5. Connections pile up and MySQL reached the maximum connections within minutes (currently set at 100).

The issues seems to be the login but the strange thing is that I have no issue when the application runs on the same server as MySQL. Tried using mysql command from the application as well and it has the same issue. Any clue?

Below is some of the processes that I captured.

+-----+----------------------+---------------------+------+---------+------+-------+------------------+
| Id  | User                 | Host                | db   | Command | Time | State | Info |
+-----+----------------------+---------------------+------+---------+------+-------+------------------+
| 6   | root                 | localhost           | NULL | Query   | 0    | NULL  | show processlist |
| 114 | unauthenticated user | 192.168.10.30:47534 | NULL | Connect |      | login | NULL             |
| 115 | unauthenticated user | 192.168.10.30:47535 | NULL | Connect |      | login | NULL             |
| 116 | unauthenticated user | 192.168.10.30:47536 | NULL | Connect |      | login | NULL             |
| 117 | unauthenticated user | 192.168.10.30:47537 | NULL | Connect |      | login | NULL             |
| 118 | unauthenticated user | 192.168.10.30:47539 | NULL | Connect |      | login | NULL             |
| 119 | unauthenticated user | 192.168.10.30:47540 | NULL | Connect |      | login | NULL             |
| 120 | unauthenticated user | 192.168.10.30:47541 | NULL | Connect |      | login | NULL             |
| 121 | unauthenticated user | 192.168.10.30:47542 | NULL | Connect |      | login | NULL             |
| 122 | unauthenticated user | 192.168.10.30:47543 | NULL | Connect |      | login | NULL             |
| 123 | unauthenticated user | 192.168.10.30:47544 | NULL | Connect |      | login | NULL             |
| 124 | unauthenticated user | 192.168.10.30:47546 | NULL | Connect |      | login | NULL             |
| 125 | unauthenticated user | 192.168.10.30:47547 | NULL | Connect |      | login | NULL             |
| 126 | unauthenticated user | 192.168.10.30:47548 | NULL | Connect |      | login | NULL             |
| 127 | unauthenticated user | 192.168.10.30:47549 | NULL | Connect |      | login | NULL             |
| 128 | unauthenticated user | 192.168.10.30:47550 | NULL | Connect |      | login | NULL             |
| 129 | unauthenticated user | 192.168.10.30:47552 | NULL | Connect |      | login | NULL             |
| 130 | unauthenticated user | 192.168.10.30:47553 | NULL | Connect |      | login | NULL             |
| 131 | unauthenticated user | 192.168.10.30:47086 | NULL | Connect |      | login | NULL             |
| 132 | unauthenticated user | 192.168.10.30:47087 | NULL | Connect |      | login | NULL             |
| 133 | unauthenticated user | 192.168.10.30:47089 | NULL | Connect |      | login | NULL             |
| 134 | unauthenticated user | 192.168.10.30:47090 | NULL | Connect |      | login | NULL             |
| 151 | unauthenticated user | 192.168.10.30:47111 | NULL | Connect |      | login | NULL             |
| 152 | unauthenticated user | 192.168.10.30:47112 | NULL | Connect |      | login | NULL             |
| 153 | unauthenticated user | 192.168.10.30:47113 | NULL | Connect |      | login | NULL             |
| 154 | unauthenticated user | 192.168.10.30:35053 | NULL | Connect |      | login | NULL             |
| 155 | unauthenticated user | 192.168.10.30:35054 | NULL | Connect |      | login | NULL             |
| 156 | unauthenticated user | 192.168.10.30:35055 | NULL | Connect |      | login | NULL             |
+-----+----------------------+---------------------+------+---------+------+-------+------------------+

Best Answer

Can you log in without delay from the database server? If so, it is likely a network problem.

  • What network hardware sits between these devices?
  • Have the NICs been tried and tested on other subnets/connections?

If you cannot login quickly, check the db server logs (out of disk space, full /tmp dir, timeouts etc).

It could also be a DNS issue, although 20 minutes seems outrageous for a DNS lookup. You could try adding

skip-name-resolve

to your my.cnf to see if that helps.

Related Topic