Andrew Mercer
on this page

MySQL/MariaDB Performance Tuning

See Overview for context on the legacy MyISAM-era parameters that appear below.

Slow Query Logging

log_slow_queries        = /var/log/mysql/slow.log
long_query_time         = 2
log-queries-not-using-indexes

long_query_time is in seconds — queries taking longer than this get logged. log-queries-not-using-indexes is noisy on a busy schema without good indexing, but very effective at surfacing missing indexes quickly after enabling it.

Example my.cnf Tuning Block

These are classic MyISAM-era tuning values — treat them as a historical reference point for what parameters exist and roughly what scale they operate at, not as recommended defaults for a modern InnoDB-based install (innodb_buffer_pool_size in particular should typically be sized much larger than shown here — commonly 60–80% of available RAM on a dedicated database host, not a fixed small value).

[mysqld]
#
# * Fine Tuning
#
key_buffer               = 16M
max_allowed_packet       = 16M
thread_stack              = 192K
thread_cache_size         = 8
myisam-recover            = BACKUP
max_connections            = 400
table_cache                = 500
wait_timeout                = 60
interactive_timeout         = 60
#
# * Query Cache Configuration
#
query_cache_limit         = 1M
query_cache_size          = 128M
innodb_buffer_pool_size    = 350M

Note: the MySQL/MariaDB query cache (query_cache_size/query_cache_limit) was removed entirely in MySQL 8.0 and is deprecated in recent MariaDB releases — it doesn't scale well under concurrent writes and is generally superseded by application-level or proxy caching. These settings only apply to older installs where it's still present.

mysqltuner

A script that inspects a running instance and suggests specific config changes based on observed usage patterns:

wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
chmod +x mysqltuner.pl
./mysqltuner.pl

It prompts for admin credentials, then reports on storage engine usage, security basics (e.g. whether all accounts have passwords), and a set of performance metrics — connection usage, key buffer hit rate, query cache efficiency (on installs where it's still present), temp table behavior, and table cache hit rate — finishing with concrete variable-by-variable recommendations. Worth running periodically on any instance that's had default or years-old tuning values sitting untouched, rather than trying to hand-derive the same numbers from SHOW STATUS output.

-- Snapshot of open file/table handles
SHOW GLOBAL STATUS LIKE '%open%';

-- Query cache stats (older installs only — see note above)
SHOW STATUS LIKE 'qc%';

# Quick uptime/throughput summary
mysqladmin status

-- Connection and version info for the current session
STATUS;
  • Troubleshooting — what to do when max_connections or table_cache limits are actually being hit