History | Log In     View a printable version of the current page.  
Issue Details (XML | Word | Printable)

Key: QB-3132
Type: Bug Bug
Status: Closed Closed
Resolution: Won't Fix
Priority: Critical Critical
Assignee: Robin Shen
Reporter: AlSt
Votes: 0
Watchers: 0

If you were logged in you would be able to see more operations.

DB Backups seems to open more DB connections than it should

Created: 19/Feb/18 09:58 AM   Updated: 19/Jan/19 01:19 PM
Component/s: None
Affects Version/s: 7.0.31
Fix Version/s: None

Original Estimate: Unknown Remaining Estimate: Unknown Time Spent: Unknown
File Attachments: 1. Text File connections.txt (156 kb)

 Description  « Hide
We're running QB 7.0.31 with PostgreSQL database. The login role had a limit of 50 connections. The QB db pool has a max size of 50.

We've seen a lot of log lines on DB side like:

quickbuild@quickbuild FATAL: too many connections for role "quickbuild"

because of this also builds failed. I increased the login role limit to 70 and it still fails. And always at around the same time: When the QB backup is running.

Now I'm wondering if QB tries to open more than the configured connections to the database during the backup process?

 All   Comments   Work Log   Change History      Sort Order:
AlSt [19/Feb/18 12:19 PM]
Or might this maybe also be a bug in c3p0. I've seen that the version used in QB is and is the newest version.

Robin Shen [19/Feb/18 11:38 PM]
QB only uses one connection to do database backup. You might put the new c3p0 jar into QB's lib (plugins/com.pmease.quickbuild/libs) directory to see if the situation is better.

AlSt [28/Feb/18 09:41 AM]
Ok it seems this has nothing to do with the backup itself. That really uses just one connection, but I've some logging now to get an insight why a pool with max size of 50 opens 100 connections. It also tries to open more than the 100 connections and then the db limit kicks in which I already increased to 100.

Please see the attached connection.txt file with the connections log. it is basically some data fetched from pg_stat_activity.

AlSt [28/Feb/18 09:58 AM]
Stay tuned... I might have found the problem :(

AlSt [26/Mar/18 10:34 AM]
Ok ... the problem was that we have a socket timeout set in the DB connection string for postgreSQL.
The measurement data truncate takes pretty long, so "delete all" is triggered which locks the table. Subsequent calls to that table are running into timeout and QB connections (although still open) get marked as broken (by c3p0) and it tries to open more and more connections.

Once the timeout got raised to a value so that the truncate call can finish without throwing a timeout exception the connection pool stays low. about 10 connections open.

Also I reconfigured c3p0 to close idle connections to only create connection when needed. Usually there are about 15 connections open and 10 are in active use. Each open connection (pool size default is 25) is costly on the DB server (memory consumption, open file handle, etc)

Robin Shen [26/Mar/18 11:24 PM]
Thanks for the detailed analysis. Definitely helps in case some other one encounters the issue.