Re: FW: pgAgent job limit

> -----Original Message-----
> From: Dave Page [mailto:dpage(at)pgadmin(dot)org]
> Sent: Tuesday, February 26, 2008 4:14 PM
> To: Roberts, Jon; pgadmin-hackers
> Cc: Lucas, Craig
> Subject: Re: FW: [pgadmin-hackers] pgAgent job limit
>
> Jon,
>
> I'm pretty busy at the moment so haven't had a chance to review this
> properly, but a couple of things spring instantly to mind - basically
> you are currently proposing a hack and not a full solution - your 1
> line of code is merely the tip of the iceberg. In addition to that and
> the script changes you note, we'd also need documentation and UI
> support.
>

I agree that a UI change would be ideal.

> The alternative approach would be to make it a command line option to
> pgagent. That wouldn't require any UI support of course.
>

I thought of the command line option too. I like the idea of using a
table so that you can dynamically adjust and tune the job throttle
without having to restart the service.

Craig did manage to compile pgAgent later today and we are testing it
now. So far, it works exactly how I had hoped and it will drastically
help reduce the number of sessions in postgres.

Thanks Dave.

Jon

> Regards, Dave.
>
> On Tue, Feb 26, 2008 at 9:47 PM, Roberts, Jon
<Jon(dot)Roberts(at)asurion(dot)com>
> wrote:
> > Dave, we have been spending all day trying to get MS Visual C++ to
work
> > with wxWidgets and compile pgAgent all for one line of code to
change.
> >
> > Do you think the below change is a good idea and if so, could you
add
> > this to speed up the deployment?
> >
> > I also think we need to add the DDL changes to pgagent.sql as well
as
> > recompile pgagent.exe.
> >
> >
> > Thanks!
> >
> >
> > Jon
> >
> >
> > -----Original Message-----
> > From: pgadmin-hackers-owner(at)postgresql(dot)org
> > [mailto:pgadmin-hackers-owner(at)postgresql(dot)org] On Behalf Of Roberts,
Jon
> > Sent: Tuesday, February 26, 2008 8:14 AM
> > To: pgadmin-hackers(at)postgresql(dot)org
> > Subject: [pgadmin-hackers] pgAgent job limit
> >
> > In pgAgent.cpp, I would like to add LIMIT as shown below:
> >
> >
> >
> > LogMessage(_("Checking for jobs to run"), LOG_DEBUG);
> > DBresult *res=serviceConn->Execute(
> > wxT("SELECT J.jobid ")
> > wxT(" FROM pgagent.pga_job J ")
> > wxT(" WHERE jobenabled ")
> > wxT(" AND jobagentid IS NULL ")
> > wxT(" AND jobnextrun <= now() ")
> > wxT(" AND (jobhostagent = '' OR jobhostagent = '") + hostname +
> > wxT("')")
> > wxT(" ORDER BY jobnextrun")
> > wxT(" LIMIT pgagent.pga_job_limit('") + hostname + wxT("')"));
> >
> >
> > This requires two new objects:
> > create table pgagent.pga_job_throttle (jobmax int);
> >
> > insert into pgagent.pga_job_throttle values (2);
> >
> > create or replace function pgagent.pga_job_limit(p_hostname
varchar)
> > returns int as
> > $$
> > declare
> > v_limit int;
> > v_running int;
> > begin
> >
> > select jobmax
> > into v_limit
> > from pgagent.pga_job_throttle;
> >
> > if v_limit < 0 or v_limit is null then
> > select count(*)
> > into v_limit
> > from pgagent.pga_job j
> > where jobenabled
> > and jobagentid is null
> > and jobnextrun <= now()
> > and (jobhostagent = '' or jobhostagent = p_hostname);
> > else
> > select count(*)
> > into v_running
> > from pgagent.pga_job j
> > join pgagent.pga_joblog l
> > on j.jobid = l.jlgjobid
> > where j.jobenabled
> > and (j.jobhostagent = '' or j.jobhostagent = p_hostname)
> > and l.jlgstatus = 'r'; --Status of job: r=running,
> > s=successfully finished, f=failed, i=no steps to execute, d=aborted
> >
> > v_limit := v_limit - v_running;
> >
> > end if;
> >
> > return v_limit;
> >
> > end;
> > $$
> > language 'plpgsql';
> >
> >
> > This function allow pgAgent to be throttled dynamically by managing
the
> > pgagent.pga_job_throttle table. If you want to disable all jobs
from
> > running, you set the value to 0. If you want to let as many jobs
run
> at
> > once (like the default) to run at a time, you either delete the
record
> > from the table or you can set the value to a negative number.
> >
> > pgAgent scales much better without having excessive number of
> > connections to the database with one line change to the C++ code.
> >
> >
> >
> > What do you guys think?
> >
> >
> > 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
> >
>
>
>
> --
> Dave Page
> EnterpriseDB UK: http://www.enterprisedb.com
> The Oracle-compatible database company