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

Today, I will talk to you about how to analyze the problems of Pgbouncer in PostgreSQL. Many people may not know much about it. In order to make you understand better, the editor has summarized the following contents for you. I hope you can get something according to this article.

The more you learn, the less you will learn, and the more you learn, the more you will discover "black holes" and your own deficiencies, so you may have been learning in a state of guilt. He may have changed what he has understood for many years, or he may not have understood it thoroughly in the first place without knowing it.

Pgbouncer seems to be a lightweight connection buffer. Let's sort it out today and take a look at the black hole of knowledge.

Question 1

Whether my pgbouncer should be installed with PostgreSQL, separately or DOCKER like other database middleware.

Personally, pgbouncer is a lightweight buffer pool program, and there is nothing wrong with deploying it with postgresql itself. it is relatively simple in function, and there is no particularly complex function, such as read-write separation, or statement change, or statement audit, and other complex functions. Pgbouncer is to improve the reuse of postgresql process connections and improve the performance of postgresql in dealing with high concurrent connections. Should be a PG to install a set of pgbouncer, here compared to proxysql functions and features are completely inconsistent, although they are database middleware buffers.

Question 2 I modified the configuration of pgbouncer. Will I break the connection during startup? How much impact it will have.

There is a test here, which is divided into two parts.

1 your pool mode is session, in our concurrent 3000 connection, restart, the overall connection fails, the program reports an error

2 whether your pool mode is transaction or concurrent 3000 connection, restart, in restart, set the suspend state is, the normal working connection is invalid, after the subsequent restart, the connection will be restored immediately.

In terms of impact, it is also divided into two aspects. If you are a session, your overall session will be cut off. If your business is busy, it is not recommended that you casually restart activities.

As for transaction, because of the nature of the connection, if you have to restart a small number of businesses (during the trough), you can still consider restarting.

However, it is inevitable to report an error in the connection, so for some newly built libraries and new application scenarios, please also ask the relevant personnel to consider that in the non-business, or trough period, negotiate with the business and carry out the relevant operations.

Question 3 postgresql is highly available, but is pgbouncer highly available, and what if it doesn't?

By default, there should be only one server in the PG cluster that provides write services. If you install pgbouncer with pg, this problem is meaningless, because pgbouncer does not provide the function of judging master and slave, and it does not provide the function of failover, so if you access it through VIP, it has nothing to do with the high availability of pgbouncer.

Question 4 in the process of using pgbouncer, if there is a network problem, or if the database itself cannot connect to pgboucner, what will happen after the network or database services are restored?

For example, I am looking for a large table, a network flash, or a server restart.

For example, we query 200 rows of records in a table. Figure 1 shows a screenshot without pgbouncer query, and figure 2 shows a screenshot after using pgbouncer.

From the figure above, we can see what pgbouncer has done when the server is unresponsive. It is actually fine from the point of view of the fault handling in the connection between the application and the database, but if Pgbouncer is installed on one side of the database, it sometimes hides some problems about the server, such as restarting or other aspects, and may reduce the number of errors on the program side. Then the analyst and responder of the problem are transformed into the database side.

So the question can go a little deeper, if there is no query at this time, will it still be reflected in the pgboucner log? The result is no.

Question 5 in the case of using pgbouncer, if transaction is set to use, but the configuration parameters with session level are set in the connection, it will not work, and even if session is used, different results will occur because of the following configuration. Please inquire about the explanation of the relevant parameters. In addition, the transaction mode is used, but the application has a strong correlation with the session mode for access, and there will also be problems. Please consider the relevant architecture designers and programmers when designing the program.

After reading the above, do you have any further understanding of how to analyze the problems of Pgbouncer in PostgreSQL? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.