Created
January 25, 2017 09:14
-
-
Save oberstet/ca03d7ab49be4c8edb70ffa1a9fe160c to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 | |
'; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment