Please note the query with regards to pgbouncer support in DHIS 2.40. The query came through from our Infrastructure engineer. We have been having issues recently with performance from some of instances and they are looking at ways to improve performance. Please advise when the pgbouncer will be available in 2.40.
"Hi there Comfort.
As we discussed via teams, can you reach out to the Dev team for DHIS2 and inquire from them if they have a timeline on using PG Bouncer with DHIS 2.40.
This is the notes about this on the reference guide for 2.39
Database datasource pool type. Supported pool types are: ## * c3p0 (default): For information see https://www.mchange.com/projects/c3p0/# # * hikari: For information see https://github.com/brettwooldridge/HikariCP## * unpooled: Some implementations might want to have more control over the pooling and database cluster architecture # (e.g., using PgBouncer as pool manager behind HAProxy for load balancing). In these cases, the internal pool is un-necessary # and gets in the way.db.pool.type=c3p0
So, from what I understand it is available on 2.39.5 but not on 2.40.
We have done some testing with the 2.39.5 Oslo release with PG Bouncer and have seen quite good gains from this, especially on big databases. From our testing Analytics runs are about 21% faster on the same hardware with PG Bouncer for full term analytics and 34% faster on 3 year runs for a database that is about 1TB in size with Analytics.
Using PG Bouncer also decreases our Web server’s memory footprint for java heap size by between 20 and 50% depending on load, meaning more Heap space for the actual work.
Queries and API calls are also much faster and yield between 25 to 50% better performance.
It eliminates the issue we have with tomcat threads not being released because of slow queries.
We want to continue testing but seeing as we are moving from 2.38 to 2.40, I am quite anxious to know when the unpooled option will become available for DHIS2 2.40.
If you require any other information, please feel free to reach out."
Unfortunately the feature was implemented just after the last patch release of 2.40, so will be part of the next patch release (probably June).
I was expecting that there would be some benefits, but what you are describing seems to be on another level :-). It is hard to believe all of this is due to pgbouncer, but I am sure you have been testing methodically,
Can you give us some more detail about your testing? Three questions in particular:
are you running with a smaller pool size than before? I recall I had some discussion with your sysadmin folk around how most of us run with pools which are too big.
where have you located your pgbouncer? On the tomcat server, on the db server or on a separate VM/container?
Hi Bob, Thanks for the feedback. Jacques from our Infrastructure has done a lot of tests because issues we have been having with some of the big instances that we hosts. He is on leave today, and I’ll ask him to share more details about how executed the tests and also answer the questions you have asked.
The testing has been occurring with test copies of our biggest instances.
To answer your direct questions.
1.) We are running the same size pool as we had without PGBouncer. It is still early days and testing was done with OSLO 2.39.5, but currently as we will using 2.40 we have not conitnued testing.
2.) Our PGBouncer was located on a seperate VM currently as to decrease load from the Tomcat server and to keep testing isolated. I think in final deployment have it on our Database servers so we can have TCP/IP to the PGBouncer from Tomcat but then use unix sockets between PGBouncer to PostgreSQL.
3.) Currently we are only testing with a single tomcat instance to a single PGBouncer to a single PostgreSQL database, as time progresses, we will test various other configurations.
What I can tell you from this initial round of testing is that we have seen a marked improvement on responsiveness and API calls being processed much faster, but again this goes hand in hand with the other testing we have been doing with memory tuning for PostgreSQL and Heap Sizes on Tomcat. I can only confirm with result with pretty hefty Databases.
If there are any other questions, please feel free to reach out.
I think placing the bouncer alongside the database is the right decision. Probably just make it a standard part of your database deployment playbooks or scripts. Making unix domain connections between the bouncer and the db makes good sense. For very large systems with large numbers of users you might need to consider some kernel level tuning to optimize better for large numbers of tcp/ip connections. Much like tweaking a high capacity web server like nginx.
pgbouncer is event-driven, single threaded and reputed to be very light weight. So it should make a good neighbour. While you have it on your separate vm might be a good opportunity to watch its resource consumption and assess.
on that topic, munin seems to have an OK agent for pgbouncer. I cant remember what system you use for monitoring, but its likely you will find something.