Postgresql – Could too many idle connections affect PostgreSQL 9.2 performance

MySQL, by default, only allows 151 connections (plus one more for a super user).

In your MATLAB output, you show 149 then 150 threads. I presume that the majority of those are connecting to the database. 150 MATLAB threads will generate 150 connections.

There are few ways you could try to fix this.

1. One possibility is to limit the number of threads in MATLAB. You can do that using the maxNumberCompThreads function. Note that this is only available in R2007b through R2010a (and it will give you warning as of R2009b). Alternatively, the -singleCompThread option will work as well, but limits you to one thread.

2. Try to limit the time that you have a connection open to the absolute shortest interval possible: Open the connection, process the database work, close the connection. This may work, depending upon how much other (non-database) work each thread is doing.

3. Finally, you could increase the number of connections allowed to the database. You will need to change the max_connections server option. Just use the command "SET MAX_CONNECTIONS = 500".

1500 to 2200 concurrent connections are only useful when you need them. Otherwise, bring the total number of open connections down and start using a connection pool like pgBouncer or PgPool-II. See Number of Connections (on the wiki). We do about 2500 transactions per second on 150 connections.

Don't forget, each connection uses RAM, RAM that could be used to get some real work done. PostgreSQL gets slower when you use too many connections without a connection pool. What "too many" is depends on your hardware and workload, but it's very unlikely that your system will perform better with 2000 than it will with 100; probably much worse.