# Sqlalchemy

## Connection pool tuning

There are multiple parameters sqlalchemy exposes for tunning connection pool. Most of them can be set via `sqlalchemy.create_engine(*args, **kwargs)`. For example:

```python
    sm = sessionmaker(
        bind=sqlalchemy.create_engine(
            URL(**conn_params),
            pool_size=pool_size,
            pool_recycle=pool_recycle,
            pool_pre_ping=pool_pre_ping,
            pool_reset_on_return=pool_reset_on_return))
```

Below are some of them with descriptions and recommendations:

* `pool_size` - The number of connections to keep open inside the connection pool. **It's reasonable to set this parameter at least to the number of threads used by the microservice**
* `pool_recycle` - Specifies the number of seconds after which sqlalchemy will forcily reopen connections \(without regard if it's valid or not\).

  In most cases openning a connection is a rather expensive operation and we would want to keep this parameter as high as possible.

  **It makes sense to find out how long your database server keeps inactive connections open and set this number close to that value \(-5 minutes\)**.

  It's also possible to set it to `-1` \(recycling is turned off\). In that case you need to make sure that the DB dialect supports handling of disconnects \(it at least should be able to invalidate connections\)

* `pool_pre_ping` - It tells sqlalchemy to execute a check query before returning the connection to the client code. This way we can make sure that the connection is actually valid \(open\).

  On the other hand the penalty is going to be ~200 ms for every query. **We're currently not using this in our microservices**

* `pool_reset_on_return` - Can be `None`, `"rollback"` or `"commit"`. Specifies what to do when a connection is returned to the pool. Defaults to `"rollback"`. You need to realize

  that executing a `rollback` or `commit` is not free they bring their own latency. **You need to figure out if you need them and if not it's recommended to avoid them**

* `max_overflow` - The number of connections to allow in connection pool "overflow", that is connections that can be

  opened above and beyond the pool\_size setting. Once these connections returned to the pool they get closed. That means that we don't want to go overflow often.

  It's only intended for occasional bursts. It's a good idea to keep track of how often we overflow and thus adjust the pool size correspondingly.

  For more info take a look at [CON-984](https://jira.theorchard.com/browse/CON-984)

### Snowflake pool tuning

For accessing snowflake from microservices we're using [snowflake-connector-sqlalchemy](https://github.com/theorchard/snowflake-connector-sqlalchemy). One of the major reasons for using sqlachemy is that it provides a connection pool implementatation. Basically it's a wrapper around sqlalchemy API. At the moment [snowflake-connector-sqlalchemy](https://github.com/theorchard/snowflake-connector-sqlalchemy) provides `set_default_sessionmaker` for initializing pool settings. You cannot have multiple pool instances per process. There will be a single pool shared among all the microservice's endpoints within a single process \(your microservices may have multiple processes in which case every process will have a dedicated pool\). The signature looks like:

```python
def set_default_sessionmaker(
        sf_config=None,
        pool_size=15,
        pool_recycle=60*55*60,
        pool_pre_ping=False,
        pool_reset_on_return=None,
        invalidate_connections=True,
        **kwargs)
```

Most of the parameters echo corresponding `create_engine` parameters. Below are details specific only to Snowflake:

* `sf_config` - snowflake connection parameters
* `pool_recycle` - Snowflake closes connections after 4 hours of inactivity. **Thus you might want to set this parameter to something like `60 * (60 - 5) * 60` \(3 hours 55 minutes\)**

  The last version of [snowflake-connector-sqlalchemy](https://github.com/theorchard/snowflake-connector-sqlalchemy)

  should be able to handle the situation when a connection raises an exception during query execution and re-execute the query so that the client code will not notice anyting \(except increased latency\). This effectively lets us turn this parameter off completely.

  But it turns out the performance gain will be not prominent in that case and probably it makes sense to keep recycling on.

* `pool_reset_on_return` - at the moment most of the microservices consuming Snowflake have this parameter swtiched off. **It's recommended to set it to `None`.** which means that we don't want to do any kind of reset when we release a connection.
* `invalidate_connections` - if sqlalchemy should invalide connections on errors. If set True it tells sqlalchemy that if there has been an exception during query execution

  the next time it wants to return the same connection it should reopen it beforehand as it's very likely to be invalid. At the moment sqlalchemy+snowflake does not support

  this out of the box. We [implemented](https://github.com/theorchard/snowflake-connector-sqlalchemy/blob/master/snowflake_connector/snowflake_conn.py#L365) a plugin for that.

  **It's recommended to enable this parameter**.

### Read-only Pool Tuning

By default, sqlalchemy will perform a ROLLBACK and commit before closing a Session and returning it to the ConnectionPool. For databases not local to the application's environment this can add significant overhead. Ex. each of these calls from an App in AWS, to art\_relation in AVL adds ~20ms.

To avoid these calls when performing a read-only query, one can create a separate Pool of connections with the pool configured `pool_reset_on_return=True` to avoid a rollback query & the session set to `autocommit=True` to avoid begin/commit queries where every query execute is implicitly committed on execution.

We do not attempt to use the existing Write pool because transactions not in autocommit mode need to be closed even when just reading to avoid read locks.

```text
# seperate pool of DB connections for SELECT queries
# These connections do not have the overhead of adding a commit & rollback
# statement before returning the connection to the Pool. Because they don't
# perform a commit/rollback to  release locks, we set autocommit=1 &
# isolation_level='READ_COMMITTED' to avoid creating any READ locks in the
# first place as a guard against deadlocks.
_read_db_engine = create_engine(
    config.AR_DB_URL, pool_size=config.POOL_SIZE,
    max_overflow=config.POOL_MAX_OVERFLOW,
    pool_recycle=config.POOL_RECYCLE_MS,
    connect_args={**config.CONNECT_ARGS, 'autocommit': 1},
    isolation_level='READ_COMMITTED',  # avoid READ locks
    pool_reset_on_return=None)  # avoid rollback when connection returns
_db_read_session = sessionmaker(
    bind=_read_db_engine,
    # https://docs.sqlalchemy.org/en/13/orm/session_transaction.html#autocommit-mode
    # autocommit=True avoids implicit BEGIN (each statement is 'committed')
    autocommit=True, autoflush=False, expire_on_commit=False)

@contextmanager
def db_read_session():
    """Provide a read scope around a series of operations.
    This handles closing of session to return connection to pool.
    No commit or rollback so use only for SELECT queries.
    Usage:
        with db_session() as session:
            session.execute(query)
    """
    session = _db_read_session()
    try:
        yield session
    except:
        session.rollback()  # safeguard against accidental locks
        raise
    finally:
        session.close()
```

ows-permissions before this change  
![before](../.gitbook/assets/sqlalchemey_ows_participants_before.png)

ows-permissions after this change  
![after](../.gitbook/assets/sqlalchemey_ows_participants_after.png)