Analyze impact of upgrade to asyncpg from aiopg

[pcrespov]

Based on the tests reported in #7826, equivalent database queries show a significant performance difference between asyncpg and aiopg. Specifically, asyncpg—the library we aim to use—performs worse than aiopg in this scenario.

This issue should explore why this happened since most reports indicate that the new library is actualy better. The conclusions should also suggest some guidelines on how to use asyncpg

✅ Benchmarking Checklist for asyncpg vs. aiopg

1. Connection Management

• Pooling Strategy: Ensure both libraries use comparable connection pooling configurations. For asyncpg, utilize asyncpg.create_pool(), and for aiopg, use aiopg.create_pool().
• Pool Size: Set identical minsize and maxsize parameters to maintain consistency.

2. Query Execution

• Query Complexity: Benchmark both simple (e.g., SELECT 1) and complex queries to assess performance across different workloads.
• Parameter Binding: Use parameterized queries to prevent SQL injection and ensure fair comparison.

3. Data Volume

• Result Set Size: Test with varying result set sizes (e.g., 10, 1000, 10000 rows) to evaluate how each library handles different data volumes.

4. Concurrency

• Concurrent Connections: Simulate multiple concurrent connections to assess how each library scales under load.
• Async Context: Ensure that the event loop is properly managed and that asynchronous contexts are correctly implemented.

5. Performance Metrics

• Latency: Measure the time taken to execute queries.
• Throughput: Assess the number of queries handled per second.
• Resource Utilization: Monitor CPU and memory usage during benchmarking.

6. Environment Consistency

• Hardware: Run benchmarks on the same hardware to eliminate discrepancies.
• Database Configuration: Ensure the PostgreSQL server settings are identical for both tests.

7. Instrumentation and Monitoring

• Logging: Enable detailed logging to trace query execution paths.
• Profiling Tools: Use tools like cProfile, py-spy, or async-profiler to identify bottlenecks.([varrazzo.com]1)

---

🔍 Tracing and Analysis Tools

• EXPLAIN ANALYZE: Use this PostgreSQL command to understand query execution plans and identify inefficiencies.
• pg_stat_statements: Enable this extension to track execution statistics of all SQL statements.
• pgbench: Utilize this benchmarking tool to simulate client load and measure performance.
• asyncpg's Built-in Logging: Leverage asyncpg's logging capabilities to monitor query execution and pool behavior.

---

📚 Additional Resources

For a deeper understanding and practical examples, consider reviewing the following resources:

• MagicStack's Benchmark Report: Provides an in-depth performance comparison between asyncpg and other PostgreSQL drivers. ([magic.io]2)

• Fernando Arteaga's Blog Post: Offers a comprehensive benchmark analysis of psycopg3 and asyncpg within a FastAPI application context. ([fernandoarteaga.dev]3)

• GitHub Gist on asyncpgsa vs. aiopg.sa: Presents a practical benchmark comparison between these two libraries. ([gist.github.com]4)

By meticulously following this checklist and utilizing the suggested tools, you can achieve a fair and thorough comparison between asyncpg and aiopg, leading to informed decisions tailored to your application's specific needs.

[pcrespov]

Drafted with chatgpt

import asyncio
import time
import sqlalchemy as sa
from sqlalchemy.ext.asyncio import create_async_engine
from aiopg.sa import create_engine as create_aiopg_engine

# Shared SQLAlchemy table definition
metadata = sa.MetaData()
users = sa.Table(
    'users',
    metadata,
    sa.Column('id', sa.Integer),
    sa.Column('role', sa.String),
    sa.Column('email', sa.String),
    sa.Column('status', sa.String),
)

stmt = sa.select(users.c.id, users.c.role).where(
    (users.c.email == sa.bindparam('email')) & (users.c.status == sa.bindparam('status'))
)
params = {"email": "test@example.com", "status": "ACTIVE"}

# Connection settings
dsn_sync = "postgresql://postgres:password@localhost:5432/testdb"
dsn_async = "postgresql+asyncpg://postgres:password@localhost:5432/testdb"
server_settings = {}  # Customize if needed

# Asyncpg SQLAlchemy engine
asyncpg_engine = create_async_engine(
    dsn_async,
    pool_size=5,
    max_overflow=5,
    connect_args={"server_settings": server_settings},
    pool_pre_ping=True,
    future=True,
)

async def run_asyncpg_sqlalchemy_query():
    async with asyncpg_engine.connect() as conn:
        result = await conn.execute(stmt, params)
        return result.one_or_none()

# Aiopg SQLAlchemy engine
async def run_aiopg_sa_query():
    aiopg_engine = await create_aiopg_engine(dsn_sync, minsize=1, maxsize=5)
    async with aiopg_engine.acquire() as conn:
        result = await conn.execute(stmt, params)
        return await result.fetchone()

# Benchmark runner
async def benchmark():
    print("Warming up...")
    await run_asyncpg_sqlalchemy_query()
    await run_aiopg_sa_query()

    print("Benchmarking asyncpg + sqlalchemy.ext.asyncio...")
    start = time.perf_counter()
    await run_asyncpg_sqlalchemy_query()
    elapsed_asyncpg = time.perf_counter() - start

    print("Benchmarking aiopg.sa...")
    start = time.perf_counter()
    await run_aiopg_sa_query()
    elapsed_aiopg = time.perf_counter() - start

    print(f"asyncpg + sqlalchemy: {elapsed_asyncpg:.6f} sec")
    print(f"aiopg.sa:              {elapsed_aiopg:.6f} sec")

    await asyncpg_engine.dispose()

asyncio.run(benchmark())

[pcrespov]

[sanderegg]

sqlalchemy 2.0
https://docs.sqlalchemy.org/en/20/faq/performance.html

[pcrespov]

NOTE by @sanderegg

some important things I found regarding connections and sqlalchemy "autobegin" feature

• reference: 🎨 Reduce the number of acquisition/release of DB connection inside function repository #7904
• This PR made tests fail and it is a good thing
• The gist of this PR was to use only one connection using pass_or_acquire_connection during a whole function in functions_repository.py instead of doing 4-5 connection acquisitions in a row (the code worked, but under load it can raise due to connections overuse)
• The tests now are failing, because SQLAlchemy has a feature called autobegin, which basically automatically begins a transaction when one does for example a simple SELECT
• This worked well when acquiring connections multiple times, as the connections are implicitely closed --> the transactions are ROLLBACK
• Now using only one connection brought the issue that since the initial auto-begin transaction is active, the DB modifications that came later with transaction_context are actually nested transactions, and these are not auto-commited, and when the connection is released at the end of the function, everything is ROLLBACK...

--> Autobegin transaction need to be explicitely COMMIT or ROLLBACK
--> when using pass_or_acquire_connection and then doing whatever SQL calls, this will actually autobegin a transaction AND this context manager will NOT autocommit anything on exit

the issue comes from code like this:

async with pass_or_acquire_connection(get_asyncpg_engine(app), connection) as conn:
        await check_user_api_access_rights(
            app,
            connection=conn,
            user_id=user_id,
            product_name=product_name,
            api_access_rights=[FunctionsApiAccessRights.READ_FUNCTIONS],
        )

after that there is actually a transaction that was auto-begun.

• the next call to async with transaction_context will then create a begin_nested
• when the begin_nested exits, there is no auto-commit taking place
• and when pass_or_acquire_connection goes out, you get a ROLLBACK
• you can nicely see these if you set echo=True when creating the engine

[sanderegg]

from #7996 I identified that some database calls became slightly "slower" or that the ordering of calls changed at least.

[bisgaard-itis]

When investigating locally the v0/me/permissions endpoint in the webserver we see the following profile:

What can be seen here is that the execution of the db query using asynpg is surprisingly slow (0.7 sec). When running the associated query via adminer it takes miliseconds.

Here is the PostgreSQL query we ran in adminer:

SELECT
  anon_1.group_id,
  anon_1.product_name,
  anon_1.internet_access,
  anon_1.override_services_specifications,
  anon_1.use_on_demand_clusters,
  anon_1.enable_telemetry,
  anon_1.enable_efs,
  anon_1.created,
  anon_1.modified,
  anon_1.type,
  anon_1.type_order
FROM (
  SELECT
    gep.group_id AS group_id,
    gep.product_name AS product_name,
    gep.internet_access AS internet_access,
    gep.override_services_specifications AS override_services_specifications,
    gep.use_on_demand_clusters AS use_on_demand_clusters,
    gep.enable_telemetry AS enable_telemetry,
    gep.enable_efs AS enable_efs,
    gep.created AS created,
    gep.modified AS modified,
    g.type AS type,
    CASE
      WHEN g.type = 'EVERYONE' THEN 3
      WHEN g.type = 'STANDARD' THEN 2
      WHEN g.type = 'PRIMARY' THEN 1
      ELSE 4
    END AS type_order
  FROM
    groups_extra_properties gep
    JOIN user_to_groups utg ON gep.group_id = utg.gid
    JOIN groups g ON gep.group_id = g.gid
  WHERE
    gep.product_name = 'osparc'
    AND utg.uid = 4
) AS anon_1
ORDER BY
  anon_1.type_order;