How do I know the database is the problem?

Slow queries, timeouts, and a CPU graph that's pegged. If the app feels slow and the database is the wait, that's the sign.

What gets better when the database is faster?

Pages get faster, and you stop paying for a bigger database that a missing index would have fixed.

Do you work with SQL and NoSQL?

Yes. I work with MySQL, PostgreSQL, and SQL Server, and with MongoDB and Redis when those are what you already run. The fix depends on which one is slow.

Will the data stay intact?

I try the change in staging first, and I take a backup before anything that rewrites data.

Can you do this on a cloud database?

Yes. On AWS, and on Google Cloud or Azure if that's where it already runs.

How long does a database pass take?

A few days for the obvious queries. A few weeks if the schema itself is the problem.

Database Optimization

Slow queries, missing indexes, and connection limits. I fix the ones that show up under real load.

Back to Services

What I do

Queries, indexes, connections

I fix the queries, indexes, and connection limits that show up under real load.

Who can reach the database

I look at who can reach the database, and close the accounts that don't need to.

More rows, before the outage

I plan for more rows and more connections before the database is the outage.

What you get

The database you already run

I work with MySQL, PostgreSQL, and the usual managed databases, and I start from the queries you actually run.

The slow query, not a guess

I start from the queries that are actually slow, not from a checklist.

The query and the app around it

A missing index and an N+1 in the app are different fixes. I check both.

A number you can watch

I leave a way to see the slow queries after I leave.

How the work goes

The slow log

I look at the slow log, the locks, and the connection count.

What hurts users first

You get a short list, ordered by what hurts users first.

One change at a time

I change one thing at a time and watch the query time.

The slow-query view stays

I leave the slow-query view in place so the next regression is obvious.

Tools I use

These show up when they fit the job.

MySQL

PostgreSQL

MongoDB

Oracle

Microsoft SQL Server

Redis

Elasticsearch

Cassandra

Amazon RDS

Google Cloud SQL

Azure SQL Database

MySQL

PostgreSQL

MongoDB

Oracle

Microsoft SQL Server

Redis

Elasticsearch

Cassandra

Amazon RDS

Google Cloud SQL

Azure SQL Database

Situations I get called in for

The database runs out of connections

Errors start under load, or right after a deploy added more workers. Each PHP-FPM worker or Lambda can hold its own connection. I look at pooling with RDS Proxy or PgBouncer before raising max_connections.

One page got slow as the table grew

A page that was fine at launch is slow now, and the table behind it is much bigger. Usually a query scans the whole table, or sorts rows an index could return in order.

A bigger instance didn't help

You moved to a larger RDS or Aurora instance and it's still slow. I find out what the queries are waiting on before anyone resizes again.

Users see stale data

Reads go to a replica, and the replica falls behind. I check replica lag, what is writing heavily on the primary, and which reads should go to the primary instead.

What I check first

Top queries by total time

pg_stat_statements on PostgreSQL, the slow query log or Performance Schema on MySQL. A fast query that runs on every request can cost more than one slow report.

EXPLAIN ANALYZE on the worst ones

I look for sequential scans on big tables, row estimates far from the real count, and sorts that spill to disk.

Indexes missing and unused

Columns used in joins and WHERE clauses with no index. PostgreSQL does not index foreign keys for you. Unused indexes go too, since each one slows every write.

Connections and open transactions

The connection count against max_connections, and sessions sitting idle in transaction. On PostgreSQL, a long open transaction also stops vacuum from cleaning dead rows.

The RDS metrics next to the queries

CPUUtilization, FreeableMemory, and DatabaseConnections in CloudWatch. On gp2 storage I also check BurstBalance. When the burst credits run out, every query slows at once.