Help for owners
My database has become slow and the site waits on it
Short answer
A slow database is usually a few slow queries, a missing index, locks between queries that wait on each other, or a machine too small for the data. Do not guess at settings. The first step is to log the slow queries for a few days, then fix the worst ones. Measure first, change second.
Does this sound like your situation?
- Pages that list orders, products or reports take a long time.
- It was fast when there was little data and slowed down as it grew.
- The site is slow only at busy hours, or while a report or import runs.
- Users see timeouts or an error that a request waited too long.
- My developer suggests a bigger server as the only cure.
Why does this happen?
A few queries do far too much work
One query that reads a whole large table on every page visit can slow everything else. PostgreSQL can log every statement that runs longer than a set time, which its documentation calls helpful for tracking down unoptimized queries, but the default setting of that log is off, so many databases have never been measured.
Confirmed by postgresql.org
Queries wait on each other
A long update or import can hold a lock that other requests wait on until they time out.
Technical detail: A statement waited for a lock held by another transaction and gave up (error 1205)
Code asks the database one row at a time
Writing a thousand rows one by one is far slower than doing it in the right way, and the app looks as if the database is at fault.
Technical detail: batchUpdate inserts one row at a time on MySQL
Connections are dropped on long requests
A query that runs too long can lose its connection, and the page fails instead of just being slow.
Technical detail: The connection to MySQL dropped while a query was running (error 2013)
The data outgrew the machine
A disk that is nearly full, or too little memory for the working data, slows every query. The remedy is to read the readings, not to upgrade blindly.
Technical detail: The Postgres container cannot write postmaster.pid because Docker is out of disk
What can I do about it today?
- Write down which pages and which hoursA slow list page tells me more than a general sentence about the site being slow.
- Ask what runs on a scheduleImports, reports, backups and email sends on the database at busy hours cause slowness that looks random.
- Do not delete old data to speed things upRemoving rows can lose records you need and may not help. Archive decisions come after measuring.
- Do not change database settings from a forum postA setting that helps one database can slow or crash another. Ask for a measurement first.
- Confirm that backups workBefore anything is tuned, ask when the last backup was made and whether anyone has restored it.
When should I bring someone in?
- Customers see timeouts on checkout, booking or login.
- The slowness grows month by month with your data.
- Your developer cannot say which query or table is the cause.
How do I handle it?
- Switch on measurementI turn on slow-statement logging or the equivalent tool for the database you have, and let it collect real traffic.
- Rank the worst offendersI list the queries that cost the most total time, not just the single slowest one, and check how each reads its data.
- Fix the biggest cause firstIndexes, rewritten queries, batching and caching, in the order the readings justify, tested on a copy before production and with a backup taken first.
- Show before and afterI repeat the same measurement and give you plain readings. Whether it is fast for a given visitor also depends on their device and network, so I do not promise a number.
The price depends on what I find, so I quote in writing after a short discovery call. Tell me what is happening and I reply within 24 hours.
What do owners ask about this?
Do I need a different database?
Rarely. Most slow databases are fixed by changing how they are queried and indexed, not by switching products.
Will tuning cause downtime?
I test changes on a copy and take a backup before production, and I plan the order so that live traffic is affected as little as possible. Some changes on large tables need a quiet period, which I tell you in advance.
Is a bigger server the answer?
Sometimes, after the readings show the machine is the limit. Without them it can cost more and fix nothing.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Found a mistake? Tell me.
For your developer: the technical fixes
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionA statement waited for a lock held by another transaction and gave up (error 1205)jdbcTemplate.batchUpdate() takes seconds for a few thousand rowsbatchUpdate inserts one row at a time on MySQLERROR 2013 (HY000): Lost connection to MySQL server during queryThe connection to MySQL dropped while a query was running (error 2013)FATAL: could not write lock file "postmaster.pid": No space left on deviceThe Postgres container cannot write postmaster.pid because Docker is out of disk