25

I use JDBC to connect to MySQL. When it’s at localhost:3306, everything is OK.

But when I move my application to another computer in the intranet, and use <Intranet-IP>:3306 to connect to the MySQL database, it takes about 1 minute to connect to MySQL successfully. What’s up with this?

Chris
  • 6,914
  • 5
  • 54
  • 80
MemoryLeak
  • 7,322
  • 23
  • 90
  • 133

5 Answers5

44

Well it could be a DNS problem. You can disable DNS host name lookups by starting mysqld with the --skip-name-resolve option in the configuration file.

Read here for more details: http://dev.mysql.com/doc/refman/5.0/en/host-cache.html

Luc
  • 5,339
  • 2
  • 48
  • 48
Davide Ungari
  • 1,920
  • 1
  • 13
  • 24
  • I had this problem and indeed it was a reverse dns problem – acemtp Jan 26 '11 at 15:18
  • 16
    Just to be clear, `--skip-name-resolve` would be used as a command line option when starting up mysql, whereas `skip-name-resolve` (without the double hyphen in front) would be used in the configuration file. – JYelton Apr 30 '12 at 18:58
  • To follow the conventions in config file it should be `skip_name_resolve`, although I think with dashes it also works – NickSoft May 03 '16 at 08:47
  • If turning on skip-name-resolve doesn't work for you, it might not be the connection that's slowing you down. In my case I thought it was a DNS problem aswell, however adding skip-name-resolve didn't work, also CLI and Workbench executed the queries lightning fast. It turned out the person whom I inherited the code from thought it was a good idea to execute another query inside the while loop that looped through the 1200+ results. Which apparently didn't give any noticable lag if the DB server is on the same machine, but it does when you work with a remote server. – Ogier Schelvis Sep 26 '16 at 12:41
  • 1
    To finish my story (was reaching the comment character limit): I replaced the query with a LEFT JOIN in the main query and the page load dropped from 40 seconds to 0.5 seconds. The issue surfaced because I needed to temporarily access the production database from the development environment. – Ogier Schelvis Sep 26 '16 at 12:44
19

The --skip-name-resolve worked great for me.

To make it permanent, I just add this line at the end of file my.ini in the [mysql] section:

skip-name-resolve

And voilá! Transactions now fly!

baltaruiz
  • 191
  • 1
  • 4
9

For me it was this solution I found here, If IP6 connectivity is enabled, connection to "localhost" may be slow, instead use the ip address, 127.0.0.1. This worked for me.

my mysql slow to connect problem was solved by this solution

Community
  • 1
  • 1
Matt
  • 91
  • 1
  • 1
  • Noticed this problem while troubleshooting a slow MediaWiki installation. mysql_connect was taking almost a second. Changed the config from localhost to 127.0.0.1 and went from 1021ms to 7ms. Thanks! – jmgardn2 Jun 11 '13 at 20:08
  • Thanks! This just solved a problem question I posted. Who knew IPv6 would mess with recent MySQL versions so much. –  Jul 24 '13 at 16:58
1

firewalls, Internet, routing etc etc slows down your connection.

You should put your database on a intra net instead. Keep it local and behind your big firewall. You can of course have firewall and security between computers. I'd recommend that you didn't expose your mysql database connection to the Internet unfiltered that way.

Makach
  • 7,435
  • 6
  • 28
  • 37
  • ah, I see you edited your question :) good to see that you found an answer to your question. – Makach Aug 18 '09 at 10:26
0

comment / finding to a really REALLY old question.

background - typical: connecting from windows (various) to sql server on win or linux server slow ... multiple seconds (even from win client on VBox to server on same host)

skip-name-resolve, dns-bind - tried all sorts, regardless no effect.

Looked at the manual - connection string options - no time to learn what they mean, one-by-one set-and-try - as long as nothing breaks.

adding sslmode=none; to connection string ... connecting in literally less than 1/10th the time. sub-second connections, milliseconds!

Note: It's in a small office private LAN so don't GAS any security FUD ... It works ... I'll take it.

Rob
  • 444
  • 3
  • 10