Didn't see any example of How to use connect pool ?

[sunshineinwater]

Didn't see any example of How to use connection pool ?

By the way, is it plan to support multi-connection of database , typically in Sanic project ?

[sunshineinwater]

add: asyncpg support connection pool , but I don't know how to config it in tortoise...

[grigi]

Hi @sunshineinwater Tortoise ORM uses connection pools automatically. Both MySQL and PostgreSQL has it enabled by default. e.g. for Postgres: https://tortoise-orm.readthedocs.io/en/latest/databases.html#id4 We default to a pool starting at 1 then extending to 5 connections by default.

[sunshineinwater]

Can I change the default connections maxsize by config in Sanic ? like this:

register_tortoise(app,
                  config={
                      'connections': {
                          # Dict format for connection
                          'default': {
                              'engine': 'tortoise.backends.asyncpg',
                              'credentials': {
                                  'host': 'localhost',
                                  'port': '5432',
                                  'user': 'postgres',
                                  'password': 'postgres',
                                  'database': 'zhangxuewei',
                              },
                              'maxsize': 100
                          },
                          # Using a DB_URL string
                          # 'default': 'postgres://postgres:qwerty123@localhost:5432/events'
                      },
                      'apps': {
                          'models': {
                              'models': ["model.tortoiseORM_Model"],
                              # If no default_connection specified, defaults to 'default'
                              'default_connection': 'default',
                          }
                      }
                  })

[grigi]

Pass the extra DB parameters under the credentials key.

[sunshineinwater]

Pass the extra DB parameters under the credentials key.

got it! THX a lot!

[sunshineinwater]

Pass the extra DB parameters under the credentials key.

by the way, as my test , set maxsize= 10 with Sanic workers = 5, it acutally generates 10*5 connections.

[grigi]

Yes, It would. multiple workers for sanic is multiple python processes behind a common connection handler. So each process would have its own instance of tortoise, etc...

[sunshineinwater]

Yes, It would. multiple workers for sanic is multiple python processes behind a common connection handler. So each process would have its own instance of tortoise, etc...

THX Author to provide such nice async ORM

[grigi]

My involvement in this was due to me looking for an asyncio ORM, and at that time there was nothing. That was only 1⅔ years ago... I think the first release Andrey did was about 2 years ago only.

[sunshineinwater]

My involvement in this was due to me looking for an asyncio ORM, and at that time there was nothing. That was only 1⅔ years ago... I think the first release Andrey did was about 2 years ago only.

I was writing async ORM demo not long ago, because the performance of current async ORM is not good... But, limit to my programming skill level, my own ORM's performance is worse than yours. It's very nice to find this ORM, it's so great. THX !

[grigi]

Thank you!

Yes, we spent a lot of effort in getting rid of some of the python-level inefficiencies. We came from a long way: https://github.com/tortoise/orm-benchmarks/#performance-of-tortoise And there is a lot of known inefficiencies, and we haven't even looked at how we perform with lots of relations. e.g. serialization of a deep structure.

If you want to help, or even want a feature, please do so :-)

[sunshineinwater]

thx for inviting.I'm glad I can do this, but I'm not familiar with it yet, I'm afraid it's hard to help

[grigi]

:+1: Some of the most helpful things is debugging an issue, or even pointing out something that could be better.

[sunshineinwater]

I actually did some performance experiments in Sanic + self ORM environment . Every time a new instance is created, there will be a 10% performance penalty, even just just call the @classmethod without instance it.

• performance test tools: wrk command: wrk -t4 -c1000 -d30s -T30s --latency http://0.0.0.0:7005/testdb

• here is my experiment code demo:

@app.route('/testdb', methods=["GET"])
async def test_orm(request):
    # Select * from * Where *

    # 5% off performance
    data = await query_first(request, [UserModel, "account_id='test'"])

    # 20% off performance
    # data = await UserModel.query(request).filter("account_id='test'").first()

    return response.json(data)

• query core code

async def asyncpgQueryMethod(request, TableModel, statement) -> list:
    #
    sql = """SELECT * FROM "%s" WHERE %s""" % (TableModel.__table_name__, statement)
    #
    SQLSession = request["SQLSession"][TableModel.__bind_key__]
    data = await SQLSession.fetch(sql)
    # result to dict
    dataDictList = []
    for item in data:
        itemDict = dict(item)
        dataDictList.append(itemDict)
    return dataDictList

async def query_first(request, queryParams):
    #
    TableModel, statement = queryParams
    #
    data = await asyncpgQueryMethod(request, TableModel, statement)
    if len(data) == 0:
        return None
    else:
        newTableModel = TableModel()
        newTableModel.from_dict(data[0])
        return newTableModel

# 20% off performance
def query(TableModel, request):
    class __SelectWhere__:
        _TableModel_: pg.BaseModel

        @classmethod
        def setParams(cls, _TableModel: pg.BaseModel, _request):
            cls._TableModel_ = _TableModel
            cls._request = _request
            return cls

        @classmethod
        def filter(cls, statement: str):
            #
            class __queryData__:
                __statement__: str
                __TableModel__: pg.BaseModel

                @classmethod
                def setParams(cls, _statement: str, _TableModel_: pg.BaseModel, _request):
                    cls.__statement__ = _statement
                    cls.__TableModel__ = _TableModel_
                    cls.__request = _request
                    return cls

                @classmethod
                async def first(cls):
                    data = await asyncpgQueryMethod()
                    if len(data) == 0:
                        return None
                    else:
                        # newTableModel = self.__TableModel__()
                        # newTableModel.from_dict(data[0])
                        # return newTableModel
                        return data[0]

            return __queryData__.setParams(statement, cls._TableModel_, cls._request)

    return __SelectWhere__.setParams(TableModel, request)

[grigi]

Thank you for the performance example. We should extend our benchmark suite to cover things such as full serdes and complicated relational queries. Possibly look at using locust?

Also, the purpose of this issue has been resolved, so I'm going to close it now.