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 ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match