search

tortoise / tortoise-orm

Familiar asyncio ORM for python, built with relations in mind
https://tortoise.github.io
Apache License 2.0
4.65k stars 387 forks source link

enabling multiple concurrent connections #227

Closed AEnterprise closed 4 years ago

AEnterprise commented 5 years ago

Is your feature request related to a problem? Please describe. Currently tortoise only uses 1 concurrent database connection instead of multiple ones, meaning that one expensive database query can lock up tortoise and cause all other database related things to be halted for a while

Describe the solution you'd like I looked into making this possible but i don't know enough of the tortoise internals to be sure of all the places that need to be edited

ideally it would also have mode/way to "group" database intensive queries and force them to queue up and share the same connection so the entire pool doesn't get used up waiting for expensive lookups to be completed. or is this something that i should be building into my side of things?

Describe alternatives you've considered For now using it with only 1 connection works but for my usecase could cause a lot of bottle-necking down the line

Additional context I have an 8GB table containing a non-indexed varchar(2000) and would like to be able to filter based on this field as well. however this requires a full tablescan and takes a while. while this is running, no other database interaction using tortoise is possible, blocking other functionality from completing in a timely manner

grigi commented 5 years ago

Firstly, let me say I agree with you that we NEED a working pooling system.

I do see two different issues in your bugreport here:

  1. Concurrency
  2. QoS (Quality of Service)

The concurrency issue would be pretty much solved by having a working DB pool. The QoS would only be mitigated, but not solved. One way to solve QoS would be to have multiple "pools" One for fast, real-time queries. And another for slower, batch-style queries.

I have a rough roadmap with this here: https://github.com/tortoise/tortoise-orm/pull/206#issuecomment-546320980 Of interest to this issue is points 2 & 4. But this excludes anything QoS.

AEnterprise commented 5 years ago

the concurrency issue would be mostly solved by that yeah, the not killing the entire bot when a single person does a heavy archive search is something i can catch and queue up on my end to prevent a single malicious users taking everything down

do you happen to have any pointers to the tortoise code of where/how to best get started making this work? i might be able to give it a shot if it doesn't require too deep knowledge of the internals

AEnterprise commented 4 years ago

implemented with #229