MySQL Bugs: #74574: Defaults for performance_schema statements history not clear

Bug #74574	Defaults for performance_schema statements history not clear
Submitted:	26 Oct 2014 18:54	Modified:	4 Dec 2014 15:29
Reporter:	Daniël van Eeden (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.6	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any
Tags:	defaults, performance_schema

Description:
The documentation about the tables lists 10 and 10,000 as defaults, but the pages about the variables say it's autosized.

Pages:

http://dev.mysql.com/doc/refman/5.6/en/events-statements-history-table.html
http://dev.mysql.com/doc/refman/5.6/en/performance-schema-system-variables.html#sysvar_per...

http://dev.mysql.com/doc/refman/5.6/en/events-statements-history-long-table.html
http://dev.mysql.com/doc/refman/5.6/en/performance-schema-system-variables.html#sysvar_per...

http://dev.mysql.com/doc/refman/5.6/en/server-default-changes.html

Is it autosized? If so, based on what?

5.6.21 uses 10 and 10,000, but they may be autosized.
mysql [information_schema] > show global variables like 'perf%statements_history%';
+--------------------------------------------------------+-------+
| Variable_name                                          | Value |
+--------------------------------------------------------+-------+
| performance_schema_events_statements_history_long_size | 10000 |
| performance_schema_events_statements_history_size      | 10    |
+--------------------------------------------------------+-------+
2 rows in set (0.00 sec)

How to repeat:
See description

Hello Daniël,

Thank you for the report.

Thanks,
Umesh

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly.

Same issue affected the descriptions for several other tables as well. Corrected those, too.

The documentation now clearly states it's autosized, but doesn't give any information about how the sizing is done.

estimate_hints() in storage/perfschema/pfs_autosize.cc seems to do this:

if max_connections, table_open_cache and table_definition_cache are equal or lower than the defaults:
  statements = 5
  statements_long = 100
  stages = 5 
  stages_long = 100
  events = 5
  events_long = 100

if max_connections, table_open_cache and table_definition_cache are equal or lower than the defaults x 2:
  statements = 10
  statements_long = 1000
  stages = 10 
  stages_long = 1000
  events = 10
  events_long = 1000

else:
  statements = 10
  statements_long = 10000
  stages = 10
  stages_long = 10000
  events = 10
  events_long = 10000

and then apply_heuristic() is used to do some corrections.

And it's not going to give the details. Those can change at any time, and a user who wants to override the autosize value can easily do so.