We are using Jira v7.8.0 with Jira Service Desk 3.11.0 and PostGreSQL 9.6.6. We are in pre-prod at the moment so max 2-3 users at any one time. Periodically when doing something in Jira the error "FATAL: remaining connection slots are reserved for non-replication superuser connections" is thrown.
Anyone able to advise how to troubleshoot this. Max connections in PostGreSQL is set at default value of 100 which should be plenty.
I typically would not expect 2-3 users to consume so many connections at once. But the error you are seeing is an indication that postgres does not have any more connections available.
I would recommend trying to run this command on your SQL database to see if you can better understand what commands/users are using up so many connections
SELECT * FROM pg_stat_activity;
Granted this won't show you Jira users, this will only show SQL users. And in turn Jira is connected to your database by a single login account to SQL. But still this can tell us more about what commands are being run, what the state of the connection is (idle/active), if there are queued queries.
Are there other databases on this SQL server? Or is this just Jira's database?
Are you running any other plugins in this Jira instance, like nfeed, scriptrunner, eazybi, etc? Some of these and more, could be configured to create a large number of database connections either via scripting some commands on a large batch of issues, or for the sake of creating reports with very large datasets.
I would also be interested to see what parameters you have in your $JIRAHOME/dbconfig.xml (we don't need to see your dbuser or dbpassword contained in that file though). This file governs how Jira connects to your database. I would expect it to look closely similar to the sample on Connecting JIRA applications to PostgreSQL.
It might also help to take a look at Monitoring database connection usage. This utility built into Jira can sometimes give you a better graphical understanding of the number of connections at any given time.
Hi Andrew, thank you for getting back to me. The database server is being shared with Confluence, and we did a bit of investigation and found its actually Confluence hogging all the connections, Jira was only running about 7 of them. Even bumping up the total connection from 100 to 150 just caused Confluence to grab a load more.
Is there an equivalent to the $JIRAHOME/dbconfig.xml for Confluence?
In most cases of Confluence that equivalent is in the $CONFHOME/ directory and is called 'confluence.cfg.xml'
I say most because it is possible you could be using a JNDI defined connector with the configuration being in the $CONFINSTALL/conf/server.xml file instead. More details on that setup in Configuring a datasource connection
Hello, Atlassian Community! I thought it would be fun to do something different for my teams' last retrospective of 2019 so I'm planning to do a "year in review" with info-graphics. Wha...
Connect with like-minded Atlassian users at free events near you!Find an event
Connect with like-minded Atlassian users at free events near you!
Unfortunately there are no Community Events near you at the moment.Host an event
You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events