mysql - Airflow:Celery worker MySQL 连接过多

Question

我们正在使用 Celery 运行 Airflow 1.10.1。面对多个打开的连接。在 DAG 启动时 - UI 挂起几分钟。

亮点:

• 所有节点都是 BareMetal:CPU(s):40，MHz 2494.015，RAM 378G，10Gb NIC -
• 数据库连接没有被重复使用
• 连接在事件时保持打开状态仅 5
• 工作人员创建了数百个连接，这些连接在 DB 清除它们之前一直保持打开状态(900 秒)
• 每个 worker 运行 100 个 celery 线程

MySQL> 显示全局状态如 'Thread%';

+-------------------------+---------     + 
| Variable_name           | Value         |
+-------------------------+---------      +
| Thread pool_idle_threads | 0            |
| Thread pool_threads      | 0            |
| Threads_cached          | 775           |
| Threads_connected       | 5323          |
| Threads_created         | 4846609       |
| Threads_running         | 5             |
+-------------------------+---------      +

MySQL 连接:

31  - worker1
215 - worker2
349 - worker53
335 - worker54
347 - worker55
336 - worker56
336 - worker57
354 - worker58
339 - worker59
328 - worker60
333 - worker61
337 - worker62
2   - scheduler

worker .cfg

[core]
sql_alchemy_pool_size = 5
sql_alchemy_pool_recycle = 900
sql_alchemy_reconnect_timeout = 300
parallelism = 1200
dag_concurrency = 800
non_pooled_task_slot_count = 1200
max_active_runs_per_dag = 10
dagbag_import_timeout = 30
[celery]
worker_concurrency = 100

调度程序.cfg:

   [core]
    sql_alchemy_pool_size = 30
    sql_alchemy_pool_recycle = 300
    sql_alchemy_reconnect_timeout = 300
    parallelism = 1200
    dag_concurrency = 800
    non_pooled_task_slot_count = 1200
    max_active_runs_per_dag = 10
    [scheduler]
    job_heartbeat_sec = 5
    scheduler_heartbeat_sec = 5
    run_duration = 1800
    min_file_process_interval = 10
    min_file_parsing_loop_time = 1
    dag_dir_list_interval = 300
    print_stats_interval = 30
    scheduler_zombie_task_threshold = 300
    max_tis_per_query = 1024
    max_threads = 29

补充一下，我正在运行 1000 个简单的任务，例如 sleep 或 ls

Answer 1

我们能够将连接从 700-800 减少到 1-10

您可以做两件事:

1. 设置 sql_alchemy_pool_enabled = False
2. 设置一个与数据库不同的result_backend，在我们的例子中，我们使用redis作为result_backend，MySQL作为主数据库