This page is original PmaControl documentation. It keeps a practical index structure for MySQL optimization work, without copying third-party content or depending on an external product.

Objective

MySQL parameters control memory, threads, caches, durability, and I/O. They should be documented by role rather than changed as a magic list.

Parameter Families

Global memory: innodb_buffer_pool_size, key_buffer_size, table caches.
Per-session memory: sort_buffer_size, join_buffer_size, read_rnd_buffer_size, tmp_table_size.
InnoDB I/O: flush, redo log, page cleaners, read and write threads.
Connections: max_connections, thread cache, file limits.
Historical compatibility: query cache, MyISAM, and obsolete parameters.

Method

Start with SHOW VARIABLES and SHOW GLOBAL STATUS, then compare values with observed behavior. A high value can worsen total memory use when it is allocated per connection.

Inspect effective values and their scope

Start with server identity and the values the running process actually uses. Configuration files alone can miss command-line overrides, persisted values or settings changed at runtime. Global defaults and an existing session can differ. Record units, scope, source, dynamic/restart requirements and the original value before proposing a change. The following inventory omits variables that the installed version does not expose.

SELECT VERSION() AS server_version, @@version_comment AS distribution;
SHOW GLOBAL VARIABLES WHERE Variable_name IN (
  'innodb_buffer_pool_size', 'innodb_log_buffer_size',
  'innodb_redo_log_capacity', 'innodb_log_file_size',
  'innodb_flush_log_at_trx_commit', 'sync_binlog',
  'max_connections', 'thread_cache_size', 'table_open_cache',
  'sort_buffer_size', 'join_buffer_size', 'tmp_table_size',
  'max_heap_table_size', 'internal_tmp_mem_storage_engine',
  'temptable_max_ram', 'temptable_max_mmap'
);
SHOW SESSION VARIABLES WHERE Variable_name IN (
  'sort_buffer_size', 'join_buffer_size', 'tmp_table_size',
  'max_heap_table_size'
);

Tie each parameter family to evidence

Family	How to confirm	Tradeoff to evaluate
Buffer pool	Working set, read deltas, process RSS	Cache benefit versus host memory headroom
Redo capacity	Write rate, log waits, checkpoint pressure	Disk capacity and recovery behavior
Session buffers	Actual sorts, joins and active concurrency	On-demand allocations and multiple operators
Connection and table limits	Rejections, thread churn, table-open rate	More capacity can increase memory and contention
Commit durability	Commit latency and required failure tolerance	Throughput versus acknowledged data-loss exposure

Budget memory by allocation behavior

Do not multiply every configured buffer by max_connections and call the result actual RAM usage. Many query buffers are allocated on demand, and a query can allocate several join buffers or temporary tables. Separate global memory, connected-session overhead and active-query memory; retain a stress scenario as a bound to investigate. Include the OS, other services and instrumentation, then compare the model with measured RSS and memory pressure.

MySQL TempTable has a shared RAM budget governed by temptable_max_ram; from MySQL 8.0.28, tmp_table_size also limits each internal TempTable table. MEMORY-based internal temporary tables use the smaller of tmp_table_size and max_heap_table_size. These rules are engine- and version-specific. Redo log capacity is disk space, whereas innodb_log_buffer_size is RAM: do not add both to a memory total.

Respect version and persistence boundaries

MySQL 8.0.30 introduced innodb_redo_log_capacity; older releases use redo-file sizing variables. The query cache belongs to older MySQL versions and was removed in MySQL 8. MariaDB has its own variable lifecycle and must be checked against its own manual. Never copy a configuration containing unknown or removed variables into a new release without validation.

For each change, verify whether it affects existing sessions, new sessions or requires restart. A runtime global change is not necessarily persistent; MySQL SET PERSIST writes a separate persisted configuration. Keep runtime and intended restart values consistent, record rollback for both, and test reconnect/restart behavior in a controlled environment. Compare one hypothesis at a time with the same workload and preserve required durability rather than trading it away for a headline throughput gain.

Continue the investigation

Use the memory calculator for explicit allocation scenarios and the parameter index for individual settings.

MySQL Performance Parameters