Efficient Connection Pooling for multithreading http server

[dertin]

Hello,

I would like to better understand the necessary steps when starting my Http server and what needs to be passed to each request/thread to reuse connections and avoid the overhead of opening and closing connections, which could affect connection management efficiency (deadlock, lags)

I want to implement a connection pool where I can obtain a connection for each request, potentially executed in different threads. Does unixodbc handle this automatically?

Please note that the application may experience a high number of requests per second.

From my understanding, I need to establish a connection in each thread using the same connection string. If a previous connection is available, it will be reused instead of establishing a new connection. Is my understanding correct? Is there a way to obtain the connection identifier from Rust to monitor the number of active connections and identify new connections?

I have simplified the code in this link, without considering the presence of hyper.rs and other elements of the final implementation, but I believe it illustrates the concept:

https://gist.github.com/dertin/d5e6b031cb86fbe71c6d2272c764dedd
Edit: Look, I added some questions in the comments of the code.

I would greatly appreciate any assistance or advice you can provide. Thank you!

[pacman82]

Hello @dertin ,

thanks for giving odbc-api a spin. Indeed Connection pooling is supported by the ODBC standard and also by the unixODBC driver manager.

In terms of practicalities you may want to check out the documentation of odbc-api on docs.rs, the guide also contains a small section about connection pooling: https://docs.rs/odbc-api/latest/odbc_api/guide/index.html#connection-pooling.

Q: To work with threads do I need to share a single Environment ?

Yes, it is recommended to work with only one enivronment for the entire process regardless. If ODBC connections are used throughout the lifetime of the service I would recommend putting the enivronment into a lazy_static or its modern equivalent once_cell.

Q: If I connect multiple times from different threads, are the connections reused? According to ODBC and Driver ?

For MSSQL and its driver I am fairly certain the answer is yes. The Microsoft driver in general is the most feature complete one I have witnessed in my test suites. Since odbc-api ultimatly is only representing bindings to the ODBC standard (and its C-API) it is ultimatly outside of my control. You can read up on the ODBC standard and connection pooling here though: https://learn.microsoft.com/en-us/sql/odbc/reference/develop-app/driver-manager-connection-pooling

Ultimatly getting connection pooling to work is not harder then to call an unsafe function (set_connection_pooling) at the start of the application. Why unsafe? Because it changes global mutable state in your process. It is completly safe as long as you do it in only once during the lifetime of your application, best before initializing the environment. It can not be safely abstracted away in a library (because you can not prove and neither can the compiler, that nobody else is calling the odbc C interface doing the same thing at the same time, maybe even through the same safe interface). This has not stopped developers, from just calling the unsafe function in their crates and reexposing the function as a safe one though. The only way you can proof it is within an application not a library you know the entire code. Then the proof is trivial though, because you just call it once. Personally I think accepting the line of unsafe code in the beginning of the app is the most "honest" way of going about it.

Hope this helps.

Best, Markus

[dertin]

I would recommend putting the enivronment into a lazy_static or its modern equivalent once_cell.

I'm using std::sync::OnceLock for the Environment because I can't use std::cell::OnceCell since it's not thread-safe to send between threads. Do you think it's OK ?

// Environment initialized once
let env = {
    pub static ENV: OnceLock<Environment> = OnceLock::new();

    ENV.get_or_init(|| unsafe {
        println!("Environment for MSSQL pool");

        Environment::set_connection_pooling(AttrConnectionPooling::DriverAware).unwrap();

        let mut env = Environment::new().unwrap();

        env.set_connection_pooling_matching(AttrCpMatch::Strict)
            .unwrap();

        env
    })
};

Yes, it is recommended to work with only one enivronment for the entire process regardless.

I understand, it's important to manage the same environment on each thread. With the help of OnceLock, I think I have succeeded.

[pacman82]

This is fine

[dertin]

1- While exploring my options to better control connection pooling for ODBC, I came across r2d2_odbc_api, which seems to be easily integrable into my project. However, I'm still undecided if I should stick with r2d2 (I'm already using r2d2 for redis connection pooling)
or change to bb8 or deadpool. Consequently, I'd need to find a compatible odbc_api layer for either of them, but I'm not sure if the effort would be worth it. What do you think? @pacman82

2- Why do I only see the description attribute ?

// Q: How can I see complete driver settings?
// I do not see "... Pooling=Yes; CPTimeout=180; PoolMaxSize=15;"
for driver_info in env.drivers().unwrap() {
    println!("{:#?}", driver_info);
    // Output:
    // DriverInfo {
    //     description: "ODBC Driver 18 for SQL Server",
    //     attributes: {
    //         "Description": "Microsoft ODBC Driver 18 for SQL Server Pool",
    //     },
    // } ...
}

dertin@dertin-desktop-ubuntu:~$ odbcinst -j
unixODBC 2.3.12pre
DRIVERS............: /usr/local/etc/odbcinst.ini
SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini
FILE DATA SOURCES..: /usr/local/etc/ODBCDataSources
USER DATA SOURCES..: /home/dertin/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8
dertin@dertin-desktop-ubuntu:~$ cat /usr/local/etc/odbcinst.ini
[ODBC Driver 18 for SQL Server]
Description=Microsoft ODBC Driver 18 for SQL Server
Driver=/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.2.so.2.1
UsageCount=1
Pooling=Yes
CPTimeout=180
PoolMaxSize=15

[pacman82]

1. You are using directly the pooling exposed by your driver manager. You would gain nothing by adding a connection pooling library.
2. It is up to the driver to decide what you see in the attributes

[dertin]

I have a warning related to the ODBC connection string:

odbc_api::handles::logging - WARN - State: 01S00, Native error: 0, Message: [Microsoft][ODBC Driver 18 for SQL Server]Invalid connection string attribute

All of these attributes must be valid in the connection string. Why does it give this warning?

let connection_string = "
                Driver={ODBC_Driver_18_for_SQL_Server_Pool};\
                Server=0.0.0.0;\
                UID=SA;\
                PWD=Pepe1234;TrustServerCertificate=Yes;Database=mydatabase;
                ";

And I think these messages are better for a debug logging level rather than warning

odbc_api::handles::logging - WARN - State: 01000, Native error: 5701, Message: [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Changed database context to 'mydatabase'.
odbc_api::handles::logging - WARN  - State: 01000, Native error: 5703, Message: [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Changed language setting to us_english.

I understand this can get complex when the driver doesn't report the appropriate log level.

odbc-api/odbc-api/src/handles/logging.rs

Line 18 in 7aea286

warn!("{}", rec);

[pacman82]

Hello @dertin ,

All of these attributes must be valid in the connection string. Why does it give this warning?

I do not know, ask Microsoft. Server 0.0.0.0 seems funny to me though. Sure you do not mean 127.0.0.1?

I understand this can get complex when the driver doesn't report the appropriate log level.

We get a severity, which is either error or everything else. I could consider logging everything else as info, but this would need some though.

[dertin]

Hello @pacman82

I was able to solve the "Invalid connection string attribute" error by removing the spaces at the beginning and end of the connection string.

let connection_string = "
                Driver={ODBC_Driver_18_for_SQL_Server_Pool};\
                Server=0.0.0.0;\
                UID=SA;\
                PWD=Pepe1234;TrustServerCertificate=Yes;Database=mydatabase;
                ";

change it to

let connection_string = "Driver={ODBC_Driver_18_for_SQL_Server_Pool};\
                Server=0.0.0.0;\
                UID=SA;\
                PWD=Pepe1234;TrustServerCertificate=Yes;Database=mydatabase;";

The IP 0.0.0.0 is because I have the MSSQL server in a Docker, and due to my network configuration, it is easier for me to access the container this way.