Bug #22821 Adding "SHOW HOST_CACHE" patch from Jeremy Cole
Submitted: 29 Sep 2006 11:51 Modified: 20 Mar 2012 16:16
Reporter: Jacques Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S4 (Feature request)
Version:5.0, 5.1, 5.5 OS:Any
Assigned to: Marc Alff CPU Architecture:Any
Tags: Contribution
Triage: Needs Triage: D5 (Feature request) / R2 (Low) / E3 (Medium)

[29 Sep 2006 11:51] Jacques
Description:
Please seriously consider adding the http://jcole.us/patches/mysql/5.0/host_cache/ patch from Jeremy Cole to MySQL 5.0 and 5.1.  More information about what these patches do is available over at http://jcole.us/blog/archives/2006/09/26/followup-on-ips-hostnames-and-mysql/

There is currently no way of figuring out which hosts are in the host cache or tuning the number of hosts that can be cached via SET GLOBAL variables.

How to repeat:
Apply the patches :)

Suggested fix:
Add the patches over @ http://jcole.us/patches/mysql/5.0/host_cache/ namely:

 * http://jcole.us/patches/mysql/5.0/host_cache/00_hash_filo.patch
 * http://jcole.us/patches/mysql/5.0/host_cache/01_my_gethostbyaddr_r.patch
 * http://jcole.us/patches/mysql/5.0/host_cache/02_hostname_cache.patch
 * http://jcole.us/patches/mysql/5.0/host_cache/03_host_cache_size.patch
 * http://jcole.us/patches/mysql/5.0/host_cache/04_show_host_cache.patch
 * http://jcole.us/patches/mysql/5.0/host_cache/05_host_cache_metrics.patch
[29 Sep 2006 15:06] Valeriy Kravchuk
Thank you for a reasonable feature request.
[18 Oct 2006 21:18] Timothy Smith
This bug has duplicate Bug #17171.
[5 Jan 2007 16:46] Chad MILLER
I refused the new SHOW command, and instead added a new table to the information schema.
[5 Jan 2007 18:31] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/17680

ChangeSet@1.2359, 2007-01-05 13:31:05-05:00, cmiller@zippy.cornsilk.net +17 -0
  Bug#22821: Adding "SHOW HOST_CACHE" patch from Jeremy Cole
  
  This doesn't implement "SHOW HOST_CACHE" as a command, but it
  does add the same functionality as a table in the system infor-
  mation schema.
[9 Jan 2007 14:45] Michael Widenius
A couple of comments for this patch:

- FLUSH GLOBAL STATUS should reset the hostname cache statistics.
  To do this there is two possible solutions:
  - Move the metrics into STATUS_VAR
  - Add the hostname metrics structure into STATUS_VAR. In this case one must   however ensure that all variables are of type 'ulong'.

- Ensure that we follow the same coding standard in the new code as in the old.
  Some examples:
  - Space after 'if'
  - Only one statement per row (ie, break 'if () statement' into two lines
[10 Jul 2009 14:33] Leandro Morgado
Can we add an additional feature to this patch? The feature would allow us to FLUSH HOSTS just for a particular hostname. For example:

mysql> FLUSH HOSTS LIKE "%.example.org";

shell>  mysqladmin hosts-flush "%.example.org" (not sure what syntax to use here, perhaps regexps)
[22 Aug 2009 9:26] Konstantin Osipov
Suggest to use the patch but make an information schema table instead.
[19 Apr 2010 8:53] Shane Bester
worklog here: http://forge.mysql.com/worklog/task.php?id=5259
[2 May 2011 8:03] Valeriy Kravchuk
Bug #59404 was marked as a duplicate of this one.
[20 Mar 2012 16:16] Paul Dubois
Noted in 5.6.5 changelog.

MySQL now provides improved access to the host cache, which contains
client host name and IP address information and is used to avoid DNS
lookups. These improvements have been implemented: 

* A host_cache Performance Schema table exposes the contents of the
  host cache so that it can be examined using SELECT statements. The
  Performance Schema must be enabled or this table is empty. 

  If you upgrade to this release of MySQL from an earlier version, you
  must run mysql_upgrade (and restart the server) to incorporate this
  change into the performance_schema database. 

* The cache size is configurable using the host_cache_size system
  variable. Setting the size to 0 disables the host cache.This is
  similar to starting the server with --skip-host-cache, but
  host_cache_size is more flexible because it can also be used to
  resize, enable, or disable the host cache at runtime, not just at
  server startup. If you start the server with --skip-host-cache to
  disable the host cache, it cannot be re-enabled at runtime. 

* There are Connection_errors_xxx status variables that provide
  information about the nature of connection errors and that can help
  diagnose connection problems. 

Improved access to host cache contents makes it possible to answer
questions such as how many hosts are cached, or how close hosts are
to being blocked (by checking whether the
host_cache.SUM_CONNECT_ERRORS column is approaching the value of the
max_connect_errors system variable).
[21 Mar 2012 10:07] Leandro Morgado
Seems like this has been asked in a few different places. These are the ones I found:

http://bugs.mysql.com/bug.php?id=24906
http://bugs.mysql.com/bug.php?id=45817
http://bugs.mysql.com/bug.php?id=50963