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?

Improve this question

1 Answer 1

Reset to default
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

Improve this answer
3
  • +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, 2011 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, 2011 at 21:46
  • 1
    Oh,and needed to mysqladmin flush-hosts after the edit to clear connections.
    – dmaestro12
    Dec 14, 2011 at 21:47

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .