Documentation Home
MySQL 9.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 40.0Mb
PDF (A4) - 40.1Mb
Man Pages (TGZ) - 259.0Kb
Man Pages (Zip) - 366.2Kb
Info (Gzip) - 4.0Mb
Info (Zip) - 4.0Mb


MySQL 9.0 Reference Manual  / MySQL Performance Schema  /  The Performance Schema Memory-Allocation Model

29.17 The Performance Schema Memory-Allocation Model

The Performance Schema uses this memory allocation model:

  • May allocate memory at server startup

  • May allocate additional memory during server operation

  • Never free memory during server operation (although it might be recycled)

  • Free all memory used at shutdown

The result is to relax memory constraints so that the Performance Schema can be used with less configuration, and to decrease the memory footprint so that consumption scales with server load. Memory used depends on the load actually seen, not the load estimated or explicitly configured for.

Several Performance Schema sizing parameters are autoscaled and need not be configured explicitly unless you want to establish an explicit limit on memory allocation:

performance_schema_accounts_sizeperformance_schema_hosts_sizeperformance_schema_max_cond_instancesperformance_schema_max_file_instancesperformance_schema_max_index_statperformance_schema_max_metadata_locksperformance_schema_max_mutex_instancesperformance_schema_max_prepared_statements_instancesperformance_schema_max_program_instancesperformance_schema_max_rwlock_instancesperformance_schema_max_socket_instancesperformance_schema_max_table_handlesperformance_schema_max_table_instancesperformance_schema_max_table_lock_statperformance_schema_max_thread_instancesperformance_schema_users_size

For an autoscaled parameter, configuration works like this:

  • With the value set to -1 (the default), the parameter is autoscaled:

    • The corresponding internal buffer is empty initially and no memory is allocated.

    • As the Performance Schema collects data, memory is allocated in the corresponding buffer. The buffer size is unbounded, and may grow with the load.

  • With the value set to 0:

    • The corresponding internal buffer is empty initially and no memory is allocated.

  • With the value set toN > 0:

    • The corresponding internal buffer is empty initially and no memory is allocated.

    • As the Performance Schema collects data, memory is allocated in the corresponding buffer, until the buffer size reachesN.

    • Once the buffer size reachesN, no more memory is allocated. Data collected by the Performance Schema for this buffer is lost, and any corresponding“lost instance” counters are incremented.

To see how much memory the Performance Schema is using, check the instruments designed for that purpose. The Performance Schema allocates memory internally and associates each buffer with a dedicated instrument so that memory consumption can be traced to individual buffers. Instruments named with the prefixmemory/performance_schema/ expose how much memory is allocated for these internal buffers. The buffers are global to the server, so the instruments are displayed only in thememory_summary_global_by_event_name table, and not in othermemory_summary_by_xxx_by_event_name tables.

This query shows the information associated with the memory instruments:

SELECT * FROM performance_schema.memory_summary_global_by_event_nameWHERE EVENT_NAME LIKE 'memory/performance_schema/%';