Bug #71304 Manual does not provide enough details about automatic sizing of P_S parameters
Submitted: 5 Jan 2014 16:45 Modified: 19 Jun 2014 22:39
Reporter: Valeriy Kravchuk Email Updates:
Status: Won't fix Impact on me:
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.6 OS:Any
Assigned to: Paul DuBois CPU Architecture:Any
Tags: heuristics, missing manual, performance_schema

[5 Jan 2014 16:45] Valeriy Kravchuk
This manual page, http://dev.mysql.com/doc/refman/5.6/en/performance-schema-startup-configuration.html, says:

"As of MySQL 5.6.6, the Performance Schema automatically sizes the values of several of its parameters at server startup if they are not set explicitly. For example, it sizes the parameters that control the sizes of the events waits tables this way. To see which parameters are sized under this policy, use mysqld --verbose --help and look for those with a default value of –1, or see Section 21.12, “Performance Schema System Variables”."

I wonder why not to run this once:

openxs@ao756:~/bzr/mysql-5.6$ mysqld --verbose --help 2>/dev/null | grep performance | grep "\-1"
performance-schema-accounts-size                           -1
performance-schema-digests-size                            -1
performance-schema-events-stages-history-long-size         -1
performance-schema-events-stages-history-size              -1
performance-schema-events-statements-history-long-size     -1
performance-schema-events-statements-history-size          -1
performance-schema-events-waits-history-long-size          -1
performance-schema-events-waits-history-size               -1
performance-schema-hosts-size                              -1
performance-schema-max-cond-instances                      -1
performance-schema-max-file-instances                      -1
performance-schema-max-mutex-instances                     -1
performance-schema-max-rwlock-instances                    -1
performance-schema-max-socket-instances                    -1
performance-schema-max-table-handles                       -1
performance-schema-max-table-instances                     -1
performance-schema-max-thread-instances                    -1
performance-schema-session-connect-attrs-size              -1
performance-schema-users-size                              -1

for every minor version and put this list on the page.

Later this page says:

" For each autosized parameter that is not set at server startup (or is set to –1), the Performance Schema determines how to set its value based on the value of the following system values, which are considered as “hints” about how you have configured your MySQL server:


In reality I see the following in the code (storage/perfschema/pfs_autosize.cc, thanks to hint in the Bug #68514) that determines one of 3 possible "sizes" for server based on these settings:

PFS_sizing_data *estimate_hints(PFS_global_param *param)
  if ((param->m_hints.m_max_connections <= MAX_CONNECTIONS_DEFAULT) &&
      (param->m_hints.m_table_definition_cache <= TABLE_DEF_CACHE_DEFAULT) &&
      (param->m_hints.m_table_open_cache <= TABLE_OPEN_CACHE_DEFAULT))
    /* The my.cnf used is either unchanged, or lower than factory defaults. */
    return & small_data;

  if ((param->m_hints.m_max_connections <= MAX_CONNECTIONS_DEFAULT * 2) &&
      (param->m_hints.m_table_definition_cache <= TABLE_DEF_CACHE_DEFAULT * 2) &&
      (param->m_hints.m_table_open_cache <= TABLE_OPEN_CACHE_DEFAULT * 2))
    /* Some defaults have been increased, to "moderate" values. */
    return & medium_data;

  /* Looks like a server in production. */
  return & large_data;

So, why not to define explicitly and specifically values that determine server "size" for the heuristics?

Missing details lead to reports like Bug #68514, as users can not set proper expectations.

How to repeat:
Try to find out what PERFORMANCE_SCHEMA parameters are automatically sized by default in 5.6 using the manual. Try to find out any specific details about heuristics used to set size for them. 

Now think why the burden of running commands or checking the source code is put onto each of wondering users (readers) instead of one smart person from the documentation team (writer).

Suggested fix:
List all automatically sized parameters explicitly. Provide details on what specific threshold values define what "size" the server is for the heuristics. ideally, calculate and provide average additional memory use "per connection" for each server "size".
[7 Jan 2014 11:23] MySQL Verification Team
Hello Valeriy,

Thank you for the bug report.

[24 Jan 2014 16:10] Paul DuBois
The point of autosizing is that the user does not have to do it. We're not going to explain in detail what goes into the process because it can change.

A user can always set an autosized value explicitly if desired.
[24 Jan 2014 16:13] Paul DuBois
re: "I wonder why not to run this once:

openxs@ao756:~/bzr/mysql-5.6$ mysqld --verbose --help 2>/dev/null | grep performance | grep "\-1"
for every minor version and put this list on the page."

For every minor version? So that it'd need to be updated for each release? The mysqld --verbose --help command (already suggested in the manual) provides this information regardless of which version happens to be installed.