f_perf_syscalls.sql

DROP FUNCTION IF EXISTS svc_sqlbalancer.f_perf_syscalls
(
    INT,
    INT
);

-- This funtion requires appropriate sudoers permissions:
-- sudo visudo
-- postgres ALL=NOPASSWD:/usr/bin/perf
--
CREATE OR REPLACE FUNCTION svc_sqlbalancer.f_perf_syscalls
(
    p_pid       INT DEFAULT NULL,
    p_duration  INT DEFAULT 30
)
RETURNS TABLE (pid INT, syscall TEXT, cnt INT, cnt_per_sec INT)
LANGUAGE plpgsql VOLATILE
SECURITY DEFINER
AS
$$
DECLARE
    l_sql           TEXT;
    l_cmd           TEXT;
BEGIN
    IF p_duration > 60 THEN
        RAISE EXCEPTION 'duration too long (maximum of 60s allowed)';
    END IF;

    -- create a temp table to hold the output from psql
    l_sql := 'CREATE LOCAL TEMP TABLE IF NOT EXISTS t_output (cnt TEXT, x TEXT, syscall TEXT) ON COMMIT DROP';
    execute(l_sql);

    -- construct the OS command to be run (which is read from using COPY FROM PROGRAM thereafter)
    IF p_pid IS NULL THEN
        -- system (PostgreSQL) global stats
        l_cmd := 'sudo /usr/bin/perf stat -e "syscalls:sys_enter_*"                    -x ";" -a sleep ' || p_duration || ' 2>&1';
    ELSE
        -- single-process stats
        l_cmd := 'sudo /usr/bin/perf stat -e "syscalls:sys_enter_*" -p ' || p_pid || ' -x ";" -a sleep ' || p_duration || ' 2>&1';
    END IF;
    RAISE NOTICE 'starting Linux perf syscalls sampling - be patient, this can take some time ..';
    RAISE NOTICE '%', l_cmd;

    -- run the OS command and insert the output into our temp table
    l_sql := 'COPY t_output FROM PROGRAM ''' || l_cmd || ''' WITH DELIMITER '';''';
    EXECUTE(l_sql);

    -- return temp table contents with output from psql. the temp table is dropped at end of transaction
    RETURN QUERY
        SELECT p_pid, r.syscall, r.cnt, (CASE WHEN r.cnt > 0 THEN ROUND(r.cnt::float / p_duration::float) ELSE NULL END)::int AS cnt_per_sec FROM
            (SELECT o.syscall, (CASE WHEN o.cnt != '<not counted>' THEN o.cnt::int ELSE NULL END) AS cnt FROM t_output o) r
                WHERE r.cnt > 0 ORDER BY r.cnt DESC;
END;
$$;

REVOKE ALL ON FUNCTION svc_sqlbalancer.f_perf_syscalls
(
    INT,
    INT
) FROM public;

GRANT EXECUTE ON FUNCTION svc_sqlbalancer.f_perf_syscalls
(
    INT,
    INT
) TO adr_devops;

COMMENT ON FUNCTION svc_sqlbalancer.f_perf_syscalls
(
    INT,
    INT
) IS
'
Measure live statistics on syscalls executed by a PostgreSQL backend process using Linux perf.

Example (single process):

    SELECT * FROM svc_sqlbalancer.f_perf_syscalls(29422) WHERE cnt > 0 ORDER BY cnt DESC;

Example (global):

    SELECT * FROM svc_sqlbalancer.f_perf_syscalls() WHERE cnt > 0 ORDER BY cnt DESC;


See:

- https://en.wikipedia.org/wiki/Perf_(Linux)
- http://www.brendangregg.com/perf.html
- https://perf.wiki.kernel.org/index.php/Tutorial
- https://wiki.postgresql.org/wiki/Profiling_with_perf
';