Implement Connection Pooling for Oracle Provider to Improve Performance

[Moritz-Langer]

Implement Connection Pooling for Oracle Provider to Improve Performance

[Moritz-Langer]

On the weekend I started with an initial implementation along the line of the following:

class DatabaseConnection:
    """Database connection class to be used as 'with' statement.
    The class returns a connection object.
    """
    pool = None  # Class-level connection pool
    @classmethod
    def initialize_pool(cls, conn_dict):
        """Initialize the connection pool if not already initialized."""
        LOGGER.debug(f"conn_dict contains {conn_dict}")
        if DatabaseConnection.pool is None:
            DatabaseConnection.pool = oracledb.create_pool(
                            user=conn_dict["user"],
                            password=conn_dict["password"],
                            dsn=dsn,
                            min=2,  
                            max=10,  
                            increment=1, 
                        )

This looks promising and I will create a pull-request after some more polishing

[tomkralidis]

cc'ing @totycro and @8luewater for feedback/input.

[Moritz-Langer]

@8luewater I am currently finishing up on this.
We ran internal tests using locust which showed an around 10x improvement in speed using session pooling.
edit: 10x improvement given around 50 concurrent users

In the current design you would have to set environment variables like this to your linux / server environment:

export ORACLE_POOL_MIN=2
export ORACLE_POOL_MAX=10

Then the DatabaseConnection class uses these env variables as input parameters for the creation of a session pool.
When these env vars are not set, the regular behaviour is triggered and single connections are established for incomming requests.
@totycro and I discussed the issue of activating/configuring the session pool in the YAML file, but currently this is tricky since it would be global but would have to be written to every collection item in the yaml file.

Do you have any feedback/wishes/critique regarding this approach?

[8luewater]

Hi Moritz,That 10x improvement in speed sounds fantastic. At this stage, I am unsure how we implement that from yaml file in our environment, and it will be exciting to work on that when it’s time for us. Will have a chat with our Oracle team too about this. Cheers.Best regards,Sayaka SandinOn 12 Jun 2024, at 10:36 PM, Moritz Langer ***@***.***> wrote: @8luewater I am currently finishing up on this. We ran internal tests using locust which showed an around 10x improvement in speed using session pooling. In the current design you would have to set environment variables like this to your linux / server environment: export ORACLE_POOL_MIN=2 export ORACLE_POOL_MAX=10 Then the DatabaseConnection class uses these env variables as input parameters for the creation of a session pool. When these env vars are not set, the regular behaviour is triggered and single connections are established for incomming requests. @totycro and I discussed the issue of activating/configuring the session pool in the YAML file, but currently this is tricky since it would be global but would have to be written to every collection item in the yaml file. Do you have any feedback/wishes/critique regarding this approach? —Reply to this email directly, view it on GitHub, or unsubscribe.You are receiving this because you were mentioned.Message ID: ***@***.***>

[tomkralidis]

Added in #1688 (thanks @Moritz-Langer!)