How to optimize MySQL or MariaDB performance
Optimizing MySQL or MariaDB performance keeps application latency predictable, reduces lock and queue buildup, and delays expensive hardware or topology changes by removing the actual bottlenecks first.
Most database slowdowns come from a small set of causes: queries that read or sort too many rows, stale optimizer statistics, memory settings that miss the working set, and connection bursts that create scheduler pressure. Slow-query logging, EXPLAIN, runtime plan tools, and status counters narrow the problem down to the statements and resources that consume the most total time.
Tuning changes are not interchangeable. Slow-query logs can capture literal values, indexes speed reads but increase write cost, buffer-pool growth competes with the rest of the host for RAM, and maintenance statements such as OPTIMIZE TABLE can rebuild large InnoDB tables. Safe tuning keeps a before-and-after measurement window, applies one change at a time, and retains a rollback path for memory, logging, or table-maintenance changes.
MySQL/MariaDB performance optimization overview:
- Capture a baseline of query rate, connection pressure, and buffer-pool read pressure before changing anything.
$ 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 | 2671 | | Innodb_buffer_pool_reads | 152 | | Queries | 10 | | Threads_connected | 1 | | Threads_running | 1 | +----------------------------------+-------+Compare deltas from similar load windows instead of trusting one isolated sample.
- Confirm the slow-query log destination and threshold before collecting tuning candidates.
$ mysql --table -u root -p -e "SHOW GLOBAL VARIABLES WHERE Variable_name IN ('slow_query_log','slow_query_log_file','long_query_time','log_output');" +---------------------+----------------------------------------------+ | Variable_name | Value | +---------------------+----------------------------------------------+ | log_output | FILE | | long_query_time | 0.100000 | | slow_query_log | ON | | slow_query_log_file | /var/lib/mysql/performance-optimize-slow.log | +---------------------+----------------------------------------------+MariaDB 10.11 and newer documentation also refers to renamed log_slow_query* variables, but the shared slow_query_log* and long_query_time names remain valid for cross-server workflows.
- Collect slow statements during a representative load window and rank digests by total time or call count before tuning anything.
High-frequency queries with moderate latency often consume more total time than the single slowest statement.
Use mysqldumpslow on MySQL and mariadb-dumpslow on current MariaDB toolchains to summarize file-based slow logs.
- Run EXPLAIN on the worst candidate query before changing indexes or server variables.
$ mysql -u root -p -e "EXPLAIN SELECT id, total FROM appdb.orders_perf WHERE customer_id = 7 ORDER BY created_at DESC LIMIT 5\G" *************************** 1. row *************************** id: 1 select_type: SIMPLE table: orders_perf type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1000 Extra: Using where; Using filesortRed flags include type: ALL, key: NULL, Using filesort, Using temporary, or a large rows estimate for a query that should be selective.
- Rewrite the statement or add a composite index only when the filter and sort pattern clearly justify it.
$ mysql -vvv -u root -p -e "CREATE INDEX idx_orders_perf_customer_created ON appdb.orders_perf (customer_id, created_at);" -------------- CREATE INDEX idx_orders_perf_customer_created ON appdb.orders_perf (customer_id, created_at) -------------- Query OK, 0 rows affected (0.010 sec) Records: 0 Duplicates: 0 Warnings: 0 Bye
Equality filters usually come first in the index, followed by range or ordering columns.
Every extra index increases write amplification, buffer-pool pressure, and disk usage on busy tables.
- Validate the tuned access path with the server's runtime plan tool before keeping the change.
$ mysql --table -u root -p -e "ANALYZE SELECT id, total FROM appdb.orders_perf WHERE customer_id = 7 ORDER BY created_at DESC LIMIT 5;" +------+-------------+-------------+------+----------------------------------+----------------------------------+---------+-------+------+--------+----------+------------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-------------+-------------+------+----------------------------------+----------------------------------+---------+-------+------+--------+----------+------------+-------------+ | 1 | SIMPLE | orders_perf | ref | idx_orders_perf_customer_created | idx_orders_perf_customer_created | 4 | const | 20 | 5.00 | 100.00 | 100.00 | Using where | +------+-------------+-------------+------+----------------------------------+----------------------------------+---------+-------+------+--------+----------+------------+-------------+
MySQL 8.4 uses EXPLAIN ANALYZE and returns tree-format output, while current MariaDB uses ANALYZE to add runtime fields such as r_rows and r_filtered.
- Refresh optimizer statistics after major data changes or when plan quality drifts.
$ mysql --table -u root -p -e "ANALYZE TABLE appdb.orders_perf;" +-------------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +-------------------+---------+----------+----------+ | appdb.orders_perf | analyze | status | OK | +-------------------+---------+----------+----------+
Statistics refresh can change plan choices immediately on the next execution, so re-check the same query after running it.
- Resize innodb_buffer_pool_size so hot data stays in memory without pushing the host into swap.
$ mysql --table -u root -p -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';" +-------------------------+-----------+ | Variable_name | Value | +-------------------------+-----------+ | innodb_buffer_pool_size | 134217728 | +-------------------------+-----------+
Oversizing memory settings can trigger swapping and make latency worse.
- Review connection and table-cache settings only after query and index work stop dominating the load.
$ 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 | 2000 | | thread_cache_size | 151 | +-------------------+-------+Connection spikes usually appear as higher Threads_running, thread churn, and open-table pressure rather than higher useful throughput.
- Use OPTIMIZE TABLE only when reclaiming space or rebuilding a fragmented table is actually part of the fix.
$ mysql --table -u root -p -e "OPTIMIZE TABLE appdb.orders_perf;" +-------------------+----------+----------+-------------------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +-------------------+----------+----------+-------------------------------------------------------------------+ | appdb.orders_perf | optimize | note | Table does not support optimize, doing recreate + analyze instead | | appdb.orders_perf | optimize | status | OK | +-------------------+----------+----------+-------------------------------------------------------------------+
On large InnoDB tables this can still trigger a long-running rebuild, so schedule it like maintenance rather than routine tuning.
- Treat the query cache as a MariaDB-only special case and skip it entirely on MySQL 8.0 and newer.
$ mysql --table -u root -p -e "SHOW VARIABLES WHERE Variable_name IN ('have_query_cache','query_cache_type','query_cache_size');" +------------------+---------+ | Variable_name | Value | +------------------+---------+ | have_query_cache | YES | | query_cache_size | 1048576 | | query_cache_type | OFF | +------------------+---------+Current MariaDB builds keep the query cache available but disabled by default because it does not scale well on high-throughput multi-core workloads.
- Treat tuning-tool output as a starting point only after the slow log, plans, and server counters already point to the main bottleneck.
Prompts from automated tools still need workload-specific validation before changing memory, concurrency, or log settings.
- Re-measure the same workload window after each accepted change and keep rollback notes for memory, logging, or table-maintenance adjustments.
$ 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 | 3282 | | Innodb_buffer_pool_reads | 152 | | Threads_running | 1 | +----------------------------------+-------+Compare the same workload window before and after each change so the accepted settings reflect real improvement instead of coincidence.