MySQL or MariaDB tuning basics

Topic: Databases core

Tune MySQL or MariaDB with innodb_buffer_pool_size, query cache (if available), connection limits, and slow query log. Use this when improving performance or after measuring bottlenecks.

Intent: How-to

Quick answer

  • Set innodb_buffer_pool_size to about 70-80 percent of RAM on a dedicated DB server. This is the main lever for InnoDB performance. Restart to apply.
  • Enable slow_query_log and long_query_time to find slow queries. Use EXPLAIN on slow queries; add indexes on filtered and joined columns. Run OPTIMIZE TABLE sparingly.
  • max_connections and thread cache; ensure enough for app peak. Monitor InnoDB metrics and disk I/O. Avoid over-allocating memory; leave room for OS and connections.

Prerequisites

Steps

  1. Buffer pool and memory

    Set innodb_buffer_pool_size in my.cnf. Restart. Monitor InnoDB buffer pool hit ratio; increase if RAM allows and hit ratio is low.

  2. Slow query log

    slow_query_log = 1; long_query_time = 2; Restart or SET GLOBAL. Analyze slow query log; EXPLAIN and add indexes.

  3. Connections and I/O

    Set max_connections to expected peak. Tune innodb_io_capacity if using SSDs. Document changes and monitor after tuning.

Summary

Tune buffer pool, enable slow query log, and adjust connections and I/O settings. Measure before and after.

Prerequisites

Steps

Step 1: Buffer pool and memory

Set innodb_buffer_pool_size; restart; monitor buffer pool usage.

Step 2: Slow query log

Enable slow query log; identify and optimize slow queries with EXPLAIN and indexes.

Step 3: Connections and I/O

Set max_connections; tune I/O capacity if needed; document and monitor.

Verification

  • Buffer pool hit ratio healthy; slow queries reduced; no connection errors.

Troubleshooting

OOM — Reduce buffer pool or max_connections. Still slow — Check disk I/O and locks; optimize queries and indexes.

Next steps

Continue to