Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 35.4Mb
PDF (A4) - 35.6Mb
PDF (RPM) - 33.8Mb
EPUB - 8.5Mb
HTML Download (TGZ) - 8.3Mb
HTML Download (Zip) - 8.4Mb
HTML Download (RPM) - 7.2Mb
Eclipse Doc Plugin (TGZ) - 9.2Mb
Eclipse Doc Plugin (Zip) - 11.3Mb
Man Pages (TGZ) - 198.4Kb
Man Pages (Zip) - 302.4Kb
Info (Gzip) - 3.2Mb
Info (Zip) - 3.2Mb
Excerpts from this Manual

23.9.15.10 Memory Summary Tables

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

  • Type of memory used (various caches, internal buffers, and so forth)

  • Thread, account, user, host indirectly performing the memory operation

The Performance Schema instruments the following aspects of memory use

  • Memory sizes used

  • Operation counts

  • Low and high water marks

Memory sizes help to understand or tune the memory consumption of a 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.

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 the setup_instruments table.

All memory summary tables have these summary columns containing aggregated values:

  • COUNT_ALLOC, COUNT_FREE

    These columns aggregate the number of calls to malloc-like and free-like functions.

  • SUM_NUMBER_OF_BYTES_ALLOC, SUM_NUMBER_OF_BYTES_FREE

    These columns indicate the aggregate size of allocated and freed memory blocks.

  • CURRENT_COUNT_USED

    This column is the aggregate number of currently allocated blocks that have not been freed yet. This is a convenience column, equal to COUNT_ALLOCCOUNT_FREE.

  • CURRENT_NUMBER_OF_BYTES_USED

    This column is the aggregate size of currently allocated memory blocks that have not been freed yet. This is a convenience column, equal to SUM_NUMBER_OF_BYTES_ALLOCSUM_NUMBER_OF_BYTES_FREE.

  • LOW_COUNT_USED, HIGH_COUNT_USED

    These columns are the low and high water marks corresponding to the CURRENT_COUNT_USED column.

  • LOW_NUMBER_OF_BYTES_USED, HIGH_NUMBER_OF_BYTES_USED

    These columns are the low and high water marks corresponding to the CURRENT_NUMBER_OF_BYTES_USED column.

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

Example memory event summary information:

mysql> SELECT * FROM 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

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

  • In general, truncation resets the baseline for statistics, but does not change the server state. That is, truncating a memory table does not free memory.

  • COUNT_ALLOC and COUNT_FREE are reset to a new baseline, by reducing each counter by the same value.

  • Likewise, SUM_NUMBER_OF_BYTES_ALLOC and SUM_NUMBER_OF_BYTES_FREE are reset to a new baseline.

  • LOW_COUNT_USED and HIGH_COUNT_USED are reset to CURRENT_COUNT_USED.

  • LOW_NUMBER_OF_BYTES_USED and HIGH_NUMBER_OF_BYTES_USED are reset to CURRENT_NUMBER_OF_BYTES_USED.

Memory Instrumentation Behavior

Most memory instrumentation is disabled by default, and can be enabled or disabled dynamically by updating the ENABLED column of the relevant instruments in the setup_instruments table. Memory instruments have names of the form memory/code_area/instrument_name.

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

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:

  • If the thread is not instrumented or the memory instrument is not enabled, the memory block allocated is not instrumented.

  • Otherwise (that is, both the thread and the instrument are enabled), the memory block allocated is instrumented.

For deallocation, these rules apply:

  • If a thread is instrumented, and a memory block is not instrumented, the free operation is not instrumented; no statistics are changed.

  • If a thread is not instrumented, and a memory block is instrumented, the free operation is instrumented, and statistics are changed.

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:

  • COUNT_ALLOC: Incremented by 1

  • CURRENT_COUNT_USED: Incremented by 1

  • HIGH_COUNT_USED: Increased if CURRENT_COUNT_USED is a new maximum

  • SUM_NUMBER_OF_BYTES_ALLOC: Increased by N

  • CURRENT_NUMBER_OF_BYTES_USED: Increased by N

  • HIGH_NUMBER_OF_BYTES_USED: Increased if CURRENT_NUMBER_OF_BYTES_USED is a new maximum

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

  • COUNT_FREE: Incremented by 1

  • CURRENT_COUNT_USED: Iecremented by 1

  • LOW_COUNT_USED: Decreased if CURRENT_COUNT_USED is a new minimum

  • SUM_NUMBER_OF_BYTES_FREE: Increased by N

  • CURRENT_NUMBER_OF_BYTES_USED: Decreased by N

  • LOW_NUMBER_OF_BYTES_USED: Decreased if CURRENT_NUMBER_OF_BYTES_USED is a new minimum

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

  • LOW_COUNT_USED and LOW_NUMBER_OF_BYTES_USED are lower estimates

  • HIGH_COUNT_USED and HIGH_NUMBER_OF_BYTES_USED are higher estimates

Lower estimates means that the value reported by the Performance Schema is guaranteed to be less than or equal to the lowest count or size of memory effectively used at runtime.

Higher estimates means that the value reported by the Performance Schema is guaranteed to be greater than or equal to the highest count or size of memory effectively used at runtime.

For lower estimates in summary tables other than memory_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 the LOW_NUMBER_OF_BYTES_USED and HIGH_NUMBER_OF_BYTES_USED columns of the memory_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, the LOW_NUMBER_OF_BYTES_USED for the higher level aggregate is the sum of each LOW_NUMBER_OF_BYTES_USED (assuming the worst case). Likewise, the HIGH_NUMBER_OF_BYTES_USED for the higher level aggregate is the sum of each HIGH_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.


User Comments
Sign Up Login You must be logged in to post a comment.