Jira (PDB-4871) Investigate queries using two Hikari connections per request

[Zachary Kent (Jira)]

Title: Message Title

Zachary Kent created an issue   PuppetDB / PDB-4871 Investigate queries using two Hikari connections per request Issue Type: Bug Assignee: Unassigned Created: 2020/08/25 2:22 PM Priority: Normal Reporter: Zachary Kent It looks like PDB queries are grabbing two connections from the Hikari PDBReadPool for each request. You can see this behavior by fetching this branch. The commit on the tip of that branch uses the Hikari AcviveConnections metric to get the number of active connections being used from the pool at any given time (refreshed every second). Once you have the branch, start PDB up locally with `lein run services -c ...` and then run any query against the `/pdb/query/v4` endpoint. This should result in the following output: "inside the outer with-transacted-connection form:" "pg_backend_pid: [{:pg_backend_pid 91592}]" "number of active connections:" {:request {:mbean "puppetlabs.puppetdb.database:name=PDBReadPool.pool.ActiveConnections", :type "read"}, :value {:Value 1}, :timestamp 1598388833, :status 200}   "inside the inner with-transacted-connection form:" "pg_backend_pid: [{:pg_backend_pid 91593}]" "number of active connections:" {:request {:mbean "puppetlabs.puppetdb.database:name=PDBReadPool.pool.ActiveConnections", :type "read"}, :value {:Value 2}, :timestamp 1598388834, :status 200}   "Inside top level query handler which calls produce-streaming-body" "Number of connections after exiting both with-transacted-connection forms:" {:request {:mbean "puppetlabs.puppetdb.database:name=PDBReadPool.pool.ActiveConnections", :type "read"}, :value {:Value 0}, :timestamp 1598388835, :status 200} You can see above that the nested with-transacted-connection marcos appear to be grabbing two connections from the Hikari read pool for each query. I believe this is because we end up calling with-db-connection twice as a result of this nesting. Also note how the pg_backend_pid is changing half way through the query, we'll want to have a way to unify these to help provide accurate correlation ids between any logging PDB does and the PG side. I tried removing the inner call to with-transacted-connection and tests in the following namespaces failed: puppetlabs.puppetdb.http.resources-test puppetlabs.puppetdb.http.facts-test puppetlabs.puppetdb.command-test puppetlabs.puppetdb.admin-test puppetlabs.puppetdb.admin-clean-test puppetlabs.puppetdb.acceptance.node-ttl puppetlabs.puppetdb.scf.sql-test We should investigate if we intended to hold two Hikari connections open per query or if all of the work we're doing can be accomplished by one. I poked around a bit and it seems like we've been doing this for at least 5+ years. Add Comment   This message was sent by Atlassian Jira (v8.5.2#805002-sha1:a66f935) -- You received this message because you are subscribed to the Google Groups "Puppet Bugs" group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/puppet-bugs/JIRA.370205.1598390523000.23816.1598390580039%40Atlassian.JIRA.