Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

About Database Connection Pool Sizing

github.com

Post reply on HN
Print view View original

21–30 of 32 posts

Re: About Database Connection Pool Sizing

#21

Post by derefr »

dlubarov wrote: post #17

Why is that Oracle instance using a max thread pool size of 2048 on a 12-core host? That seems like the real problem. Why not configure a smaller thread pool like cores * 2, and do more queuing on the server? It seems wrong IMO to expect clients to tune connection pools based on how much concurrency they think a server can productively handle. The client doesn't necessarily know how many cores the server has, or more…

The SQL "protocol"† is a hairy one for a backend to deal with, state-wise. Each concurrent connection to an (ACID conformant) SQL-speaking server implies that that connection can have a transaction in progress.

A transaction, in SQL, requires the ability for the client (or the backend) to roll it back at any time before it gets committed—and thus, a transaction in progress requires keeping around at least two "world states" of the DB engine: the one before the transaction began, and the way the world looks after the current statement's modifications—but not those of any other concurrently-executing transaction.

Thus, a backend concurrent connection (i.e. transaction) cap of N, translates to O(N) concurrent MVCC "world states" that the DB engine has to keep track of. No matter how efficient the DB engine is at doing that, that's still a lot of state!

Imagine an RDBMS instance shared between numerous clients built and run by different companies. This RDBMS is locked down in various ways to guarantee each company an SLA on their queries. And, of course, the most important part of that SLA is that a query that is ACKed by the server as having gone through, must never lose data.

If you allow clients unlimited concurrency, they can execute enough transactions to fill memory and disk with MVCC world-states, causing potential data loss (since the full disk may have prevented the DB engine from writing down a committed world-state.)

Thus, ACID-conformant SQL-speaking databases are always going to limit the number of concurrent transactions, and so the number of concurrent connections; and so—at least for SQL-speaking databases designed for instance-addressable rather than cluster-addressable deployment—the responsibility for deciding how much concurrency a client wants/needs is always going to be pushed to the client, so that an enterprise with a DB cluster can set up its less concurrency-intensive clients to self-limit their pool size.

† SQL isn't a wire protocol per se; it's just a syntax and semantics of a formal language, without even a shared encoding. But the standard does specify per-connection and per-transaction state-machines the backend must have, and how these are affected by executing particular statements. So, in this way, SQL is an abstract contract for the semantics that any particular RDBMS's wire protocol must obey. A "meta-protocol", if you will.

Re: About Database Connection Pool Sizing

#22

Post by dlubarov »

derefr wrote: post #21
dlubarov wrote: post #17

Why is that Oracle instance using a max thread pool size of 2048 on a 12-core host? That seems like the real problem. Why not configure a smaller thread pool like cores * 2, and do more queuing on the server? It seems wrong IMO to expect clients to tune connection pools based on how much concurrency they think a server can productively handle. The client doesn't necessarily know how many cores the server has, or more…

The SQL "protocol"† is a hairy one for a backend to deal with, state-wise. Each concurrent connection to an (ACID conformant) SQL-speaking server implies that that connection can have a transaction in progress. A transaction, in SQL, requires the ability for the client (or the backend) to roll it back at any time before it gets committed—and thus, a transaction in progress requires keeping around at least two "world…

That's a fair point -- most (all?) SQL servers use the connection limit as an indirect transaction limit, rather than separating the two (in which case BEGIN TRANSACTION would sometimes have to block or fail).

Are large numbers of concurrent transactions really an issue in practice though? There may be some per-transaction overhead, but I haven't seen any stern warnings about it, e.g. in the Postgres docs for max_connections.

> Thus, a backend concurrent connection (i.e. transaction) cap of N, translates to O(N) concurrent MVCC "world states" that the DB engine has to keep track of. No matter how efficient the DB engine is at doing that, that's still a lot of state!

I don't quite see what you mean about world state. An abort just rolls back changes made by the aborted transaction, so I would expect SQL engines to store a diff of each updated row. And if that's the case, it seems like a single huge transaction could use as much memory as many small transactions, if the same rows were modified.

Re: About Database Connection Pool Sizing

#23

Post by [deleted] »

dlubarov wrote: post #22
derefr wrote: post #21

Earlier quoted context omitted.

The SQL "protocol"† is a hairy one for a backend to deal with, state-wise. Each concurrent connection to an (ACID conformant) SQL-speaking server implies that that connection can have a transaction in progress. A transaction, in SQL, requires the ability for the client (or the backend) to roll it back at any time before it gets committed—and thus, a transaction in progress requires keeping around at least two "world…

That's a fair point -- most (all?) SQL servers use the connection limit as an indirect transaction limit, rather than separating the two (in which case BEGIN TRANSACTION would sometimes have to block or fail). Are large numbers of concurrent transactions really an issue in practice though? There may be some per-transaction overhead, but I haven't seen any stern warnings about it, e.g. in the Postgres docs for max_con…

[deleted]

Re: About Database Connection Pool Sizing

#24

Post by anarazel »

dlubarov wrote: post #22
derefr wrote: post #21

Earlier quoted context omitted.

The SQL "protocol"† is a hairy one for a backend to deal with, state-wise. Each concurrent connection to an (ACID conformant) SQL-speaking server implies that that connection can have a transaction in progress. A transaction, in SQL, requires the ability for the client (or the backend) to roll it back at any time before it gets committed—and thus, a transaction in progress requires keeping around at least two "world…

That's a fair point -- most (all?) SQL servers use the connection limit as an indirect transaction limit, rather than separating the two (in which case BEGIN TRANSACTION would sometimes have to block or fail). Are large numbers of concurrent transactions really an issue in practice though? There may be some per-transaction overhead, but I haven't seen any stern warnings about it, e.g. in the Postgres docs for max_con…

> Are large numbers of concurrent transactions really an issue in practice though? There may be some per-transaction overhead, but I haven't seen any stern warnings about it, e.g. in the Postgres docs for max_connections.

There's definitely scalability implications to higher number of established connections in postgres. Essentially computing a "snapshot" (visibility information for queries etc, needs to be computed at least once a transaction) is O(#established-connections). If you get in the high hundreds on a large NUMA machine, and you run tiny fast transactions, you definitely can observe the overhead of that.

>> > Thus, a backend concurrent connection (i.e. transaction) cap of N, translates to O(N) concurrent MVCC "world states" that the DB engine has to keep track of. No matter how efficient the DB engine is at doing that, that's still a lot of state!

There's definitely ways to handle that better than O(N), at least in the average case.

Re: About Database Connection Pool Sizing

#25

Post by derefr »

anarazel wrote: post #24
dlubarov wrote: post #22

Earlier quoted context omitted.

That's a fair point -- most (all?) SQL servers use the connection limit as an indirect transaction limit, rather than separating the two (in which case BEGIN TRANSACTION would sometimes have to block or fail). Are large numbers of concurrent transactions really an issue in practice though? There may be some per-transaction overhead, but I haven't seen any stern warnings about it, e.g. in the Postgres docs for max_con…

> Are large numbers of concurrent transactions really an issue in practice though? There may be some per-transaction overhead, but I haven't seen any stern warnings about it, e.g. in the Postgres docs for max_connections. There's definitely scalability implications to higher number of established connections in postgres. Essentially computing a "snapshot" (visibility information for queries etc, needs to be computed…

> There's definitely ways to handle that better than O(N), at least in the average case.

O(N) root-level snapshot objects of the state, I mean. Like O(N) git commits. The minimum size of the persisted MVCC snapshot data, probably just scales with O(log N) of the number of connections, depending on what sort of data structure you're using for copy-on-write'ing your snapshots.

Re: About Database Connection Pool Sizing

#26

Post by anarazel »

derefr wrote: post #21
dlubarov wrote: post #17

Why is that Oracle instance using a max thread pool size of 2048 on a 12-core host? That seems like the real problem. Why not configure a smaller thread pool like cores * 2, and do more queuing on the server? It seems wrong IMO to expect clients to tune connection pools based on how much concurrency they think a server can productively handle. The client doesn't necessarily know how many cores the server has, or more…

The SQL "protocol"† is a hairy one for a backend to deal with, state-wise. Each concurrent connection to an (ACID conformant) SQL-speaking server implies that that connection can have a transaction in progress. A transaction, in SQL, requires the ability for the client (or the backend) to roll it back at any time before it gets committed—and thus, a transaction in progress requires keeping around at least two "world…

> Thus, a backend concurrent connection (i.e. transaction) cap of N, translates to O(N) concurrent MVCC "world states" that the DB engine has to keep track of. No matter how efficient the DB engine is at doing that, that's still a lot of state!

I don't think any realistic implementation keeps information that increases at a rate this implies. Undo based systems have the undo size grow roughly at the amount of changes that aren't visible to everyone, and postgres style in-heap MVCC systems have similar amount of data growth inside the table. Usually the amount of that additional state is more correlated with the age of the oldest observer than with the number of concurrent observers.

> If you allow clients unlimited concurrency, they can execute enough transactions to fill memory and disk with MVCC world-states, causing potential data loss (since the full disk may have prevented the DB engine from writing down a committed world-state.)

I don't think any real-world DB implementation would work that way. The definition of committed in most of such systems include that the necessary data has been written to disk before signalling success for the commit.

This problem also seems largely independent of connection/transaction limits.

Re: About Database Connection Pool Sizing

#27

Post by anarazel »

derefr wrote: post #25
anarazel wrote: post #24

Earlier quoted context omitted.

> Are large numbers of concurrent transactions really an issue in practice though? There may be some per-transaction overhead, but I haven't seen any stern warnings about it, e.g. in the Postgres docs for max_connections. There's definitely scalability implications to higher number of established connections in postgres. Essentially computing a "snapshot" (visibility information for queries etc, needs to be computed…

> There's definitely ways to handle that better than O(N), at least in the average case. O(N) root-level snapshot objects of the state, I mean. Like O(N) git commits. The minimum size of the persisted MVCC snapshot data , probably just scales with O(log N) of the number of connections, depending on what sort of data structure you're using for copy-on-write'ing your snapshots.

Most databases don't have "root level snapshots of the state" in the way you appear to imagine them. Leaving trivial per-transaction information ('did this transaction id commit', ~a few bits per xact) aside, usually it won't scale with the number of snapshots, but with the amount of change done by writing transactions.

Re: About Database Connection Pool Sizing

#30

Post by fnord123 »

macintux wrote: post #20
sb8244 wrote: post #16

Earlier quoted context omitted.

This is so true and easy to overlook until a post mortem. A great exercise is to audit how connections are maintained in your application, before it's too late.

I'm reminded of a systems failure described in, IIRC, the O'Reilly book "Release It!". Java ODBC exception swallowed resulted in eventually running out of connections. Very tough problem to troubleshoot without extensive monitoring.

Release it! is Prag Press. Good book too!
Post reply on HN