MySQL Database Performance Tuning Basics

MySQL Database Performance Tuning Basics

A slow database is the most common cause of a slow application. Before you scale up your server or rewrite code, a few hours of methodical MySQL tuning often recovers most of the lost performance. This guide walks through the fundamentals on a typical Linux VPS or cloud server running MySQL 8.0: measure, size memory, read query plans, and add the right indexes.

These steps apply to any standard MySQL 8.0 install — including databases hosted in-Kingdom on a Skyline Cloud VPS or cloud server, where keeping your data resident in Saudi Arabia helps with PDPL and NCA compliance.

Prerequisites

• A Linux server (Ubuntu 22.04/24.04, Debian, or RHEL family) with MySQL 8.0 installed.
• A user with sudo access and a MySQL account with administrative privileges.
• A real or representative workload — tuning against an idle database tells you nothing.

Always change one variable at a time and re-measure. Tuning without measurement is guessing.

Step 1 — Measure First With the Slow Query Log

You cannot tune what you do not measure. The slow query log records every statement that exceeds a time threshold, which is the fastest way to find your worst offenders.

Enable it at runtime (no restart needed):

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;            -- log queries slower than 1 second
SET GLOBAL log_queries_not_using_indexes = 'ON';

To make it permanent, add this to your MySQL config (/etc/mysql/mysql.conf.d/mysqld.cnf on Ubuntu/Debian, or /etc/my.cnf on RHEL):

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1
log_queries_not_using_indexes = 1

Let a representative workload run, then analyze the log. The Percona Toolkit's pt-query-digest groups identical queries by fingerprint and ranks them by total time consumed:

sudo apt install percona-toolkit   # Debian/Ubuntu
pt-query-digest /var/log/mysql/mysql-slow.log | less

Focus on the queries at the top of the report — the ones with the highest total execution time, not just the highest single-run time. Optimizing a query that runs 10,000 times a minute beats optimizing one that runs nightly.

Step 2 — Size the InnoDB Buffer Pool

InnoDB is the default storage engine, and its buffer pool caches table and index data in RAM. If your working set fits in the buffer pool, MySQL serves most reads from memory instead of disk — usually the single biggest win.

Check your current size and hit ratio:

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

SELECT
  (1 - (SELECT VARIABLE_VALUE FROM performance_schema.global_status
        WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads')
      / NULLIF((SELECT VARIABLE_VALUE FROM performance_schema.global_status
        WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'), 0)) * 100
  AS hit_ratio_pct;

Aim for a hit ratio of 99% or higher. Per Oracle's documentation, set the buffer pool to 50–75% of total RAM on a dedicated database server, leaving room for the OS and MySQL's other buffers (InnoDB itself reserves roughly 10% more than the configured size). On a small shared server, stay nearer 50% and leave at least 1–2 GB for the OS.

For an 8 GB dedicated server, around 5–6 GB is a reasonable starting point:

[mysqld]
innodb_buffer_pool_size = 5G

Restart MySQL and re-check the hit ratio after the workload warms the cache. Do not oversize it: if the buffer pool plus other processes exceed RAM, the OS swaps to disk and performance collapses.

Step 3 — Read Query Plans With EXPLAIN

Once you know which queries are slow, find out why. EXPLAIN shows how MySQL intends to execute a statement:

EXPLAIN SELECT * FROM orders WHERE customer_id = 42 AND status = 'shipped';

The columns that matter most:

In MySQL 8.0 you can go further with EXPLAIN ANALYZE, which actually runs the query and reports real timings alongside the estimates:

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42 AND status = 'shipped';

This is the most reliable way to confirm whether an index change actually helped.

Step 4 — Add the Right Indexes

A full table scan (type: ALL) on a large table is the classic cause of slow queries. Add an index on the columns used in WHERE, JOIN, and ORDER BY clauses.

For the query above, a composite index covering both filtered columns lets MySQL jump straight to matching rows:

CREATE INDEX idx_orders_customer_status
  ON orders (customer_id, status);

Column order matters: put the most selective column — or the one used for equality matches — first. A composite index also serves queries that filter on just the leading column (customer_id), so you rarely need a separate single-column index for it.

Re-run EXPLAIN ANALYZE to confirm type improved (e.g. from ALL to ref) and that rows dropped sharply.

Indexes are not free — they consume disk space and slow down writes (INSERT/UPDATE/DELETE), since each index must be maintained. Index deliberately, and remove unused indexes you can identify via the sys.schema_unused_indexes view:

SELECT * FROM sys.schema_unused_indexes;

Step 5 — Verify, Then Iterate

Tuning is a loop, not a one-off. After each change:

1. Re-run the affected query with EXPLAIN ANALYZE and compare timings.
2. Let the slow query log run again and re-digest it.
3. Confirm the buffer pool hit ratio stayed high.

Roll back any change that did not help. Keep notes on what you changed and why — future-you will thank present-you.

Common Pitfalls

• Tuning a single query in isolation. Optimize by total impact (frequency × cost), which the slow log digest reveals.
• Copying my.cnf values from blogs. Buffer pool size depends on your RAM and dataset. Measure, don't copy.
• Wrapping indexed columns in functions. WHERE DATE(created_at) = '2026-06-08' cannot use an index on created_at; rewrite as a range: WHERE created_at >= '2026-06-08' AND created_at < '2026-06-09'.
• SELECT * on wide tables. Fetch only the columns you need so MySQL can use covering indexes.

Conclusion

MySQL performance tuning is methodical, not magical: measure with the slow query log, give InnoDB enough RAM, read query plans with EXPLAIN ANALYZE, and add indexes deliberately. These four basics resolve the majority of real-world slowdowns before any hardware upgrade is needed.

For workloads that need scaling, predictable latency, or Arabic-speaking local support, host your databases in-Kingdom. Explore Skyline Cloud VPS and cloud servers, pair them with business email hosting for a complete stack, and create your Skyline Cloud account to deploy a tuned MySQL server in minutes.