Thread: Process priority.

Hi,
        I wrote a C program that can be called through postgres via stored procedures. It allows you to change the current postmaster process priority.

        Essentially it's intended purpose would be to allow a highly resource intensive postmaster process to run at a lower priority, allowing other postmaster processes more cpu time. For example admin/reporting backend sql's that impact the system. I guess this is the same as renice, however it can be accessed programatically from the postgres box via any client.

        The three stored procs are as follows:

        get_pid()
        get_priority()
        set_priority(int) <!--- setting a negative number(high priority) requires root access, typical usage would be to set a lower priority, i.e. of 20.

        I would not use this as a alternative to setting up a replication slave for reporting on a large production system.

        I have attached the source code, it has already been compiled for redhat Linux ES3. There is a README file to get you started. This was my first attempt at writing a c function for postgres.

        <<priority.rar>>
       

Theo Galanakis
Database Administrator
Lonely Planet Publications
90 Maribyrnong St, Footscray
Melbourne, Vic 3011
Australia
tel +61 3 8379 8000 ext 8395
fax + 61 3 8379 8111
theo_galanakis@lonelyplanet.com.au

Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au> writes:
>     I wrote a C program that can be called through postgres via stored
> procedures. It allows you to change the current postmaster process priority.

You are aware that that's widely considered either useless or
counterproductive?  Renice-ing one backend seldom gives helpful results,
because (a) what usually matters more than CPU usage is I/O, and renice
doesn't change I/O priorities; (b) priority inversion results in
higher-priority backends blocking behind the low-priority one whenever
it's managed to acquire a lock.
        regards, tom lane

<p><font size="2">It depends what the intended purpose if for. In our scenario, there are a number of ad-hoc export
tasksrunning during the day that are quite cpu intensive. </font><p><font size="2">There is a noticeably slow response
timewhen exports are being run. By lowering the priority of the export postmaster, the system appeared to run far
smootherfor standard users.</font><p><font size="2">I guess you could set-up a replication database which I probably
willdo in the end, however I just wanted to have a play and see if I could extend postgres with c. I don't profess to
beinga c or os guru. Thankyou for your feedback.</font><p><font size="2">Theo</font><p><font size="2">-----Original
Message-----</font><br/><font size="2">From: Tom Lane [<a href="mailto:tgl@sss.pgh.pa.us">mailto:tgl@sss.pgh.pa.us</a>]
</font><br/><font size="2">Sent: Friday, 18 March 2005 11:06 AM</font><br /><font size="2">To: Theo Galanakis</font><br
/><fontsize="2">Cc: pgsql-sql@postgresql.org</font><br /><font size="2">Subject: Re: [SQL] Process priority. </font><br
/><p><fontsize="2">Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au> writes:</font><br /><font size="2">>
     I wrote a C program that can be called through postgres via stored </font><br /><font size="2">> procedures. It
allowsyou to change the current postmaster process </font><br /><font size="2">> priority.</font><p><font
size="2">Youare aware that that's widely considered either useless or counterproductive?  Renice-ing one backend seldom
giveshelpful results, because (a) what usually matters more than CPU usage is I/O, and renice doesn't change I/O
priorities;(b) priority inversion results in higher-priority backends blocking behind the low-priority one whenever
it'smanaged to acquire a lock.</font><p>                        <font size="2">regards, tom lane</font><table><tr><td
bgcolor="#ffffff"><fontcolor="#000000">______________________________________________________________________<br />
Thisemail, including attachments, is intended only for the addressee<br /> and may be confidential, privileged and
subjectto copyright. If you<br /> have received this email in error, please advise the sender and delete<br /> it. If
youare not the intended recipient of this email, you must not<br /> use, copy or disclose its content to anyone. You
mustnot copy or <br /> communicate to others content that is confidential or subject to <br /> copyright, unless you
havethe consent of the content owner.<br /></font></td></tr></table>