Bug #84195 innodb_ft_result_cache_limit default of 2G seems obscenely high
Submitted: 13 Dec 2016 22:14 Modified: 14 Dec 2016 9:22
Reporter: Rick James Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: FULLTEXT search Severity:S3 (Non-critical)
Version:5.6.13, 5.6.35, 5.7.17 OS:Any
Assigned to: CPU Architecture:Any
Tags: innodb FULLTEXT innodb_ft_result_cache_limit default memory

[13 Dec 2016 22:14] Rick James
Description:
The manual says "The InnoDB FULLTEXT search (FTS) query result cache limit (defined in bytes) per FTS query or per thread."  On a 4GB server, 2GB for this one thing is dangerously high, especially with multiple connections allocating multiple caches.  Even on a 32GB server, I would not want this much space potentially being allocated.

This setting was introduced in 5.6.13 / 5.7.2, apparently to avoid an even worse memory allocation problem.  Cf Bug #16625973 .

This bug report stems from 
http://dba.stackexchange.com/questions/157462/how-innodb-ft-result-cache-limit-work
by Hamoon Mohammadian Pour

How to repeat:
N/A

Suggested fix:
Plan A:  If this is not really per-FT-query, then change the documentation.

Plan B:  Lower the default to either something sensible, or some percentage, say 1%, of RAM

In either case implement a STATUS value (SESSION and/or GLOBAL, as appropriate) to give some feed back of what memory is actually being used.  A "high-water" value may be best.
[14 Dec 2016 9:22] Umesh Shastry
Hello Rick James,

Thank you for the report and feedback!

Thanks,
Umesh