How to optimize MySQL or MariaDB performance

Optimizing MySQL or MariaDB performance reduces query latency, improves concurrency, and preserves headroom before traffic growth turns small inefficiencies into outages.

Database speed is largely a product of query plans (indexes, joins, and sorting), InnoDB caching (buffer pool behavior and internal structures), and storage characteristics (fsync latency, I/O contention, and throughput). Instrumentation such as the slow query log, execution-plan inspection, and runtime status counters helps isolate the small set of statements and resources that dominate total time.

Server-level changes can shift memory pressure, increase log volume, or change locking and flushing behavior, so tuning works best as a measured loop: capture a baseline, apply one change, re-test, and keep a rollback path. Maintenance operations such as table rebuilds or large buffer pool changes can be disruptive on large datasets and may require a maintenance window.

MySQL/MariaDB performance optimization overview:

Capture a baseline of throughput, concurrency, and InnoDB read pressure before making changes.

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

Capture at least two samples under similar load and compare deltas rather than single snapshots.

Check the current slow query threshold before enabling logging.

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

Start with a higher threshold if log volume is unknown, then lower it as the workflow stabilizes.

Enable slow query logging during representative load.

$ mysql -vvv -u root -p -e "SET GLOBAL slow_query_log = ON; SHOW VARIABLES WHERE Variable_name IN ('slow_query_log','slow_query_log_file','log_output');"
--------------
SET GLOBAL slow_query_log = ON
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
SHOW VARIABLES WHERE Variable_name IN ('slow_query_log','slow_query_log_file','log_output')
--------------

+---------------------+-------------------------------+
| Variable_name       | Value                         |
+---------------------+-------------------------------+
| log_output          | FILE                          |
| slow_query_log      | ON                            |
| slow_query_log_file | /var/log/mysql/mysql-slow.log |
+---------------------+-------------------------------+
3 rows in set (0.01 sec)

Bye

Persist settings in the server configuration so logging survives a restart.

Summarize the slow query log.

Digests and totals tend to be more actionable than the single slowest entry.

Related: How to analyze MySQL or MariaDB slow query log
Pick the top candidates by total time and call count.

High-frequency statements with moderate latency often dominate overall load.

Related: How to optimize MySQL and MariaDB query performance

Run EXPLAIN on each candidate statement to confirm the access path.

$ mysql -u root -p -e "EXPLAIN SELECT id, total FROM appdb.orders_perf WHERE customer_id = 1 ORDER BY created_at DESC LIMIT 3\G"
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: orders_perf
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 8
     filtered: 12.50
        Extra: Using where; Using filesort

Flags like Using filesort or large rows estimates often point to missing composite indexes or predicates that are not index-friendly.

Rewrite queries that force scans, filesorts, or large temporary tables.

Change one query at a time and re-check the plan after each change.

Related: How to optimize MySQL and MariaDB query performance
Add or adjust indexes only where EXPLAIN shows a measurable reduction in scanned rows or avoided sorts.

Extra indexes speed reads but increase write cost and can bloat disk usage on high-churn tables.

Related: How to create an index in MySQL or MariaDB

Tune innodb_buffer_pool_size to reduce physical reads without pushing the host into swapping.

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

Oversizing memory settings can trigger swapping and make latency worse.

Tune connection and cache variables to match workload and memory budget.
```
$ mysql --table -u root -p -e "SHOW VARIABLES WHERE Variable_name IN ('max_connections','table_open_cache','thread_cache_size');"
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| max_connections   | 151   |
| table_open_cache  | 4000  |
| thread_cache_size | 9     |
+-------------------+-------+
```
Connection spikes often show up as higher Threads_running and scheduler contention rather than higher throughput.

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

Refresh table statistics when plans become unstable after major data changes.

$ mysql --table -u root -p -e "ANALYZE TABLE appdb.orders_perf;"
+-------------------+---------+----------+----------+
| Table             | Op      | Msg_type | Msg_text |
+-------------------+---------+----------+----------+
| appdb.orders_perf | analyze | status   | OK       |
+-------------------+---------+----------+----------+

Updated statistics help the optimizer choose better join orders and index usage.

Rebuild tables only when reclaiming space or reducing fragmentation.

Table rebuilds can be disruptive on large tables and may require a maintenance window.

Related: How to optimize table structures in MySQL or MariaDB
Tune the query cache only on versions that still support it and only when the hit rate is healthy.

MySQL removed the query cache in newer versions; some MariaDB builds still support it.

Related: How to optimize query cache in MariaDB or MySQL
Run a tuning tool.

Tool output is a starting point, not a checklist.

Related: How to use MySQLTuner for MySQL or MariaDB
Validate tuning-tool recommendations with before/after measurements.

Apply only changes that improve the workload metrics and do not introduce regressions under peak load.

Related: How to use MySQLTuner for MySQL or MariaDB

Re-check the baseline metrics after each change to confirm improvement and avoid regressions.

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

Keep a rollback plan for changes that shift memory usage or require a restart.

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.