Airflow 使用 Celery 时，如何添加 Celery 配置

背景

前段时间我选用了 airflow 对 wms 进行数据归档，在运行一段时间后，经常发现会报以下错误：

[2020-01-07 14:41:34,465: warning/forkpoolworker-5] failed operation _store_result.  retrying 2 more times.
traceback (most recent call last):
  file "/usr/local/python38/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1245, in _execute_context
    self.dialect.do_execute(
  file "/usr/local/python38/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 581, in do_execute
    cursor.execute(statement, parameters)
  file "/usr/local/python38/lib/python3.8/site-packages/mysqldb/cursors.py", line 255, in execute
    self.errorhandler(self, exc, value)
  file "/usr/local/python38/lib/python3.8/site-packages/mysqldb/connections.py", line 50, in defaulterrorhandler
    raise errorvalue
  file "/usr/local/python38/lib/python3.8/site-packages/mysqldb/cursors.py", line 252, in execute
    res = self._query(query)
  file "/usr/local/python38/lib/python3.8/site-packages/mysqldb/cursors.py", line 378, in _query
    db.query(q)
  file "/usr/local/python38/lib/python3.8/site-packages/mysqldb/connections.py", line 280, in query
    _mysql.connection.query(self, query)
_mysql_exceptions.operationalerror: (2006, 'mysql server has gone away')

the above exception was the direct cause of the following exception:

traceback (most recent call last):
  file "/usr/local/python38/lib/python3.8/site-packages/celery/backends/database/__init__.py", line 53, in _inner
    return fun(*args, **kwargs)
  file "/usr/local/python38/lib/python3.8/site-packages/celery/backends/database/__init__.py", line 107, in _store_result
    task = list(session.query(task).filter(task.task_id == task_id))
  file "/usr/local/python38/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 3367, in __iter__
    return self._execute_and_instances(context)
  file "/usr/local/python38/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 3392, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  file "/usr/local/python38/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 982, in execute
    return meth(self, multiparams, params)
  file "/usr/local/python38/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 287, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  file "/usr/local/python38/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1095, in _execute_clauseelement
    ret = self._execute_context(
  file "/usr/local/python38/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1249, in _execute_context
    self._handle_dbapi_exception(
  file "/usr/local/python38/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1476, in _handle_dbapi_exception
    util.raise_from_cause(sqlalchemy_exception, exc_info)
  file "/usr/local/python38/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 398, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  file "/usr/local/python38/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 152, in reraise
    raise value.with_traceback(tb)
  file "/usr/local/python38/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1245, in _execute_context
    self.dialect.do_execute(
  file "/usr/local/python38/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 581, in do_execute
    cursor.execute(statement, parameters)
  file "/usr/local/python38/lib/python3.8/site-packages/mysqldb/cursors.py", line 255, in execute
    self.errorhandler(self, exc, value)
  file "/usr/local/python38/lib/python3.8/site-packages/mysqldb/connections.py", line 50, in defaulterrorhandler
    raise errorvalue
  file "/usr/local/python38/lib/python3.8/site-packages/mysqldb/cursors.py", line 252, in execute
    res = self._query(query)
  file "/usr/local/python38/lib/python3.8/site-packages/mysqldb/cursors.py", line 378, in _query
    db.query(q)
  file "/usr/local/python38/lib/python3.8/site-packages/mysqldb/connections.py", line 280, in query
    _mysql.connection.query(self, query)
sqlalchemy.exc.operationalerror: (_mysql_exceptions.operationalerror) (2006, 'mysql server has gone away')
[sql: select celery_taskmeta.id as celery_taskmeta_id, celery_taskmeta.task_id as celery_taskmeta_task_id, celery_taskmeta.status as celery_taskmeta_status, celery_tas
kmeta.result as celery_taskmeta_result, celery_taskmeta.date_done as celery_taskmeta_date_done, celery_taskmeta.traceback as celery_taskmeta_traceback 
from celery_taskmeta 
where celery_taskmeta.task_id = %s]
[parameters: ('e909b916-4284-47c4-bc5b-321bc32eb9f9',)]
(background on this error at: http://sqlalche.me/e/e3q8)

 

解决过程

查了下资料一般情况下数据库服务器断开连接后，被连接池未收回将会导致以下错误：

mysql server has gone away

所以看了下 sqlalchemy 的配置：

sql_alchemy_pool_enabled = true

# the sqlalchemy pool size is the maximum number of database connections
# in the pool. 0 indicates no limit.
sql_alchemy_pool_size = 5

# the maximum overflow size of the pool.
# when the number of checked-out connections reaches the size set in pool_size,
# additional connections will be returned up to this limit.
# when those additional connections are returned to the pool, they are disconnected and discarded.
# it follows then that the total number of simultaneous connections the pool will allow is pool_size + max_overflow,
# and the total number of "sleeping" connections the pool will allow is pool_size.
# max_overflow can be set to -1 to indicate no overflow limit;
# no limit will be placed on the total number of concurrent connections. defaults to 10.
sql_alchemy_max_overflow = 10

# the sqlalchemy pool recycle is the number of seconds a connection
# can be idle in the pool before it is invalidated. this config does
# not apply to sqlite. if the number of db connections is ever exceeded,
# a lower config value will allow the system to recover faster.
sql_alchemy_pool_recycle = 1800

# check connection at the start of each connection pool checkout.
# typically, this is a simple statement like “select 1”.
# more information here: https://docs.sqlalchemy.org/en/13/core/pooling.html#disconnect-handling-pessimistic
sql_alchemy_pool_pre_ping = true

sql_alchemy_pool_size = 5

# the maximum overflow size of the pool.
# when the number of checked-out connections reaches the size set in pool_size,
# additional connections will be returned up to this limit.
# when those additional connections are returned to the pool, they are disconnected and discarded.
# it follows then that the total number of simultaneous connections the pool will allow is pool_size + max_overflow,
# and the total number of "sleeping" connections the pool will allow is pool_size.
# max_overflow can be set to -1 to indicate no overflow limit;
# no limit will be placed on the total number of concurrent connections. defaults to 10.
sql_alchemy_max_overflow = 10

# the sqlalchemy pool recycle is the number of seconds a connection
# can be idle in the pool before it is invalidated. this config does
# not apply to sqlite. if the number of db connections is ever exceeded,
# a lower config value will allow the system to recover faster.
sql_alchemy_pool_recycle = 1800

# check connection at the start of each connection pool checkout.
# typically, this is a simple statement like “select 1”.
# more information here: https://docs.sqlalchemy.org/en/13/core/pooling.html#disconnect-handling-pessimistic
sql_alchemy_pool_pre_ping = true

 

该配的都配置上了，因为我们的任务是一天跑一次，查了下数据库变量 waits_timeout 是 28800 ，所以直接改成25个小时。

到了第二天发现还是报这个错，很奇怪该配的都配上了，到底是哪里的问题？

仔细翻下报错日志：

file "/usr/local/python38/lib/python3.8/site-packages/celery/backends/database/__init__.py", line 107, in _store_result
    task = list(session.query(task).filter(task.task_id == task_id))

难道 airflow 的 sqlalchemy 配置对 celery 不生效？

翻阅下源码发现果然 airflow 配置的 sqlalchemy 只对 airflow 生效

app = celery(
    conf.get('celery', 'celery_app_name'),
    config_source=celery_configuration)

 

在继续翻阅 celery 文档看有没有办法配置

database_short_lived_sessions default: disabled by default.

short lived sessions are disabled by default. if enabled they can drastically reduce performance, especially on systems processing lots of tasks. this option is useful on low-traffic workers that experience errors as a result of cached database connections going stale through inactivity. for example, intermittent errors like (operationalerror) (2006, ‘mysql server has gone away’) can be fixed by enabling short lived sessions. this option only affects the database backend.

文档告知通过database_short_lived_sessions 参数就可以避免这个问题，但是新的问题又来了，如何在 airflow 中配置额外的 celery 配置呢？

解决方案

找到以下文件拷贝到 dags 目录下，重新命名为 my_celery_config 随便起

python/python37/site-packages/airflow/config_templates/default_celery.py

修改 airflow.cfg 配置 找到 celery_config_options 将配置改为 刚才起的名字

celery_config_options = my_celery_config.default_celery_config

在 my_celery_config 文件中的 default_celery_config dict 中就可以随便加自己需要的 celery 配置