0

Just like the title says. I am monitoring the pg_stat_activity table in my Postgres DB and trying to capture the behavior of my Tomcat JDBC connection pooling implementation. The idle connections that are opened on startup being returned to the pool is working as intended. I have my min-idle configuration set to 1, so I am trying to see if I can get more connections to open up by "overloading" the single idle connection that is still open.

In my testing, I turned off any caching so all requests will hit the DB. Not only can I not get another connection to open up by sending over 200 requests a second, but the single connection serving all of these just shows as idle state with "COMMIT" as the last query for the whole duration of requesting. I'm refreshing my query to pg_stat_activity once a second.

I'm willing to accept that maybe I just need more load to open a new connection, but why is this single connection never active even while using it? I see the state_change column update but I can't see the state itself actually update.

What I'm expecting is for the connection to show as active until it serves the last request, from which it will show idle after. Is this process just happening too fast to capture it given the fastest I can query pg_stat_activity is once a second?

Any help is appreciated. Thanks.

Iceweasel
  • 1
  • Does the value in the `state_change` column change between the queries? – Laurenz Albe May 25 '23 at 21:57
  • @LaurenzAlbe Yes. That's why I'm inclined to think that these transactions are simply happening too fast for me to actually see the state as "active" – Iceweasel May 25 '23 at 22:03
  • Yes, that's exactly what it looks like. So nothing to worry about, right? – Laurenz Albe May 25 '23 at 22:05
  • If you really want to know, run `SELECT pg_sleep(2)` which will guarantee a 2 second query – Richard Huxton May 25 '23 at 22:16
  • @LaurenzAlbe I suppose. I just thought that sending that many requests in a second, I'd be able to see the connection as active at least once. But I have yet to see it. – Iceweasel May 25 '23 at 22:17
  • [`minIdle=initiallSize` (10) by default](https://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html). Double check [number of connection on client side](https://stackoverflow.com/a/51161663/2834978). Also, [`PGSTAT_MIN_INTERVAL=1000 milliseconds`](https://www.postgresql.org/docs/current/monitoring-stats.html) so may be too slow to monitor tomcat pool. – LMC May 25 '23 at 23:23
  • It sounds like the bottleneck is within Tomcat, or the network (but in the case of the network, you would think it try to open multiple connection). As Richard says, make your query intentionally slow if you want to force it to be seen in the active state. That way you will prove to yourself that you can recognize an active query when you see one. – jjanes May 26 '23 at 14:33

0 Answers0