MySQL Bugs: #68635: Doc: Multiple issues with performance_schema_max_statement

Bug #68635	Doc: Multiple issues with performance_schema_max_statement_classes
Submitted:	11 Mar 2013 11:47	Modified:	27 Mar 2013 16:22
Reporter:	Alexey Kopytov	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:
The manual states the following about performance_schema_max_statement_classes:

- it is "The maximum number of statement instruments."
- its default value is 100.

Issues:

1. The default value is wrong. On 5.6.10 the default value is 167

2. The default value is automatically calculated at compile time based on the number of protocol commands and SQL statement types supported by the server.

3. That automatically calculated value is enforced by 83 tests in the test suite. Which makes introducing and maintaining new commands or statement types a nightmare. Whenever a new command/type is added, one has to update 83 totally unrelated test cases. Is it possible to change the default to a sufficiently large constant and thus not introduce additional maintenance work for other MySQL branches?

4. The effect of changing that variable is absolutely unclear from the docs. What happens when it's set to a lower value than the automatically calculated default? What happens when it's set to a higher (e.g. the maximum allowed) value?

How to repeat:
Read http://dev.mysql.com/doc/refman/5.6/en/performance-schema-system-variables.html#sysvar_per...
Try adding a new protocol command or SQL statement type.

Thanks for the report!

Thanks for the report.

Several comments here:

1. The default value is wrong. On 5.6.10 the default value is 167

Correct. This is a doc bug.

2. The default value is automatically calculated at compile time based on the number of protocol commands and SQL statement types supported by the server.

Correct.

For other types of instrumentation (for example mutexes), the maximum number of mutex classes is hard coded, and is higher that the actual number of classes instrumented in the code.
The rational in this case is that the exact number varies a lot (it can change with a simple bug fix), and this number is platform dependent.

Having values that varies often is and cause test result changes is very annoying.
Having values that varies by platform is un manageable with MTR
so based on this, the choice was made to hard code a higher than needed value for max mutex classes, to have stable tests at the expense of unused memory.

For statements, the list of statements is expected to be:
- more stable over time: *new statements* are typically not created just for bug reports, only for new features
- completely stable over platforms.

For this reason, the max number of statements is defined to the exact value needed, at compile time, instead of using a higher hard coded value.

That is a choice.

3. That automatically calculated value is enforced by 83 tests in the test suite. Which makes introducing and maintaining new commands or statement types a nightmare. Whenever a new command/type is added, one has to update 83 totally unrelated test cases. Is it possible to change the default to a sufficiently large constant and thus not introduce additional maintenance work for other MySQL branches?

First, this is not a 'nightmare', and this affects equally all mysql branches where new statements are added, whether internal at oracle or external at forks.

I agree it is annoying for development in a trunk branch, but this is not a problem for maintaining released versions.

To work around this, there are several ways:

a) Fix all the results files at once with a grep + sed, that is trivial.
b) Record the test cases again, with grep -l, xargs, mtr --record. Equally trivial.
c) Hard code a bigger value instead of using the exact limit at compile time.
This will make the test results more stable, changing less often.

b) works very well

4. The effect of changing that variable is absolutely unclear from the docs. What happens when it's set to a lower value than the automatically calculated default? What happens when it's set to a higher (e.g. the maximum allowed) value?

Using a higher value has *currently* no benefits, and only drawbacks: it consumes more memory that needed.

The memory impact can be seen in
SHOW ENGINE PERFORMANCE_SCHEMA STATUS

Using a lower but non zero value is technically possible, but serves no practical use case.

Using a setting of 0 is valid, to totally disable all the statement instrumentation, and save all the memory associated with is.
This is useful for benchmarks to isolate each type of instrumentation, or when troubleshooting the server.

Increasing the sizing beyond the current default will be useful when a third party plugin defines its own concept os statements.

For example, a debugger plugin could define "add breakpoint", "step in", etc statements, and the performance schema will need to know how many statements are defined to provide statistics for these.

Doc team:

Point 1, 2 and 4 are a doc bug, see my previous comments for details.

Point 3 is not a bug, works as designed.

Changing this report to a doc bug.

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.

Updated description:

The maximum number of statement instruments. The default value is
calculated at server build time based on the number of commands in 
the client/server protocol and the number of SQL statement types
supported by the server.

This variable should not be changed, unless to set it to 0 to disable
all statement instrumentation and save all memory associated with it.
Setting the variable to nonzero values other than the default has no
benefit; in particular, values larger than the default cause more
memory to be allocated then is needed.

Point 3 has been fixed by

$ git show -s 08d8b15
commit 08d8b1522f26cc9f8a4c027bb9078270f5f006ff
Author: Marc Alff <marc.alff@oracle.com>
Date:   Mon Jan 26 16:27:37 2015 +0100

    Bug#20391871 WEBSCALESQL: DON'T REQUIRE INTERNAL VALUES, THAT SHOULD CHANGE,
    TO NOT CHANGE
    
    Merge upstream the following contribution from WebScaleSql:
    
    https://github.com/webscalesql/webscalesql-5.6/commit/049aa3335ef9c8e927d125a64e27e7b0ffe4...
    
    Don't require internal values, that should change, to not change
    
    Summary:
    Feature: Basic Test Fixes
    
    1) A number of PS tests show performance_schema_max_statement_classes
    along with the other PS variables.  This value is not important, and
    this number will just go up every time a new statement class is added.
    
    The only thing testing for this number accomplishes is the creation
    of pointless conflicts with all the diffs which introduce these.
    
    So, this one variable is now excluded from the output of those tests.
    
    2) Many internal values are included in the full output of
    'show engine performance_schema status', so this has the same issue.
    
    So, these tests were changed to hide the actual numerical values in
    the required test output.