![]() The config file shows the following options: This is important to understand because it offers a lot of potential to adjust the tool’s behavior to our exact needs. Pgbouncer has three types of pooling modes. It will keep connections to the database open and make sure that the number of fork() calls needed is significantly reduced. Pgbouncer will sit between the database and the client. Sure, there is a bit more latency – but overall it helps to greatly reduce the need to open and close connections. In other words, it is really efficient, and it can hold thousands of connections for very little overhead. The key here is that a pgbouncer connection has only 2 Kb or so in overhead. Pgbouncer can enter the picture to address the problem. pgbouncer: the best tool for connection pooling Therefore pooling makes a lot of sense, because it allows us to recycle the connection. “ SELECT 1” is way cheaper than fork() plus all the other overhead. Wow, we reduced the speed to 3770 transactions per second which is a 98.7% drop in performance. Tps = 3770.311424 (including reconnection times) Number of transactions actually processed: 37713 HansJurgensMini:~ hs$ pgbench -c 10 -T 10 -j 10 -f /tmp/sample.sql -C test This time, each transaction will open a separate connection ( -C): ![]() This gave us 295825 transactions per second. Tps = 295825.128043 (without initial connection time) Number of failed transactions: 0 (0.000%) Number of transactions actually processed: 2957846 HansJurgensMini:~ hs$ pgbench -c 10 -T 10 -j 10 -f /tmp/sample.sql test Let’s run a simple test: 10 seconds, 10 concurrent transactions given our SQL script. To maximize the result, I’ve used the most basic script possible. HansJurgensMini:~ hs$ cat /tmp/sample.sql Those costs are often underestimated by developers and DBAs alike. However, forking a process for just a very short query can be really expensive. ![]() In case a connection lives for a very long time, this is no problem. In PostgreSQL, we have to fork an entire process to create a connection. Reducing connection overhead with connection poolingĪs stated already, connections and their creation are not free. Creating new connections is not free of charge. It has proven to be reliable as well as useful.īefore we dive into different pooling modes and their implications, why do we need a connection pooler in the first place? The reason is that we want to reduce the overhead of new connections. At CYBERTEC, we’ve successfully deployed it in many different situations. Pgbouncer is one of the most widely used tool for connection pooling. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |