Bug #87498 performance_schema_max_digest_length=1048576 could not work correctly

• Submitted: 22 Aug 2017 6:16
• Modified: 25 Oct 2018 8:47
• Reporter: ashe sun (OCA)
• Status: Not a Bug
• Category: MySQL Server: Performance Schema
• Severity: S2 (Serious)
• Version: mysql-5.7.19
• OS: Any
• CPU Architecture: Any
• Tags: performance_schema

[22 Aug 2017 6:16] ashe sun

Description:
Add
"performance_schema_max_digest_length=1048576"
to my.cnf and then restart the server.

mysql> show global variables like '%performance%'
    -> ;
Empty set (0.00 sec)

mysql> 
mysql> 
mysql> 
mysql> select * from performance_schema.setup_actors;
Empty set (0.01 sec)

mysql> select * from performance_schema.setup_consumers;
Empty set (0.00 sec)

How to repeat:
Add performance_schema_max_digest_length=1048576 to my.cnf.

and restart mysqld.

[22 Aug 2017 7:44] MySQL Verification Team

Hello Ashe Sun,

Thank you for the report.

Thanks,
Umesh

[25 Oct 2018 8:47] Erlend Dahl

Posted by developer - Chris Powers

The failure is due to an out-of-memory condition that prevents the
Performance Schema from initializing.

From /var/log/mysql/error.log:
  [Warning] Failed to allocate 10,485,760,000 bytes for buffer
  'memory/performance_schema/events_statements_history_long.tokens'
  due to out-of-memory

During server initialization, the Performance Schema allocates a variety of
static memory buffers based on the sizing parameters.

The default size for events_statements_history_long is 10,000.
Each statement event references a unique statement digest, so increasing the
max digest to 1MB requires 10,000 * 1MB = 10GB of memory.

To allow for larger statement digests, the default statement history long
size has been reduced from 10,000 to 1,000 in subsequent releases.