Resolving DNS Resolution Delays in DigitalOcean's Managed MySQL

In our recent experience with a cloud-based application, we faced a perplexing challenge: significant latency in HTTP requests. This article delves into how we identified and resolved this issue, which ultimately stemmed from database connectivity in our DigitalOcean-managed MySQL environment.

Initial Observation

The first sign of trouble was a noticeable slowdown in our HTTP request responses. This was a critical concern, impacting the overall user experience. To uncover the underlying cause, we employed New Relic, an advanced performance monitoring tool.

Pinpointing the Cause with New Relic

With New Relic’s insights, we could drill down into the specifics of the latency. Initially, we suspected various potential culprits like server performance or application code. However, the data led us to a surprising revelation: the latency was primarily occurring during the initial connection to our DigitalOcean-managed MySQL database.

We observed a similar issue with our Redis database, as highlighted by this New Relic transaction screenshot. It shows that establishing a connection to Redis is taking over 5 seconds, indicating significant latency.

Understanding the Specific Challenge

Our application relies on DigitalOcean’s managed MySQL services and managed Redis instance, which involves connecting to a database via a hostname. The delay was traced back to the DNS resolution process, where this hostname was translated into an IP address - a necessary step for establishing a database connection.

Upon switching from the hostname to a direct IP address for the database connection, we were pleasantly surprised to find an immediate resolution to the latency issue. The connection speeds improved significantly, leading us to conclude that an effective solution would involve enhancing the speed of host DNS resolution.

Exploring Solutions:

1. Direct IP Address Usage: While using a direct IP address could bypass DNS lookups, this method posed reliability issues. In a managed service environment like DigitalOcean, IP addresses can change, making this a risky approach.

2. Local DNS Resolver Implementation: Another potential solution was setting up a local DNS resolver to cache DNS queries. This could reduce resolution times but also require additional infrastructure setup and maintenance.

3. Application-Level Caching Mechanism: Implementing caching within the application to store resolved IP addresses was considered. This approach required extra coding and was not ideal, given the dynamic nature of managed services.

4. Scheduled DNS Lookup Updates (Chosen Solution): We decided to automate DNS lookups for the database hostname, updating our application’s configuration periodically. This balanced approach provided both reliability and performance improvements.

Implementing Scheduled DNS Lookup Updates:

1. Creating a Script: We wrote a script to regularly perform DNS lookups for our DigitalOcean MySQL database hostname and update the local /etc/hosts file.

   In our case, we are using php-fpm docker container, so we need to adjust the script to change the container's local hosts file, this is the full version of the final script:

#!/bin/bash

# Define the Docker container and temporary files for DNS updates
container="docker_php_fpm_container"  # Replace with your container name
docker_hosts="hosts.docker"
docker_hosts_tmp="hosts.docker.tmp"

# Copy the current hosts file from the container
docker cp $container:/etc/hosts $docker_hosts

# Function to update hosts with new IP addresses
update_hosts() {
    local HOSTNAME="$1"
    local IP_ADDRESS=$(dig +short @"8.8.8.8" "$HOSTNAME")  # Using Google's DNS server for resolution
    local LOG_FILE="/path/to/log/update_hosts.log"  # Replace with your log file path

    # Check if the IP address is valid
    if [[ -n "$IP_ADDRESS" && "$IP_ADDRESS" =~ ^[0-9]+\.[0-9]+\.[0-9]+\.[0-9]+$ ]]; then
        # Update the hosts file if the hostname is found, otherwise add a new entry
        if grep -q "$HOSTNAME" $docker_hosts; then
            awk -v ip="$IP_ADDRESS" -v hostname="$HOSTNAME" '$2 == hostname { $1=ip; print } $2 != hostname' $docker_hosts > $docker_hosts_tmp
            mv $docker_hosts_tmp $docker_hosts
        else
            echo -e "\n$IP_ADDRESS $HOSTNAME" >> $docker_hosts
        fi
        # Log the update
        echo "Updated /etc/hosts for $HOSTNAME ($IP_ADDRESS) at $(date)" >> "$LOG_FILE"
    else
        # Log an error if the IP address is invalid
        echo "Error: Invalid IP address for $HOSTNAME" >> "$LOG_FILE"
    fi
}

# List of hostnames to update
host_names=(
    "hostname1.example.com"
    "hostname2.example.com"
    # Add additional hostnames here
)
# Iterate over the hostnames and update them
for host in "${host_names[@]}"; do
    update_hosts "$host"
done

# Move temporary file to actual if exists
if [ -e $docker_hosts_tmp ]; then
  mv $docker_hosts_tmp $docker_hosts
fi

# Copy updated hosts file back to the container and clean up
if [ -e $docker_hosts ]; then
  docker cp $docker_hosts $container:"/etc/$docker_hosts"
  docker exec $container truncate -s 0 /etc/hosts
  docker exec $container sh -c "cat "/etc/$docker_hosts" >> /etc/hosts"
  rm $docker_hosts
fi

1. Scheduling the Script with Cron: The script was scheduled to run at regular intervals using crontab, ensuring that our application always had the latest IP address without manual intervention.

2. Monitoring and Logging: We added logging to the script to track its execution and any changes in the DNS resolution, aiding in transparency and troubleshooting.

Conclusion

This experience highlighted the importance of considering all aspects of cloud-based application architecture, including external dependencies like managed database services. By addressing the DNS resolution delay through scheduled updates, we significantly reduced the latency in our HTTP requests from 5 seconds to less than 40 ms, enhancing the application's performance and reliability in the DigitalOcean environment.