MySQL Bugs: #70552: host_cache_size value is ignored

Bug #70552	host_cache_size value is ignored
Submitted:	8 Oct 2013 10:54	Modified:	10 Oct 2013 17:27
Reporter:	Moritz Schuepp	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Options	Severity:	S3 (Non-critical)
Version:	5.6.13	OS:	Linux
Assigned to:	Marc ALFF	CPU Architecture:	Any
Tags:	capped, host_cache, performance_schema

Description:
The host_cache_size value, introduced in MySQL 5.6.5, is ignored by the server. The host_cache table only holds 128 entries. 

First i checked the contects of the host_cache on the MySQL-host:

-------
root@host [performance_schema]> select count(*) from host_cache;
+----------+
| count(*) |
+----------+
|      128 |
+----------+
1 row in set (0.00 sec)

root@host [performance_schema]> show variables like 'host_cache_size';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| host_cache_size | 2000  |
+-----------------+-------+
1 row in set (0.00 sec)

root@master [performance_schema]> select * from host_cache where HOST like 'foo.example.com';
Empty set (0.00 sec)
--------------

No entry for a specific server in the host_cache. So i made a connection attempt from foo.example.com:

--------------
[root@foo.example.com ~]# mysql -h $MYSQL_HOST -p tcp
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'foo.example.com' (using password: NO)
--------------

On the mysql-host foo.example.com now shows up in the host_cache:

--------------
root@host [performance_schema]> select HOST,FIRST_SEEN,LAST_SEEN from host_cache where HOST like 'foo.example.com';
+---------------------------------+---------------------+---------------------+
| HOST                            | FIRST_SEEN          | LAST_SEEN           |
+---------------------------------+---------------------+---------------------+
| foo.example.com | 2013-10-08 12:51:16 | 2013-10-08 12:51:16 |
+---------------------------------+---------------------+---------------------+
1 row in set (0.00 sec)
---------------

But the host_cache still did not grew up to 129:

--------------
root@host [performance_schema]> select count(*) from host_cache;
+----------+
| count(*) |
+----------+
|      128 |
+----------+
1 row in set (0.00 sec)
---------------

How to repeat:
1. Start mysql with a host_cache_size higher than 128, for example 2000. 

2. Connect from 129 different hosts to the mysql server. 

One host entry will be removed, the new one added and the table performance_schema.host_cache will still hold 128 rows, not more.

What I'm interested in testing is whether the host cache is properly sized for host_cache_size values > 128.  For example, with a host_cache_size = 2000, is it possible to block a given host (by exceeding max_connect_errors using telnet or something similar) and have the host remain blocked while not reported in P_S.HOST_CACHE.  If so, that would indicate that the P_S.HOST_CACHE representation of the actual host cache is truncated, but I see nothing in a quick (albeit uninformed) code review that suggests that this is the case.  My own experience here has been that P_S does a very effective job of reflecting actual behavior of the host cache - including exposing bugs and problematic behavior (e.g., Bug#69807).  If P_S.HOST_CACHE reports 128 entries in the host cache, I tend to believe it.

I haven't had a chance yet to test host_cache_size > 128, but will work on that.

I should note that testing with host_cache_size = 1 *does* work, in that:

* P_S.HOST_CACHE table never grows beyond 1 row
* A blocked host is unblocked when a new host is cached (causing the only other host to fall out of the cache).

Still speculation, but it seems that host_cache_size values < 128 are honored, but possibly that the host cache is capped at 128.

Observations from more comprehensive testing:

1.  The host cache is initialized with a size of 128, regardless of host_cache_size definition.  For example, when started with a my.cnf file containing host_cache_size = 75, the following is observed:

[oracle@oraclelinux6 ~]$ mysql -uroot -e"SELECT COUNT(*) hosts, SUM(IF(sum_connect_errors > 1, 1, 0)) with_errors FROM performance_schema.host_cache;SELECT @@global.host_cache_size;"
+-------+-------------+
| hosts | with_errors |
+-------+-------------+
|   128 |           0 |
+-------+-------------+
+--------------------------+
| @@global.host_cache_size |
+--------------------------+
|                       75 |
+--------------------------+

Likewise, when started with a value of 2000, the following results:

+-------+-------------+
| hosts | with_errors |
+-------+-------------+
|   128 |           0 |
+-------+-------------+
+--------------------------+
| @@global.host_cache_size |
+--------------------------+
|                     2000 |
+--------------------------+

2.  Dynamically setting host_cache_size variable seems to trigger the expected behavior.  For example:

mysql -uroot -e"SET @@global.host_cache_size = 200;"

./test.sh

mysql -uroot -e"SELECT COUNT(*) hosts, SUM(IF(sum_connect_errors > 1, 1, 0)) with_errors FROM performance_schema.host_cache;SELECT @@global.host_cache_size;"

+-------+-------------+
| hosts | with_errors |
+-------+-------------+
|   200 |         200 |
+-------+-------------+
+--------------------------+
| @@global.host_cache_size |
+--------------------------+
|                      200 |
+--------------------------+

So the workaround is to issue SET GLOBAL host_cache_size = X to properly size the host cache size.  You can also force the dynamic command to be issued at startup with an init-script in the server options file:

[mysqld]
...
init-file=/path/to/set-hostcache-size.sql

where set-hostcache-size.sql contains:

SET GLOBAL host_cache_size = @@global.host_cache_size;

The above is just referenced as a workaround for the bug; thank you for your bug report!

Thanks for the bug report, verified.

The bug is that the host cache is initialized with 'HOST_CACHE_SIZE', instead of 'host_cache_size', in sql/hostname.cc

The former is the default value for the host cache size variable, or 128.
The later is the server variable host cache size itself.

Upon resize, the proper variable is used, so using:
  SET GLOBAL host_cache_size = X
is a viable work around.

Noted in 5.6.15, 5.7.3 changelogs.

Setting host_cache_size at startup had no effect.

5.6$ bzr log -r 5501
------------------------------------------------------------
revno: 5501
committer: Marc Alff <marc.alff@oracle.com>
branch nick: mysql-5.6-bug70552
timestamp: Tue 2013-10-08 22:48:13 +0200
message:
  BUG#17576516 HOST_CACHE_SIZE VALUE IS IGNORED  
  
  Before this fix, starting the server with
    host_cache_size = N
  in the my.cnf file had no effect.
  The internal host cache size was always 128.
  
  The root cause is in hostname_cache_init(),
  which is using 'HOST_CACHE_SIZE' (a #define to 128)
  instead of 'host_cache_size' (the C global variable that corresponds to the
  SQL system value named host_cache_size).
  
  Overall, the initialization of the host cache is not satisfactory, and needs
  to be cleaned up.
  
  In particular, the fact that there are distinct:
  - data path (mysqld.cc setting the host_cache_size global variable, 
    to be used by hostname.cc)
  - control path (mysqld.cc calling hostname_cache_init with no parameters)
  can only be a source of confusion.
  
  This fix:
  - moves the global variable host_cache_size to mysqld, where it belongs,
    because this is a server global variable
  - changes the variable type from ulong to uint, given that the size really
    is an unsigned int, per hash_filo::m_size
  - changes hostname_cache_init() to take explicitely a size,
    for consistency with hostname_cache_resize(),
    and for a cleaner interface.