Docker部署MySQL经Debezium插入后，Python查询数小时后超时

问题场景 #

通过Debezium连接器向Docker部署的MySQL数据库插入数据，初始数小时内查询一切正常，但之后执行相同查询时抛出超时异常。手动登录机器执行查询仍能正常获取结果，但Python脚本通过SSH执行查询时出现Socket超时错误。

执行的查询命令：

export JAVA_HOME=/tmp/tests/artifacts/java-17/jdk-17; export PATH=$PATH:/tmp/tests/artifacts/java-17/jdk-17/bin; docker exec -i mysql_be1e6a mysql --user=demo --password=demo -D demo -e "select count(k) from test_cdc_f0bf84 where uuid = 'd1e5cd6d-8f7a-457c-b2ea-880c2be52f69'"

抛出的异常栈：

2023-01-02 16:27:43,812:ERROR: failed to execute query MySQL rows count by uuid: 
Traceback (most recent call last):
  File "/home/ubuntu/workspace/stress_tests/run_test_with_universe/src/env/lib/python3.11/site-packages/paramiko/channel.py", line 699, in recv
    out = self.in_buffer.read(nbytes, self.timeout)
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/ubuntu/workspace/stress_tests/run_test_with_universe/src/env/lib/python3.11/site-packages/paramiko/buffered_pipe.py", line 164, in read
    raise PipeTimeout()
paramiko.buffered_pipe.PipeTimeout

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/ubuntu/workspace/stress_tests/run_test_with_universe/src/suites/cdc/abstract.py", line 667, in try_query
    res = query_function()
          ^^^^^^^^^^^^^^^^
  File "/home/ubuntu/workspace/stress_tests/run_test_with_universe/src/suites/cdc/test_cdc.py", line 635, in <lambda>
    query = lambda: self.mysql_query(
                    ^^^^^^^^^^^^^^^^^
  File "/home/ubuntu/workspace/stress_tests/run_test_with_universe/src/suites/cdc/abstract.py", line 544, in mysql_query
    result = self.ssh.exec_on_host(host, [
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/ubuntu/workspace/stress_tests/run_test_with_universe/src/main/connection.py", line 335, in exec_on_host
    return self._exec_on_host(host, commands, fetch, timeout=timeout, limit_output=limit_output)[host]
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/ubuntu/workspace/stress_tests/run_test_with_universe/src/main/connection.py", line 321, in _exec_on_host
    res = list(out)
          ^^^^^^^^^
  File "/home/ubuntu/workspace/stress_tests/run_test_with_universe/src/env/lib/python3.11/site-packages/paramiko/file.py", line 125, in __next__
    line = self.readline()
           ^^^^^^^^^^^^^^^
  File "/home/ubuntu/workspace/stress_tests/run_test_with_universe/src/env/lib/python3.11/site-packages/paramiko/file.py", line 291, in readline
    new_data = self._read(n)
               ^^^^^^^^^^^^^
  File "/home/ubuntu/workspace/stress_tests/run_test_with_universe/src/env/lib/python3.11/site-packages/paramiko/channel.py", line 1361, in _read
    return self.channel.recv(size)
           ^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/ubuntu/workspace/stress_tests/run_test_with_universe/src/env/lib/python3.11/site-packages/paramiko/channel.py", line 701, in recv
    raise socket.timeout()
TimeoutError

可能原因分析 #

• SSH超时配置过短：Python脚本使用paramiko执行SSH命令时，超时参数设置过小，数据量增长后查询耗时超过阈值触发超时。
• MySQL查询性能不足：Debezium持续插入数据导致表数据量增大，uuid字段未建立索引，查询耗时变长超出SSH等待时间。
• Docker容器资源受限：MySQL容器的CPU/内存配额不足，导致查询响应变慢，无法在SSH超时窗口内返回结果。
• SSH连接管理异常：脚本中SSH连接未正确复用或释放，长时间运行后连接出现闲置异常，导致读取输出超时。

解决方案 #

• 优化查询性能：给目标表的uuid字段添加索引，减少查询耗时：

  CREATE INDEX idx_test_cdc_uuid ON test_cdc_f0bf84(uuid);
  

• 调整SSH超时参数：在Python脚本调用exec_on_host时，增大timeout参数值，例如设置为30秒：

  result = self.ssh.exec_on_host(host, commands, timeout=30)
  

• 扩容Docker容器资源：检查MySQL容器的资源使用情况，增加CPU/内存配额：

  # 查看容器实时资源占用
  docker stats mysql_be1e6a
  # 修改容器资源限制（需重启容器生效）
  docker update --cpus 2 --memory 4g mysql_be1e6a
  

• 优化SSH连接管理：确保脚本中SSH连接使用后正确关闭，或采用连接池复用连接，避免闲置连接异常。
• 直接连接MySQL：跳过SSH中转，使用Python MySQL客户端库（如pymysql）直接连接数据库，减少超时风险：

  import pymysql
  
  conn = pymysql.connect(
      host='mysql_host_ip',
      user='demo',
      password='demo',
      database='demo'
  )
  with conn.cursor() as cursor:
      cursor.execute("select count(k) from test_cdc_f0bf84 where uuid = %s", ('d1e5cd6d-8f7a-457c-b2ea-880c2be52f69',))
      result = cursor.fetchone()
  conn.close()
  

内容的提问来源于stack exchange，提问作者Shamanth Chandra