Search code examples
linuxpostgresqlplpgsqlscheduling

Set processor priority of PL/pgSQL stored procedures

I would like to know if there´s a way to set a lower priority to a PL/pgSQL stored procedure when user calls it to PostgreSQL. When I call this procedure, the scheduler sets almost 100% of the CPU to the stored procedure I wrote, leaving other processes almost unattended.

System Info: PostgreSQL 8.3.11 on a Linux 2.6.30.10-105.2.23.fc11.x86_64 box.

Solution

  • It is possible to do, in a roundabout way.

    Say your stored procedure is called uses_too_many_cycles(). Let's write a psql wrapper script for it:

    \set QUIET on
\set ECHO errors
SELECT pg_backend_pid() AS my_pg_backend_pid
\gset
\pset tuples_only on
\pset format unaligned
\! rm -f /tmp/renice_my_pg_backend
\out     /tmp/renice_my_pg_backend
SELECT '#!/bin/bash' ;
SELECT 'renice +19 --pid ' || :my_pg_backend_pid ;
\out
\! chmod +x /tmp/renice_my_pg_backend
\!          /tmp/renice_my_pg_backend

CALL uses_too_many_cycles();

    We write a tiny shell script containing our desired command, and then we execute that shell script; we could not just execute our desired command directly, because psql treats everything to right of \! as a literal string.

    General Caveats

    • Playing with OS scheduler priorities can trigger unusual, and unpleasant, edge cases in behavior of server applications. There is an element of risk.
    • This trick attacks the symptom, not the cause; it should not be the first thing to reach for.
    • "Technically possible" does not mean "advisable".

    Specific Technical Caveats

    • The lowered OS scheduler priority will persist for lifetime of the affected PostgreSQL backed process, unless you reset it.
    • If parallel query is enabled, the lowered scheduler priority will not be inherited by any helper backend processes PostgreSQL spins up.
    • Depending on your Linux distribution, renice(1) syntax may differ.
    • This trick will only work if one of the following conditions holds:
      • executing OS user is the PostgreSQL user
      • executing OS user is root
      • executing OS user is privileged through some mechanism ( sudoers, PAM, ...) to renice(1) other users' processes, or anyway the PostgreSQL user's processes
      • wrapped in a setuid-privileged script

    Each of those conditions is operationally inadvisable, does not scale well, or both.