1

For a Jira system, if use Jira's default connection configuration as below:

<?xml version="1.0" encoding="UTF-8"?>

<jira-database-config>
  <name>defaultDS</name>
  <delegator-name>default</delegator-name>
  <database-type>postgres72</database-type>
  <schema-name>public</schema-name>
  <jdbc-datasource>
    <url>jdbc:postgresql://[IP]:5432/jiradb</url>
    <driver-class>org.postgresql.Driver</driver-class>
    <username>jiradbuser</username>
    <password>{PASSWORD}</password>
    <pool-min-size>20</pool-min-size>
    <pool-max-size>20</pool-max-size>
    <pool-max-wait>30000</pool-max-wait>
    <validation-query>select 1</validation-query>
    <min-evictable-idle-time-millis>60000</min-evictable-idle-time-millis>
    <time-between-eviction-runs-millis>300000</time-between-eviction-runs-millis>
    <pool-max-idle>20</pool-max-idle>
    <pool-remove-abandoned>true</pool-remove-abandoned>
    <pool-remove-abandoned-timeout>300</pool-remove-abandoned-timeout>
    <pool-test-on-borrow>false</pool-test-on-borrow>
    <pool-test-while-idle>true</pool-test-while-idle>
  </jdbc-datasource>
</jira-database-config>

In postgresql's config file:

postgresql.conf

max_connections = 300
shared_buffers = 80MB

In /etc/sysctl.conf file:

kernel.shmmax=100663296

At test stage, it can work. But in the production environment, for 1000 users use this system, if one app server serve 500 users, how many pool connection size is good to set in the postgresql.conf file? And, if use pgbouncer, how many connections it can be reduced?

0
2

The answer depends on how many cores you have on your database system, how many concurrent I/O requests it can handle and what the ratio of active versus idle time is while the application is using a connection.

I would start with a maximum of 20 sessions.

4
  • 2 cores. I don't how to find out how many concurrent I/O requests. Also, how to get the ratio of active versus idle time?
    – rawmain
    Feb 18 '20 at 4:13
  • To find out how much I/O your system can take, you have to know the storage system. The idle time ratio is hard to figure out. Your best bet is to play with the session maximum and see when you can exhaust the database's resources. Set the limit somewhere below that. Feb 18 '20 at 4:34
  • Using SSD now. This site is good to calculate configuration items: pgtune.leopard.in.ua/#
    – rawmain
    Feb 18 '20 at 4:44
  • The site will give you a random number for max_connections. You'll have to test. Feb 18 '20 at 4:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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