WSL2中Docker容器内Pandas read_sql查询速度极慢求助

我在Windows 10的WSL2上运行Docker应用，包含Flask应用（Waitress提供服务）、Celery Worker（暂未启用）、Redis及Traefik反向代理。目前遇到两个核心问题：

• 容器外页面加载仅需3秒，但容器内访问页面耗时超过5分钟
• 使用pandas.read_sql从MSSQL拉取数据时，容器外查询平均耗时0.02秒，容器内平均耗时2.7秒

通过docker stats观察，内存和CPU占用分别未超过8%和4%，暂排除资源不足问题。此前在Windows直装Docker环境未出现该问题，仅新增了Celery/Redis组件并切换到WSL2环境后出现异常。

相关配置文件 #

docker-compose.yml #

version: '3.8'

services:
  home:
    restart: always
    image: home_dev
    networks:
      - traefik
    build:
      context: ./
      dockerfile: ./home/Dockerfile.flask
    env_file:
      - .env
    depends_on:
      - redis
      - home_worker
    labels:
      - traefik.http.routers.home_http.entrypoints=web
      - traefik.http.routers.home_http.rule=Host(`localhost`)
      - traefik.enable=true

  home_worker:
    build:
      context: ./
      dockerfile: ./home/Dockerfile.celery
    networks:
      - traefik
    command: poetry run celery -A tasks worker -l info -E
    environment:
      CELERY_BROKER_URL: redis://redis
      CELERY_RESULT_BACKEND: redis://redis
    depends_on:
      - redis

  reverse-proxy:
    restart: always
    image: traefik:v2.10.3
    networks:
      - traefik
    command:
      - --api.insecure=true
      - --providers.docker=true
      - --providers.docker.exposedbydefault=false
      - --entrypoints.web.address=:80
    labels:
      - traefik.enable=true
      - traefik.http.routers.traefik=true
    ports:
      - 80:80
      #- 443:443
      - 8080:8080
    volumes:
      - /var/run/docker.sock:/var/run/docker.sock:ro
    
  redis:
    restart: always
    networks:
      - traefik
    image: redis:7
    ports:
      - 6379:6379

networks:
  traefik:
    external: true

Dockerfile.flask #

FROM python:3.11

# redis config
ENV CELERY_BROKER_URL redis://redis:6379/0
ENV CELERY_RESULT_BACKEND redis://redis:6379/0
ENV C_FORCE_ROOT true

EXPOSE 5000

# instal odbc drivers
RUN apt-get update && \
    apt-get install -y curl && \
    curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add - && \
    curl https://packages.microsoft.com/config/debian/11/prod.list > /etc/apt/sources.list.d/mssql-release.list && \
    apt-get update && \
    ACCEPT_EULA=Y apt-get install -y msodbcsql18

COPY /home/home ./app/home
COPY /home/static ./app/static
COPY /home/templates ./app/templates
COPY /home/app.py ./app/app.py
COPY /home/poetry.lock ./app/poetry.lock
COPY /home/pyproject.toml ./app/pyproject.toml
COPY /home/waitress_server.py ./app/waitress_server.py
COPY /home/README.md ./app/README.md
COPY /toolbox ./toolbox

WORKDIR /app

RUN pip install poetry==1.3.2
RUN poetry config virtualenvs.in-project true
RUN poetry install

# Run app
CMD poetry run python -u waitress_server.py
#CMD poetry run python app.py

SQL数据拉取函数 #

def pull_query(query, database=None):
    if database:
        engine = connection(database)
    else:
        engine = connection()
    if engine:
        df = read_sql(query, engine)
        return df
    else: 
        return None

def connection(database='master', username=None, password=None):
    username = getenv('SQL_USER')
    password = getenv('SQL_PASSWORD')
    server = getenv('SQL_SERVER')
    connection_string = 'DRIVER={ODBC Driver 18 for SQL Server};SERVER=' + server + ';DATABASE=' + database + ';UID=' + username + ';PWD=' + password + ';Encrypt=no'
    connection_url = URL.create("mssql+pyodbc", query={"odbc_connect": connection_string})
    engine = create_engine(connection_url)
    try:
        engine = create_engine(connection_url)
    except:
        engine = None
    return engine

排查方向建议 #

1. WSL2网络配置问题 #

• 检查WSL2网络模式，尝试切换为桥接模式（默认是NAT），NAT模式下容器访问外部资源可能存在转发延迟
• 在WSL2终端执行ping <SQL_SERVER_IP>，对比Windows主机的ping耗时，判断是否存在WSL2层面的网络延迟
• 确认SQL_SERVER地址是否用了localhost或127.0.0.1，容器内访问Windows主机服务需用WSL2主机IP（可通过cat /etc/resolv.conf获取nameserver对应的IP）

2. ODBC驱动及SQL连接优化 #

• 在连接字符串中添加TrustServerCertificate=yes，避免证书验证耗时
• 检查pyodbc版本，在容器内执行poetry show pyodbc，对比Windows环境版本，尝试降级或升级到匹配版本
• 改用连接池：创建SQLAlchemy引擎时添加pool_size=5、max_overflow=10参数，避免每次查询新建连接

3. Docker网络配置 #

• 检查traefik外部网络配置，尝试改为内部网络（去掉external: true），避免跨网络转发开销
• 直接在容器内访问Flask服务（curl http://localhost:5000），判断是否是Traefik反向代理导致的延迟

4. WSL2系统优化 #

• 开启WSL2内存限制，在%USERPROFILE%\.wslconfig中添加：

  [wsl2]
  memory=4GB
  processors=2
  

  避免WSL2自动占用过多内存导致系统卡顿
• 更新WSL2内核版本，执行wsl --update获取最新内核

内容的提问来源于stack exchange，提问作者ceej pie