2025-01-31 約 1700 字預計閱讀 8 分鐘

04-3. 連接數不足問題

連接數不足是高並發場景下常見的瓶頸。本章將深入探討資料庫連接池、文件描述符與網路連接的限制與解決方案。

1. 三種連接數問題

問題 1：資料庫連接池耗盡

# 錯誤訊息：
django.db.utils.OperationalError: FATAL: sorry, too many clients already
# PostgreSQL 預設最大連接數：100

# 或
django.db.utils.OperationalError: Can't connect to MySQL server; Too many connections
# MySQL 預設最大連接數：151

問題 2：系統文件描述符不足

# 錯誤訊息：
OSError: [Errno 24] Too many open files

# 或 Nginx 錯誤：
2025/01/31 16:00:00 [error] worker_rlimit_nofile is not enough

問題 3：應用層連接洩漏

# 錯誤訊息：
psycopg2.pool.PoolError: connection pool exhausted

# 原因：連接未正確關閉，累積到上限

2. 為什麼會發生連接數不足？

原因 1：Worker 數量 × 連接數超過限制

# 配置：
# - Gunicorn workers: 8
# - 每個 worker 預設連接：1
# - 總連接數：8 × 1 = 8 ✅ 正常

# 但如果使用 Gthread：
# - workers: 4
# - threads per worker: 10
# - 總連接數：4 × 10 = 40

# 如果流量大：
# - 每個 thread 可能同時持有多個連接
# - 實際連接數：40 × 2 = 80

# PostgreSQL 預設最大連接：100
# 80 / 100 = 80% → 接近上限！

原因 2：連接未正確關閉

# ❌ 錯誤：手動管理連接但忘記關閉
from django.db import connection

def buggy_view(request):
    cursor = connection.cursor()
    cursor.execute("SELECT * FROM users")
    results = cursor.fetchall()

    # 忘記關閉！
    # cursor.close()
    # connection.close()

    return JsonResponse({'data': results})

# 每次請求洩漏一個連接
# 100 個請求後：資料庫連接池耗盡

原因 3：長時間持有連接

# ❌ 錯誤：在長時間操作中持有連接
from django.db import transaction

@transaction.atomic
def slow_process(request):
    # 開啟事務，持有連接
    user = User.objects.select_for_update().get(id=1)

    # 長時間處理（30 秒）
    result = process_large_file()  # 30 秒

    user.data = result
    user.save()

    # 連接被占用 30 秒！
    # 其他請求無法獲取連接

原因 4：文件描述符不足

# Linux 預設限制
ulimit -n
# 輸出：1024（每個進程最多 1024 個文件）

# Gunicorn 需要的文件描述符：
# - 每個 Worker 的連接
# - 日誌文件
# - Socket 連接
# - 臨時文件

# 如果 worker 數量多、並發高：
# 4 workers × 10 threads × 5 connections = 200
# 加上其他文件 = 300+ → 超過 1024 會出錯

3. 如何診斷連接數問題？

步驟 1：檢查資料庫當前連接數

-- PostgreSQL：查看當前連接數
SELECT count(*) FROM pg_stat_activity;

-- 查看每個資料庫的連接
SELECT datname, count(*)
FROM pg_stat_activity
GROUP BY datname;

-- 查看連接的詳細資訊
SELECT pid, usename, datname, state, query, state_change
FROM pg_stat_activity
WHERE state = 'idle in transaction'  -- 空閒但未關閉的連接
ORDER BY state_change;

-- 查看最大連接數限制
SHOW max_connections;  -- 預設：100

-- MySQL：查看當前連接數
SHOW STATUS LIKE 'Threads_connected';

-- 查看最大連接數
SHOW VARIABLES LIKE 'max_connections';  -- 預設：151

-- 查看連接詳情
SHOW PROCESSLIST;

步驟 2：檢查 Django 連接池狀態

# views.py - 監控連接池
from django.db import connection
from django.http import JsonResponse

def db_pool_status(request):
    """查看 Django 資料庫連接狀態"""
    with connection.cursor() as cursor:
        # PostgreSQL
        cursor.execute("""
            SELECT
                count(*) as total,
                count(*) FILTER (WHERE state = 'active') as active,
                count(*) FILTER (WHERE state = 'idle') as idle,
                count(*) FILTER (WHERE state = 'idle in transaction') as idle_in_transaction
            FROM pg_stat_activity
            WHERE datname = current_database()
        """)

        result = cursor.fetchone()

    return JsonResponse({
        'total_connections': result[0],
        'active': result[1],
        'idle': result[2],
        'idle_in_transaction': result[3],
    })

步驟 3：檢查系統文件描述符

# 查看當前進程的文件描述符使用
ls -la /proc/<PID>/fd | wc -l

# 查看所有 Gunicorn Worker 的文件描述符
for pid in $(pgrep -f 'gunicorn.*worker'); do
  echo "Worker $pid: $(ls /proc/$pid/fd 2>/dev/null | wc -l) open files"
done

# 輸出：
# Worker 1234: 156 open files
# Worker 1235: 203 open files
# Worker 1236: 189 open files

# 查看系統限制
ulimit -n  # 當前進程限制
cat /proc/sys/fs/file-max  # 系統全局限制

步驟 4：使用 lsof 查看連接

# 查看特定進程打開的文件和連接
sudo lsof -p <worker_pid>

# 輸出：
# COMMAND   PID USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
# gunicorn 1234 www    3u  IPv4  12345      0t0  TCP *:8000 (LISTEN)
# gunicorn 1234 www    4u  IPv4  12346      0t0  TCP localhost:52614->localhost:5432 (ESTABLISHED)
# gunicorn 1234 www    5u  IPv4  12347      0t0  TCP localhost:52615->localhost:5432 (ESTABLISHED)

# 統計連接數
sudo lsof -p <worker_pid> | grep ESTABLISHED | wc -l

4. 解決方案

方案 1：配置資料庫連接池

Django 3.1+ 支援內建連接池：

# settings.py
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'mydb',
        'USER': 'user',
        'PASSWORD': 'password',
        'HOST': 'localhost',
        'PORT': '5432',

        # ✅ 連接池配置
        'CONN_MAX_AGE': 600,  # 連接最大存活時間（秒）
        'CONN_HEALTH_CHECKS': True,  # 啟用連接健康檢查

        # PostgreSQL 特定選項
        'OPTIONS': {
            'connect_timeout': 10,  # 連接超時
        },
    }
}

# CONN_MAX_AGE 設置建議：
# - 0：每次請求都新建連接（不推薦，性能差）
# - None：持久連接，永不關閉（危險，可能累積死連接）
# - 600：推薦值，10 分鐘後關閉空閒連接

方案 2：使用第三方連接池（Django 3.0 以前）

# 安裝 django-db-connection-pool
pip install django-db-connection-pool

# settings.py
DATABASES = {
    'default': {
        'ENGINE': 'dj_db_conn_pool.backends.postgresql',  # 使用連接池引擎
        'NAME': 'mydb',
        'USER': 'user',
        'PASSWORD': 'password',
        'HOST': 'localhost',
        'PORT': '5432',

        'POOL_OPTIONS': {
            'POOL_SIZE': 10,  # 連接池大小
            'MAX_OVERFLOW': 5,  # 超過 POOL_SIZE 時可額外創建的連接數
            'POOL_TIMEOUT': 30,  # 等待可用連接的超時時間
            'POOL_RECYCLE': 3600,  # 連接回收時間（秒）
        }
    }
}

方案 3：計算合理的連接池大小

# 連接數計算公式：

# Sync Worker：
# 每個 worker 最多 1 個活躍連接
workers = 4
connections_needed = workers × 1 = 4

# Gthread Worker：
# 每個 thread 最多 1 個活躍連接
workers = 4
threads_per_worker = 10
connections_needed = workers × threads_per_worker = 40

# Async Worker (Uvicorn)：
# 協程共享連接，實際連接數較少
workers = 4
connections_needed = workers × 2 = 8  # 通常是 worker 數量的 1-2 倍

# ✅ 推薦配置：
# PostgreSQL max_connections = connections_needed × 1.5（留 50% 餘量）
# 例如：40 個連接 → max_connections = 60

方案 4：增加資料庫最大連接數

-- PostgreSQL：修改最大連接數
-- 編輯 postgresql.conf
max_connections = 200  # 從 100 增加到 200

-- 重啟 PostgreSQL
sudo systemctl restart postgresql

-- ⚠️ 注意：每個連接占用記憶體
-- 1 個連接 ≈ 10MB
-- 200 個連接 ≈ 2GB

-- MySQL：修改最大連接數
-- 編輯 my.cnf
[mysqld]
max_connections = 500

-- 重啟 MySQL
sudo systemctl restart mysql

方案 5：增加系統文件描述符限制

# 臨時增加（重啟後失效）
ulimit -n 65535

# ✅ 永久修改
# 編輯 /etc/security/limits.conf
*  soft  nofile  65535
*  hard  nofile  65535

# 編輯 /etc/sysctl.conf
fs.file-max = 2097152

# 應用配置
sudo sysctl -p

# ✅ Systemd 服務配置
# /etc/systemd/system/gunicorn.service
[Service]
LimitNOFILE=65535

# 重新載入
sudo systemctl daemon-reload
sudo systemctl restart gunicorn

方案 6：使用連接上下文管理器

# ✅ 正確：使用 with 確保連接關閉
from django.db import connection

def safe_raw_query(request):
    with connection.cursor() as cursor:
        cursor.execute("SELECT * FROM users WHERE id = %s", [1])
        result = cursor.fetchone()

    # cursor 自動關閉
    return JsonResponse({'data': result})

# ✅ 正確：ORM 自動管理連接
def orm_query(request):
    users = User.objects.filter(is_active=True)
    # 連接在請求結束後自動釋放
    return JsonResponse({'users': list(users.values())})

方案 7：避免長時間持有連接

# ❌ 錯誤：在事務中執行長時間操作
@transaction.atomic
def bad_process(request):
    user = User.objects.select_for_update().get(id=1)
    result = slow_operation()  # 30 秒
    user.data = result
    user.save()

# ✅ 正確 1：縮短事務範圍
def good_process_1(request):
    # 先執行長時間操作（不持有連接）
    result = slow_operation()  # 30 秒

    # 快速更新資料庫
    with transaction.atomic():
        user = User.objects.select_for_update().get(id=1)
        user.data = result
        user.save()

# ✅ 正確 2：使用樂觀鎖
def good_process_2(request):
    user = User.objects.get(id=1)  # 不鎖定
    result = slow_operation()  # 30 秒

    # 使用版本號檢查
    User.objects.filter(id=1, version=user.version).update(
        data=result,
        version=F('version') + 1
    )

5. 最佳實踐

原則 1：監控連接池使用率

# middleware.py - 連接池監控
import logging
from django.db import connection

logger = logging.getLogger(__name__)

class ConnectionPoolMonitor:
    def __init__(self, get_response):
        self.get_response = get_response

    def __call__(self, request):
        response = self.get_response(request)

        # 記錄查詢數量
        queries = len(connection.queries)

        if queries > 50:
            logger.warning(
                f"High query count: {request.path} "
                f"executed {queries} queries"
            )

        return response

# settings.py
MIDDLEWARE = [
    'myapp.middleware.ConnectionPoolMonitor',
    # ...
]

原則 2：使用連接池健康檢查

# settings.py
DATABASES = {
    'default': {
        # ...
        'CONN_HEALTH_CHECKS': True,  # ✅ Django 4.1+
    }
}

# 健康檢查作用：
# 1. 每次使用連接前先測試
# 2. 發現死連接會自動重建
# 3. 避免使用已斷開的連接

原則 3：設置合理的超時

# settings.py
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        # ...
        'OPTIONS': {
            'connect_timeout': 10,  # 連接超時
            'options': '-c statement_timeout=30000'  # 查詢超時（毫秒）
        },
    }
}

# 或在程式碼中設置
from django.db import connection

with connection.cursor() as cursor:
    cursor.execute("SET statement_timeout TO 5000")  # 5 秒
    cursor.execute("SELECT * FROM large_table")

原則 4：使用連接池分離

# settings.py
# ✅ 讀寫分離 + 連接池分離

DATABASES = {
    # 主資料庫（寫）
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'mydb',
        'HOST': 'master.db.example.com',
        'CONN_MAX_AGE': 600,
    },

    # 只讀副本（讀）
    'replica': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'mydb',
        'HOST': 'replica.db.example.com',
        'CONN_MAX_AGE': 600,
    },

    # Celery 專用（後台任務）
    'celery': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'mydb',
        'HOST': 'master.db.example.com',
        'CONN_MAX_AGE': None,  # 持久連接（Celery worker 長期運行）
    },
}

# 使用 Router 自動路由
class DatabaseRouter:
    def db_for_read(self, model, **hints):
        return 'replica'  # 讀取使用副本

    def db_for_write(self, model, **hints):
        return 'default'  # 寫入使用主庫

DATABASE_ROUTERS = ['myapp.routers.DatabaseRouter']

6. 實戰案例：高並發下的連接數不足

問題描述

# 場景：電商促銷活動
# - 流量：10,000 QPS
# - Gunicorn 配置：4 workers × 10 threads = 40 併發
# - 每個請求需要 2 次資料庫查詢

# 錯誤：
django.db.utils.OperationalError: FATAL: sorry, too many clients already

# 診斷：
# PostgreSQL max_connections = 100
# 實際需要：40 threads × 2 connections = 80
# 加上其他服務（Celery、監控）= 100+
# → 連接數不足！

解決方案

方案 1：優化查詢，減少連接使用

# ❌ 錯誤：每個請求 2 次查詢
def get_product(request, product_id):
    product = Product.objects.get(id=product_id)  # 查詢 1
    reviews = Review.objects.filter(product=product)  # 查詢 2

    return JsonResponse({
        'product': product.name,
        'reviews': list(reviews.values()),
    })

# ✅ 正確：使用 prefetch_related 合併查詢
def get_product_optimized(request, product_id):
    product = Product.objects.prefetch_related('reviews').get(id=product_id)

    return JsonResponse({
        'product': product.name,
        'reviews': [r.content for r in product.reviews.all()],
    })

# 從 2 次查詢降為 2 次，但第二次是 JOIN
# 連接占用時間更短

方案 2：使用 Redis 快取

# ✅ 熱門商品使用快取
from django.core.cache import cache

def get_product_cached(request, product_id):
    cache_key = f'product_{product_id}'

    # 先檢查快取
    data = cache.get(cache_key)

    if data is None:
        # 快取未命中，查詢資料庫
        product = Product.objects.prefetch_related('reviews').get(id=product_id)

        data = {
            'product': product.name,
            'reviews': [r.content for r in product.reviews.all()],
        }

        # 儲存到快取，TTL 5 分鐘
        cache.set(cache_key, data, timeout=300)

    return JsonResponse(data)

# 快取命中率 80% → 資料庫連接需求降低 80%

方案 3：增加資料庫連接數

-- PostgreSQL：增加最大連接數
-- postgresql.conf
max_connections = 200

-- 調整連接相關參數
shared_buffers = 4GB  # 增加緩衝區
work_mem = 16MB
maintenance_work_mem = 256MB

-- 重啟
sudo systemctl restart postgresql

方案 4：使用 PgBouncer 連接池

# 安裝 PgBouncer
sudo apt-get install pgbouncer

# 配置 /etc/pgbouncer/pgbouncer.ini
[databases]
mydb = host=localhost port=5432 dbname=mydb

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

# 連接池模式
pool_mode = transaction  # 推薦：事務級連接池

# 連接數限制
max_client_conn = 1000  # 應用最多 1000 個連接
default_pool_size = 25  # 每個資料庫維持 25 個實際連接

# 啟動
sudo systemctl start pgbouncer

# Django 配置使用 PgBouncer
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'mydb',
        'HOST': '127.0.0.1',
        'PORT': '6432',  # ✅ 連接到 PgBouncer
        'USER': 'user',
        'PASSWORD': 'password',

        'CONN_MAX_AGE': 0,  # ⚠️ 使用 PgBouncer 時設為 0
        # 因為 PgBouncer 會管理連接池
    }
}

# 架構：
# Django (1000 connections) → PgBouncer → PostgreSQL (25 connections)
# 大幅減少資料庫實際連接數！

面試常見問題

Q1：Django 的 CONN_MAX_AGE 參數有什麼作用？

答案：

CONN_MAX_AGE 控制資料庫連接的重用時間：

# settings.py
DATABASES = {
    'default': {
        'CONN_MAX_AGE': 600,  # 連接存活 10 分鐘
    }
}

行為：

0：每次請求都新建連接（預設，性能差）
None：持久連接，永不關閉（可能累積死連接）
600：推薦值，連接空閒 10 分鐘後關閉

好處： 減少建立/關閉連接的開銷，提升性能。

Q2：如何計算需要多少資料庫連接數？

答案：

連接數 = 併發數 × 每次請求的平均活躍連接數

# Gthread Worker：
workers = 4
threads_per_worker = 10
併發數 = 4 × 10 = 40

# 每次請求平均 1 個活躍連接
連接數 = 40 × 1 = 40

# ✅ 設置資料庫最大連接數
max_connections = 40 × 1.5 = 60  # 留 50% 餘量

注意： 要考慮其他服務（Celery、監控）也會使用連接。

Q3：PgBouncer 的三種連接池模式有什麼區別？

答案：

模式	說明	適用場景
session	連接綁定客戶端整個會話	使用臨時表、預處理語句
transaction	連接綁定單個事務	✅ 推薦，適合大多數 Django 應用
statement	連接綁定單個 SQL	不支援事務，罕見使用

# pgbouncer.ini
pool_mode = transaction  # ✅ 推薦

transaction 模式好處：

高效複用連接
支援 Django 的 @transaction.atomic
1000 個應用連接 → 只需 25 個資料庫連接

Q4：如何診斷是否有連接洩漏？

答案：

使用 PostgreSQL 查詢：

-- 查看空閒但未關閉的連接
SELECT pid, usename, application_name, state, state_change
FROM pg_stat_activity
WHERE state = 'idle in transaction'  -- 問題連接
  AND state_change < now() - interval '5 minutes'  -- 超過 5 分鐘
ORDER BY state_change;

-- 如果有大量 'idle in transaction' 連接，可能是洩漏

解決：

檢查程式碼，確保所有 cursor 都使用 with 語句
啟用 CONN_HEALTH_CHECKS
設置合理的 CONN_MAX_AGE

小結

連接數不足問題的處理原則：

計算連接需求：併發數 × 活躍連接數 × 1.5 餘量
配置連接池：使用 CONN_MAX_AGE 重用連接
監控連接使用：定期檢查 pg_stat_activity
增加系統限制：文件描述符 65535、資料庫 max_connections
優化查詢：減少 N+1 查詢，使用快取
使用 PgBouncer：在應用和資料庫之間加入連接池中介層
避免連接洩漏：使用 with 語句，啟用健康檢查

記住：連接是寶貴資源，要像對待記憶體一樣小心管理！

Django 面試準備 04-3：連接數不足問題

04-3. 連接數不足問題

1. 三種連接數問題

問題 1：資料庫連接池耗盡

問題 2：系統文件描述符不足

問題 3：應用層連接洩漏

2. 為什麼會發生連接數不足？

原因 1：Worker 數量 × 連接數超過限制

原因 2：連接未正確關閉

原因 3：長時間持有連接

原因 4：文件描述符不足

3. 如何診斷連接數問題？

步驟 1：檢查資料庫當前連接數

步驟 2：檢查 Django 連接池狀態

步驟 3：檢查系統文件描述符

步驟 4：使用 lsof 查看連接

4. 解決方案

方案 1：配置資料庫連接池

方案 2：使用第三方連接池（Django 3.0 以前）

方案 3：計算合理的連接池大小

方案 4：增加資料庫最大連接數

方案 5：增加系統文件描述符限制

方案 6：使用連接上下文管理器

方案 7：避免長時間持有連接

5. 最佳實踐

原則 1：監控連接池使用率

原則 2：使用連接池健康檢查

原則 3：設置合理的超時

原則 4：使用連接池分離

6. 實戰案例：高並發下的連接數不足

問題描述

解決方案

方案 1：優化查詢，減少連接使用

方案 2：使用 Redis 快取

方案 3：增加資料庫連接數

方案 4：使用 PgBouncer 連接池

面試常見問題

Q1：Django 的 CONN_MAX_AGE 參數有什麼作用？

Q2：如何計算需要多少資料庫連接數？

Q3：PgBouncer 的三種連接池模式有什麼區別？

Q4：如何診斷是否有連接洩漏？

小結