I have a Flask application deployed on AWS Elastic Beanstalk, using SQLAlchemy as the ORM for interacting with a MySQL database. Despite configuring SQLAlchemy with a default pool size of five connections, I’ve noticed delays in fulfilling requests from the second user onwards when multiple users make requests simultaneously.
Here’s a more detailed overview of my setup:
-
Deployment Environment: The Flask application is deployed on AWS Elastic Beanstalk. I’m using a load balancer to distribute incoming traffic across multiple instances of my application.
-
Database Connection Pooling: I’ve configured SQLAlchemy with a default pool size of five connections to manage database connections.
-
Long-running Queries: Some requests involve long-running database queries, which may tie up connections in the pool for an extended period.
-
Connection Leakage: I suspect there might be issues with connection leakage, where connections are not properly closed or returned to the pool after use, eventually exhausting the pool and causing subsequent requests to wait.
-
Concurrency Limits: I’ve reviewed my application’s configuration and the database settings but haven’t found any explicit concurrency limits that could be causing the delays.
-
Resource Contention: I’ve monitored server resources (CPU, memory, disk I/O) and haven’t noticed any significant contention that could explain the delays.
Deployment Details:
-
Deployment Platform: AWS Elastic Beanstalk
-
Database: MySQL
-
Application Load Balancer: Used for distributing incoming traffic
-
Web Server: Gunicorn with GeventWebSocketWorker
-
Procfile Configuration:
web: gunicorn --worker-class geventwebsocket.gunicorn.workers.GeventWebSocketWorker -w 1 --timeout 940 --graceful-timeout 940 application:application
Given these observations and deployment details, I’m seeking advice on how to diagnose and address the delays in fulfilling requests from multiple users concurrently in my Flask application. Are there any common pitfalls or best practices I should consider when dealing with concurrent database access in Flask applications deployed on AWS Elastic Beanstalk?
Any insights or suggestions would be greatly appreciated. Thank you!
I attempted to increase the pool size from the default of five to 20 connections in the SQLAlchemy configuration
app.config['SQLALCHEMY_POOL_SIZE'] = 20
, but it did not resolve the issue. I expected that increasing the pool size would allow my Flask application to manage concurrent requests more effectively, ensuring that multiple users can access the database simultaneously without experiencing delays. However, despite the configuration change, I still observe delays in fulfilling requests from multiple users concurrently.