0

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?

  • The number of users doesn't tell us much, as each one could only do something that hits the database once per day, or multiple times per second. But it doesn't make much sense for max_connections to be 300 when pool-max-size=20. Unless you are running like 10 copies of the app each with its own pool. – jjanes Feb 17 at 14:59
1

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.

|improve this answer|||||
  • 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 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. – Laurenz Albe Feb 18 at 4:34
  • Using SSD now. This site is good to calculate configuration items: pgtune.leopard.in.ua/# – rawmain Feb 18 at 4:44
  • The site will give you a random number for max_connections. You'll have to test. – Laurenz Albe Feb 18 at 4:50
  • Yes, thank you. – rawmain Feb 18 at 5:24

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.