EDB Postgres Advanced Server 15 TPS Benchmark

Overview

There is often a need to increase the count of users connected to an application mainly due to business requirements and factors such as business growth and addition of business units etc. The Database Engineers and DBAs are frequently requested to figure out capacity management of database performance for the intended growth. One of the key database performance indicators is transactions per second (TPS) for a given increase in the connection load.

The TPS factor becomes significant in a PostgreSQL database world as every new connection to the database only increases the CPU utilization on the underlying host. The default setting for the maximum number of clients concurrently connecting to a database instance is 100 configured by the parameter max_connections. It is usually adjusted to higher values based on application needs.

However, due to critical factors of business requirements and growing traffic to an application, there is a challenge to increase the number of concurrent connections beyond its general recommendation. It can get as high as 1000 concurrent connections as has been observed in various database environments. The usual method of increasing the connections and adding more compute resources to cope with the load is a reactive measure which may no longer be sufficient for database capacity planning.

A more proactive approach would be to determine how much throughput (as expressed by TPS) more connections may provide. This allows us to visualize the actual performance metrics and decide whether our large-scale business requirements can be met this way. This article gives a detailed account of a set of such benchmark tests executed against an EDB Postgres Advanced Server 15 database instance. It clarifies concerns when considering whether or not to introduce Pgbouncer as a connection pooler into a database solution design. It gives an elaborate account of whether or not Pgbouncer should be co-located on the same host as the database or functioning in a dedicated host. The answers to some of the common and ‘run off the mill’ kind of questions such as below can be found based on evidence.

When is a connection pooler needed and under what circumstance ?

What level of connection overhead will an EPAS15 database experience without a connection pooler ?

What would be the optimal TPS possible to achieve with current settings ?

How can the TPS be maximized ?

When can there be a degradation of database performance with respect to TPS ?

Benchmark tool and connection pooler

Benchmark setup configuration

The compute and resources of the cloud hosts involved in the test are as below:

NOTE

• ●  Every sample of the test was taken using a ‘best of three runs’ approach to eliminate any skew

• ●  Every run involved flushing database and OS cache along with database restarts in order to avoid any residual cached data impacting the outcome

• ●  Pgbouncer was configured to run in session mode

• ●  The number of client connections was gradually increased at every step by 50, starting from 50 to 1000 concurrent connections

•  

Explanation

The first set of tests analyze the performance outcome of a database (based on TPS) when connections are made from a remote host (host: PGB) running pgbouncer to the database (host: EPAS15). The pgbouncer process was shutdown when evaluating direct connections to the database from the PGB host so that the pgbouncer process does not impact results in any shape or form.

The tests are also evaluated to include connection overhead to paint a complete picture of how connection establishment can affect database performance. Although, a complete elimination of connection overhead is not an ideal situation in a live environment, because there will always be connection overhead, hence the benchmark tests comparing with and without connection overhead.

The first graph exhibits a consistent TPS performance for connections via pgbouncer to the database well above 750 TPS while the TPS offered by direct connections to the database is around 200. The average latency experienced when connecting over pgbouncer is negligible in the beginning but it builds up to slightly above 1 sec. when the number of clients reach closer to 1000. Whereas, the latency average experienced with direct connections to the database grows steadily as the number of clients increase until the latency is about 5 secs. for 1000 clients.

The second graph below shows the outcome of the benchmark without connection overhead. The TPS performance for pgbouncer connections is consistent from the start to end, with negligible fluctuations around 1000 TPS.  While the performance without pgbouncer (direct connections) is outstanding for the initial 200 connections but it drops down to consistent levels on par with pgbouncer’s performance. This indicates that if the database connection load is to grow beyond 300 connections, then having a connection pooler like pgbouncer can be beneficial.

The average latency of both pgbouncer bound connections and direct connections grows steadily, closer to 1 sec around the 1000 concurrent connections.

The two graphs above show the ability of a database to complete requests as and when a request comes in, as quickly as possible. It shows the outcome of a database performance when there is a sudden spike in requests. The subsequent two graphs emphasize the database performance when the requests build up and continue to consume the database over a period of 10 minutes.

These tests were carried out with and without connection overhead as well, for comparative study. Note that PgBouncer shows much higher throughput even at incredibly high client connection counts, though we do see some variance at the end due to increased resource demands.

The next graph shows the result of the same test without connection overhead, which proves that TPS performance with connections via pgbouncer is consistent, although falling short compared to direct database connections. The reason for the TPS perfornance of pgbouncer remaining constantly between 825 and 850 is because each of the tests is forced to run for a duration of 10 mins (600 secs) with the defualt_pool_size set to 20 (pgbouncer default). The finding between this and the previous graph indicates that pgbouncer is clearly beneficial because connecting via pgbouncer is much faster than connecting directly to Postgres.

The results so far have pointed out the fact that it is beneficial to have Pgbouncer as a connection pooler between a database and application. The tests performed thus far had a dedicated host for Pgbouncer, but can co-locating Pgbouncer with the database on the same host be a good idea ? The answer follows...

The next two graphs show the results of testing TPS outcome for connections through Pgbouncer co-located on the same host as the database against a dedicated host for Pgbouncer. The throughput of a co-located Pgbouncer in the database host is poor compared to the performance of a Pgbouncer on a dedicated host. This indicates it is beneficial to separate Pgbouncer from the database. The graph below shows performance of transactions completed as quick as they can (-t 10) 

The final graph shows the performance of transactions executed over a period of 10 mins. (-T 600) which shows Pgbouncer on a dedicated host can be advantageous.

Final Opinion

If the database is anticipated to receive a large number of connection requests, then it is advised to introduce pgbouncer in front of the database in order to get better TPS performance.

And, excellent results are accomplished when pgbouncer is NOT co-located on the database host and running on a dedicated server.

Much of the pgbouncer settings were set to their defaults during these tests and perhaps better throughput could be achieved with different pool modes, bigger pool sizes and other non-default settings of pgbouncer.

It is hoped that an elaborative account of utilizing pgbouncer by and large to achieve connection throughput has been presented with the help of this blog but if there are any extreme cases that require assistance then EDB Professional Services team is always happy to help.

Acknowledgement

Srikanksha Kopparthy: for conducting all the benchmark tests

Performance Engineering and Architecture Team (PEAT) headed by Vibhor Kumar: for the initial guidance such as how to conduct benchmark tests, how to get the best sample, eliminating and minimizing the factors that can impact results etc.