10

I have a MySQL 5.0.75 server running on my Linux notebook to which I want to connect from another machine in the local network.

This connection takes 5-6 seconds:

mysql -h 172.22.65.101 -u myuser -p123

A ping to the MySQL host:

PING 172.22.65.101 (172.22.65.101) 56(84) bytes of data.
64 bytes from 172.22.65.101: icmp_seq=1 ttl=64 time=0.799 ms
64 bytes from 172.22.65.101: icmp_seq=2 ttl=64 time=0.000 ms
64 bytes from 172.22.65.101: icmp_seq=3 ttl=64 time=6.43 ms
64 bytes from 172.22.65.101: icmp_seq=4 ttl=64 time=0.000 ms
64 bytes from 172.22.65.101: icmp_seq=5 ttl=64 time=3.81 ms
64 bytes from 172.22.65.101: icmp_seq=6 ttl=64 time=0.706 ms
^C
--- 172.22.65.101 ping statistics ---
6 packets transmitted, 6 received, 0% packet loss, time 5027ms
rtt min/avg/max/mdev = 0.000/1.959/6.437/2.383 ms

Any Ideas? When I monitor the connection with SHOW PROCESSLIST; on the MySQL host, I can see that the command is "connect" and the user is "unauthenticated user". This lasts until the connection is established. (The user is then displayed as "myuser" and the command is "sleep")

I am a developer and need your suggestions about how to find the bottleneck!

My my.cnf on the host:

[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice  = 0

[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
key_buffer = 16M
max_allowed_packet = 16M
thread_stack = 128K
thread_cache_size = 8
myisam-recover = BACKUP
query_cache_limit = 1M
query_cache_size = 16M
expire_logs_days = 10
max_binlog_size = 100M
skip-federated

[mysqldump]
quick
quote-names
max_allowed_packet = 16M

[isamchk]
key_buffer = 16M

CLient:

mysql  Ver 14.12 Distrib 5.0.51a, for debian-linux-gnu (i486) using readline 5.2

Server:

mysql  Ver 14.12 Distrib 5.0.75, for debian-linux-gnu (i486) using readline 5.2
chaos
  • 7,463
  • 4
  • 33
  • 49
Lennart
  • 203
  • 2
  • 5

1 Answers1

17

Probably you're lagging on an attempt to retrieve and verify reverse DNS of the connecting host. You can test this by turning on skip_name_resolve in the server's my.cnf, [mysqld] section.

If it is in fact the case (demonstrated by that parameter eliminating the delay), then you can solve the problem by either setting up DNS properly (forward and reverse) for the client, or by running with skip_name_resolve all the time (which means you can't use hostnames in your GRANT tables).

chaos
  • 7,463
  • 4
  • 33
  • 49
  • That fixed it! I defined skip_name_resolve in the my.cnf of my MySQL host, restarted MySQL and the problem was resolved. I owe you some beer. :) – Lennart May 29 '09 at 20:09
  • Glad to be of service. :) – chaos May 29 '09 at 20:09
  • 1
    great, thank you! just to be clear though (in case anyone messes up like me), it's simply "skip_name_resolve" on one line, not "skip_name_resolve=1" or anything... otherwise your service won't start! – James Crowley Sep 18 '10 at 18:05