MySQL Bugs: #71888: Improve performance_schema.host

Bug #71888	Improve performance_schema.host_cache documentation
Submitted:	28 Feb 2014 16:07	Modified:	1 Nov 2018 16:14
Reporter:	Simon Mudd (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.6.15	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any
Tags:	host_cache, performance_schema

Description:
https://dev.mysql.com/doc/refman/5.6/en/host-cache-table.html provides useful information on the host_cache table but does not reflect a couple of things:

- the size the table occupies in memory, per row ( it's a fixed allocation per row I believe )
- the default number of entries that are configured. 
- how this can be changed if needed:
  - which setting to change to make this value larger
  - basically a restart as P_S size changes are not dynamic for performance reasons
- it does not mention that you can check for entries being lost in the status variable Performance_schema_hosts_lost and if that value changes rapidly it may be worthwhile increasing the host_cache_size setting if you have an interest in collecting connection metrics of the hosts concerned.

How to repeat:
see above.

Suggested fix:
Please provide an indication of memory consumption of this table per entry.

So maybe part of the issue is reading the web pages directly you may not read other related pages, perhaps adding links to:

- tracking lost data in P_S tables
- resizing of P_S tables

would help in pages such as this to avoid duplication but at the same time providing links for anyone jumping straight to the page who might need related information.

Not a big thing but I needed to look in several places to find the information I needed.

I bumped into bug#70552 which shows this particular table is dynamically configurable in size, so it's good to make this clear as other P_S settings are not. It's also clear now that I can just do set global host_cache_size = X to adjust the servers that needed this adjustment without having to shut them down. that is of course very good :-)

So improved documentation will get us to this goal.

Other settings are mentioned here: http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_host_cache_size (but no reference to performance_schema).

So it seems to me probably better to as with other variables document the default setting in this URL (as now) but then to document everything else in the first URL that I posted and link to that from the server-system-variables.html page.

Hello Simon,

Thank you for the bug report.

Thanks,
Umesh

Posted by developer:
 
Revised these sections to clarify that the host_cache table is linked to the host cache, that its size is controlled by host_cache_size, and that it can be resized dynamically:

https://dev.mysql.com/doc/refman/8.0/en/host-cache-table.html
https://dev.mysql.com/doc/refman/8.0/en/host-cache.html
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_host_cache_siz...

re:
"- it does not mention that you can check for entries being lost in the status
variable Performance_schema_hosts_lost and if that value changes rapidly it
may be worthwhile increasing the host_cache_size setting if you have an
interest in collecting connection metrics of the hosts concerned."

That status variable corresponds to the host table, not the host_cache table.

re:
"Please provide an indication of memory consumption of this table per entry."

No action taken. Internal implementation issue that is subject to change any time.