Working through performance complaints.
WHAT TO ESTABLISH FIRST
Whether the database is actually the problem.
HOW
Time the queries, rather than assuming.
WHAT TO LOOK AT
The running process list, for current activity The slow query log, for history
WHAT TO CHECK ABOUT THE SERVER
Whether it is processor, memory or disk constrained.
WHAT HIGH DISK WAIT WITH LOW PROCESSOR USE MEANS
Reading from disk rather than memory.
WHAT THAT USUALLY MEANS
Data has outgrown the buffer pool, or a query is scanning.
WHAT TO CHECK NEXT
The worst query in the slow log, and its plan.
WHAT USUALLY FIXES IT
An index.
WHAT THE SECOND COMMONEST CAUSE IS
An application issuing many queries where one would do.
HOW TO SEE THAT
Query count per request, from the application or the general log.
WHY THAT PATTERN IS EASY TO MISS
Each query is fast, and only the total is slow.
WHAT THE THIRD IS
A lock held by something else.
HOW TO SEE IT
Transactions open and what they wait on.
WHAT TO CHECK BEFORE BLAMING TRAFFIC
Whether a backup, import or report is running.
WHAT TO FIX FIRST
The single worst offender, then measure again.
