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
• Status: Verified
• Category: MySQL Server: FULLTEXT search
• Severity: S3 (Non-critical)
• Version: 5.6.13, 5.6.35, 5.7.17
• OS: Any
• 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] MySQL Verification Team

Hello Rick James,

Thank you for the report and feedback!

Thanks,
Umesh