Thread: Re: FW: 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.
The alternative approach would be to make it a command line option to
pgagent. That wouldn't require any UI support of course.
Regards, Dave.
On Tue, Feb 26, 2008 at 9:47 PM, Roberts, Jon <Jon.Roberts@asurion.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@postgresql.org
> [mailto:pgadmin-hackers-owner@postgresql.org] On Behalf Of Roberts, Jon
> Sent: Tuesday, February 26, 2008 8:14 AM
> To: pgadmin-hackers@postgresql.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
On Tue, Feb 26, 2008 at 10:18 PM, Lucas, Craig <Craig.Lucas@asurion.com> wrote: > Dave, > > If we could get pgagent to compile we would make the change in our local > copy and deploy it here. We are having a really rough time compiling > pgagent, atleast on our dev boxes. The problem seems to be around > manifest files and that the compiler seems to want to include > "Microsoft.VC90.DebugCRT" which is the visual studio 2008 crt dll. This > is odd because I am compiling it in 2005, but I also have 2008 > installed. And when I compile in 2008 I get errors that it cant find > MSVCRT90D.dll and when I copy the file into the pgagent folder I get > other errors because its trying to pull in a dll that's not in the > manifest file. Craig, I've not yet got round to trying VC2008, so cannot help with it. All I can suggest is trying a machine with just VC2005 installed - we know that works. You should be able to get a build environment working in an hour, maybe 2 if you've not done it before. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com The Oracle-compatible database company
Dave,
If we could get pgagent to compile we would make the change in our local
copy and deploy it here. We are having a really rough time compiling
pgagent, atleast on our dev boxes. The problem seems to be around
manifest files and that the compiler seems to want to include
"Microsoft.VC90.DebugCRT" which is the visual studio 2008 crt dll. This
is odd because I am compiling it in 2005, but I also have 2008
installed. And when I compile in 2008 I get errors that it cant find
MSVCRT90D.dll and when I copy the file into the pgagent folder I get
other errors because its trying to pull in a dll that's not in the
manifest file.
Here is the manifest generated when I try to compile in VS2008 (you can
see its referncing the same assembly twice):
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<assembly xmlns="urn:schemas-microsoft-com:asm.v1"
manifestVersion="1.0">
<trustInfo xmlns="urn:schemas-microsoft-com:asm.v3">
<security>
<requestedPrivileges>
<requestedExecutionLevel level="asInvoker"
uiAccess="false"></requestedExecutionLevel>
</requestedPrivileges>
</security>
</trustInfo>
<dependency>
<dependentAssembly>
<assemblyIdentity type="win32" name="Microsoft.VC90.DebugCRT"
version="9.0.21022.8" processorArchitecture="x86"
publicKeyToken="1fc8b3b9a1e18e3b"></assemblyIdentity>
</dependentAssembly>
</dependency>
<dependency>
<dependentAssembly>
<assemblyIdentity type="win32"
name="Microsoft.Windows.Common-Controls" version="6.0.0.0"
processorArchitecture="x86" publicKeyToken="6595b64144ccf1df"
language="*"></assemblyIdentity>
</dependentAssembly>
</dependency>
<dependency>
<dependentAssembly>
<assemblyIdentity type="win32" name="Microsoft.VC90.DebugCRT"
version="9.0.20706.1" processorArchitecture="x86"
publicKeyToken="1fc8b3b9a1e18e3b"></assemblyIdentity>
</dependentAssembly>
</dependency>
</assembly>
-----Original Message-----
From: Dave Page [mailto:dpage@pgadmin.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.
The alternative approach would be to make it a command line option to
pgagent. That wouldn't require any UI support of course.
Regards, Dave.
On Tue, Feb 26, 2008 at 9:47 PM, Roberts, Jon <Jon.Roberts@asurion.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@postgresql.org
> [mailto:pgadmin-hackers-owner@postgresql.org] On Behalf Of Roberts,
Jon
> Sent: Tuesday, February 26, 2008 8:14 AM
> To: pgadmin-hackers@postgresql.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
On Tue, Feb 26, 2008 at 10:26 PM, Lucas, Craig <Craig.Lucas@asurion.com> wrote: > Dave, > > This is probly a dumb question, but is it possible to distribute pgadmin > source code with wxWidgets and the libxml projects and all other > dependencies as a single distributable? It would make it so much easier > if it was more of a 1-click compile rather than having to download and > install/build all these things separately. It's possible, sure, but it's not something I expect anyone on the project will spend any time on for the miniscule number of people that would use it. Plus it's not exactly difficult to setup - you should try building MIT Kerberos on Windows if you want to experience real pain :-p -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com The Oracle-compatible database company
> -----Original Message-----
> From: Dave Page [mailto:dpage@pgadmin.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.Roberts@asurion.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@postgresql.org
> > [mailto:pgadmin-hackers-owner@postgresql.org] On Behalf Of Roberts,
Jon
> > Sent: Tuesday, February 26, 2008 8:14 AM
> > To: pgadmin-hackers@postgresql.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
On Wed, Feb 27, 2008 at 3:29 AM, Roberts, Jon <Jon.Roberts@asurion.com> wrote: > > I agree that a UI change would be ideal. Required, if this design is to be accepted in the standard code. Let me check it over first though - I may have other concerns. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com The HOT PostgreSQL Company
I was thinking about this part of the pgagent.sql script the other day:
INSERT INTO pgagent.pga_jobclass (jclname) VALUES ('Routine
Maintenance');
INSERT INTO pgagent.pga_jobclass (jclname) VALUES ('Data Import');
INSERT INTO pgagent.pga_jobclass (jclname) VALUES ('Data Export');
INSERT INTO pgagent.pga_jobclass (jclname) VALUES ('Data
Summarisation');
INSERT INTO pgagent.pga_jobclass (jclname) VALUES ('Miscellaneous');
You don't have a UI for these job classes but they are used to populate
the drop down list when creating a new job. If someone wants to
insert/update/delete any of these job classes, they have to do this
manually without a UI. Based on this, I would think we could add this
feature without a UI change.
I also thought about putting the job throttling into the job class by
throttling jobs based on the job class they are in. It would take more
changes to the SQL code but I think it would be doable and add more
flexibility.
Jon
> -----Original Message-----
> From: Dave Page [mailto:dpage@pgadmin.org]
> Sent: Wednesday, February 27, 2008 3:14 AM
> To: Roberts, Jon
> Cc: pgadmin-hackers; Lucas, Craig
> Subject: Re: FW: [pgadmin-hackers] pgAgent job limit
>
> On Wed, Feb 27, 2008 at 3:29 AM, Roberts, Jon
<Jon.Roberts@asurion.com>
> wrote:
> >
> > I agree that a UI change would be ideal.
>
> Required, if this design is to be accepted in the standard code. Let
> me check it over first though - I may have other concerns.
>
> --
> Dave Page
> EnterpriseDB UK: http://www.enterprisedb.com
> The HOT PostgreSQL Company
On Wed, Mar 19, 2008 at 4:32 PM, Roberts, Jon <Jon.Roberts@asurion.com> wrote:
> I was thinking about this part of the pgagent.sql script the other day:
>
> INSERT INTO pgagent.pga_jobclass (jclname) VALUES ('Routine
> Maintenance');
> INSERT INTO pgagent.pga_jobclass (jclname) VALUES ('Data Import');
> INSERT INTO pgagent.pga_jobclass (jclname) VALUES ('Data Export');
> INSERT INTO pgagent.pga_jobclass (jclname) VALUES ('Data
> Summarisation');
> INSERT INTO pgagent.pga_jobclass (jclname) VALUES ('Miscellaneous');
>
> You don't have a UI for these job classes but they are used to populate
> the drop down list when creating a new job. If someone wants to
> insert/update/delete any of these job classes, they have to do this
> manually without a UI. Based on this, I would think we could add this
> feature without a UI change.
2 wrongs don't make a right.
> I also thought about putting the job throttling into the job class by
> throttling jobs based on the job class they are in. It would take more
> changes to the SQL code but I think it would be doable and add more
> flexibility.
I would be less averse to letting that slide in.
--
Dave Page
EnterpriseDB UK Ltd: http://www.enterprisedb.com
PostgreSQL UK 2008 Conference: http://www.postgresql.org.uk