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