Understanding MySQL Host Cache and skip_name_resolve Impact on Performance

In a production environment with a master‑slave semi‑synchronous MySQL cluster, connections to the master were fast while connections to the slave experienced a ten‑second delay after the TCP three‑way handshake.

Packet capture revealed that the delay originated on the MySQL side; the slave’s configuration lacked the skip_name_resolve option. Adding this option and restarting the slave resolved the latency issue.

MySQL reverse resolution process

The client initiates a connection, the server creates a thread, checks the performance_schema.host_cache table for the hostname, and if not found, performs IP‑to‑hostname and hostname‑to‑IP lookups, comparing the results before proceeding to authentication.

1. mysql-client initiates connection;<br/>2. mysqld creates a thread;<br/>3. Thread checks performance_schema.host_cache;<br/>4. If present, proceeds to privilege check;<br/>5. If absent, resolves IP to hostname;<br/>6. Resolves hostname back to IP;<br/>7. Compares resolved IP with original;<br/>8. If match, caches entry and authenticates;<br/>9. If mismatch, returns error.

Only non‑local TCP connections use the host cache; connections via loopback or socket bypass it.

Purpose of the host cache

Caches IP‑to‑hostname lookup results, reducing DNS queries; the number of entries is controlled by host_cache_size.

Stores error information for failed connections; repeated errors from the same host can block further connections, governed by max_connect_errors.

Relevant parameters host_cache_size: sets the size of the host cache; a value of 0 disables caching, forcing DNS lookups on every connection. skip-host-cache: disables the host cache at runtime but cannot be changed dynamically and is deprecated in newer versions. skip_name_resolve: when enabled (ON), MySQL skips hostname resolution and requires all grant tables to use IP addresses only.

Viewing the host_cache table

Example query:

mysql> select * from performance_schema.host_cache\G<br/>*************************** 1. row ***************************<br/>IP: 10.186.61.21<br/>HOST: NULL<br/>HOST_VALIDATED: YES<br/>SUM_CONNECT_ERRORS: 0<br/>... (additional columns omitted for brevity) ...<br/>FIRST_SEEN: 2020-12-18 10:30:03<br/>LAST_SEEN: 2020-12-18 14:50:41<br/>FIRST_ERROR_SEEN: 2020-12-18 10:30:03<br/>LAST_ERROR_SEEN: 2020-12-18 14:46:37

Running FLUSH HOSTS clears the in‑memory host cache.

Impact on performance

When skip_name_resolve=OFF and host_cache_size=700, the initial DNS resolution and caching affect MySQL performance, but subsequent connections see only a slight improvement.

Conclusion

The observed behavior matches expectations: disabling hostname resolution or properly sizing the host cache reduces the latency caused by DNS lookups, leading to modest performance gains after the cache is populated.