Followup: On IPs, hostnames, and MySQL

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.01, 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!

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

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

9 thoughts on “Followup: On IPs, hostnames, and MySQL

  1. Pretty cool Jeremy! We were just talking about this the other day as it happens!

    One question – having the SHOW is great, but have you considered adding an INFORMATION_SCHEMA.HOSTS table as well? I’d love to see any new “SHOW” commands also have an INFORMATION_SCHEMA table along with them, especially >= 5.0.

    Great work though!

  2. Die wunderbare Welt von Isotopp

  3. I’m also suffering from this tiny fixed cache size and the fact that it’s contents are not visible. Are you planning on making a patch for the current GA 5.1 version?

    I agree with Mark, it seems best to put the content into INFORMATION_SCHEMA as output from the SHOW commands can not be manipulated as easily as the general SQL you can use on I_S.

    One final thought. The incoming requests often require one or more DNS requests in order to resolve the ip -> hostname. It might be useful to record these times as the data is put in the cache as we’ve noticed some unexpected variations in connection times that “appear” to be related to this. Without the status it’s hard to be sure so it would be nice to have them incorporated into the host table and thus viewable later.

  4. O MySQL e Resolução de Nomes | Blog: Alexandre M Almeida

  5. 『株式会社シンプル・アイ』 ブログ » MySQLで「Host ‘hogehoge.com’ is blocked because of many connection errors」が発生

What do you think?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s