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

This article introduces the relevant knowledge of "the comparative use of PostgreSQL and Pgbouncer". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

It is well known that database connection pooling is not needed, so it must be necessary to give an answer to database connection pooling.

While giving the answer, if someone asks you why you want connection pooling, what is the role of connection pooling? which connection pool is good for Postgresql? If you can't answer these questions for a while, follow me.

First of all, let's assume, what if our database doesn't have a connection pool?

Database without any connection pool, the application must directly access the database to establish a connection. Of course we might think this is a quick way to connect to a database, but is that the case? You are opening a direct connection to the database, indicating that your database does not open a connection for you, and in this case, the consumption of an open connection, the consumption of closing a connection, or you use encrypted connections. There are other related consumption, and this consumption, the more connections you open, the more consumption you consume. Especially for some short connections on the Internet, there may be many concurrent connections at the same time, but the time to occupy this connection is very short. This will cause another problem. If the number of connections you set does not match the number of connections that pop up, it will eventually lead to the problem of rejecting connections.

So this puts forward a problem for the connection of the database. Reuse and the reuse of connections are very important to the database, which can reduce some fast connections. fast disconnected database access consumes database performance and causes some unnecessary trouble.

Speaking of which, if you are developer, you will soon lose interest in what I have below, because you will say, I have connection pooling, every program framework has connection pooling, and I don't need your connection pooling.

Really? Yours are called Framework pooling. I'm talking about Persistent connections and standlone connection pooling today.

Why do I ask this question, because a database cannot accept connections from only one program, and a program must have its own connection pool, but a database can serve more than just one application for you. So when the database accepts multiple connections from the program, where is the protection of the database itself?

When PG accepts a connection, it allocates some memory to cope with the memory consumption of some data brought by the connection. In fact, the same is true for other databases. The concept of PGA SGA also exists in PG.

So too many connections will inevitably consume PGA, resulting in memory leakage, leading to some major database performance and security problems.

Connect to the database through the connection pool of the software's architecture (figure 1)

Use software connection pool to connect to the database (figure 2)

At present, the main connection pools of PostgreSQL are PGPOOL-II and PgBouncer.

Two kinds, PgBouncer. Compared with PGPOOL, PgBouncer is much less famous, in fact, its functions are simplified compared with PGPOOL, so why is it recommended that PgBouncer rather than PGPOOL is recommended? the main reason is that PgBouncer is not worse than PGPOOL-II in connection pooling, and it is easier to install and configure management. with Repmgr, you can carry out PostgreSQL super high availability mode, which is a highly available solution for PostgreSQL.

Installation is very simple.

$. / configure-prefix=/usr/local-with-libevent=libevent-prefix

$make

$make install

Nonsense, let's start the configuration and use it first.

What time shall we configure? 1 2 3

1 set up an account to connect to the current database

2 configuration Profil

3 configure account files

PgBouncer has a profile pgbouncer.ini and a user account file userlist.txt

There are two ini files for configuration, one is database, the other is pgbouncer database, which is to configure an authentication to connect to the actual database, and the other is to configure pgbouncer snooping information and some basic configuration.

[databases]

Test_database = host=192.168.198.123 port=5432 dbname=postgre user=admin password='password'

[pgbouncer]

Listen_port = 1818

Listen_addr = 192.168.198.123

Auth_type = md5

Auth_file = / usr/local/pgbouncer/share/doc/pgbouncer/userlist.txt

Logfile = / usr/local/pgbouncer/pgbouncer.log

Pidfile = / usr/local/pgbouncer/pgbouncer.pid

Admin_users = admin

Pool_mode = Transaction

Then we fill in the account and password used to connect to the server in the userlist.txt file

Ok We can connect to PG through pgbouncer

Start pgbouncer above

And then we try to log in to the database.

Psql-h 192.168.198.123-p 1818-U admin test_database

After logging in, we can look at pgbouncer's own database, where there are some commands that are unique to pgbouncer.

Through some commands, we can know some configuration of the server.

And the ultimate question is, what are the benefits of using PGBOUNCER?

1 pg now has 5 connections to the physical server through PG

2 the physical server now throws out the system connection, as well as the local connection, and there are only 4 real external connections

This shows that there is a difference between the connection accepted by the database and the client that actually connects to the database, and the amount of data of the client actually connected is greater than that of the database, indicating that Pgbouncer plays the role of connection pool.

This is the end of the content of "the Comparative use of PostgreSQL and Pgbouncer". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.