8.12.4.2 DNS Lookup Optimization and the Host Cache

The server uses the host cache for several purposes:

For each new client connection, the server uses the client IP address to check whether the client host name is in the host cache. If so, the server refuses or continues to process the connection request depending on whether or not the host is blocked. If the host is not in the cache, the server attempts to resolve the host name. First, it resolves the IP address to a host name and resolves that host name back to an IP address. Then it compares the result to the original IP address to ensure that they are the same. The server stores information about the result of this operation in the host cache. If the cache is full, the least recently used entry is discarded.

The server handles entries in the host cache like this:

The server performs host name resolution using the gethostbyaddr() and gethostbyname() system calls.

To unblock blocked hosts, flush the host cache by executing a FLUSH HOSTS statement, a TRUNCATE TABLE statement that truncates the Performance Schema host_cache table, or a mysqladmin flush-hosts command. FLUSH HOSTS and mysqladmin flush-hosts require the RELOAD privilege. TRUNCATE TABLE requires the DROP privilege for the host_cache table.

It is possible for a blocked host to become unblocked even without flushing the host cache if activity from other hosts has occurred since the last connection attempt from the blocked host. This can occur because the server discards the least recently used cache entry to make room for a new entry if the cache is full when a connection arrives from a client IP not in the cache. If the discarded entry is for a blocked host, that host becomes unblocked.

Some connection errors are not associated with TCP connections, occur very early in the connection process (even before an IP address is known), or are not specific to any particular IP address (such as out-of-memory conditions). For information about these errors, check the Connection_errors_xxx status variables (see Section 5.1.10, “Server Status Variables”).

The host cache is enabled by default. The host_cache_size system variable controls its size, as well as the size of the Performance Schema host_cache table that exposes the cache contents. The cache size can be set at server startup and changed at runtime. For example, to set the size to 100 at startup, put these lines in the server my.cnf file:

[mysqld]
host_cache_size=200

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

Setting host_cache_size to 0, either at server startup or at runtime, disables the host cache. With the cache disabled, the server performs a DNS lookup every time a client connects.

Changing the cache size at runtime causes an implicit FLUSH HOSTS operation that clears the host cache, truncates the host_cache table, and unblocks any blocked hosts.

Using the --skip-host-cache option is similar to setting the host_cache_size system variable to 0, but host_cache_size is more flexible because it can also be used to resize, enable, and disable the host cache at runtime, not just at server startup. Starting the server with --skip-host-cache does not prevent changes to the value of host_cache_size, but such changes have no effect and the cache is not re-enabled even if host_cache_size is set larger than 0 at runtime.

To disable DNS host name lookups, start the server with the --skip-name-resolve option. In this case, the server uses only IP addresses and not host names to match connecting hosts to rows in the MySQL grant tables. Only accounts specified in those tables using IP addresses can be used. (A client may not be able to connect if no account exists that specifies the client IP address.)

If you have a very slow DNS and many hosts, you might be able to improve performance either by disabling DNS lookups with --skip-name-resolve or by increasing the value of host_cache_size to make the host cache larger.

To disallow TCP/IP connections entirely, start the server with the --skip-networking option.