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.
Related: How to optimize MySQL and MariaDB query performance
Related: How to optimize MySQL or MariaDB server performance
Related: How to configure slow query log in MySQL or MariaDB
Related: How to analyze MySQL or MariaDB slow query log
Related: How to use EXPLAIN to view query plans in MySQL or MariaDB
Related: How to create an index in MySQL or MariaDB
Related: How to tune InnoDB buffer pool size in MySQL or MariaDB
Related: How to optimize table structures in MySQL or MariaDB
Related: How to monitor performance metrics in MySQL or MariaDB
Related: How to use MySQLTuner for MySQL or MariaDB
MySQL/MariaDB performance optimization overview:
- Capture a baseline of throughput, concurrency, and InnoDB read pressure before making changes.
mysql> 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_reads | 1842 | | Innodb_buffer_pool_read_requests | 59218471 | | Queries | 221885913 | | Threads_connected | 38 | | Threads_running | 6 | +----------------------------------+-----------+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> SHOW VARIABLES LIKE 'long_query_time'; +-----------------+-----------+ | Variable_name | Value | +-----------------+-----------+ | long_query_time | 10.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> SET GLOBAL slow_query_log = ON; Query OK, 0 rows affected (0.00 sec) mysql> 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 | +--------------------+-------------------------------+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.
- Pick the top candidates by total time and call count.
High-frequency statements with moderate latency often dominate overall load.
- Run EXPLAIN on each candidate statement to confirm the access path.
mysql> EXPLAIN SELECT o.id, o.created_at -> FROM orders o -> WHERE o.customer_id = 123 -> ORDER BY o.created_at DESC -> LIMIT 10; +----+-------------+-------+------+---------------+--------------+---------+-------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+--------------+---------+-------+------+-----------------------------+ | 1 | SIMPLE | o | ref | idx_customer | idx_customer | 4 | const | 42 | 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.
- 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.
- Tune innodb_buffer_pool_size to reduce physical reads without pushing the host into swapping.
mysql> SHOW VARIABLES LIKE 'innodb_buffer_pool_size'; +------------------------+------------+ | Variable_name | Value | +------------------------+------------+ | innodb_buffer_pool_size| 8589934592 | +------------------------+------------+
Oversizing memory settings can trigger swapping and make latency worse.
- Tune connection and cache variables to match workload and memory budget.
mysql> SHOW VARIABLES WHERE Variable_name IN ('max_connections','table_open_cache','thread_cache_size'); ##### snipped #####Connection spikes often show up as higher Threads_running and scheduler contention rather than higher throughput.
- Refresh table statistics when plans become unstable after major data changes.
mysql> ANALYZE TABLE db.table; ##### snipped #####
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.
- 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.
- Run a tuning tool.
Tool output is a starting point, not a checklist.
- Validate tuning-tool recommendations with before/after measurements.
Apply only changes that improve the workload metrics and do not introduce regressions under peak load.
- Re-check the baseline metrics after each change to confirm improvement and avoid regressions.
mysql> SHOW GLOBAL STATUS WHERE Variable_name IN ('Threads_running','Innodb_buffer_pool_reads','Innodb_buffer_pool_read_requests'); ##### snipped #####Keep a rollback plan for changes that shift memory usage or require a restart.
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.
Comment anonymously. Login not required.