Shulou(Shulou.com)06/02 Report--

This article shows you how to use Pgbouncer in PostgreSQL. The content is concise and easy to understand. It will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

Pgbouncer installation is relatively simple, download and compile directly

$. / configure-prefix=/usr/local/pgbouncer

$make

$make install

Installation is very simple, the question is how to use pgbouncer to meet the relevant requirements and what pgbouncer can provide to us. First of all, PG is defined as a lightweight database connection pool product, and there are three connection methods in PG, which is the main point to pay attention to.

The figure above shows a process in which the customer connects to the statement execution, where questions is the problem point, and many of the session have the status of idle for a long time, which leads to that if a connection is needed, a new process needs to be established to access the database, so the number of connections will go up. The main reason for using pgbouncer is to reuse the access of multiple connnections to the database, that is, to reduce the idle state of the session. 2 if the connection is insufficient, the connection that cannot be allocated temporarily will be put into the waiting state in the pgbouncer, and the idle process of the idle will be arranged.

If you want to use vernacular, the connection method that does not use pgbouncer is a private car, if there are too many cars, the road will be full, while the way using pgbouncer is similar to a bus or minibus, some people get on the bus and some people get off, and the seats are fixed, so if the bus itself has 30 seats, but in fact, it not only supports 30 people during the whole journey, but as to how many people it supports. That depends on the speed of the transaction connected to the database and whether a connection can be reused. This is a bit of the concept of time-sharing in CPU.

The following is based on different ways of dealing with pgbouncer, and different angles of handling connections

1 session pooling here is for session. When the user's connection task is completed, pgbouncer will reuse the connection. This setting itself is basically consistent with the meaning of the connection pool of the program.

2 Transaction pooling here the unit in the concept of connection becomes transaction, that is, the time-sharing use of a connection channel, this benefit is much more obvious than the session pooling comparison above, and the connection utilization will follow the relevant allocation to have higher reuse and performance improvement.

3 Statement pooling is divided according to the way of statements. Although the performance may be the best, most scenarios are not suitable for the way PG runs transactions.

Session pooling

Most polite method. When client connects, a server connection will be assigned to it for the whole duration the client stays connected. When the client disconnects, the server connection will be put back into the pool. This is the default method.

Transaction pooling

A server connection is assigned to client only during a transaction. When PgBouncer notices that transaction is over, the server connection will be put back into the pool.

Statement pooling

Most aggressive method. The server connection will be put back into pool immediately after a query completes. Multi-statement transactions are disallowed in this mode as they would break.

So today our overall test is also to see the performance gap in the first two modes, not the latter.

Then we will conduct relevant tests around the above options.

The system is configured as follows

Postgresql itself has a max connection of 10000 (10, 000, of course, which is too much for any database). Use the program to simulate 3000 concurrent connections. At present, after the program connects to PG, the overall database state is stable at 2017, but the overall machine has become relatively slow to respond.

Well, we are doing the test in a different way of transaction pooling, and we will be related to

In the same case, the default pools of pgbouncer itself is 200. the number of connections of PG is always 2010.And the concurrent query of 3000 connections does not report an error.

And the difference between not using pgbouncer and not using it just now does not slow down the response of the system because of the large number of connections used in the intuitive use of system resources.

Then let's continue to use session pooling as the processing method provided by pgbouncer.

In fact, the result is basically the same as above, but it is different on the program side.

This is transaction.

This is session.

Basically, using the result of transaction in an instant, basically all connections have been completed to access and query the database, while session can only accept 213 connections. The relevant principles have been mentioned above. Here is to use this demonstration to prove that when we use pgbouncer, we should choose transaction rather than session. Otherwise, basically most of the links (related to the business and related design) Session doesn't help you make any big changes, most connections are waiting.

The above is how to use Pgbouncer in PostgreSQL. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.