MySQL  & Mongo DB problem • slow database connection

MySQL & Mongo DB problem • slow database connection


Wojtek Andrzejczak
Wojtek Andrzejczak
MySQL & Mongo DB problem • slow...

If it takes a few seconds to load a single page event, if you have a small page, the problem might be caused by something else, then your server configuration.

TL;DR

If your application is on the same machine as your database, set database connection as 127.0.0.1 instead of localhost.

My MySQL / Mongo DB is slow

Quoting popular meme: well yes, but actually no. To understand this problem, we need to go back to how our software and network works. In MySQL or Mongo DB, it is common that when in the configuration we set “localhost” as a host address for our database, it takes some time to connect. However, you can also change the host address to a specific IP, and then the connection takes milliseconds, not seconds.

TCP & DNS Lookup

Many database drivers, by default, are trying to identify your localhost IP by looking for IPv6. The problem here is that many operating systems do not have a configured localhost domain to handle IPv6. After database driver hits connection timeout for IPv6, it switches to IPv4, and then it finds the IP address for our localhost.

Never use hostnames?

So again, well yes, but actually no. Using hostnames is mandatory for network administration when we have server clusters with load balancing, redundancy — so very complicated setup. When one network node goes offline, we replace it under XY specific hostname, and we don’t need to reconfigure all other nodes configurations.

Solve MySQL slow connection

If you use PHP, just replace:
mysqli_connect('localhost','username','password','db');
with:
mysqli_connect('127.0.0.1','username','password','db');

Additionally, you can adjust configuration file my.ini by setting bind-address = 0.0.0.0
It allows TCP/IP connections on all server host IPv4 interfaces.

You can also enable a skip-networking option, which doesn’t listen for TCP/IP connections. Instead, it uses system sockets. This option is recommended when only local connections are allowed.

Solve MongoDB slow connection

In Mongo DB, there are fewer configuration options. Remember, if your application is on the same machine.
Use
mongoose.connect('mongodb://127.0.0.1:27017/test');
Instead of
mongoose.connect('mongodb://localhost:27017/test');

Useful links


  • Contact Me
    Contact me if you need advice or if you need help. Would you please choose the most suitable contact channel for you?

Subscribe to receive updates about new articles.

[newsletter_form button_color=”#E74C3C”]
Show Comments (1)

Comments

  • Anonymous
    Anonymous

    great tip. my issue was hostname not resolved since dns settings were bad / timing out. even localhost was not being resolved. great blog thank you

    • Article Author
    • Reply

Related Articles

Firebase Security •  avoid client-side issues
Troubleshooting

Firebase Security • avoid client-side issues

When you use Firebase Project you also create Google Cloud Console Project for all platforms. Generated API keys require a security audit to avoid problems.

Posted on by Wojtek Andrzejczak
7 facts about sandbox attribute • HTML5 iFrame
Troubleshooting

7 facts about sandbox attribute • HTML5 iFrame

Learn three facts about the iframe sandbox attribute. What HTML5 IFrame tag is and how you can use the sandbox attribute.

Posted on by Wojtek Andrzejczak