Mysql - How To Improve Response Times By Configuring DNS Lookups

DNS lookups are often left unnoticed while trying to resolve MySQL response time issues. This guide aims at showing you two ways of tackling the configuration of DNS lookups on a Mysql database.

Prerequisites

Before you begin this guide you'll need the following:

• One Ubuntu 18.04 server is configured with a firewall and a non-root user. You can refer to the Initial Server Setup with Ubuntu 18.04 guide to configuring your server.
  
  
• A MySQL server is set up as detailed in this How To Install MySQL on Ubuntu 18.04 tutorial. Ensure you set a root password for the MySQL server.

Step 1 - Access Your Server And Mysql

ssh user_name@your_server_ip

Then, run the following command to log in to the MySQL server as the root user:

sudo mysql -u root -p

Step 2 - Disable Or Configure DNS Hostname Lookups

• Option 1 - Disable DNS Hostname Lookup
  
  If you have a lightweight architecture for example on a single server with a web server and database hosted on it you can just disable DNS hostname lookups completely:
  
  You can re-start MySQL with the option --skip-name-resolve for a temporary fix:
  

  
  sudo systemctl restart mysql option --skip-name-resolve
    
  

  You can also configure your changes more permanently after testing with the option above by modifying the my.cnf and adding the skip-name-resolve to the file.
  
  Use vi to edit the file:

  
  sudo vi /etc/mysql/my.cnf
    
  

  Add the following information to the file:

  
  [mysqld]
  .....
  .....
  skip-name-resolve
    
  

  Save the file changes and exit vi.
  
  
• Option 2 - Configuring the Host Cache
  
  If it is not an option to disable DNS hostname lookups because of a complex architecture, you can then try to optimize the host cache by configuring the host cache sizes. You can also configure the number of successive connection errors before host blocking occurs.
  
  Use vi to edit the file:

  
  sudo vi /etc/mysql/my.cnf
     

  Add the following information to the file:

  
  [mysqld]
  .....
  .....
  
  host_cache_size=200
  
  max_connect_errors=10000
    
  

  To change the size to 300 at runtime, do this:

  
  SET GLOBAL host_cache_size=300
    
  

  and to increase the number of successive connection errors at runtime:

  
  SET GLOBAL max_connect_errors=10000;
    
  

  Save the file changes and exit vi.

Conclusion

Configuring DNS lookups correctly can have a big impact on server response times, especially on high volume servers and it is often an unnoticed problem if not configured correctly. Hopefully, this guide will aid you in reducing server latency by either tunning or disabling DNS lookup round trips.