0

I am using PostgreSQL 9.5 and we have max_connections set to 200 in our postgresql.conf file.

We have 2 apps server running java apps which are load balanced. When we run our tests via JMETER, I run pg_stat_activity and I can see there is a lot of IDLE connections with most of the queries called COMMIT.

Questions

  1. Do I need to be concerned with the IDLE connections and will this affect any new tests I run?
  2. Could this be an issue with my Java apps not auto_committing?

My assumption is that the connections will be re-used, but I am concerned if we run more tests that new connections will spin up and the max_connections of 200 will be hit.

Improve this question

1 Answer 1

Reset to default
1

When the state is idle, then the query shown in pg_stat_acitivy is the last query that was run before it went idle. Since that was apparently COMMIT (and since the state is idle, not 'idle in transaction'), then no, you don't need to worry about autocommit not working.

My assumption is that the connections will be re-used but concerned if we run more tests new connections will spin up and the max_connection of 200 users will be hit.

If your java connection poolers are set up correctly, they should be reused. If you run out of max_connections, you will hear about it in the log files. Why preemptively worry about it? Are you running your tests against your production database?

Improve this answer
2
  • Thanks for getting back. I set a value of 200 in the max_connection but if the Java app poolers are set then in theory it shouldnt use as many, if the amount of max_connections was reduced I could then increase the work_mem. The tests are not against a production environment. The worry I had was even when there is no tests running there is still 100+ amount of connections in idle states. Is this expected behaviour?
    – rdbmsNoob
    Feb 26, 2021 at 11:34
  • The point of a pooler is to not close the connection immediately, but wait for one to be needed again. There is sometimes code to limit how many idle ones you have before it starts actually closing them, but different poolers do different things and can be configured differently. We don't have enough information to know.
    – jjanes
    Feb 26, 2021 at 16:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.