Description:
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:
max_connections
open_files_limit
table_definition_cache
table_open_cache"
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".