2

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             |
+-----+----------------------+---------------------+------+---------+------+-------+------------------+
splattne
  • 28,348
  • 19
  • 97
  • 147
chouputra
  • 23
  • 1
  • 4

2 Answers2

2

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.

Andy
  • 5,190
  • 23
  • 34
  • I also found out the same problem occured to the NFS. skip-name-resolve temporarily resolves the problem for now and I have added few things to /etc/hosts on every servers. It is still bugging me on the root cause of the problem as it was working fine before until yesterday. – chouputra Aug 26 '09 at 02:00
1

is your dns set up correctly? looks like you don't have reverse DNS for 192.168.10.30 at least.

alternatively, if you don't want to/can't set up rdns, have you granted access to 'username'@'192.168.10.30'?

cas
  • 6,653
  • 31
  • 34
  • 1) I use IP to connect directly from the app server (192.168.10.30) to the MySQL Server (192.168.10.32). 2) I have granted access for root for all access and some users to access from 192.168.10.30 and it just takes forever to login. 3) if i run "mysql -h 192.168.10.32 -u root -p" it will eventually login after sometimes (may take 20 minutes or so) – chouputra Aug 25 '09 at 10:59
  • as andy says, 20 mins for DNS lookup sounds outrageous...but i'm still thinking it's likely to be a DNS issue. what's in your mysql server's resolv.conf? can it resolve hostnames at all? how long does it take to resolve a hostname or ip address from the command line? finally, if you don't have one already, a local dns server is fairly easy to set up and definitely worth having on a network. – cas Aug 25 '09 at 11:26