Correct query for monitor
От | veem v |
---|---|
Тема | Correct query for monitor |
Дата | |
Msg-id | CAB+=1TWW4DJwD0XPgud2bTJOuW0HkJ+rU11CL=mU93d4HXwfeA@mail.gmail.com обсуждение исходный текст |
Список | pgsql-general |
Hello,
We want to have monitoring on three things 1) If the database restarted or went down in the last few hours? 2)If the connections are high 3) High tablespace growth . Want to understand , if we can utilize below queries for the same or any flaws in this strategy?
1)SELECT
CASE
WHEN now() - pg_postmaster_start_time() < interval '12 hours'
THEN 'ALERT: DB was restarted in the last 12 hours'
ELSE 'OK'
END AS status;
2)SELECT
CASE
WHEN conn_count > max_conn * 0.8 THEN
'ALERT: Connection usage is above 80%'
ELSE
'OK: Connection usage is under control'
END AS status,
conn_count AS current_connections,
max_conn AS max_connections,
ROUND(conn_count * 100.0 / max_conn, 2) AS percent_used
FROM (
SELECT
COUNT(*) AS conn_count,
(SELECT setting::int FROM pg_settings WHERE name = 'max_connections') AS max_conn
FROM pg_stat_activity
) sub;
3)SELECT spcname, pg_size_pretty(pg_tablespace_size(oid)) AS size
FROM pg_tablespace;
We want to have monitoring on three things 1) If the database restarted or went down in the last few hours? 2)If the connections are high 3) High tablespace growth . Want to understand , if we can utilize below queries for the same or any flaws in this strategy?
1)SELECT
CASE
WHEN now() - pg_postmaster_start_time() < interval '12 hours'
THEN 'ALERT: DB was restarted in the last 12 hours'
ELSE 'OK'
END AS status;
2)SELECT
CASE
WHEN conn_count > max_conn * 0.8 THEN
'ALERT: Connection usage is above 80%'
ELSE
'OK: Connection usage is under control'
END AS status,
conn_count AS current_connections,
max_conn AS max_connections,
ROUND(conn_count * 100.0 / max_conn, 2) AS percent_used
FROM (
SELECT
COUNT(*) AS conn_count,
(SELECT setting::int FROM pg_settings WHERE name = 'max_connections') AS max_conn
FROM pg_stat_activity
) sub;
3)SELECT spcname, pg_size_pretty(pg_tablespace_size(oid)) AS size
FROM pg_tablespace;
Regards
Veem
В списке pgsql-general по дате отправления: