S
S
SciFi2016-09-20 12:28:57
MySQL
SciFi, 2016-09-20 12:28:57

How to create multi-threaded applications on Flask + SQLAlchemy?

Making my first big Python application: Flask + SQL Alchemy
MySQL database.
The bottom line is that tasks are created inside the application. Each job runs in multi-threaded mode.
Within each thread, there are many reads and writes to the database.
The problem is that with a large enough number, the (50+) MySQL thread just dies.
The important point is that each job runs in a separate process. Threads are already created inside the process.
I can't figure out exactly where the bottleneck is. Simple read-write data operations are performed. The amount of data is not large.
Based on my extensive past experience with PHP, I know that MySQL should be able to easily handle these kinds of loads.
SQL Alchemy settings:

SQLALCHEMY_TRACK_MODIFICATIONS = True
SQLALCHEMY_POOL_SIZE = 20
SQLALCHEMY_MAX_OVERFLOW = 5
SQLALCHEMY_POOL_RECYCLE = 5

I create a db object:
db = SQLAlchemy(app)
Then I import it and use it in other scripts.
I don't think I'm doing it right in some places.
For example, I need to increment a value in a database in real time. This is the only working version of the code:
db.session.commit()
task = Task.query.filter_by(id=self.task.id).first()
db.session.query(Task).filter_by(id=self.task.id).update({'progress': task.progress + 1})
db.session.commit()

The key question is: how to properly organize work with such an application on SQL Alchemy?
How to use sessions correctly? And is it possible to trace the moment and source of creating a large load on MySQL?
The server used is extremely powerful and this is not a bottleneck. Maybe MySQL specific settings?

Answer the question

In order to leave comments, you need to log in

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question