MySQL Bugs: #69318: performance_schema_max_thread

Bug #69318	performance_schema_max_thread_instances documentation tweak
Submitted:	25 May 2013 16:27	Modified:	3 Jul 2013 14:09
Reporter:	Sheeri Cabral (Candidate Quality Contributor)	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

Description:
In MySQL 5.5, the documentation for performance_schema_max_thread_instances says that it should be set larger than max_connections+max_delayed threads. This is accurate. - http://dev.mysql.com/doc/refman/5.5/en/performance-schema-system-variables.html#sysvar_per...

However, the documentation is the same for MySQL 5.6, even though it seems it's auto-sized (default is -1, autosized):
http://dev.mysql.com/doc/refman/5.6/en/performance-schema-system-variables.html#sysvar_per...

My guess is that since INSERT DELAYED and the associated variables are deprecated in MySQL 5.6, performance_schema_max_thread_instances is actually autosized based only on max_connections. 

How to repeat:
Look at http://dev.mysql.com/doc/refman/5.6/en/performance-schema-system-variables.html#sysvar_per...

Suggested fix:
Fix http://dev.mysql.com/doc/refman/5.6/en/performance-schema-system-variables.html#sysvar_per... to note that max_delayed_threads are deprecated and have the text reflect the default that is autosized, and explain if it's auto-sized based on only max_connections, or on max_delayed_threads too.

Thank you for the report.

"
In MySQL 5.6, performance_schema_max_thread_instances is actually autosized based only on max_connections.
"

Yes, this is correct.

The exact heuristic used is implementation dependent and subject to change without notice (*), but to clarify the current behavior (see pfs_autosize.cc), it is at scale implemented as:

performance_schema_max_thread_instances =
(50 + max_connections) / 0.5

The fixed 50 threads are to account for miscelaneous things such as storage engine background threads, etc.

The 0.5 load factor ratio is for performance reasons, to reduce overhead and avoid contention when creating threads.

(*) Note:
The whole point of the "autosize" feature is to estimate the overall consumption of the server so a user does not have to.
This is by definition very implementation dependent.
Whenever the server implementation changes (bug fix, new features) that affect how many threads / tables / mutexes / etc are used internally, the corresponding heuristic can change.

-- Marc Alff.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

I've changed the 5.6/5.7 manuals to omit mention of max_delayed_threads. They
now say:

The max_connections system variable affects how many threads are run
in the server. performance_schema_max_thread_instances affects how
many of these running threads can be instrumented. The default value
of performance_schema_max_thread_instances is autosized based on the
value of max_connections.