Navigation

MySQL & Mongo DB problem • slow database connection


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

Subscribe to receive updates about new articles.

Show Comments (1)

Comments

  • 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

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
Troubleshooting

HTTP Cache-Control • what should you know

Caching resources of our application or website is very important. We want to deliver content to the user in the shortest possible time.

Posted on by Wojtek Andrzejczak