Back in April, I wrote On IPs, hostnames, and MySQL, which described the (sometimes surprising) ways in which MySQL deals with IP addresses, hostnames, and privileges, as well as some basics about the host cache itself. In a footnote to that post, I mentioned a patch I had written against MySQL 4.1 to give some more visibility into the host cache.

Over the past two days, I have worked on porting that patch to MySQL 5.0^{1, 2}, and making some fairly large improvements to it. The patch implements a few things:

1. Configurable Size — Without the patch, the size of the cache is fixed at 128 entries, and can only be changed by changing a #define and recompiling. You may now tune the size of the host cache using SET GLOBAL host_cache_size=X.
2. Metrics — You may now see how well the cache is performing using SHOW GLOBAL STATUS LIKE 'Host%'. (See example below.)
3. Display — You may now see the complete contents of the cache using SHOW HOST CACHE. (See example below.)

The above new features should give MySQL DBAs much better insight into what’s happening in their MySQL server, in an area where there has historically been very little information and a lot of misunderstandings.

Now, for the examples:

SHOW GLOBAL STATUS LIKE 'Host%'

mysql> show status like 'host%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| Host_cache_free    | 127   | 
| Host_cache_hits    | 2     | 
| Host_cache_inserts | 1     | 
| Host_cache_misses  | 1     | 
| Host_cache_prunes  | 0     | 
| Host_cache_used    | 1     | 
+--------------------+-------+
6 rows in set (0.01 sec)

SHOW HOST CACHE

mysql> show host cache;
+-----------+----------+--------+------+
| IP        | Hostname | Errors | Hits |
+-----------+----------+--------+------+
| 10.0.0.22 | hiriko   |      0 |    2 | 
+-----------+----------+--------+------+
1 row in set (0.00 sec)

Enjoy!

¹ Hopefully this will be accepted into MySQL 5.0. Brian?

² The patch is against mysql-5.0.25-nightly-20060823 specifically.