New MySQL 5.6-Feature host_cache_size does not work

Today as i was learning for the new MySQL 5.6-certification (more about that in a later post) i stumbled again across the host_cache-Table that was added to MySQL in 5.6.5. Before that you had no chance to check which hosts are already known by the MySQL-server.

So thats a pretty good feature for us. And even better: you could resize the size of the host_cache now! Whoohoo, awesome! As we have a lot of servers that need to connect to one of our MySQL-server and we could not switch to ip-based-authentication we were really happy to tune the host_cache-size without recompiling MySQL.

Unfortunately i noticed that the performance_schema.host_cache table only holds 128 rows and that the content was changing everytime i checked. So i logged in to a server that wasn’t already in the host_cache-table, made a connection attempt to the mysql server  and checked again the host_cache-table:

The server was now in the host_cache-table but still the table held only 128 rows though the host_cache_size-variable was set to 2000. :/

You can see my bug report here: http://bugs.mysql.com/bug.php?id=70552

Hopefully it won’t take Oracle years to fix this ;)

2 comments

  1. Hello Moritz,

    Great that you are looking at the MySQL 5.6 certification exam – the current beta period is a great opportunity to obtain that certification at a small cost. Many thanks for your bug report as well – I’ve verified it and noted a workaround, based on the fact that setting the host_cache_size variable dynamically on a running server works correctly, and thought I would mention it here as well. To get the host cache sized correctly, simply execute the following statement:

    SET GLOBAL host_cache_size = @@global.host_cache_size;

    This is another win for PERFORMANCE_SCHEMA, in my mind – it brings visibility to internal workings of the host cache that are otherwise difficult to observe. In this case, it was telling you – correctly – that the host cache was limited to 128 entries, even though the global variable says otherwise.

  2. Whoa, first of all thanks for your time and effort in looking into this, Todd! And second whoa: You’re absolutely right! Setting the host_cache_size static through my.cnf does not work but setting the variable dynamic does work!

    I would have never thought that setting the variable again would do the trick as mysql already reported the correct size (2000 as stated in my my.cnf).

    Thanks for your quick response!

    And for the MySQL 5.6-certification: I already saw that there is finally an updated MySQL-certification coming but i didn’t knew that there was a beta period at a very low price till i saw your blog post about that. Thanks for that! :)