Database performance: finding and fixing slow queries
Pages load sluggishly, reports run into the night and load climbs at month-end closing: usually only a few causes are behind it. Sortia Labs measures where the time is really lost, in the database, the network or the application, and backs every improvement with before and after numbers.
- Slow database
- SQL query optimisation
- Missing indexes
- Execution plans
- Database tuning
- MeasureSlow queries, waits
- Find the bottleneckPlan, locks, I/O
- FixIndex, query, setting
- Measure againBefore and after
Where we look for the lost time
The expensive queries
The database's own statistics show how the running time is spread across queries. Often it is not one long query but a short one that runs thousands of times a minute.
Execution plans
EXPLAIN shows whether the database uses an index or reads whole tables, and where it misjudges the number of rows. Then we adjust indexes, queries and statistics.
The application
Hundreds of small queries per page view (the N+1 problem), missing pagination or transactions left open come from the code, not the database. Because we build software ourselves, we propose the code change along with it.
Locks and waits
When queries are waiting rather than working, the cause is often locks, long transactions or an exhausted connection pool. Wait events show this, CPU usage does not, and they reveal which session is blocking which.
Configuration and hardware
Buffer memory, memory for sorting and the number of connections often no longer match today's data volume, and slow disks hold back writes. Before you buy bigger servers, we check whether the existing ones are configured properly.
Measuring after the change
Response times and load before and after each measure, as graphs in your monitoring. You see whether a change works and notice early when growing data volumes start to slow things down again.
From measurement to improvement
- 1
Measure
We record a typical load period: queries, waits and resources, if needed exactly when things get slow, for example during a nightly import.
- 2
Rank the causes
We sort the findings by impact and effort. Often a few changes bring a large share of the improvement, and we implement those first.
- 3
Change and verify
Every change is tested with realistic data volumes and measured again. What does not help is rolled back, so that no unnecessary index slows down writes.
Software we build and run ourselves.
Questions about Database performance
Why is our database suddenly slow when nothing has changed?
Usually something did change, just unnoticed: a table grew past a threshold at which the database picks a different plan, statistics are stale, an update shifted defaults, or a new feature runs queries in a loop. Comparing execution plans usually shows where.
Will a bigger server help?
Sometimes, for a while. If a query reads a whole table, twice the computing power makes it at most twice as fast, while a suitable index often speeds it up many times over. So we check queries and settings first and recommend new hardware only when the measurements support it.
Does the analysis disrupt operations?
Hardly. We mainly use statistics the database collects anyway and test changes on a copy. Anything that has to change in production we plan with you, in a maintenance window if needed.
At what data volume does a database get slow?
There is no fixed limit. With suitable indexes and a well-designed data model, tables with many millions of rows can stay fast. Problems tend to come from queries that grow with the data, such as lists without pagination, and from maintenance that cannot keep up.
Read on.
Let us measure where the time goes.
Send us an example: a slow page, a report or a query. We describe how we would track down the cause.