Slow connections on Remote MySQL servers.
Posted by: Bobby Allen on: November 2, 2007
- In: General | My Projects | UNIX & Linux
- Comment!
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.
14 Responses to "Slow connections on Remote MySQL servers."
April 21, 2009 at 5:32 am
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. [...]
July 2, 2010 at 7:15 pm
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!
August 17, 2010 at 11:18 am
Ohhh MATE! I have been so close to tearing my hair out for the last couple of hours then I found this. Life saver.
December 1, 2010 at 2:51 am
Thanks so much…
But, why the connection sometime still back to slow after few moment..?
9 | Ramūns Usovs
June 30, 2011 at 11:53 am
Thank you so much! Me and my dev team are extremely grateful.
12 | Arun Viswanath (@arunvishy09)
November 21, 2011 at 12:14 pm
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. [...]
Leave a Reply
Search
- How to give a standard Ubuntu User full root permissions.
- Install and configure webalizer on Ubuntu
- Installing Ubuntu with a Software RAID1 configuration
- How to add and remove user's in Ubuntu.
- DynDNS Client Setup on Ubuntu
- How to install Exim4 on Ubuntu as a smarthost
- VB.net 2008 Database connection test..
- Slow connections on Remote MySQL servers.
- Installing Samba & SWAT for easy Windows File Sharing on Ubuntu 6.06
- How to install Subversion with Web Access on Ubuntu
- uncledrella: It worked! Thanks!
- Remote MySQL Server | Andi Malik: [...] Script PHP ini disimpan dan dijalankan pada host web server. Ternyata koneksi berhasil. Sayangnya, proses eksekusi script berjalan begitu lambat
- Arun Viswanath (@arunvishy09): 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 ha
Categories
- General (29)
- Java Development (3)
- My Projects (44)
- Uncategorized (2)
- UNIX & Linux (60)
- VB .net (4)
- Zantastico (7)
May 16, 2008 at 11:08 am
Thanks! This helped me a lot. I had suffered from slow connections after changing the MySQL server.