What is database pooling?

Question

I just wanted to know the concept of database connection pooling and how it is achieved.

Mantilla

Answer 1

Database connection pooling is a method used to keep database connections open so they can be reused by others.

Typically, opening a database connection is an expensive operation, especially if the database is remote. You have to open up network sessions, authenticate, have authorisation checked, and so on. Pooling keeps the connections active so that, when a connection is later requested, one of the active ones is used in preference to having to create another one.

Refer to the following diagram for the next few paragraphs:

  +---------+
  |         |
  | Clients |
+---------+ |
|         |-+  (1) +------+ (3) +----------+
| Clients | ==#==> | Open | ==> | RealOpen |
|         |   |    +------+     +----------+
+---------+   |       ^
              |       |(2)
              |    /------\     +---------+ (6) +-----------+
              |    | Pool | --> | Cleaner | ==> | RealClose |
              |    \------/     +---------+     +-----------+
          (4) |       ^
              |       |(5)
              |    +-------+
              #==> | Close |
                   +-------+

In it's simplest form, it's just an API call (1) to an Open API call which is similar to the "real" one, RealOpen. This first checks the pool for a suitable connection (2) and, if one is available, that's given to the client. Otherwise a new one is created (3) and given to the client.

A "suitable connection" is just one that already has access to the database using the correct information (such as database instance, credentials, and possibly other things).

Similarly, there's a Close API call (4) which doesn't actually call the real RealClose, rather it puts the connection back into the pool (5) for later use. At some point, connections in the pool may be actually closed (6). This could be done by a thread that continuously monitors the pool and calls RealClose if they are old enough or certain other conditions are met.

That's a pretty simplistic explanation. Real implementations may be arbitrarily more complex such as the previously mentioned:

• handling connections to multiple servers and using multiple user accounts;
• using arbitrary rules to decide when connections should be really shut down, things like its age, how many similar connections there are, and so on.

---

Database connection pooling is a specific case for a more general one, that of maintaining cached things where they may be expensive to start. As you see from a similar answer of mine, it can apply to thread pools as well (or pools of backup tapes, communication devices, laser cutters, or dozens of other different things).

In all cases, it's best if it's "expensive" to bring another resource online rather than use one that had previously been online.

That linked answer also shows one possible algorithm for deciding when to start up or (fully) shut down a resource.

Depurate

Where do you have the nice little graph from? – Hammond

I made it from scratch (silly me). If you want to see some decent graphics, have a look at zengr's answer. – Depurate

If one is available, that's given to the client, otherwise a new one is created.Similarly, there's a close API call which doesn't actually call the real close-connection, rather it puts the connection into the pool for later use. As your mention this, I have little question. When 1000 client request connection and not closed. So 1000 connection is alive in pool.This will good for pool performance?? And let me know my understanding is little wrong?? – Alrick

@YeWin, no that sounds good. Re your question about 1000 connections being left in the pool, that can happen but generally only if you end up at some point with 1000 concurrent active connections. Otherwise, there'd be re-use and it wouldn't reach 1000. In terms of that happening, see my penultimate paragraph, particularly the "may actually close old connections when the usage pattern quietens down" bit. – Depurate

Ohh!Thank you for your reply and I understand well what you really point out. So we assume 1000 concurrent active connections is rare case?? – Alrick

good explanation! Btw, how long did it take to make the draw ? – Payer

@DiegoMariani, slower than if I'd done it by hand, faster than if I'd try to coerce MS Word into making it easier :-) – Depurate

What does the graph mean? It is obscure. Could you at least put numbers there to represent order of connection steps – Subalternate

> opening a database connection is an expensive operation... I don't understand it. Do you mean that keeping 10, 20, 30, you name it number of connections opened is less costly for memory and overall system performance than opening one connection when required? How it can be? 30 vs. 1 is less costly? – Subalternate

@Green, the 'costly' comment has more to do with time, especially when the DB is remote. All that session establishment, authentication, authorisation, and so on, is not free. If that can be bypassed by using an existing connection, it'll save some time. In terms of understanding, please read paras 3 and 4, they should hopefully make the steps clear re what happens on open and close. – Depurate

@Green: However, I'll try to clarify it a little re one possible set of steps. – Depurate

This may be obvious to some but I was confused how the client could take a connection from the pool without having to go through authentication. It should be noted in this case that only connections with the same configuration can be pooled (i.e. connections can be separated into pools by connection string). – Shellyshelman

@Depurate While reading answer just amazed by how well it is explained but when finished & see you written that answer then I don't wonder. – Claxton

Answer 2

Images speak a thousand words (paxdiablo gave an awesome description):

Source

Dilapidate

And apparently, good images speak a few hundred pieces of ASCII art as well :-) – Depurate

@sagar please select the answer which you found to be most useful. You have no accept record. – Dilapidate

I seen 4 connection in Pool.So connection number is restrict in this pool by pooling type??Or what will happen when connection is not free in Pool? Client need to wait connection free?? – Alrick

@zengr. Please reply for Ye Win question. – Chaim

@DEADEND It really depends on how the connection pool is implemented. Most of the pools create a new connection when connections reach the max capacity. This can keep on growing till the db reaches a threshold. In some case (like oracle jdbc) you can specify "initial size" and "max size" during pool construction itself. – Dilapidate

Unfortunately, the image doesn't say the most important thing. That is: Why keeping 10, 20, 30, ... number of connections opened is less costly for memory and overall system performance than opening one connection when required? How it can be? 30 vs. 1 is less costly? How? – Subalternate

Answer 3

As the name suggests. If a few people wants to swim, they can swim in the same swimming-pool, does it really make sense to construct a new swimming-pool each time someone adds in ? Time and cost is a priority.

Fannie

Is there any limitation on the number of people in the swimming pool? Like the number of connections connected to the database? When is the time for database to start a new connection? – Racy

Pool lanes might be a better fit. Start the day with only one lane open but then, when the second swimmer arrives, open another one. When one of the swimmers exits the pool, don't immediately close their lane, since another swimmer may soon show up. Otherwise the staff will spend the majority of their time opening and closing lanes :-) – Depurate

Answer 4

Database connection pooling is simply caching connections to databases so that they can be reused next time to reduce the cost of establishing a new connection each time we want to connect to a database.

Erection

Answer 5

Connection Pooling concept not only in Java but across many programming languages. Creating a new connection object is costly so a fixed number of connections are made and maintained in lifecycle creating a virtual pool Java Just ( http://javajust.com/javaques.html ) see question 14 on this page

Feld