Rechercher dans le manuel MySQL
8.12.4.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. The Performance Schema
host_cache
table exposes the
contents of the host cache so that it can be examined using
SELECT
statements. This may
help you diagnose the causes of connection problems. See
Section 26.12.18.1, “The host_cache Table”.
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.4.2.5, “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 handles entries in the host cache like this:
When the first TCP client connection reaches the server from a given IP address, a new cache entry is created to record the client IP, host name, and client lookup validation flag. Initially, the host name is set to
NULL
and the flag is false. This entry is also used for subsequent client TCP connections from the same originating IP.If the validation flag for the client IP entry is false, the server attempts an IP-to-host name-to-IP DNS resolution. If that is successful, the host name is updated with the resolved host name and the validation flag is set to true. If resolution is unsuccessful, the action taken depends on whether the error is permanent or transient. For permanent failures, the host name remains
NULL
and the validation flag is set to true. For transient failures, the host name and validation flag remain unchanged. (In this case, another DNS resolution attempt occurs the next time a client connects from this IP.)If an error occurs while processing an incoming client connection from a given IP address, the server updates the corresponding error counters in the entry for that IP. For a description of the errors recorded, see Section 26.12.18.1, “The host_cache Table”.
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_
status variables (see
Section 5.1.10, “Server Status Variables”).
xxx
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.
Document created the 26/06/2006, last modified the 26/10/2018
Source of the printed document:https://www.gaudry.be/en/mysql-rf-host-cache.html
The infobrol is a personal site whose content is my sole responsibility. The text is available under CreativeCommons license (BY-NC-SA). More info on the terms of use and the author.
References
These references and links indicate documents consulted during the writing of this page, or which may provide additional information, but the authors of these sources can not be held responsible for the content of this page.
The author This site is solely responsible for the way in which the various concepts, and the freedoms that are taken with the reference works, are presented here. Remember that you must cross multiple source information to reduce the risk of errors.