The page
This was my first infrastructure job, onsite at a client. Their main system went down most mornings, right when everyone logged in. By the time someone looked, it had usually come back on its own, so nobody had ever caught it in the act.
Triage
I stopped waiting for the call and sat on the box from 8:45 with a few terminals open.
# CPU, memory and run queue every second
vmstat 1
# disk: look at %util and await, not just throughput
iostat -x 1
# what the database is doing right now
mysql -e "SHOW FULL PROCESSLIST;"
# how close we are to the connection limit
mysql -e "SHOW STATUS LIKE 'Threads_connected'; SHOW VARIABLES LIKE 'max_connections';"
CPU looked fine and disk was busy but not pinned. The process list was the interesting one. Connections climbed fast just before 9, and a lot of them sat in a lock wait behind one long query.
Root cause
An overnight report job was set to run "early morning" and in practice was still running when people logged in. It held locks on the tables the login path needed. Every new session waited, the app opened another connection, and it hit the connection limit. Then everything failed together.
The fix
The short-term fix was moving the job's schedule and killing it if it ran past a cut-off. After that I added the index the report's query was missing, so it finished in a fraction of the time. I kept watching the box every morning for a couple of weeks to be sure.
Downtime for that client went down 83%.
What I learnt
If a problem fixes itself before anyone looks, you have to be there when it starts. Sitting on the box at the right time told me more than any of the logs from after the fact.
What I do now
- When something fails on a schedule, look for the other thing on that schedule.
- Watch connection counts next to the hard limit. The limit is where the outage starts.
- Give batch jobs a deadline, and stop them when they miss it.
stage 0 starts the same way, find the bottleneck first → get the free audit