Skip site navigation (1) Skip section navigation (2)

pgAgent job throttling

From: "Roberts, Jon" <Jon(dot)Roberts(at)asurion(dot)com>
To: <pgsql-hackers(at)postgresql(dot)org>
Subject: pgAgent job throttling
Date: 2008-02-25 17:36:55
Message-ID: 1A6E6D554222284AB25ABE3229A9276271564E@nrtexcus702.int.asurion.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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

In response to

  • Tuning 8.3 at 2008-02-25 15:47:37 from Roberts, Jon

pgsql-hackers by date

Next:From: Tom LaneDate: 2008-02-25 17:40:28
Subject: Re: Questions about indexes with text_pattern_ops
Previous:From: Alvaro HerreraDate: 2008-02-25 17:32:55
Subject: Re: [PATCHES] Avahi support for Postgresql

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group