Sanic+Gino and Flask+Sqlalchemy have similar pressure test performance.

[sunshineinwater]

Description

use WRK test three types of web frame to read one recorder in postgresql database : 1、Sanic+gino, 2、flask+sqlalchemy+gevent+gunicorn, 3、Golang gin+gorm.

test machine: MacBook Pro 8GB

• concurrence result :

1. sanic+gino: 1700 requests/seconds
2. flask+sqlalchemy:1100 requests/seconds
3. Golang : 5000+ requests/seconds

flask + SQLAlchemy :

sanic + gino

use Same Configration test purl web performance:

• concurrence result :

1. Sanic : 20000 requests/seconds
2. flask: 3300 requests/seconds
3. Golang : 80000 requests/seconds

Why it has nearly performance between sanic+gino and flask+sqlalchemy ?

What I Did

Sanic + gino :

@app.route('/testdb', methods=['GET'])
async def 测试数据库功能(request):
    data = await UserModel.query.where(UserModel.accountID == "test").gino.first()
    if data is None:
        user = await UserModel.create(accountID='test')
        data = await UserModel.query.where(UserModel.accountID == "test").gino.first()

    return response.text(data.openID)

if __name__ == '__main__':
    app.run(debug=False, port=7005, host='0.0.0.0', workers=5, access_log=False)

Flask + SQLalchemy:

@app.route("/testdb", methods=["GET"])
def test_db():
    data = UserModel.query.filter(UserModel.accountID == "test").first()
    if data is not None:
        return data.accountID
    return "None"

run:
gunicorn main:app --bind 0.0.0.0:7005 --worker-class gevent --workers 9 --threads 8

[sunshineinwater]

Use Sanic+SQLalchmey, with default sqlalchemy setting:

the performance even higher than gino ?

....

# sqlalchemy
engine = create_engine("postgresql://postgres:postgres@localhost:5432/zhangxuewei", echo=False)
Base = declarative_base(engine)
session = sessionmaker(engine)()

....

@app.route('/testdb', methods=['GET'])
async def test(request):
    data = session.query(UserModelalc).filter(UserModelalc.accountID == "test").first()
    return response.text(data.openID)

if __name__ == '__main__':
    app.run(debug=False, port=7005, host='0.0.0.0', workers=5, access_log=False)

[sunshineinwater]

The newest test of Sanic+asyncpg : 5300 Requests/Sec it obviously gino have performance bottle neck? /

[sunshineinwater]

I guess that Gino use the pool of SQLalchemy instead of asyncpg's pool ?

[sunshineinwater]

Find out the reason block the thread: 1、generate query statement in sqlalchemy Core 2、generate sql command from sqlalchemy query statement.

[fantix]

Find out the reason block the thread: 1、generate query statement in sqlalchemy Core 2、generate sql command from sqlalchemy query statement.

Yeah, that's usually the cost having the convenience of query builder or ORM. I think we can implement something like the baked query to boost that.

[sunshineinwater]

Find out the reason block the thread: 1、generate query statement in sqlalchemy Core 2、generate sql command from sqlalchemy query statement.

Yeah, that's usually the cost having the convenience of query builder or ORM. I think we can implement something like the baked query to boost that.

It doesn't work or I misunderstand your point? I have tried to caching generated SQL statements by SA and parse it to sql command. Meanwhile I have wrote a ORM demo use asyncpg directly, it perform well.

code demo:

from asyncpg import create_pool
from sanic import Blueprint, Sanic
from math import floor


class PG_ORM:
    @staticmethod
    def init_db_withPool(app: Sanic = None, bp: Blueprint = None,
                         user="postgres", password="postgres", database="mydb",
                         host="localhost", port=5432,
                         max_inactive_connection_lifetime=60, pool_min_size=1,
                         pool_max_size=100, workers=5):
        """
        Params of bp and app:  just need one of them.（bp或者app仅需传入其中一个）
        """
        # check params（参数校验，并赋值）
        if app is None and bp is None:
            raise Exception("PG_ORM().init_db()  need one parameter of app or bp ")
        if workers < 1:
            raise Exception("param worders must >= 1")
        if workers >= pool_max_size:
            raise Exception("工作核心超过数据库连接池最大连接数")
        if app is not None:
            temp = app
        if bp is not None:
            temp = bp

        # set middleware（设置中间件）
        @temp.listener('before_server_start')
        async def init_pg(app, loop):
            """
            Init Postgresql DB.
            """
            max_size = floor(pool_max_size / workers)
            min_size = floor(pool_min_size / workers)
            app.pg_pool = await create_pool(
                user=user, password=password,
                database=database, host=host, port=port,
                max_inactive_connection_lifetime=max_inactive_connection_lifetime,
                min_size=min_size if min_size > 0 else 1,
                max_size=max_size if max_size > 0 else 1,
                loop=loop)

            app.session = Session(app.pg_pool)

# Define to Execute Origin SQL command(执行SQL原生语句)
class Session:
    def __init__(self, pg_pool):
        self.pg_pool = pg_pool

    async def fetch(self, sql, *args, **kwargs):
        async with self.pg_pool.acquire() as connection:
            return await connection.fetch(sql, *args, **kwargs)

    async def execute(self, sql, *args, **kwargs):
        async with self.pg_pool.acquire() as connection:
            return await connection.execute(sql, *args, **kwargs)


"""
    define Table Model (定义表模型)
"""

# overloaded operator
class 重载运算符:
    columnName: str

    # overloaded operator： ==
    def __eq__(self, other):
        return "%s = '%s'" % (self.columnName, other)


class String(重载运算符):
    def __init__(self, column_name, lens: int = 128):
        self.lens = 128
        self.columnName = column_name


class BaseModel:
    __table_name__: str

    def query(self, app):
        return self.__Query(app, self.__table_name__)

    class __Query:
        def __init__(self, app, __table_name__):
            self.__table_name__ = __table_name__
            # print(__table_name__)
            self.session = app.session

        async def filter(self, statement: str):
            sql = """SELECT * FROM "%s" WHERE %s""" % (self.__table_name__, statement)
            # print(sql)
            return await self.session.fetch(sql)

use it demo:

from sanic import response, Sanic
from xxxx import PG_ORM, BaseModel, String

# create sanic(创建sanic实例)
app = Sanic(__name__)

#  SQL Pool(数据库连接池)
PG_ORM().init_db_withPool(app=app, workers=10)

# define orm Model（定义模型）
class User(BaseModel):
    __table_name__ = "user账号表"
    account_id = String("account_id", 128)

@app.route('/testorm', methods=["GET"])
async def t(rquest):
    data = await User().query(app).filter(User.account_id == "test")
    return response.text(data)

[fantix]

It doesn't work or I misunderstand your point?

The baked-query-like caching feature is not implemented yet.

[sunshineinwater]

It doesn't work or I misunderstand your point?

The baked-query-like caching feature is not implemented yet.

As my test result, the main influence to permanence is SA's core ? here is my test code:

@app.route('/testdb', methods=['GET'])
async def testdb(request):

    # 此处两条命令阻塞线程，并发性能损失巨大。
    # this two functions is main reason to lost permanence . 
    # test result:  5000  ->  3700  ->  1700  requests/second
    sqlStatement = SASession.query(UserModel).filter(UserModel.account_id == "test").statement
    sqlCommandStr = literalquery(sql)
    # 
    values = await app.session.fetch(sqlCommandStr)
    return response.json({"nihoa": "nimei"})