Bug #69665 please print the pfs_allocated_memory in error log during startup
Submitted: 3 Jul 2013 18:09 Modified: 10 Mar 2015 9:41
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server: Performance Schema Severity:S4 (Feature request)
Version:5.6.13 OS:Any
Assigned to: Marc Alff

[3 Jul 2013 18:09] Shane Bester
Description:
performance_schema allocates all memory once during startup.
to find out how much memory is in use, 'show engine performance_schema status' can be run.

this is a feature request to print a one-liner to the error log containing the memory allocation amount during startup (much like innodb does).

How to repeat:
.

Suggested fix:
a small note in the error log can help, e.g:

[Note] Performance_schema using 3233167624 bytes
[3 Jul 2013 18:13] Shane Bester
many folks do not know about 
'show engine performance_schema status' command..

http://dev.mysql.com/doc/refman/5.6/en/show-engine.html
[6 Jun 2014 22:48] James Day
Here's an example of that output and how to get it at the command line:

mysql -e "show engine performance_schema status" | find "memory"
performance_schema      events_waits_history.memory     707520
performance_schema      events_waits_history_long.memory        1760000
performance_schema      (pfs_mutex_class).memory        51200
performance_schema      (pfs_rwlock_class).memory       9600
performance_schema      (pfs_cond_class).memory 20480
performance_schema      (pfs_thread_class).memory       9600
performance_schema      (pfs_file_class).memory 16000
performance_schema      mutex_instances.memory  2035968
performance_schema      rwlock_instances.memory 1747584
performance_schema      cond_instances.memory   448512
performance_schema      threads.memory  1132032
performance_schema      file_instances.memory   5415872
performance_schema      (pfs_file_handle).memory        262144
performance_schema      events_waits_summary_by_thread_by_event_name.memory     4798272
performance_schema      (pfs_table_share).memory        277600000
performance_schema      (pfs_table).memory      37120000
performance_schema      setup_actors.memory     25600
performance_schema      setup_objects.memory    44800
performance_schema      (pfs_account).memory    25600
performance_schema      events_waits_summary_by_account_by_event_name.memory    1193600
performance_schema      events_waits_summary_by_user_by_event_name.memory       1193600
performance_schema      events_waits_summary_by_host_by_event_name.memory       1193600
performance_schema      (pfs_user).memory       12800
performance_schema      (pfs_host).memory       12800
performance_schema      (pfs_stage_class).memory        38400
performance_schema      events_stages_history.memory    353760
performance_schema      events_stages_history_long.memory       880000
performance_schema      events_stages_summary_by_thread_by_event_name.memory    1929600
performance_schema      events_stages_summary_global_by_event_name.memory       4800
performance_schema      events_stages_summary_by_account_by_event_name.memory   480000
performance_schema      events_stages_summary_by_user_by_event_name.memory      480000
performance_schema      events_stages_summary_by_host_by_event_name.memory      480000
performance_schema      (pfs_statement_class).memory    32256
performance_schema      events_statements_history.memory        12156480
performance_schema      events_statements_history_long.memory   30240000
performance_schema      events_statements_summary_by_thread_by_event_name.memory        12426624
performance_schema      events_statements_summary_global_by_event_name.memory   30912
performance_schema      events_statements_summary_by_account_by_event_name.memory       3091200
performance_schema      events_statements_summary_by_user_by_event_name.memory  3091200
performance_schema      events_statements_summary_by_host_by_event_name.memory  3091200
performance_schema      events_statements_current.memory        1215648
performance_schema      (pfs_socket_class).memory       3200
performance_schema      socket_instances.memory 103040
performance_schema      events_statements_summary_by_digest.memory      14720000
performance_schema      session_connect_attrs.memory    205824
performance_schema      performance_schema.memory       421891328

This output is from a noinstall Windows 5.6.19 64 bit build, hence the use of find instead of grep.

James Day, MySQL Senior Principal Support Engineer, Oracle
[6 Jun 2014 22:59] James Day
A more detailed description of how the performance schema works out how much memory to allocate is given at bug #68287. It uses a small, medium or large approach is based on the settings of table_definition_cache, table_open_cache and max_connections. If you want to reduce the memory usage but leave PS enabled, just change one of those three settings in my.cnf/ini to a value smaller than default and leave the others are or also below the default.

Here is a linux example to show the effect of just changing table_definition_cache, with max_connections always 151, table_open_cache 2000 and open_files_limit 65000, all other server settings default and little data in any table:

400: small size: 52.6 megabytes
401: medium size: 90.7 megabytes
800: medium size: 98.4 megabytes
801: large size: 400 megabytes
[10 Mar 2015 9:35] Marc Alff
Starting with MySQL 5.7.6, the performance schema does not allocate all memory needed up-front, but allocate memory on demand during the server execution.

How much memory is currently used can be seen:
- in SHOW ENGINE PERFORMANCE_SCHEMA STATUS, as before
- also in table performance_schema.memory_summary_global_by_event_name,
with new memory instruments for the performance schema itself.

Printing pfs_allocated_memory at startup is no longer relevant starting with 5.7.6