How to optimize MySQL or MariaDB server performance

Optimizing MySQL or MariaDB server performance keeps transaction latency, queue depth, and read response times stable as traffic grows. A measured tuning pass lets you postpone expensive hardware or topology changes by proving which server-side bottleneck is actually limiting throughput.

Most server-wide slowdowns come from four pressure points: hot data falling out of the InnoDB buffer pool, connection churn creating extra threads, temporary tables spilling to disk, or the redo and binary-log path forcing more flush work than the storage layer can absorb. Status counters such as Threads_running, Created_tmp_disk_tables, Innodb_buffer_pool_reads, and Innodb_log_waits show which condition is growing, while the slow query log separates bad SQL from genuine server-level pressure.

Current branch differences matter. MySQL 8.4 uses innodb_redo_log_capacity for redo sizing and can auto-size key InnoDB settings with innodb_dedicated_server on dedicated hosts, while current MariaDB releases still expose redo sizing through innodb_log_file_size and also expose innodb_buffer_pool_size_max for live buffer-pool growth limits. Make one change at a time, persist it in the option-file chain, and restart only when the current variable or branch actually requires it.

Steps to optimize MySQL or MariaDB server performance:

Identify the server product and version before changing any performance setting.

$ mysql --table -u root -p -e "SELECT VERSION() AS version, @@version_comment AS comment;"
+----------------+--------------------+
| version        | comment            |
+----------------+--------------------+
| 12.2.2-MariaDB | MariaDB Server     |
+----------------+--------------------+

Current MariaDB client packages may provide the preferred mariadb binary as well as a compatibility mysql alias. On MySQL, the same query commonly reports a comment such as MySQL Community Server - GPL.

Capture baseline concurrency, cache, temp-table, and redo-pressure counters during a representative load window.

$ mysql --table -u root -p -e "SHOW GLOBAL STATUS WHERE Variable_name IN ('Threads_connected','Threads_running','Innodb_buffer_pool_read_requests','Innodb_buffer_pool_reads','Created_tmp_tables','Created_tmp_disk_tables','Innodb_log_waits');"
+----------------------------------+-------+
| Variable_name                    | Value |
+----------------------------------+-------+
| Created_tmp_disk_tables          | 0     |
| Created_tmp_tables               | 7     |
| Innodb_buffer_pool_read_requests | 315   |
| Innodb_buffer_pool_reads         | 169   |
| Innodb_log_waits                 | 0     |
| Threads_connected                | 1     |
| Threads_running                  | 1     |
+----------------------------------+-------+

Compare the change in these counters over the same load window before and after each tuning change instead of trusting one isolated sample.

After the working set is warm, a fast-growing Innodb_buffer_pool_reads count points to buffer-pool misses, Created_tmp_disk_tables points to disk-backed sorts or GROUP BY work, and Innodb_log_waits points to redo pressure.

Check whether connection churn is forcing extra thread creation.

$ mysql --table -u root -p -e "SHOW GLOBAL STATUS WHERE Variable_name IN ('Threads_created','Connections');"
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| Connections     | 20    |
| Threads_created | 2     |
+-----------------+-------+

$ mysql --table -u root -p -e "SHOW GLOBAL VARIABLES LIKE 'thread_cache_size';"
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| thread_cache_size | 151   |
+-------------------+-------+

If Threads_created keeps rising quickly relative to Connections, either the thread cache is too small for the workload or clients are reconnecting too often.

Confirm that slow-query visibility is enabled before changing memory, concurrency, or durability settings.

$ mysql --table -u root -p -e "SHOW GLOBAL VARIABLES WHERE Variable_name IN ('slow_query_log','long_query_time','log_output');"
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| log_output      | FILE      |
| long_query_time | 10.000000 |
| slow_query_log  | OFF       |
+-----------------+-----------+

Server tuning without the slow query log often masks the real issue by treating inefficient SQL as a memory or connection problem.

Review the current memory, concurrency, and durability variables before deciding which knob actually matches the bottleneck.

$ mysql --table -u root -p -e "SHOW GLOBAL VARIABLES WHERE Variable_name IN ('innodb_buffer_pool_size','innodb_buffer_pool_size_max','max_connections','thread_cache_size','table_open_cache','innodb_flush_log_at_trx_commit','sync_binlog');"
+--------------------------------+-----------+
| Variable_name                  | Value     |
+--------------------------------+-----------+
| innodb_buffer_pool_size        | 134217728 |
| innodb_buffer_pool_size_max    | 134217728 |
| innodb_flush_log_at_trx_commit | 1         |
| max_connections                | 151       |
| sync_binlog                    | 0         |
| table_open_cache               | 2000      |
| thread_cache_size              | 151       |
+--------------------------------+-----------+

Do not lower innodb_flush_log_at_trx_commit or relax sync_binlog just to chase benchmark numbers unless the workload owner explicitly accepts weaker crash durability.

On dedicated MySQL hosts, current upstream documentation also supports --innodb-dedicated-server to auto-size the buffer pool and redo capacity at startup. Do not assume MariaDB provides the same feature.

Check the redo-sizing variable that matches the server branch you identified.

# MySQL 8.0.30+ / 8.4
$ mysql --table -u root -p -e "SHOW GLOBAL VARIABLES LIKE 'innodb_redo_log_capacity';"

# MariaDB and older MySQL
$ mysql --table -u root -p -e "SHOW GLOBAL VARIABLES WHERE Variable_name IN ('innodb_log_file_size','innodb_log_files_in_group');"
+----------------------+-----------+
| Variable_name        | Value     |
+----------------------+-----------+
| innodb_log_file_size | 100663296 |
+----------------------+-----------+

Current MySQL uses innodb_redo_log_capacity as the primary redo-size control. Current MariaDB still exposes redo sizing through innodb_log_file_size, and newer MariaDB branches no longer guarantee that innodb_log_files_in_group is present.

Inspect InnoDB memory state when cache misses or write stalls stay high.

$ mysql -u root -p -e "SHOW ENGINE INNODB STATUS\G"
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 167772160
Dictionary memory allocated 861600
Buffer pool size   8032
Free buffers       7722
Database pages     310
Old database pages 0
Modified db pages  29

Buffer pool size is shown in pages, typically 16KiB each. Review this output together with Innodb_buffer_pool_reads and Innodb_log_waits instead of changing several InnoDB settings at once.

Print the option-file search order before deciding where to persist a tuning change.
```
$ server_bin=$(command -v mariadbd || command -v mysqld)
$ "$server_bin" --verbose --help 2>/dev/null | sed -n '/Default options are read from the following files in the given order:/,+3p'
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf
##### snipped #####
```
Use the discovered include chain instead of guessing. Common late-loaded override paths are /etc/mysql/conf.d/, /etc/mysql/mariadb.conf.d/, and /etc/my.cnf.d/ depending on the package layout.

Related: How to locate and modify MySQL and MariaDB configuration files
Persist only the setting that matches the bottleneck you measured, and keep it in a late-loaded drop-in instead of editing vendor defaults in place.
```
$ sudoedit /etc/mysql/conf.d/server-performance.cnf
```
```
[mysqld]
innodb_buffer_pool_size = 8G
thread_cache_size = 64
table_open_cache = 4000
```
Add only the variables justified by your baseline. When redo pressure is the real problem, use innodb_redo_log_capacity on current MySQL or innodb_log_file_size on current MariaDB instead of inflating unrelated settings.

Keep innodb_flush_log_at_trx_commit = 1 and, when binary logging is part of recovery or replication, sync_binlog = 1 unless the business has explicitly accepted the weaker durability trade-off.
Trial a dynamic change live only when the current branch documents that variable as dynamic.
```
# MySQL
mysql> SET PERSIST innodb_buffer_pool_size = 8589934592;

# MariaDB
MariaDB> SET GLOBAL innodb_buffer_pool_size = 8589934592;
```
MySQL SET PERSIST writes the value to mysqld-auto.cnf. MariaDB SET GLOBAL changes runtime behavior only, so the same value still belongs in the option file. Current MariaDB can grow the buffer pool live only up to innodb_buffer_pool_size_max set at startup.

Redo sizing is branch-sensitive: current MySQL can resize innodb_redo_log_capacity online, and current MariaDB can resize innodb_log_file_size dynamically, but older releases still need the classic stop-change-start procedure.
Restart the database service only when the chosen change requires it, then inspect the error log immediately if the service does not come back cleanly.
```
# MySQL
$ sudo systemctl restart mysql
$ sudo systemctl is-active mysql
active

# MariaDB
$ sudo systemctl restart mariadb
$ sudo systemctl is-active mariadb
active
```
A bad option-file change can prevent mysqld from starting at all, so do not queue multiple unrelated edits into the same restart.

Related: How to manage MySQL or MariaDB service in Linux
Related: How to view the MySQL or MariaDB server error log

Re-run the same variable and status queries after the change and accept the tuning only when the same workload window shows the expected direction.

$ mysql --table -u root -p -e "SHOW GLOBAL STATUS WHERE Variable_name IN ('Threads_running','Innodb_buffer_pool_reads','Innodb_buffer_pool_read_requests','Created_tmp_disk_tables','Innodb_log_waits');"
+----------------------------------+-------+
| Variable_name                    | Value |
+----------------------------------+-------+
| Created_tmp_disk_tables          | 0     |
| Innodb_buffer_pool_read_requests | 315   |
| Innodb_buffer_pool_reads         | 169   |
| Innodb_log_waits                 | 0     |
| Threads_running                  | 1     |
+----------------------------------+-------+

Successful tuning usually shows as lower growth rates for cache misses, disk-backed temp tables, redo waits, or runnable threads under comparable load, not just a different static variable value.

Escalate to query tuning, read scaling, or topology changes only after the single-node counters stop pointing at a server-level fix.

Related: How to optimize MySQL or MariaDB query performance
Related: How to use MySQLTuner for MySQL or MariaDB
Related: How to configure MySQL or MariaDB replication
Related: How to set up MySQL read/write load balancing with HAProxy and replication

Author: Mohd Shakir Zakaria
Mohd Shakir Zakaria is a cloud architect with deep roots in software development and open-source advocacy. Certified in AWS, Red Hat, VMware, ITIL, and Linux, he specializes in designing and managing robust cloud and on-premises infrastructures.