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

How to understand the PgBouncer principle, I believe that many inexperienced people do not know what to do about it. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

Some problems in PGBOUNCER need to be understood more deeply, especially why connection pooling is recommended for postgresql, why MYSQL has not heard of wall cracks recommending connection pooling, and how to operate pgbouncer are all topics to talk about this time.

Accept external connections to the postgresql through postmaster when using PostgreSQL to connect. And this way will cause each connection will initially allocate a large amount of memory, and this approach will encounter a large number of high-frequency connections, will occupy a lot of memory, resulting in system performance problems, of course, the general solution can be solved through the buffer pool set in the program, but such a structure is generally controlled and set by the developer, which belongs to the black box and uncontrollable state for operation and maintenance and DBA. That's why we use database connection pooling to reuse connections to save the consumption of system resources that open redundant connections.

Let's look at the general working principle of pgbouncer from the diagram above.

1 if the customer connects to Pgbouncer, you need to verify whether the address, user name, password and other information of the database you are connected to is correct. If not, the connection will be kicked out of the PgBouncer directly. First of all, in terms of security, you can shield some offensive connections to the outside and cut off from the database.

2 after passing the first layer of PgBouncer, the following needs to check the existing connection between the connection pool and the database. The first question to confirm is, is there any spare database connection? If not, a new connection will be tried, but during most stable periods, free connections can be found.

3 after the connection between the client and the Pgbouncer and the database is opened, pgbouncer will determine when the surviving connection is withdrawn to the pgbouncer resource pool according to the following connection methods.

Pgbouncer provides several ways to connect

Session pooling: the default method in pgbouncer is session pooling. The connection pool creates a connection from the application to the end of the SESSION. Pgbouncer will keep your connection in the connection pool. After the end of the SESSION, the connection will be reused to the next application connection.

Transaction pooling: the difference between this method and the above session pooling connection method is that the basis for maintaining a connection is not a session but a transaction, a transaction. After a transaction ends, pgbouncer reuses the connection to the next transaction.

Statement pooling: the third method is not commonly used. The criterion for judging the release and use of a connection is that a SQL statement has been executed.

4 there is a situation where the connection pool of pgbouncer is full and new connections cannot be opened. In this case, a wait login will be created and the connection will enter the waiting list.

5 there is another situation, that is, the connection has already connected and interacted with pgbouncer, but at present, the connection from pgbouncer to the database cannot open a new connection, and there is no active connection to use at present, then the connection that exists in pgbouncer also needs to wait for the idle connection to be used.

The general principle of OK is like this (if you have any questions, you can add Wechat and study together)

Let's go back to the operation and configuration of pgbouncer.

After installing pgbouncer, a virtual database pgbouncer is generated that allows connection pooling situations.

For example: we set up an admin database in the destination database and add a tag to connect to the admin database in the configuration file

And add the user name and password for the connection to the userlist.txt

Execute the reload command in the pgbouncer library to load the new configuration

Connect to pgbouncer and view logs

Enter the command to view the current connection pool status

There are some important parameters that can be adjusted in the ini file of pgbouner, such as

Max_client_conn adjusts the maximum number of acceptable connections

Concurrent connections for default_pool_size default connection pooling

And the hold connection of the buffer pool

At the same time, you can also set the maximum connection and other information for each database or each user.

Therefore, if postgresql uses multiple connections and short connections in an environment similar to MYSQL, pgbouncer will be a protective layer of postgresql to avoid resource consumption caused by frequent opening of connections.

After reading the above, have you mastered the method of how to understand the PgBouncer principle? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.