Documentation Home
MySQL 5.5 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 27.2Mb
PDF (A4) - 27.2Mb
PDF (RPM) - 25.8Mb
HTML Download (TGZ) - 6.5Mb
HTML Download (Zip) - 6.6Mb
HTML Download (RPM) - 5.6Mb
Man Pages (TGZ) - 158.5Kb
Man Pages (Zip) - 262.1Kb
Info (Gzip) - 2.6Mb
Info (Zip) - 2.6Mb
Excerpts from this Manual

MySQL 5.5 Reference Manual  /  ...  /  DNS Lookup Optimization and the Host Cache

8.12.5.2 DNS Lookup Optimization and the Host Cache

The MySQL server maintains a host cache in memory that contains information about clients: IP address, host name, and error information.

Note

The server uses the host cache only for nonlocal TCP connections. It does not use the cache for TCP connections established using a loopback interface address (for example, 127.0.0.1 or ::1), or for connections established using a Unix socket file, named pipe, or shared memory.

Host Cache Operation

The server uses the host cache for several purposes:

  • By caching the results of IP-to-host name lookups, the server avoids doing a Domain Name System (DNS) lookup for each client connection. Instead, for a given host, it needs to perform a lookup only for the first connection from that host.

  • The cache contains information about errors that occur during the connection process. Some errors are considered blocking. If too many of these occur successively from a given host without a successful connection, the server blocks further connections from that host. The max_connect_errors system variable determines the permitted number of successive errors before blocking occurs (see Section B.6.2.6, “Host 'host_name' is blocked”).

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 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 or mysqladmin flush-hosts command.

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).

Host Cache Configuration

The host cache is enabled by default. To disable it, start the server with the --skip-host-cache option. With the cache disabled, the server performs a DNS lookup every time a client connects.

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 HOST_CACHE_SIZE define (default value: 128) and recompiling the server

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


User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
Sign Up Login You must be logged in to post a comment.