Re: Tuning 8.3

I need to run about 1000 PostgreSQL connections on a server that I can
use about 4 GB of the total 16 GB of total RAM. It seems that each
session creates a process that uses about 15 MB of RAM just for
connecting so I'm running out of RAM rather quickly.

I have these non-default settings:

shared_buffers = 30MB

max_connections = 1000

I tried decreasing the work_mem but the db wouldn't start then.

I'm running version 8.3 on Windows 2003 Server.

Any tips for reducing the memory footprint per session? There is
pgBouncer but is there anything I can do in the configuration before I
go with a connection pooler?

Jon

Roberts, Jon wrote:
>
> I need to run about 1000 PostgreSQL connections on a server that I can
> use about 4 GB of the total 16 GB of total RAM. It seems that each
> session creates a process that uses about 15 MB of RAM just for
> connecting so I’m running out of RAM rather quickly.
>
> I have these non-default settings:
>
> shared_buffers = 30MB
>
> max_connections = 1000
>
> I tried decreasing the work_mem but the db wouldn’t start then.
>
> I’m running version 8.3 on Windows 2003 Server.
>
> Any tips for reducing the memory footprint per session? There is
> pgBouncer but is there anything I can do in the configuration before I
> go with a connection pooler?
>
>

Please ask usage questions on the appropriate list (in this case one of:
pgsql-general, pgsql-performance or pgsql-admin). pgsql-hackers is for
discussion of development of features, not for usage issues.

cheers

andrew

"Roberts, Jon" <Jon(dot)Roberts(at)asurion(dot)com> writes:

> I need to run about 1000 PostgreSQL connections on a server that I can
> use about 4 GB of the total 16 GB of total RAM. It seems that each
> session creates a process that uses about 15 MB of RAM just for
> connecting so I'm running out of RAM rather quickly.

I think you're being bitten by a different problem than it appears. Windows
has a fixed size per-session shared memory pool which runs out rather quickly.
You can raise that parameter though. (The 125 mentioned there is raised to
about 300 with Pg 8.3.)

See:

http://www.postgresql.org/docs/faqs.FAQ_windows.html#4.4

> Any tips for reducing the memory footprint per session? There is
> pgBouncer but is there anything I can do in the configuration before I
> go with a connection pooler?

I think at 1,000 you're probably into the domain where pgbouncer (or others
like it) is a good idea. Or you could pool or batch at a higher level and have
fewer sessions active at all. You don't win any performance by trying to do
more things simultaneously if they're just competing for cpu timeslices or i/o
bandwidth.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!

> > I need to run about 1000 PostgreSQL connections on a server that I
can
> > use about 4 GB of the total 16 GB of total RAM. It seems that each
> > session creates a process that uses about 15 MB of RAM just for
> > connecting so I'm running out of RAM rather quickly.
>
> I think you're being bitten by a different problem than it appears.
> Windows
> has a fixed size per-session shared memory pool which runs out rather
> quickly.
> You can raise that parameter though. (The 125 mentioned there is
raised to
> about 300 with Pg 8.3.)
>
> See:
>
> http://www.postgresql.org/docs/faqs.FAQ_windows.html#4.4
>
>

Thanks for the tip and I'll be moving this to the performance forum.
Although, with 8.3, it seems that the FAQ is out of date?

Jon

I posted earlier about how to tune my server and I think the real
problem is how many connections pgAgent creates for my job needs.

I basically need to run hundreds of jobs daily all to be executed at
4:00 AM. To keep the jobs from killing the other systems, I am
throttling this with a queue table.

With pgAgent, it creates 2 connections (one to the maintenance db and
one to the target db) and then my queue throttling makes a third
connection every 10 seconds checking the job queue to see if there is an
available queue to execute.

A better solution would be to incorporate job throttling in pgAgent.
Currently, pgAgent will spawn as many jobs as required and it creates a
minimum of two database connections per job. I think a solution would
be for pgAgent to not create the connection and execute my job steps
unless the current number of jobs running is less than a result from a
function.

Sort of like this:

select count(*) into v_count from queue where status = 'Processing';

while v_count >= fn_get_max_jobs() loop

pg_sleep(fn_get_sleep_time());

select count(*) into v_count from queue where status = 'Processing';

end loop;

I'm doing this now but inside a function being executed by pgAgent.
This means I have two connections open for each job. Plus, I use a
function that uses a dblink to lock the queue table and then update the
status so that is a third connection that lasts just for a millisecond.

So if 200 jobs are queued to run at 4:00 AM, then I have 400 connections
open and then it will spike a little bit as each queued job checks to
see if it can run.

Do you guys think it is a good idea to add job throttling to pgAgent to
limit the number of connections? Setting the value to -1 could be the
default value which would allow an unlimited number of jobs to run at a
time (like it is now) but a value greater than -1 would be the max
number of jobs that can run concurrently.

Jon

"Get thee to a connection pooler ASAP."

We've got systems where we establish ~1K connections, but that's on
UNIX, where the handling of large systems is *WAY* more mature than
Windows.

Any time those kinds of quantities of connections appear necessary, it
seems highly preferable to be using connection pooling so as to try to
reduce the number of actual connections and to increase the
per-connection usage.

--
http://linuxfinances.info/info/linuxdistributions.html
"The definition of insanity is doing the same thing over and over and
expecting different results." -- assortedly attributed to Albert
Einstein, Benjamin Franklin, Rita Mae Brown, and Rudyard Kipling