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
