MySQL 8.0 Reference Manual Including MySQL NDB Cluster 8.0
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 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, see Memory 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 the
setup_instruments
table:
memory_summary_by_account_by_event_name
has USER
, HOST
, and
EVENT_NAME
columns. Each row summarizes
events for a given account (user and host combination) and
event name.
memory_summary_by_host_by_event_name
has HOST
and
EVENT_NAME
columns. Each row summarizes
events for a given host and event name.
memory_summary_by_thread_by_event_name
has THREAD_ID
and
EVENT_NAME
columns. Each row summarizes
events for a given thread and event name.
memory_summary_by_user_by_event_name
has USER
and
EVENT_NAME
columns. Each row summarizes
events for a given user and event name.
memory_summary_global_by_event_name
has an EVENT_NAME
column. Each row
summarizes events for a given event name.
Each memory summary table has these summary columns containing aggregated values:
COUNT_ALLOC
,
COUNT_FREE
The aggregated numbers of calls to memory-allocation and memory-free functions.
SUM_NUMBER_OF_BYTES_ALLOC
,
SUM_NUMBER_OF_BYTES_FREE
The aggregated sizes of allocated and freed memory blocks.
CURRENT_COUNT_USED
The aggregated number of currently allocated blocks that
have not been freed yet. This is a convenience column,
equal to COUNT_ALLOC
−
COUNT_FREE
.
CURRENT_NUMBER_OF_BYTES_USED
The aggregated size of currently allocated memory blocks
that have not been freed yet. This is a convenience
column, equal to
SUM_NUMBER_OF_BYTES_ALLOC
−
SUM_NUMBER_OF_BYTES_FREE
.
LOW_COUNT_USED
,
HIGH_COUNT_USED
The low and high water marks corresponding to the
CURRENT_COUNT_USED
column.
LOW_NUMBER_OF_BYTES_USED
,
HIGH_NUMBER_OF_BYTES_USED
The low and high water marks corresponding to the
CURRENT_NUMBER_OF_BYTES_USED
column.
The memory summary tables have these indexes:
memory_summary_by_account_by_event_name
:
Primary key on (USER
,
HOST
,
EVENT_NAME
)
memory_summary_by_host_by_event_name
:
Primary key on (HOST
,
EVENT_NAME
)
memory_summary_by_thread_by_event_name
:
Primary key on (THREAD_ID
,
EVENT_NAME
)
memory_summary_by_user_by_event_name
:
Primary key on (USER
,
EVENT_NAME
)
memory_summary_global_by_event_name
:
Primary key on (EVENT_NAME
)
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
.
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 of
memory_summary_global_by_event_name
.
For details, see
Section 27.12.8, “Performance Schema Connection Tables”.
Memory instruments are listed in the
setup_instruments
table and
have names of the form
memory/
.
Memory instrumentation is enabled by default.
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 itself. The
memory/performance_schema/
instruments
are built in, always enabled, and cannot be disabled at
startup or runtime. Built-in memory instruments are
displayed only in the
memory_summary_global_by_event_name
table.
To control memory instrumentation state at server startup,
use lines like these in your my.cnf
file:
Enable:
[mysqld] performance-schema-instrument='memory/%=ON'
Disable:
[mysqld] performance-schema-instrument='memory/%=OFF'
To control memory instrumentation state at runtime, update
the ENABLED
column of the relevant
instruments in the
setup_instruments
table:
Enable:
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE 'memory/%';
Disable:
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:
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 memory allocation operation was instrumented, the corresponding free operation is instrumented, regardless of the current instrument or thread enabled status.
If a memory allocation operation was not instrumented, the corresponding free operation is not instrumented, regardless of the current instrument or thread enabled status.
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
: Increased by 1
CURRENT_COUNT_USED
: Increased 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
: Increased by 1
CURRENT_COUNT_USED
: Decreased 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. 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.
HIGH_COUNT_USED
and
HIGH_NUMBER_OF_BYTES_USED
are higher
estimates. 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.