MySQL and MariaDB Performance Tuning for Production

MySQL and MariaDB can support the same application patterns but are separate products with differing features and version behavior. Performance tuning should therefore begin by recording the exact engine, version, storage engine, workload, and slow query. Copying an innodb_buffer_pool_size value from another server without accounting for RAM and other services can cause swapping or an out-of-memory failure.

The SQL below is diagnostic and illustrative for recent MySQL/MariaDB installations. Test on a representative staging copy. Some statements require administrative privileges; never post real query text or credentials in a public support thread.

1. Establish a baseline and the engine version

Record version, uptime, query rate, connection count, CPU, memory, disk latency, and database size. Changes need a before/after comparison. A slow page can be caused by app code, network calls, or cache misses rather than the database server.

SELECT VERSION();
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Slow_queries';
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

These values are starting points. Slow_queries depends on logging configuration and is cumulative since startup; it is not a direct count of current bad queries. Avoid enabling verbose query logging without considering performance and sensitive data.

2. Investigate the query that matters

Find a representative slow query from approved telemetry. Use EXPLAIN to inspect whether it scans a large table, uses an index, sorts, or joins inefficiently. Check row counts and query frequency. Add or change an index only after verifying the predicate and expected selectivity.

EXPLAIN SELECT id, total
FROM invoices
WHERE client_id = 123 AND status = 'unpaid'
ORDER BY due_at
LIMIT 20;

The table and values are examples. A composite index may help if the application routinely filters by client_id and status, then sorts by due_at, but the exact index requires measurement. Every index adds write and storage cost; redundant indexes make inserts and updates more expensive.

3. Size memory for the whole host

The InnoDB buffer pool is important for frequently accessed data and indexes, but the host also needs memory for connections, temporary tables, OS cache, PHP/app workers, and monitoring. Do not allocate nearly all RAM to the database if it shares the server with web processes.

Check swap and memory pressure during peak traffic. A server with high buffer pool hit rate can still be slow because of lock contention or poor queries. Apply configuration changes one at a time and document the result.

4. Review connections, locks, and disk

Connection storms may reflect missing application pooling, slow queries, or a retry loop. Increasing max_connections without memory headroom can turn a queue into a host-wide outage. Examine long-running transactions and locks using the engine's current supported instrumentation. Monitor storage free bytes and inode use; a full disk can affect temporary tables, logs, and replication.

5. Preserve a tested backup path

A tuned production database still needs a backup that meets recovery objectives. MySQL's documentation describes logical and physical backup methods; MariaDB has its own supported tooling. Choose consistency options appropriate to the storage engine and write activity. Protect the backup file and perform an isolated restore after major version or schema changes.

Troubleshooting and common errors

• Query still slow after adding index: inspect actual plan, selectivity, statistics, and whether the application uses a different SQL statement.
• Performance worsens after increasing buffer pool: check total host memory, swap, and concurrent application workers.
• Connections hit the limit: find why clients remain open or requests are slow before raising the limit.
• Database latency spikes at the same time each day: correlate backups, reports, batch jobs, and checkpoint/storage activity.
• MySQL advice behaves differently on MariaDB: verify the documentation for the exact engine and version; do not assume settings are interchangeable.

Security considerations

Use a least-privilege application account, restrict database network access, and encrypt backups. Query logs and performance views may expose customer data; limit access and retention. Avoid running tuning scripts from untrusted sources with database administrator privileges.

FAQ

Is a larger buffer pool always better?

Only until it competes with the OS and other processes. Measure real memory pressure and workload before increasing it.

Should I turn on the slow-query log?

It is useful when configured with an appropriate threshold, access control, and retention. Review overhead and sensitive query content.

Can a new index slow down writes?

Yes. Inserts and updates may maintain each relevant index, so compare both read and write behavior.

Continue reading

Compare PostgreSQL indexing and backups and build a restore-tested backup plan. Contact East4Serv for workload-specific database support.

References: MySQL 8.4 optimization, MySQL backup and recovery, and MariaDB server performance documentation.