Life in code, apps and OS's

Slow connections on Remote MySQL servers.

Posted on: November 2, 2007

Now a few of my projects are getting much bigger and the need for having dedicated Database servers are now more appealing than ever.

With MySQL when setting it up I noticed that when trying to access a remote database from an application server, it would takes ages to respond/connect to it.

After searching around the web, I have found what appears to be the solution…

It appears to be that the MySQL server lookups hostnames for authentication EVEN IF you are using for example: remoteuser@231.23.65.23 so to speed up remote connections to your remote database servers try adding the following line…

skip-name-resolve

To your MySQL configuration file (my.ini/my.cnf) and then be sure to reload (restart) the MySQL database server.  The ‘[mysqld]’ section of the my.cnf file now looks like this:

[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
skip-name-resolve

From Microsoft Windows this can be done from the ‘Services’ window in ‘Administrative tools’ or in Debian/Ubuntu entering this command:

/etc/init.d/mysql restart

The above addition will ONLY work if you are using IP Address or ‘%’ for the Database User accounts and will ofcourse disable the ability to use user accounts with the hostname eg. remoteuser@server2.example.com.

22 Responses to "Slow connections on Remote MySQL servers."

Thanks! This helped me a lot. I had suffered from slow connections after changing the MySQL server.

No worries barosl🙂

Thanks a lot! that helped a lot. I was struggling for past 8 hours on this.

[…] 为了避免缓慢的远程连接,还必须在[mysqld]段加上: skip-name-resolve […]

[…] Kemudian script PHP untuk uji cobanya adalah sebagai berikut: <?php var_dump(mysql_connect('192.168.1.8','nyoba','password')); mysql_close(); ?> Script ini disimpan dan dijalankan pada host web server. Ternyata koneksi berhasil. Sayangnya, proses eksekusi script berjalan begitu lambat. Padahal ini masih pada jaringan lokal tapi kok pelan bener ya? Abis searching di Internet, terus nemu artikel ini. […]

I googled for “mysql ubuntu slow connection”, got this blog post as the top result, and had my web application responding quickly in a matter of seconds. Great article!

Ohhh MATE! I have been so close to tearing my hair out for the last couple of hours then I found this. Life saver.

Thanks so much…
But, why the connection sometime still back to slow after few moment..?

Thank you so much! Me and my dev team are extremely grateful.

Thank you very much. It helps a lot !

Hi,
I tried this on linux, here are the results from my web application.
There are 10 pc’s 6 with windows 7 and 4 with winxp. Out of the 6 win7 3 have a slow connection and out of 4 winxp 2 has slow connection.
So out of 10 pc’s 5 pc’s access the application slowly. Is it to do with the configuration of mysql or the DNS or the network(this is ruled out as they all are connected to the same hub).

[…] Script PHP ini disimpan dan dijalankan pada host web server. Ternyata koneksi berhasil. Sayangnya, proses eksekusi script berjalan begitu lambat. Padahal ini masih pada jaringan lokal tapi kok pelan bener ya? Abis searching di Internet, terus nemu artikel ini. […]

It worked! Thanks!

Thanks a lot
You are saving my time

Incredible. That saved me so much pain I actually reactivated my old WordPress account just to post my reply. Thank you so much!!

It turns out this is a documented technique even in newer MySQL (I’m using 5.5 Community Edition): http://dev.mysql.com/doc/refman/5.5/en/host-cache.html

By the way, the reason this was a problem for me is that my local DNS server doesn’t provide reverse lookup entries for client machines – i.e., there’s no way to resolve their IP addresses to hostnames (the other way around works fine of course). In that case MySQL will try to re-cache the hostname on every call, and of course fail every time with a timeout, hence the ridiculously long delay.

Thank you very much. That helped a lot:)

Thanks! is really funcionally over windows xp

Best Regards,
Gilberto Gonzalez
Monterrey, México

Thank you, That helped a lot

[…] you indication about the database access speed. You can try to add “skip-name-resolve” (https://bobbyallen.wordpress.com/2007/11/02/slow-connections-on-remote-mysql-servers/) to your MySQL configuration file to see if the problem comes from DNS. […]

greaatttt .. solve my server problem, thanks

Dear. i have a separate application server and a separate mysql database server. when i want to access the application server to login from the local network or directly on the server machine. the application response is very slow. the database server response is normal. need help.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


  • How To Dyndns Ubuntu | Order Goods: […] DynDNS Client Setup on Ubuntu | Life in code, apps and OS’s – May 18, 2008  · 7 Responses to "DynDNS Client Setu
  • lizrandolph10886: Wonderful article! We are linking to this particularly great post on our website.nKeep up the great writing. Click
  • waseem Ahmad: Dear. i have a separate application server and a separate mysql database server. when i want to access the application server to login from the local
%d bloggers like this: