MySQL :: MySQL 8.0 Reference Manual :: 29.12.20.10 Memory Summary Tables (original) (raw)

29.12.20.10 Memory Summary Tables

The Performance Schema instruments memory usage and aggregates memory usage statistics, detailed by these factors:

The Performance Schema instruments the following aspects of memory use

Memory sizes help to understand or tune the memory consumption of the server.

Operation counts help to understand or tune the overall pressure the server is putting on the memory allocator, which has an impact on performance. Allocating a single byte one million times is not the same as allocating one million bytes a single time; tracking both sizes and counts can expose the difference.

Low and high water marks are critical to detect workload spikes, overall workload stability, and possible memory leaks.

Memory summary tables do not contain timing information because memory events are not timed.

For information about collecting memory usage data, seeMemory Instrumentation Behavior.

Example memory event summary information:

mysql> SELECT *
       FROM performance_schema.memory_summary_global_by_event_name
       WHERE EVENT_NAME = 'memory/sql/TABLE'\G
*************************** 1. row ***************************
                  EVENT_NAME: memory/sql/TABLE
                 COUNT_ALLOC: 1381
                  COUNT_FREE: 924
   SUM_NUMBER_OF_BYTES_ALLOC: 2059873
    SUM_NUMBER_OF_BYTES_FREE: 1407432
              LOW_COUNT_USED: 0
          CURRENT_COUNT_USED: 457
             HIGH_COUNT_USED: 461
    LOW_NUMBER_OF_BYTES_USED: 0
CURRENT_NUMBER_OF_BYTES_USED: 652441
   HIGH_NUMBER_OF_BYTES_USED: 669269

Each memory summary table has one or more grouping columns to indicate how the table aggregates events. Event names refer to names of event instruments in thesetup_instruments table:

Each memory summary table has these summary columns containing aggregated values:

The memory summary tables have these indexes:

TRUNCATE TABLE is permitted for memory summary tables. It has these effects:

In addition, each memory summary table that is aggregated by account, host, user, or thread is implicitly truncated by truncation of the connection table on which it depends, or truncation ofmemory_summary_global_by_event_name. For details, seeSection 29.12.8, “Performance Schema Connection Tables”.

Memory Instrumentation Behavior

Memory instruments are listed in thesetup_instruments table and have names of the formmemory/_`codearea`_/_`instrumentname`_. Memory instrumentation is enabled by default.

Instruments named with the prefixmemory/performance_schema/ expose how much memory is allocated for internal buffers in the Performance Schema itself. Thememory/performance_schema/ instruments are built in, always enabled, and cannot be disabled at startup or runtime. Built-in memory instruments are displayed only in thememory_summary_global_by_event_name table.

To control memory instrumentation state at server startup, use lines like these in your my.cnf file:

[mysqld]  
performance-schema-instrument='memory/%=ON'  
[mysqld]  
performance-schema-instrument='memory/%=OFF'

To control memory instrumentation state at runtime, update the ENABLED column of the relevant instruments in thesetup_instruments table:

UPDATE performance_schema.setup_instruments  
SET ENABLED = 'YES'  
WHERE NAME LIKE 'memory/%';  
UPDATE performance_schema.setup_instruments  
SET ENABLED = 'NO'  
WHERE NAME LIKE 'memory/%';

For memory instruments, the TIMED column in setup_instruments is ignored because memory operations are not timed.

When a thread in the server executes a memory allocation that has been instrumented, these rules apply:

For deallocation, these rules apply:

For the per-thread statistics, the following rules apply.

When an instrumented memory block of size_N_ is allocated, the Performance Schema makes these updates to memory summary table columns:

When an instrumented memory block is deallocated, the Performance Schema makes these updates to memory summary table columns:

For higher-level aggregates (global, by account, by user, by host), the same rules apply as expected for low and high water marks.

For lower estimates in summary tables other thanmemory_summary_global_by_event_name, it is possible for values to go negative if memory ownership is transferred between threads.

Here is an example of estimate computation; but note that estimate implementation is subject to change:

Thread 1 uses memory in the range from 1MB to 2MB during execution, as reported by theLOW_NUMBER_OF_BYTES_USED andHIGH_NUMBER_OF_BYTES_USED columns of thememory_summary_by_thread_by_event_name table.

Thread 2 uses memory in the range from 10MB to 12MB during execution, as reported likewise.

When these two threads belong to the same user account, the per-account summary estimates that this account used memory in the range from 11MB to 14MB. That is, theLOW_NUMBER_OF_BYTES_USED for the higher level aggregate is the sum of eachLOW_NUMBER_OF_BYTES_USED (assuming the worst case). Likewise, theHIGH_NUMBER_OF_BYTES_USED for the higher level aggregate is the sum of eachHIGH_NUMBER_OF_BYTES_USED (assuming the worst case).

11MB is a lower estimate that can occur only if both threads hit the low usage mark at the same time.

14MB is a higher estimate that can occur only if both threads hit the high usage mark at the same time.

The real memory usage for this account could have been in the range from 11.5MB to 13.5MB.

For capacity planning, reporting the worst case is actually the desired behavior, as it shows what can potentially happen when sessions are uncorrelated, which is typically the case.