DB connections

[thielj]

Hi

I've started to play around with a (serverless, SSL secured) Postgres as DB backend. Queries seem to be awfully slow. Is it possible that connections aren't kept open? I'm no Rust programmer, but I was wondering if this might need e.g. min_connections(2) and a decent idle_timeout(60)?

        let mut sql_opt = sea_orm::ConnectOptions::new(database_url.to_string());
        sql_opt
            .max_connections(5)
            .sqlx_logging(true)
            .sqlx_logging_level(log::LevelFilter::Debug);

https://docs.rs/sqlx/latest/sqlx/struct.Pool.html

My understanding from the above and a bit snooping around the code is that the default "pool" is quite dry, i.e.

The pool will establish connections only as needed.

The default configuration is mainly suited for testing and light-duty applications. For production applications, you’ll likely want to make at least few tweaks.

(I tried a docker build with min_connections and idle_timeout but couldn't get past error: failed to compile 'wasm-pack v0.12.1')

[nitnelave]

Well, this is a light-duty application (unless you have a very different use case for lldap than what I had in mind).

But it seems that setting a min_connection(1) could do the trick (how many requests in parallel do you usually make?)

[nitnelave]

Note that you can also enable debug mode and see for each query what takes time.

[thielj]

As I said, I couldn't build/test it :( any hints?

[nitnelave]

Hmm, not sure, since I don't know your setup. Have you tried the lldap/rust_dev image? (I think that's what it's called)

[thielj]

I tried a docker build . using the images in Dockerfile

256.5 error: failed to compile `wasm-pack v0.12.1`, intermediate artifacts can be found at `/tmp/cargo-installFPDn6k`
256.5
256.5 Caused by:
256.5   package `clap_derive v4.5.4` cannot be built because it requires rustc 1.74 or newer, while the currently active rustc version is 1.69.0
256.5   Try re-running cargo install with `--locked`
256.5 error: failed to compile `cargo-chef v0.1.66`, intermediate artifacts can be found at `/tmp/cargo-installBodofi`
256.5
256.5 Caused by:
256.5   package `cargo-platform v0.1.8` cannot be built because it requires rustc 1.73 or newer, while the currently active rustc version is 1.69.0
256.5   Try re-running cargo install with `--locked`
256.5      Summary Failed to install wasm-pack, cargo-chef (see error(s) above).
256.5 error: some crates failed to install

Looks like I need a more recent builder, rustc>1.74

[nitnelave]

That Dockerfile is not well maintained, it might need updating

[thielj]

Hahaha. Definitely. rust:alpine3.18 seems to do the job. Let me test this first and get back to you tomorrow. I'm sure these queries can run faster than 3.7 seconds...

🚧 [warn]: | summary: "SELECT "users"."password_hash" FROM "users" …" | db.statement: "\n\nSELECT\n "users"."password_hash"\nFROM\n "users"\nWHERE\n "users"."user_id" = $1\nLIMIT\n $2\n" | rows_affected: 0 | rows_returned: 1 | elapsed: 3.694387658s

🚧 [warn]: | summary: "SELECT "users"."password_hash" FROM "users" …" | db.statement: "\n\nSELECT\n "users"."password_hash"\nFROM\n "users"\nWHERE\n "users"."user_id" = $1\nLIMIT\n $2\n" | rows_affected: 0 | rows_returned: 1 | elapsed: 2.221506552s

[thielj]

Some findings and suggestions.

Short version:

Make it configurable.

If you don't fancy that, or need defaults, I would suggest min_connections(4 + #cores) for embedded/SQLite and (8 + #cores) for anything else, and max_connections of twice the minimum. Plus sensible timeouts for remote servers to reflect the high connection costs.

Long version:

With the current settings, I was reaching the max_connections(5) very quickly. When I lifted this to max_connections(16), I peaked at 8 quickly. It might need another 1 or 2 when the scheduler kicks in. I'm not sure why it needs all these connections though. I'm certainly not trying to authenticate 8 users in parallel. Connections not being released in time / waiting to be GC'ed, maybe? As I said, I don't know Rust...

Could it be related to pooled LDAP connections holding on to DB connections? I'm using the Authelia feature build with 4 pooled LDAP connections. This is what I get:

# docker exec ldap netstat
Active Internet connections (w/o servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State
tcp        0      0 ldap$.xxx.docker.internal:ldap authelia.services:35750 ESTABLISHED
tcp        0      0 ldap$.xxx.docker.internal:57280 ec2-xxx:postgresql ESTABLISHED
tcp        0      0 ldap$.xxx.docker.internal:60618 ec2-xxx:postgresql TIME_WAIT
tcp        0      0 ldap$.xxx.docker.internal:33144 ec2-xxx:postgresql ESTABLISHED
tcp        0      0 ldap$.xxx.docker.internal:37294 ec2-xxx:postgresql TIME_WAIT
tcp        0      0 ldap$.xxx.docker.internal:58052 ec2-xxx:postgresql ESTABLISHED
tcp        0      0 ldap$.xxx.docker.internal:34336 ec2-xxx:postgresql TIME_WAIT
tcp        0      0 ldap$.xxx.docker.internal:ldap authelia.services:35766 ESTABLISHED
tcp        0      0 ldap$.xxx.docker.internal:ldap authelia.services:35738 ESTABLISHED
tcp        0      0 ldap$.xxx.docker.internal:33920 ec2-xxx:postgresql ESTABLISHED
tcp        0      0 ldap$.xxx.docker.internal:ldap authelia.services:35764 ESTABLISHED
tcp        0      0 ldap$.xxx.docker.internal:38974 ec2-xxx:postgresql TIME_WAIT

In general, I would suggest that at least min/max is configurable.

It may not matter much for an embedded SQLite instance with minimal overheads, but for an SSL secured server, consider: it's 3 round trips to establish the SSL connection, at least one round-trip to authenticate with the DB and another for the actual query. Add to that processing, crypto, hashing, load balancers, etc. It was enough for LLDAP to show warnings when simple queries took longer than one second...

We don't need to optimize for throughput and LLDAP is unlikely to saturate the DB engine (and if it does, it's time to upgrade). When optimizing for latency, I want to make sure that no query has to wait for a connection to be established or returned to the pool. The resource overhead for keeping an established connection around is neglectable. So I rather have a few too many connections, unless there are limits server side that would starve other apps.

---

The below works well for me, for example. Connections would time-out after 5 minutes and be forcefully recycled each hour, just in case. I think PgBouncer uses similar timeouts.

            .min_connections(8)
            .max_connections(16)
            .idle_timeout( Duration::from_secs(300) )
            .max_lifetime( Duration::from_secs(3600) )

[nitnelave]

Sounds like you did your homework 😅
Yeah, making it configurable (maybe under an advanced section?) works for me.

[thielj]

Everything to never ever touch OpenLDAP again ;)

[nitnelave]

Do you want to send a PR with this?

[thielj]

Naa. These "docker build" runs take forever. Adding more than those 3 lines would require setting up a Rust dev environment first.

…

On Fri, May 10, 2024, 12:26 nitnelave ***@***.***> wrote: Do you want to send a PR with this? — Reply to this email directly, view it on GitHub <#910 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AA6CUVI4FDAC5NNEXL76W7TZBSVHLAVCNFSM6AAAAABHLL33T2VHI2DSMVQWIX3LMV43SRDJONRXK43TNFXW4Q3PNVWWK3TUHM4TGOBRGYYDG> . You are receiving this because you authored the thread.Message ID: ***@***.***>

[nitnelave]

That's not too hard. rustup gets you the compiler, cargo and the tools, and then it's just a cargo build and you have all the dependencies. cargo test to run the tests, cargo fmt to reformat the code.

[nitnelave]

I'm saying that because I don't have much time behind a computer to write code, so it probably won't be done for a long time unless you do it. But I can help with questions and reviews.

[thielj]

That's okay with me. I have a working image now and can share it here if someone else runs into this issue. Learning Rust is on my list - but it won't be this month or next and probably not this year either 😆😭

…

On Fri, May 10, 2024, 12:45 nitnelave ***@***.***> wrote: I'm saying that because I don't have much time behind a computer to write code, so it probably won't be done for a long time unless you do it. But I can help with questions and reviews. — Reply to this email directly, view it on GitHub <#910 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AA6CUVMTU47LH4ZNYL66YJDZBSXNVAVCNFSM6AAAAABHLL33T2VHI2DSMVQWIX3LMV43SRDJONRXK43TNFXW4Q3PNVWWK3TUHM4TGOBRG42DS> . You are receiving this because you authored the thread.Message ID: ***@***.***>

[thielj]

Let's revisit this when Authelia releases the LDAP pooling and I update my own setup.

…

On Fri, May 10, 2024, 12:59 Jens ***@***.***> wrote: That's okay with me. I have a working image now and can share it here if someone else runs into this issue. Learning Rust is on my list - but it won't be this month or next and probably not this year either 😆😭 On Fri, May 10, 2024, 12:45 nitnelave ***@***.***> wrote: > I'm saying that because I don't have much time behind a computer to write > code, so it probably won't be done for a long time unless you do it. But I > can help with questions and reviews. > > — > Reply to this email directly, view it on GitHub > <#910 (reply in thread)>, > or unsubscribe > <https://github.com/notifications/unsubscribe-auth/AA6CUVMTU47LH4ZNYL66YJDZBSXNVAVCNFSM6AAAAABHLL33T2VHI2DSMVQWIX3LMV43SRDJONRXK43TNFXW4Q3PNVWWK3TUHM4TGOBRG42DS> > . > You are receiving this because you authored the thread.Message ID: > ***@***.***> >