Prometheus 및 Grafana를 사용하여 PostgreSQL로 Oracle Cloud Infrastructure Database 모니터링

소개

PostgreSQL 애플리케이션 개발자 중 가장 인기 있는 데이터베이스 중 하나입니다. Oracle Cloud Infrastructure Database with PostgreSQL(OCI Database with PostgreSQL)가 제공하는 관리 오버헤드 제거 외에도 모니터링 편의성을 구현할 때 한 걸음 더 나아갑니다. Prometheus 및 Grafana는 시계열 데이터 저장 및 시각화를 위해 각각 애플리케이션 성능을 모니터링할 때 매우 인기 있는 쌍입니다.

이 사용지침서에서는 PostgreSQL Exporter를 사용하여 쿼리 레벨 측정지표를 추출하고 Prometheus로 스크래핑하며 Grafana에서 시각화하며 bloat 측정지표에 대한 경보를 설정하여 PostgreSQL로 OCI 데이터베이스를 모니터링하는 방법을 보여줍니다.

구조 개요

다음 스키마는 전체적인 접근 방식을 보여줍니다. 사용되는 구성 요소는 다음과 같습니다.

PostgreSQL을 사용한 OCI 데이터베이스.
Postgres Exporter - 측정항목을 노출합니다.
Prometheus: 측정 단위를 스크레이핑합니다.
Grafana는 측정 지표를 시각화합니다.

구조 보기

주: 아키텍처는 명확성을 위해 세 개의 서브넷을 표시하지만 이 자습서에서는 두 개만 사용하고 점프 호스트는 Prometheus 및 Grafana 배치에 사용되는 VM이기도 합니다.

목표

Prometheus용 PostgreSQL 익스포터로 PostgreSQL 측정항목을 추출합니다.
Prometheus에서 이러한 측정 단위를 수집합니다.
Grafana 대시보드를 사용하여 측정지표를 시각화합니다.
PostgreSQL 익스포터를 통해 노출된 측정항목을 사용하여 테이블 bloat를 감지하도록 경보를 설정합니다.

필수 조건

PostgreSQL 클러스터가 프로비저닝된 OCI 데이터베이스를 사용하여 OCI 테넌시에 액세스합니다. 자세한 내용은 DBeaver을 사용하여 PostgreSQL를 사용하여 Oracle Cloud Infrastructure Database에 연결을 참조하십시오.
PostgreSQL를 사용하는 OCI 데이터베이스와 동일한 VCN의 공용 서브넷에 있는 OCI 컴퓨트 인스턴스(점프 호스트)입니다.
OCI 보안 목록: 인바운드 포트 9090, 9187, 3000를 허용합니다.

다음 명령을 실행하여 인스턴스에 방화벽을 설정합니다.

sudo firewall-cmd --permanent --add-port=9090/tcp
sudo firewall-cmd --permanent --add-port=9187/tcp
sudo firewall-cmd --permanent --add-port=3000/tcp
sudo firewall-cmd --reload

작업 1: PostgreSQL를 사용하여 OCI 데이터베이스에서 확장 모니터링 사용

pg_stat_statements 확장을 사용으로 설정합니다.

OCI 콘솔을 통해 PostgreSQL 구성을 사용하여 OCI 데이터베이스에 pg_stat_statements를 추가합니다. 자세한 내용은 데이터베이스에 대한 확장 사용을 참조하십시오.
모니터링 사용자 생성.

다음 query를 실행하여 점프 호스트를 통해 DBeaver를 사용하여 유저를 생성하고 연결합니다.
```
CREATE USER postgres_exporter WITH PASSWORD '<password>';
GRANT CONNECT ON DATABASE postgres TO postgres_exporter;
GRANT pg_monitor TO postgres_exporter;
```
참고: pg_stat_io 확장은 PostgreSQL v16 이상의 OCI 데이터베이스에서 사용할 수 있습니다.

작업 2: Prometheus용 PostgreSQL 익스포터 설치

주: OCI Compute 인스턴스에서 다음 명령을 실행하십시오.

PostgreSQL Exporter를 설치합니다.

wget https://github.com/prometheus-community/postgres_exporter/releases/download/v0.15.0/postgres_exporter-0.15.0.linux-amd64.tar.gz

tar -xvf postgres_exporter-0.15.0.linux-amd64.tar.gz

sudo mv postgres_exporter-0.15.0.linux-amd64/postgres_exporter /usr/local/bin/

사용자정의 queries.yaml 생성.

nano queries.yaml

# Custom PostgreSQL Query Metrics for Postgres Exporter

# Top 5 slowest SQL statements
top_sql_statements:
query: >
	SELECT query, calls, total_exec_time + total_plan_time AS total_time
	FROM pg_stat_statements
	ORDER BY total_time DESC
	LIMIT 5;
metrics:
	- query:
		usage: "LABEL"
		description: "SQL query text"
	- calls:
		usage: "COUNTER"
		description: "Number of executions"
	- total_time:
		usage: "GAUGE"
		description: "Total execution + plan time"

# 2. Top users by total execution time
top_sql_users:
query: >
	SELECT usename, SUM(total_exec_time + total_plan_time) AS total_exec_time
	FROM pg_stat_statements
	JOIN pg_user ON pg_stat_statements.userid = pg_user.usesysid
	GROUP BY usename
	ORDER BY total_exec_time DESC
	LIMIT 5;
metrics:
	- usename:
		usage: "LABEL"
		description: "PostgreSQL user"
	- total_exec_time:
		usage: "GAUGE"
		description: "Total execution time by user"

# 3. Top client IPs by connection count
top_clients:
query: >
	SELECT client_addr, COUNT(*) AS connections
	FROM pg_stat_activity
	WHERE client_addr IS NOT NULL
	GROUP BY client_addr
	ORDER BY connections DESC
	LIMIT 5;
metrics:
	- client_addr:
		usage: "LABEL"
		description: "Client IP address"
	- connections:
		usage: "GAUGE"
		description: "Number of active connections"

# 4. Currently running queries by user and IP
active_queries_by_user_ip:
query: >
	SELECT usename, client_addr, COUNT(*) as active_queries
	FROM pg_stat_activity
	WHERE state = 'active'
	GROUP BY usename, client_addr;
metrics:
	- usename:
		usage: "LABEL"
		description: "User running queries"
	- client_addr:
		usage: "LABEL"
		description: "Client IP address"
	- active_queries:
		usage: "GAUGE"
		description: "Number of currently active queries"

# 5. Query frequency (most called)
frequent_sql:
query: >
	SELECT query, calls
	FROM pg_stat_statements
	ORDER BY calls DESC
	LIMIT 5;
metrics:
	- query:
		usage: "LABEL"
		description: "SQL query text"
	- calls:
		usage: "COUNTER"
		description: "Number of executions"

# 6. Table bloat statistics
table_bloat:
query: >
	SELECT
	current_database() AS current_db,
	schemaname AS schema_name,
	tblname AS table_name,
	(bs * tblpages) / 1024 AS actual_table_size_kb,
	((tblpages - est_tblpages) * bs) / 1024 AS wasted_space_kb,
	CASE
		WHEN tblpages > 0 AND tblpages - est_tblpages > 0 THEN 100 * (tblpages - est_tblpages) / tblpages::float
		ELSE 0
	END AS wasted_space_percent,
	fillfactor AS table_fill_factor,
	CASE
		WHEN tblpages - est_tblpages_ff > 0 THEN (tblpages - est_tblpages_ff) * bs / 1024
		ELSE 0
	END AS bloat_size_kb,
	CASE
		WHEN tblpages > 0 AND tblpages - est_tblpages_ff > 0 THEN 100 * (tblpages - est_tblpages_ff) / tblpages::float
		ELSE 0
	END AS bloat_size_percent
	FROM (
	SELECT
		ceil(reltuples / ((bs - page_hdr) / tpl_size)) + ceil(toasttuples / 4) AS est_tblpages,
		ceil(reltuples / ((bs - page_hdr) * fillfactor / (tpl_size * 100))) + ceil(toasttuples / 4) AS est_tblpages_ff,
		tblpages,
		fillfactor,
		bs,
		schemaname,
		tblname
	FROM (
		SELECT
		(4 + tpl_hdr_size + tpl_data_size + (2 * ma)
		- CASE WHEN tpl_hdr_size % ma = 0 THEN ma ELSE tpl_hdr_size % ma END
		- CASE WHEN ceil(tpl_data_size)::int % ma = 0 THEN ma ELSE ceil(tpl_data_size)::int % ma END
		) AS tpl_size,
		(heappages + toastpages) AS tblpages,
		reltuples,
		toasttuples,
		bs,
		page_hdr,
		schemaname,
		tblname,
		fillfactor,
		ma,
		tpl_hdr_size,
		tpl_data_size
		FROM (
		SELECT
			ns.nspname AS schemaname,
			tbl.relname AS tblname,
			tbl.reltuples,
			tbl.relpages AS heappages,
			COALESCE(toast.relpages, 0) AS toastpages,
			COALESCE(toast.reltuples, 0) AS toasttuples,
			COALESCE(substring(array_to_string(tbl.reloptions, ' ') FROM 'fillfactor=([0-9]+)')::smallint, 100) AS fillfactor,
			current_setting('block_size')::numeric AS bs,
			CASE
			WHEN version() ~ 'mingw32|64-bit|x86_64|ppc64|ia64|amd64' THEN 8
			ELSE 4
			END AS ma,
			24 AS page_hdr,
			23 +
			CASE WHEN MAX(COALESCE(s.null_frac, 0)) > 0 THEN (7 + count(s.attname)) / 8 ELSE 0 END +
			CASE WHEN bool_or(att.attname = 'oid' AND att.attnum < 0) THEN 4 ELSE 0 END AS tpl_hdr_size,
			SUM((1 - COALESCE(s.null_frac, 0)) * COALESCE(s.avg_width, 0)) AS tpl_data_size
		FROM pg_attribute AS att
		JOIN pg_class AS tbl ON att.attrelid = tbl.oid
		JOIN pg_namespace AS ns ON ns.oid = tbl.relnamespace
		LEFT JOIN pg_stats AS s ON s.schemaname = ns.nspname AND s.tablename = tbl.relname AND s.attname = att.attname
		LEFT JOIN pg_class AS toast ON tbl.reltoastrelid = toast.oid
		WHERE NOT att.attisdropped
			AND tbl.relkind IN ('r', 'm')
		GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9
		) AS inner_subquery
	) AS mid_subquery
	) AS bloat_subquery
	WHERE schemaname = 'public'
	ORDER BY schema_name, table_name;
metrics:
	- schema_name:
		usage: "LABEL"
		description: "Schema name"
	- table_name:
		usage: "LABEL"
		description: "Table name"
	- bloat_size_kb:
		usage: "GAUGE"
		description: "Bloat size in KB"
	- bloat_size_percent:
		usage: "GAUGE"
		description: "Bloat size as a percentage"

데이터 소스 환경 변수 설정.
```
export DATA_SOURCE_NAME="postgresql://postgres_exporter:<password>@<POSTGRES_HOST>:5432/postgres?sslmode=require"
```
주: <POSTGRES_HOST>는 기본 끝점 FQDN입니다. OCI 콘솔의 PostgreSQL 클러스터 세부정보 페이지에서 찾을 수 있습니다.
PostgreSQL 익스포터를 시작합니다.

다음 명령을 사용하여 사용자 정의 queries.yaml 파일을 사용하여 PostgreSQL Exporter를 실행합니다. 그러면 9187 포트에서 수출자가 시작되고 출력이 exporter.log에 기록됩니다.
```
nohup /usr/local/bin/postgres_exporter --extend.query-path=/home/opc/queries.yaml --web.listen-address=":9187" > exporter.log 2>&1 &
```
주: 재부트 후에도 작업이 계속 실행되도록 하려면 systemd를 사용하여 서비스를 설정하는 것이 좋습니다.
수출자 확인.
```
curl http://localhost:9187/metrics
```

작업 3: Prometheus 설치 및 구성

Prometheus 설치

wget https://github.com/prometheus/prometheus/releases/download/v2.52.0/prometheus-2.52.0.linux-amd64.tar.gz

tar -xvf prometheus-2.52.0.linux-amd64.tar.gz

sudo mv prometheus-2.52.0.linux-amd64/prometheus /usr/local/bin/
sudo mv prometheus-2.52.0.linux-amd64/promtool /usr/local/bin/

Prometheus 구성 생성

다음 내용으로 /etc/prometheus/prometheus.yml 파일을 만듭니다.

global:
scrape_interval: 15s

scrape_configs:
- job_name: 'postgres_exporter'
	static_configs:
	- targets: ['127.0.0.1:9187']

Prometheus 시작
```
nohup prometheus --config.file=/etc/prometheus/prometheus.yml > prometheus.log 2>&1 &
```
주: 재부트 후에도 작업이 계속 실행되도록 하려면 systemd를 사용하여 서비스를 설정하는 것이 좋습니다.
대상 확인.

여기 방문: http://<instance_ip>:9090/targets.

작업 4: Grafana 설치 및 구성

Grafana 설치하기.

sudo yum install -y https://dl.grafana.com/oss/release/grafana-10.4.2-1.x86_64.rpm
sudo systemctl enable grafana-server
sudo systemctl start grafana-server

Grafana에 로그인합니다.

URL http://<instance_ip>:3000로 이동하고 Username을 admin으로, Password를 admin로 입력합니다.
Grafana에서 데이터 소스를 추가합니다.
1. 연결, 데이터 소스로 이동하고 데이터 소스 추가를 누릅니다.
2. Prometheus를 선택합니다.
3. URL에 http://localhost:9090을 입력합니다.
PostgreSQL 대시보드 임포트.
1. 대시보드 임포트를 선택합니다.
2. Grafana 대시보드에서 9628 대시보드 ID를 임포트합니다.

작업 5: Grafana의 PostgreSQL Metrics를 활용한 OCI 데이터베이스 살펴보기

다음 이미지는 최종 대시보드를 보여줍니다.

대시보드 예

Grafana의 탐색 탭에서 사용자 정의 queries.yaml 파일에서 노출된 측정 단위를 봅니다.
다음에 대한 시각화 추가:
- 가장 느린 최상위 SQL 문
- 최상위 사용자입니다.
- 활성 질의입니다.
- 최상위 클라이언트 IP입니다.
- 질의 빈도(질의라고 함).

작업 6: Grafana에서 PostgreSQL Bloat Metrics를 사용하여 OCI 데이터베이스에 대한 경보 설정

부피 측정 단위 수집.
- 사용자정의 SQL 질의는 pg_class, pg_namespace, pg_attribute 및 pg_stats와 같은 PostgreSQL 시스템 카탈로그 뷰를 기반으로 테이블 bloat를 예측하는 데 사용되었습니다.
- 이 query는 Task 2.2와 같이 queries.yaml 파일에 추가하여 PostgreSQL Exporter를 통해 커스텀 Metric로 표시되었으며 이후 Prometheus에서 폐기되었습니다.
경보 규칙 구성.
1. 경보로 이동하고 Grafana에서 경보 규칙을 누릅니다.
2. + New alert rule을 누릅니다.
3. 경보 규칙의 이름을 입력합니다.
4. 프로메테우스를 데이터 소스로 선택합니다.
5. 질의 및 경보 조건 정의 섹션에서 다음 정보를 입력합니다.
  - table_bloat_bloat_size_percent 측정항목을 검색합니다.
  - 경보 조건 작성기에서 다음 정보를 입력합니다.
    - 입력 설정: A를 선택합니다.
    - 조건: IS ABOVE 30을 선택합니다.
    주: 미리보기를 눌러 현재 조건에서 경보가 실행될지 여부를 검증할 수 있습니다.
6. 평가 동작 설정 섹션에서 다음 정보를 입력합니다.
  1. + 새 평가 그룹을 누릅니다.
  2. 평가 그룹: postgres-bloat-alerts를 입력합니다.
  3. 평가 간격: 5m을 선택합니다(Grafana에서 경보 규칙을 평가하는 빈도를 정의함).
  4. 보류 기간: 5m로 설정합니다. 경보가 트리거되기 전에 연속 5분 동안 true로 유지되어야 합니다. 평가 간격보다 크거나 같아야 합니다.)
7. 규칙 저장 및 종료를 눌러 경보를 활성화합니다.
Configure Email Alerting through SMTP (Gmail)

Grafana에서 경보에 대한 전자 메일 알림을 사용으로 설정하려면 grafana.ini 파일의 smtp 섹션을 구성합니다.
1. Grafana를 설치한 OCI Compute 인스턴스에서 Grafana 구성 파일을 편집합니다.
```
sudo nano /etc/grafana/grafana.ini
```
2. smtp 섹션의 주석 처리를 해제하고 다음 콘텐츠로 업데이트합니다.
```
[smtp]
enabled = true
host = smtp.gmail.com:587
user = your_email@gmail.com
password = ***************
skip_verify = true
from_address = your_email@gmail.com
from_name = Grafana
ehlo_identity = localhost
startTLS_policy = OpportunisticStartTLS
enable_tracing = false
```
  참고: Gmail을 사용하려면 2단계 확인을 사용으로 설정하고 Google 계정 설정에서 앱 비밀번호를 생성해야 합니다. 일반 Gmail 암호를 사용하지 마십시오.
3. 구성을 저장한 후 다음 명령을 사용하여 변경 사항을 적용하려면 Grafana를 다시 시작합니다.
```
sudo systemctl restart grafana-server
```
경보 통지에 대한 연락처 설정.

연락처는 전자메일, Slack, Webhook 등과 같은 규칙이 트리거될 때 Grafana에서 경보를 보내는 위치와 방법을 정의합니다.
1. 경보로 이동하고 Grafana에서 연락처를 누릅니다.
2. + 새 연락처를 눌러 연락처를 생성하거나 기존 연락처를 선택하고 편집합니다.
3. 전자메일 연락처를 구성하려면 다음 정보를 입력합니다.
  - 이름: 연락처 이름(예: grafana-default-email)을 입력합니다.
  - 통합: 전자메일을 선택합니다.
  - 주소: 전자메일 주소를 하나 이상 입력합니다.
4. 테스트를 눌러 전자메일 전달을 확인합니다.
5. 연락처 저장을 누릅니다.
통지 정책 구성.

Grafana의 알림 정책은 경보가 연락처로 라우팅되는 방법과 시기를 제어합니다. 연락처를 설정한 후 해당 연락처에 경보를 연결하기 위한 통지 정책을 생성해야 합니다.
1. 경보로 이동하고 Grafana에서 통지 정책을 누릅니다.
2. 기본 정책 또는 + 새로 만들기 정책에서 편집을 누릅니다.
3. 기본 연락처에서 연락처(grafana-default-email)를 선택합니다.
4. 그룹화 기준에서 다음과 같은 레이블 값을 기반으로 경보를 그룹화할 수 있습니다.
  - grafana_folder
  - alertname
5. 타이밍 옵션을 누르고 다음 정보를 입력할 수 있습니다.
  - 그룹 대기: 30s을 선택합니다. 이 시간은 그룹에서 첫번째 경보를 전송하기 전에 대기할 시간입니다.
  - 그룹 간격: 5m를 선택합니다. 이 시간은 그룹화된 경보의 일괄 처리 사이의 시간입니다.
  - 반복 간격: 30m을 선택합니다. 이 시간은 계속 실행 중인 경우 동일한 경보가 재전송되는 시간입니다.
6. 저장하려면 기본 정책 업데이트를 누릅니다.
Trigger and Verify Alert(알림 트리거 및 확인).
1. 임계값 위반 시뮬레이션(선택 사항)
  
  경보 트리거를 테스트하려면 다음 중 하나를 수행합니다.
  - 일시적으로 경보 임계값을 낮춥니다(예: bloat_size_percent > 0 설정).
    
    또는
  - 대형 테스트 테이블에 대해 반복되는 UPDATE 또는 DELETE 작업을 수행하여 bloat를 소개합니다.
2. 평가 대기.
  
  Grafana는 정의된 평가 간격(예: 5분마다)에 따라 경보 조건을 검사합니다. 보류 기간(예: 5분)에 대해 조건이 참이면 경보가 실행됩니다.
3. 이메일 딜리버리 확인.
  
  트리거되면 구성된 SMTP 접점(예: Gmail)을 통해 경보가 전송됩니다. Grafana의 알림 이메일에 대한 수신자 주소의 받은 편지함을 선택합니다.
4. Grafana UI 피드백.
  
  경보 상태를 보려면 경보로 이동하고 경보 규칙을 누릅니다. 경보 규칙 페이지에 실행 중과 같은 상태 표시기가 표시됩니다.

승인

작성자 - Irine Benoy(오픈소스 데이터 블랙 벨트)

추가 학습 자원

docs.oracle.com/learn에서 다른 랩을 탐색하거나 Oracle Learning YouTube 채널에서 더 많은 무료 학습 콘텐츠에 액세스하세요. 또한 education.oracle.com/learning-explorer를 방문하여 Oracle Learning Explorer가 되십시오.

제품 설명서는 Oracle Help Center를 참조하십시오.

제목 및 저작권 정보

Monitor an OCI Database with PostgreSQL using Prometheus and Grafana

G39870-01