我正在将Flask-SqlAlchemy
与MySQL数据库一起使用。最近我开始收到此错误:
sqlalchemy.exc.OperationalError: (MySQLdb._exceptions.OperationalError) (1040, 'Too many connections')
经过一番挖掘,我似乎没有使用连接池。
根据我的研究SQLALCHEMY_POOL_SIZE
现在在 v2.4 中已弃用,不应再使用。
SQLAlchemy==1.3.7
Flask-SQLAlchemy==2.4.1
那么正确的做法是什么呢?
配置:
SQLALCHEMY_DATABASE_URI = 'mysql://root:PASSWORD@localhost/main_db'
SQLALCHEMY_BINDS = {
'radius': 'mysql://root:PASSWORD@localhost/radius_db',
'cache': 'mysql://root:PASSWORD@localhost/cache_db',
}
法典:
def make_app():
app = Flask(__name__, template_folder="../templates")
app.config.from_object(config)
db.init_app(app)
app = my_fabric.make_app()
根据SqlAlchemy的说法,我应该通过create_engine
来做到这一点。
engine = create_engine("mysql+pymysql://user:pw@host/db", pool_size=20, max_overflow=0))
但是Flask-SqlAlchemy应该抽象出这一点。所以我不知道应该如何配置。
更新:
我正在运行带有两个进程的uWSGI。
我现在已经将max_connections增加到 500。很难说我是否有高流量,但我 16 小时后的数据库统计数据显示了这一点:
mysql> show status like '%onn%';
+-------------------------------------------------------+---------------------+
| Variable_name | Value |
+-------------------------------------------------------+---------------------+
| Aborted_connects | 5 |
| Connection_errors_accept | 0 |
| Connection_errors_internal | 0 |
| Connection_errors_max_connections | 0 |
| Connection_errors_peer_address | 0 |
| Connection_errors_select | 0 |
| Connection_errors_tcpwrap | 0 |
| Connections | 3897 |
| Locked_connects | 0 |
| Max_used_connections | 167 |
| Max_used_connections_time | 2019-11-29 00:11:51 |
| Mysqlx_connection_accept_errors | 0 |
| Mysqlx_connection_errors | 0 |
| Mysqlx_connections_accepted | 0 |
| Mysqlx_connections_closed | 0 |
| Mysqlx_connections_rejected | 0 |
| Performance_schema_session_connect_attrs_longest_seen | 117 |
| Performance_schema_session_connect_attrs_lost | 0 |
| Ssl_client_connects | 0 |
| Ssl_connect_renegotiates | 0 |
| Ssl_finished_connects | 0 |
| Threads_connected | 97 |
+-------------------------------------------------------+---------------------+
和
mysql> SHOW STATUS WHERE variable_name LIKE "Threads_%" OR variable_name = "Connections";
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Connections | 3896 |
| Threads_cached | 8 |
| Threads_connected | 97 |
| Threads_created | 365 |
| Threads_running | 2 |
+-------------------+-------+
和
mysql> SHOW VARIABLES LIKE 'max_connections';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 500 |
+-----------------+-------+
1 row in set (0.01 sec)
您可以使用会话而不是连接
my_session = Session(engine)
results = my_session.execute(query)
my_session.close()
在创建引擎时,您可以设置
pool_recycle=60.
(或更高一点(。 https://docs.sqlalchemy.org/en/13/core/pooling.html#pool-setting-recycle
并不是说这将完全解决您的问题,但我很少在使用此设置时遇到问题。