Increase max database connection limit
Posted by Paul Smith on 20 October 2015 11:08 AM

> We would like to incease the Max Connections value under Database / Connections / Advanced Settings
> This is currently set to the default of 50 and we're finding that we are reaching that value quite frequently. because there are notes about having to amend the PostgreSQL service configuration, it's not something we'd undertake without advice first.

If you are hitting that limit, then it usually indicates a performance issue on the server.

I don't know how many users you have, but on our hosted servers we have customers with 100+ users where the limit is set to 30 connections, and they rarely get close to that limit and don't have performance problems.

If there is a performance issue on the server, then increasing the limit is unlikely to help much at all, since it'll just make the server even busier.

Things to check:
- check the server disk is regularly defragmented (unless it's an SSD). Most performance problems we see are due to problems with disk fragmentation.
- check the server disk is NOT a RAID 5 (or 'parity') array
- check any antivirus software on the server is excluding the VPOP3\pgsql\data folder and subfolders
- Use Windows Performance monitor to check the disk 'average queue length' (should be consistently less than 2 or 3), 'disk sec/transfer' (should always be less than 0.1 or so) and 'transfers/sec' (indicative of whether the disk is going flat out - this is similar to 'IOPS' that you may find referred to on websites talking about computer performance).

If you do still want to increase the connection limit, then you can increase the 'max connections' up to 80 without problems. To go more than that, you can edit the VPOP3\pgsql\data\postgresql.conf file, to change the 'max_connections=' setting there. Note that on Windows this shouldn't go much above about 125. Then you can change the 'absolute max connections' up to around 110. If you need to go higher than that you would need to move the database server onto a Linux server which can handle many more connections.

But, do remember that if you have disk performance issues, then increasing the max connections will make things WORSE not better, because ALL the connections will just run slower, leading to more timeouts.

(1 vote(s))
Not helpful

Comments (0)