Как исправить Connection timeout в базе данных

 1# Python с SQLAlchemy
 2from sqlalchemy import create_engine
 3from sqlalchemy.pool import QueuePool
 4
 5engine = create_engine(
 6    'postgresql://user:pass@host/db',
 7    pool_size=10,
 8    max_overflow=20,
 9    pool_timeout=30,
10    pool_recycle=3600,
11    connect_args={
12        'connect_timeout': 10,
13        'application_name': 'myapp'
14    }
15)

 1-- PostgreSQL
 2-- Проверь текущие подключения
 3SELECT count(*) FROM pg_stat_activity;
 4
 5-- Убей зависшие подключения
 6SELECT pg_terminate_backend(pid) 
 7FROM pg_stat_activity 
 8WHERE state = 'idle' AND now() - state_change > interval '10 minutes';
 9
10-- Проверь лимиты
11SHOW max_connections;
12SHOW statement_timeout;

 1-- MySQL
 2-- Проверь текущие подключения
 3SHOW PROCESSLIST;
 4
 5-- Убей зависшие подключения
 6KILL connection_id;
 7
 8-- Проверь лимиты
 9SHOW VARIABLES LIKE 'max_connections';
10SHOW VARIABLES LIKE 'wait_timeout';

 1# connection_monitor.py
 2import psycopg2
 3import time
 4from contextlib import contextmanager
 5
 6@contextmanager
 7def db_connection():
 8    start_time = time.time()
 9    try:
10        conn = psycopg2.connect(
11            host='localhost',
12            database='myapp',
13            user='user',
14            password='pass',
15            connect_timeout=10
16        )
17        yield conn
18    except psycopg2.OperationalError as e:
19        print(f"Connection failed after {time.time() - start_time:.2f}s: {e}")
20        raise
21    finally:
22        if 'conn' in locals():
23            conn.close()
24
25# Использование
26try:
27    with db_connection() as conn:
28        with conn.cursor() as cur:
29            cur.execute("SELECT 1")
30            print("Connection successful")
31except Exception as e:
32    print(f"Connection error: {e}")

 1# retry_connection.py
 2import time
 3import psycopg2
 4from functools import wraps
 5
 6def retry_on_timeout(max_retries=3, delay=1):
 7    def decorator(func):
 8        @wraps(func)
 9        def wrapper(*args, **kwargs):
10            for attempt in range(max_retries):
11                try:
12                    return func(*args, **kwargs)
13                except psycopg2.OperationalError as e:
14                    if "timeout" in str(e).lower() and attempt < max_retries - 1:
15                        print(f"Connection timeout, retrying in {delay}s...")
16                        time.sleep(delay)
17                        delay *= 2  # Exponential backoff
18                    else:
19                        raise
20            return func(*args, **kwargs)
21        return wrapper
22    return decorator
23
24@retry_on_timeout(max_retries=3)
25def execute_query(query):
26    with psycopg2.connect(
27        host='localhost',
28        database='myapp',
29        user='user',
30        password='pass',
31        connect_timeout=10
32    ) as conn:
33        with conn.cursor() as cur:
34            cur.execute(query)
35            return cur.fetchall()

 1# health_check.py
 2import psycopg2
 3import time
 4from datetime import datetime
 5
 6def check_db_health():
 7    try:
 8        start_time = time.time()
 9        conn = psycopg2.connect(
10            host='localhost',
11            database='myapp',
12            user='user',
13            password='pass',
14            connect_timeout=5
15        )
16        with conn.cursor() as cur:
17            cur.execute("SELECT 1")
18            result = cur.fetchone()
19        
20        response_time = time.time() - start_time
21        conn.close()
22        
23        return {
24            'status': 'healthy',
25            'response_time': response_time,
26            'timestamp': datetime.now()
27        }
28    except Exception as e:
29        return {
30            'status': 'unhealthy',
31            'error': str(e),
32            'timestamp': datetime.now()
33        }
34
35# Мониторинг
36while True:
37    health = check_db_health()
38    print(f"DB Health: {health}")
39    time.sleep(30)

 1# Проверка сетевого подключения
 2# Тест ping
 3ping -c 3 db-server
 4
 5# Тест порта
 6nc -z db-server 5432
 7
 8# Проверка DNS
 9nslookup db-server
10
11# Проверка маршрута
12traceroute db-server