How to optimize MySQL or MariaDB server performance

Optimizing MySQL or MariaDB server performance keeps application latency predictable, reduces timeout cascades, and delays expensive scaling by extracting more work from existing CPU, memory, and storage.

Most OLTP performance is determined by the query optimizer choosing index-friendly plans, InnoDB keeping hot pages in the buffer pool, and the write path committing to the redo log without avoidable I/O stalls. Global status counters plus slow query visibility provide a reliable signal for separating CPU pressure, disk latency, lock contention, and inefficient SQL.

Configuration variables interact with workload shape, storage latency, and durability requirements, so changes belong in small, reversible increments backed by before/after measurements. Some settings require a service restart, invalid options can prevent mysqld from starting, and oversized memory settings can trigger swapping or the OOM killer.

MySQL/MariaDB server tuning checklist:

Open a mysql client session with administrative read access.

$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
mysql>

Capture baseline concurrency counters during representative load.

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

Capture baseline InnoDB buffer pool read counters during the same load.

$ mysql --table -u root -p -e "SHOW GLOBAL STATUS WHERE Variable_name IN ('Innodb_buffer_pool_read_requests','Innodb_buffer_pool_reads');"
+----------------------------------+-------+
| Variable_name                    | Value |
+----------------------------------+-------+
| Innodb_buffer_pool_read_requests | 26327 |
| Innodb_buffer_pool_reads         | 1056  |
+----------------------------------+-------+

Buffer pool hit ratio can be approximated as 1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests).

Capture baseline temporary table spill counters to estimate disk-based temp work.

$ mysql --table -u root -p -e "SHOW GLOBAL STATUS WHERE Variable_name IN ('Created_tmp_tables','Created_tmp_disk_tables');"
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_tables      | 25    |
+-------------------------+-------+

High Created_tmp_disk_tables values often correlate with slow sorts, large GROUP BY operations, or insufficient temp limits.

Confirm slow query logging variables for troubleshooting visibility.

$ mysql --table -u root -p -e "SHOW VARIABLES WHERE Variable_name IN ('slow_query_log','long_query_time','log_output','slow_query_log_file');"
+---------------------+-------------------------------+
| Variable_name       | Value                         |
+---------------------+-------------------------------+
| log_output          | FILE                          |
| long_query_time     | 1.000000                      |
| slow_query_log      | ON                            |
| slow_query_log_file | /var/log/mysql/mysql-slow.log |
+---------------------+-------------------------------+

Review innodb_buffer_pool_size against the host memory budget.

$ mysql --table -u root -p -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 268435456 |
+-------------------------+-----------+

Leave headroom for OS page cache, other daemons, per-connection memory, filesystem cache to avoid swapping.

Review max_connections against expected peak concurrency.
```
$ mysql --table -u root -p -e "SHOW VARIABLES LIKE 'max_connections';"
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
```
High connection limits can multiply per-connection memory usage through thread stacks, buffers, and session state.

Related: How to limit active connections in MySQL or MariaDB
Related: How to view and set server variables in MySQL or MariaDB

Review thread_cache_size to reduce thread creation overhead.

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

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

Fast-growing Threads_created relative to Connections can indicate insufficient thread caching or frequent connect/disconnect patterns.

Review redo log sizing variables to identify checkpoint pressure risk.

$ mysql --table -u root -p -e "SHOW VARIABLES WHERE Variable_name IN ('innodb_log_file_size','innodb_log_files_in_group','innodb_redo_log_capacity');"
+---------------------------+-----------+
| Variable_name             | Value     |
+---------------------------+-----------+
| innodb_log_file_size      | 50331648  |
| innodb_log_files_in_group | 2         |
| innodb_redo_log_capacity  | 268435456 |
+---------------------------+-----------+

Some MySQL versions expose redo sizing via innodb_redo_log_capacity, while many MariaDB deployments use innodb_log_file_size plus innodb_log_files_in_group.

Apply configuration changes incrementally with before/after measurement.

Single-variable changes reduce the chance of masking regressions or attributing improvements to the wrong change.

Related: How to monitor performance metrics in MySQL or MariaDB
Restart the database service using a safe procedure when a change requires it.

Related: How to manage MySQL or MariaDB service in Linux
Inspect the server error log for startup failures caused by invalid options.

Invalid options can prevent mysqld from starting, which can turn a performance change into an outage without console access.

Related: How to view MySQL or MariaDB error log
Run tuning tools to generate hypotheses for the next change.

Related: How to use MySQLTuner for MySQL or MariaDB
Related: How to monitor performance metrics in MySQL or MariaDB

Re-capture baseline counters after changes to confirm expected movement.

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

Improvement usually shows as lower growth rates for Innodb_buffer_pool_reads and Created_tmp_disk_tables under comparable load.

Offload read-heavy traffic with replication when workload characteristics allow it.

Related: How to configure MySQL or MariaDB replication
Distribute replica reads through a load balancer when multiple nodes exist.

Related: How to set up a MySQL load balancer 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.