MySQL performance: finding the constraint before changing the configuration
Database slowness is usually attributed to the database and frequently caused somewhere specific — a missing index, a query shape, a schema that grew past its assumptions. Finding which is the work, and there is no setting that substitutes for it.
The configuration is the last place to look, and it is the first place people change
A slow application points at the database, and the accessible response is to raise a buffer size or install a tuning script. Those changes are cheap, reversible and occasionally help. They also produce a server that has been altered repeatedly with no record of what changed, on top of a query that is doing a full table scan — which no configuration setting will fix.
- Pages are slow and the database is the suspected cause
- A query that was fast has degraded as the tables grew
- The application makes hundreds of small queries per page
- Disk I/O is saturated while CPU is idle, or the reverse
- The database was tuned by a script and nobody knows what it changed
- Reports or admin screens are slow while the public site is fine
- Replication lag is affecting reads
- A table has grown past the point where its schema still makes sense
The people who usually bring us this problem
An application that has outgrown its database
It was fast when the tables were small and the same queries are now slow, and you need to know which part is the constraint.
Someone who has already tuned the configuration
Settings have been changed, some of them by a script, and the problem persists or has moved.
A team planning for growth
The database is adequate now and will not be at the volume being planned for.
What this costs while it goes unfixed
Engineering faults are rarely confined to the engineering layer. These are the commercial consequences we see most often.
A missing index is the most common cause and the easiest to prove
It is also the one most often missed, because the query looks reasonable and the table looks normal. An execution plan shows the scan directly, and the fix is a schema change rather than a configuration one — which is why establishing the cause before changing settings matters so much.
Configuration changes are cheap to make and expensive to undo
A buffer size raised once, then again, then by a script, on a server nobody has a baseline for. Each change is defensible in isolation and the accumulation is not, and the original problem is still there underneath. A baseline is what makes any later change attributable.
The query pattern matters as much as the query
An application that issues three hundred queries to render a page has a different problem from one issuing three slow ones, and the fixes are unrelated. Measuring the pattern before optimising any individual statement is what prevents a fast query from being optimised while the count stays where it was.
Schema decisions harden with volume
A column type, a missing foreign key, a table that has become too wide or a primary key that is not the one being queried by. These are cheap to change at a thousand rows and disruptive at a hundred million, which is why the schema is examined before it is a migration rather than after.
Capabilities
Each of these is work we carry out, not an area we advise on.
Measurement before configuration
Establishing where the time actually goes: which statements, how often, how long, and whether the server is waiting on I/O, locks or the CPU. Every later change is judged against this, which is what makes the work reviewable rather than a series of hopeful adjustments.
Slow query analysis
The statements that consume the most total time, which is not the same as the slowest individual statement. A query returning in ten milliseconds three hundred thousand times a day is a larger problem than one taking two seconds twice, and the log sorts by duration rather than by cost.
Execution plan reading
What the planner actually does with each statement: full scans, index selection, join order, temporary tables and filesorts. This is where a missing index becomes visible as a fact rather than an inference, and it is the step that decides whether the fix is in the schema or in the query.
Index design and review
Indexes added for the access patterns that exist, and redundant or unused ones removed. An index is not free — it costs writes, storage and memory — and a table carrying eight indexes where three are used has a write path slower than it needs to be.
Query and application-pattern changes
Rewriting statements, and where the fix is structural, changing how the application accesses the data: fewer round trips, batched reads, pagination that does not use a large offset, and avoiding the N+1 pattern that turns one logical read into hundreds.
Schema review
Column types sized to their data, keys that match the queries, normalisation where it belongs and deliberate denormalisation where it does not, and the tables that have grown past their original assumptions. Examined before volume makes the change disruptive.
Server configuration
Buffer pools, connection handling, logging and I/O settings — reviewed against the measured workload rather than against a script's recommendations, and changed one at a time with the effect recorded. Configuration is a real lever and it is the wrong first one.
Replication and read distribution
Replication lag, read/write splitting and the queries that should not be going to the primary. Where reads are already distributed, lag is frequently the cause of a slowness that looks like a database problem and is a routing one.
Engineering methodology
The sequence is deliberate. The order is usually what determines whether the work holds or has to be repeated.
Record a baseline before touching anything
Query timings, server counters and resource behaviour under normal load. Without it, no later change can be attributed and the engagement cannot conclude — which is the position a server tuned by successive scripts is already in.
Rank by total time, not by individual duration
The query log sorted by duration shows the dramatic statements; sorted by total time it shows the expensive ones. The second is almost always the real constraint on an application workload, and the first is where effort usually goes.
Read the plan before proposing a change
An execution plan converts an opinion about the cause into evidence for it. A full table scan, a filesort or a poor join order is visible directly, and the fix that addresses it is then specific rather than speculative.
Change one thing and measure it
Index, query, schema or configuration — one at a time, measured after each. On a database the interactions are real: an index can change the planner's choice for an unrelated query, and a configuration change can move the constraint rather than remove it.
Fix the pattern before the statement
Where the application issues hundreds of queries per request, optimising each one leaves the count in place. The structural fix is usually the larger gain and it is found by measuring the pattern rather than reading the log.
Do not claim a speed-up that was not measured
The report states what was measured, what changed and what the measurement then showed — on the same workload, with the same data. Where a change produced no improvement it is reverted and recorded as such, because a database carrying changes that did nothing is harder to work on than one that was left alone.
What an engagement produces
Documentation is a deliverable, not an afterthought. On most of these engagements a large part of the value is a defect report precise enough for another team to act on.
Diagnosis
- Where the time goes, measured rather than estimated
- The statements ranked by total cost, and separately by duration
- Execution plans for the statements that matter
- Index usage: what is used, what is unused, what is redundant
- The application's query pattern per request, where that is the constraint
- Schema findings that will become disruptive at higher volume
- Server configuration against the measured workload
Change
- Indexes added for real access patterns, unused ones removed
- Statements rewritten where the query is the cause
- Application access patterns changed where the pattern is the cause
- Schema changes, planned with their migration
- Configuration adjusted one setting at a time, each measured
- Changes that did not help, reverted and recorded
Handover
- What was measured before and after, on the same workload
- What changed and what each change did
- What was deliberately not changed, and why
- The queries to watch, and the signals that indicate the next constraint
- What will need revisiting as volume grows
Architecture and technology
Where database time goes
- Individual statements, by total cost across the workload
- The number of statements per request, where the pattern is the problem
- Index availability, selection and redundancy
- Schema shape: types, keys, width and normalisation
- Server configuration: buffers, connections, logging, I/O
- Lock contention and transaction scope
- Replication lag, where reads are distributed
- Storage performance underneath the database
What a configuration change cannot fix
- A missing index, which a buffer pool cannot compensate for
- A query pattern that issues hundreds of statements per request
- A schema whose keys do not match its queries
- An offset-based pagination reading a million rows to return twenty
- A table scanned because its type prevents index use
- Replication lag caused by the primary's write volume
If this is not quite your problem
These overlap at the edges. Sending you to the right page is more useful than having you work it out.
The server is the constraint, not the database
Serving-path design, capacity and the platform underneath.
Server managementThe application is slow rather than the database
The discipline below the platform, including application-level bottlenecks.
Website performanceThe server needs ongoing administration
Patching, backups, monitoring and access as a routine.
Managed Linux VPS administrationThe platform is a forum
XenForo performance work, where the query patterns are the platform's own.
XenForo performance optimisationFrequently asked
How much faster will the database be?
We will not give a number before measuring, and the reason is not caution. What a change produces depends on the workload, the data distribution, the storage and what the application does with the result — and a figure given before the measurement is a figure invented to win the work. What the engagement commits to is the measurement itself: the same queries, the same data, the same conditions, before and after, with the difference stated plainly and the changes that produced nothing reverted rather than kept.
Is it worth tuning the MySQL configuration?
Sometimes, and it is the last lever rather than the first. Configuration matters when the workload is already reasonable and the server is misconfigured for it — a buffer pool far too small for the working set, or a connection limit that queues requests. It does nothing for a full table scan, a query issued four hundred times per page, or a schema whose keys do not match its queries, and those are the common causes. The order matters because configuration changes are the ones most likely to accumulate without a record.
Do you have DBA references?
No, and we will not present a forum or platform engagement as though it were a database consultancy reference. Our published work is platform and infrastructure engineering in which database performance was part of the engagement, not a standalone DBA engagement a reader could check. What the page can describe is the method: measurement first, execution plans before proposals, one change at a time, and no unmeasured speed claims. If a specialist DBA reference is what you need, that is a reasonable requirement and this is not the page that has one.
Can you work on a managed or hosted database?
Yes, within what the provider allows. A managed database frequently gives you the query layer and not the server layer, which means index and schema work is available and configuration work may not be. That boundary is established during the assessment rather than assumed, and where the constraint sits behind the boundary it is stated as a finding with its consequence — a provider's limits are a real answer, and better stated than worked around.
The problem only appears under load. Can that be reproduced?
Often, and it is worth doing rather than reasoning about. A slow query log from production captures what the workload actually does, and a copy of the data at representative volume lets a change be tested against it. Where the volume cannot be reproduced, the diagnosis works from the production log and the plans, and the verification is done by measuring the same statements in production before and after — slower, and still attributable.
Related capabilities and work
Bring us the problem you have not been able to fix
Describe what is happening rather than what you think the cause is. If we are not the right people for it, we will say so.