AI summary by TwinMind
We Had Our First Outage...
Theo - t3․gg
9m 47s
Outage Discovery & Initial Response
- Sunday 9:00 AM: Noticed increased phone notifications, initially assumed viral content
- 30 minutes later: More notifications from CTO and team
- DMs from UploadThing users, group chat, server chat support going off non-stop
- First major outage for UploadThing service
Outage Scale & Root Cause
- Normal daily uploads: 4,000-5,000 files
- Outage day spike: 190,000 to 270,000 files total
- Critical period: 40,000 files uploaded in 30 minutes (10x daily throughput)
- Root cause: Single power user stress-testing service limits
- User was active Discord member, immediately reported issues
- Correlation between user's high throughput and reported service issues
Technical Root Cause Analysis
- P99 latency spiked to 500 milliseconds
- P95 latency reached 250 milliseconds range
- Database query analysis revealed:
- 38 hours of compute time in 30-minute window
- 410 million rows read to return only 165,000 results
- Problem query: Pre-signed POST URL requests using API key lookup
- Missing database index on API key field in database table
- Only had indexes on ID and app_id
- Added unique key index on API key 3 days prior to outage
- Before fix: Full table scan required for every API key lookup
Database Performance Issues
- Each query required scanning entire table of all keys/apps
- Thousands of queries per second during spike
- No indexed lookup available for API key authentication
- Additional issue: Dashboard file query not optimized (indexing by app_id but not file_size due to nullable field)
Resolution & Performance Improvement
- Added proper database index on API key field
- Implemented PlanetScale Boost feature for query caching
- Post-fix performance:
- P95 latency: Under 37 milliseconds (down from 250ms)
- Dramatic improvement in query response times
- Service stability restored
Business & Customer Management
- Immediate transparency approach on Sunday morning
- Public Discord communication about issues and timeline
- Direct outreach to affected users
- Clear communication: issue acknowledgment, fix timeline, prevention measures
- Power user who caused spike:
- Made pull request fixing Cloudflare support as apology
- Continued collaboration and support
- Customer understanding due to new/fast-growing service nature
- 15-minute spike of bad requests acknowledged as serious issue
Tools & Debugging Process
- PlanetScale UI enabled quick identification of problem queries
- Ability to sort by highest P99 latency queries
- Row read analysis capabilities
- Query caching and optimization features
- Significantly easier debugging compared to previous experiences at Twitch
- CEO Sam Lambert personally assisted with throughput optimization
Future Improvements & Lessons
- Focus on transparency and resilience
- Potential status page implementation (challenging due to rapid development pace)
- Realistic customer expectations about service reliability
- No service is 100% outage-free
- Additional pull request in progress for file size indexing edge case
- Continued service optimization and monitoring
