Stop database outages before they happen: A monitoring and migration guide

Database emergencies always happen at the worst possible time. You're dealing with angry users, stressed stakeholders, and the pressure to fix everything immediately. The solution? Catch the warning signs early and migrate on your terms, not during a crisis.

This guide covers the specific metrics that predict database problems and how to execute a seamless migration when it's time to upgrade your infrastructure.

What you need to get started

• Database monitoring capabilities (built-in tools work fine)
• Admin access to your database servers
• Understanding of your app's typical database behavior
• Ability to run queries and check system metrics

We'll focus on MySQL and PostgreSQL, but these principles work for most relational databases.

The metrics that actually matter

Database issues develop slowly, then hit you all at once. Here's what to watch:

Connection pool exhaustion

This kills applications faster than any slow query. Monitor your active connections:

-- MySQL
SHOW STATUS LIKE 'Threads_connected';
SHOW VARIABLES LIKE 'max_connections';

-- PostgreSQL
SELECT count(*) FROM pg_stat_activity WHERE state = 'active';
SHOW max_connections;

Alert at 70% of max connections. At 80%, you're in the danger zone.

Query performance trends

Track average execution time over weeks, not individual slow queries:

-- MySQL: Enable slow query logging
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1.0;

-- PostgreSQL: Check query stats
SELECT query, calls, total_time, mean_time
FROM pg_stat_statements
ORDER BY mean_time DESC LIMIT 10;

A steady upward trend in average query time signals growing data or degrading indexes.

Lock contention

Locks create cascading slowdowns across your entire application:

-- MySQL
SELECT * FROM performance_schema.events_waits_summary_global_by_event_name
WHERE event_name LIKE '%lock%' AND count_star > 0;

-- PostgreSQL
SELECT mode, locktype, granted, COUNT(*)
FROM pg_locks
GROUP BY mode, locktype, granted;

Regular lock waits above 100ms indicate table design issues.

Storage performance

Database performance ultimately depends on disk I/O:

# Monitor disk utilization
iostat -x 1

# Watch for:
# %util consistently above 80%
# avgqu-sz above 2
# await times above 20ms

Planning your zero downtime migration

When your metrics consistently show problems, migrate before you're forced into emergency mode.

Choose your strategy

Blue-green deployment for smaller databases (under 100GB):

-- Set up read replica
CHANGE MASTER TO MASTER_HOST='source-db.example.com';
START SLAVE;

-- Monitor replication lag
SHOW SLAVE STATUS\G

Logical replication for larger databases:

-- PostgreSQL setup
-- Source database
CREATE PUBLICATION migration_pub FOR ALL TABLES;

-- Target database
CREATE SUBSCRIPTION migration_sub 
CONNECTION 'host=source-db.example.com user=replicator dbname=production'
PUBLICATION migration_pub;

Verify data consistency

Never migrate without verification. Set up checksums for critical tables:

SELECT 
  table_name,
  COUNT(*) as row_count,
  COALESCE(SUM(CRC32(CONCAT_WS('|', col1, col2, col3))), 0) as checksum
FROM your_table
GROUP BY table_name;

Execute the switchover

1. Stop writes to source database
2. Wait for replication lag to reach zero
3. Verify data consistency with checksums
4. Update application database config
5. Redirect traffic to new database
6. Monitor for errors

Verification after migration

Check multiple layers to confirm success:

Application health

# Response time check
curl -w "Total time: %{time_total}s\n" -o /dev/null -s https://your-app.com/health

# Error rate monitoring
grep "ERROR" /var/log/application.log | wc -l

Database performance

SELECT 
  query_digest,
  avg_timer_wait/1000000 as avg_time_ms,
  count_star as executions
FROM performance_schema.events_statements_summary_by_digest
ORDER BY avg_timer_wait DESC LIMIT 10;

Performance should improve or stay equivalent. Any degradation suggests configuration issues.

Common mistakes to avoid

• Ignoring replication lag: Always verify replication is current before switching
• Connection pool mismatches: Ensure your new environment handles the same connection load
• Missing indexes: Verify all expected indexes exist and are being used
• No rollback plan: Always maintain the ability to switch back

Key takeaways

Database problems are predictable if you measure the right things. Connection exhaustion, trending query slowdowns, lock contention, and storage bottlenecks give you weeks or months of warning before users notice.

The monitoring practices covered here prevent future emergency migrations. Early detection always costs less than emergency response, and migrating on your schedule beats crisis management every time.

Originally published on binadit.com