1

I have a mysql5.0 server that is very slow authenticating database requests. After a just a few requests there is a backlog of processes, showing waiting for login:

| Id | User                 | Host               | db   | Command | Time | State | Info             |
+----+----------------------+--------------------+------+---------+------+-------+------------------+
|  1 | root                 | 192.168.1.15:51725 | ouw  | Query   |    0 | NULL  | show processlist |
| 13 | unauthenticated user | 192.168.1.4:47782  | NULL | Connect | NULL | login | NULL             |
| 14 | unauthenticated user | 192.168.1.4:47783  | NULL | Connect | NULL | login | NULL             |
| 15 | unauthenticated user | 192.168.1.4:47784  | NULL | Connect | NULL | login | NULL             |
| 16 | unauthenticated user | 192.168.1.4:47785  | NULL | Connect | NULL | login | NULL             |
| 17 | unauthenticated user | 192.168.1.4:47786  | NULL | Connect | NULL | login | NULL             |
| 18 | unauthenticated user | 192.168.1.4:47787  | NULL | Connect | NULL | login | NULL             |
| 19 | unauthenticated user | 192.168.1.4:47788  | NULL | Connect | NULL | login | NULL             |
| 20 | unauthenticated user | 192.168.1.4:47789  | NULL | Connect | NULL | login | NULL             |
| 21 | unauthenticated user | 192.168.1.4:47790  | NULL | Connect | NULL | login | NULL             |
| 22 | unauthenticated user | 192.168.1.4:47791  | NULL | Connect | NULL | login | NULL             |
| 23 | unauthenticated user | 192.168.1.4:47792  | NULL | Connect | NULL | login | NULL             |
| 24 | unauthenticated user | 192.168.1.4:38101  | NULL | Connect | NULL | login | NULL             |
| 25 | unauthenticated user | 192.168.1.4:38102  | NULL | Connect | NULL | login | NULL             |
| 26 | unauthenticated user | 192.168.1.4:38103  | NULL | Connect | NULL | login | NULL             |
| 27 | unauthenticated user | 192.168.1.4:38104  | NULL | Connect | NULL | login | NULL             |
| 28 | unauthenticated user | 192.168.1.4:38105  | NULL | Connect | NULL | login | NULL             |
...

and so on. The earliest connections get slowly dropped, but if there are many connections they requestors eventually time out. The processlist entries stay in the table for quite a while. It is as if only one thread can authenticate at a time.

The server had crashed and was restarted. I double-checked the database credentials and ran myisamchk -f against the mysql and wordpress databases. (no errors other than improperly closed tables)

What could cause this slowdown?

dmaestro12
  • 172
  • 1
  • 7

1 Answers1

1

Have a look if DNS works. IMHO mysql tries to do a reverse-lookup the client ip. If this times out (UDP) it could lead to such effects

powo
  • 356
  • 2
  • 6
  • +1 for this, I've been bitten by it before. Setting an `/etc/hosts` record for `192.168.1.4` may speed things up dramatically. – ceejayoz Dec 14 '11 at 21:23
  • That's a lifesaver. I suspected DNS, but everything was OK. Didn't think about reverse lookup on the local lan segment, but editing /etc/hosts did fix it. mysqld must have had the ip/host cached before the crash and restart (from many months ago). – dmaestro12 Dec 14 '11 at 21:46
  • 1
    Oh,and needed to mysqladmin flush-hosts after the edit to clear connections. – dmaestro12 Dec 14 '11 at 21:47