| 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: | |
| Category: | MySQL Server: Performance Schema | Severity: | S4 (Feature request) |
| Version: | 5.6.13 | OS: | Any |
| Assigned to: | Marc ALFF | CPU Architecture: | Any |
[3 Jul 2013 18:09]
Shane Bester
[3 Jul 2013 18:13]
MySQL Verification Team
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
