CODEWITHSUNDEEP
postgresqlhibernatejboss7.x
Andriy Kharchuk
Andriy Kharchuk

Reputation: 1304

PostgreSQL DB performance issues with thousands of connections and distributed transactions

We are trying to evaluate PostgreSQL DB as an alternative to Oracle database in our application. We use PostgreSQL 9.5 which is installed on a Linux machine with 128 GBs of memory, 32 CPU cores and SSD storage. Connection pools and distributed transactions are managed by JBoss 7 application server, SQL queries are generated/executed by Hibernate 4. Most tables have tens millions rows, one of them has hundreds millions rows. In total around 3,000 database connections (they are pooled by the application server) are active and used concurrently. We modified some queries, created indexes for slow ones, tuned DB and OS settings based on documentation, etc. However, throughput is few times slower and eventually DB response time increases 10-20 times.

I've done some googling and I couldn't find information about anyone else (ab)using PostgreSQL DB the same way:

Oracle doesn't have any problem handling even higher load. I would appreciate sharing your experience, suggestions, links, etc.

Thanks

Upvotes: 8

Views: 1989

Answers (2)

Andriy Kharchuk
Andriy Kharchuk

Reputation: 1304

The solution was upgrading the Linux kernel and decreasing the number of DB connections in our Java connection pools from 3000 to 300. After this change we could handle the same traffic as we could with Oracle DB.

By accident I have discovered a precious piece of information which lead to problem resolution in comments section for the post Did I Say 32 Cores? How about 64? written by Robert Haas (VP, Chief Architect, Database Server @ EnterpriseDB, PostgreSQL Major Contributor and Committer):

No, I'm saying that to get good performance on a 64-core server, you're going to need PostgreSQL >= 9.2 and Linux >= 3.2. Most of the changes are actually on the PostgreSQL side, but the lseek scaling stuff in the Linux kernel was important, too.

Upvotes: 3

Sri
Sri

Reputation: 291

Approprite settings should be provided in postgresql.conf file to handle large number of connections. Also it can be front-ended by pgpool2 for replication and load balancing. We are using Postgres in a clustered env and it works well.

Upvotes: -1

Related Questions