Itan igba ti nṣiṣe lọwọ PostgreSQL - itẹsiwaju pgsentinel tuntun

Duro pgsentinel tu itusilẹ pgsentinel ti orukọ kanna (ibi ipamọ github), eyiti o ṣafikun wiwo pg_active_session_history si PostgreSQL — itan-akọọlẹ awọn akoko ti nṣiṣe lọwọ (bii Oracle's v$active_session_history).

Ni pataki, iwọnyi jẹ awọn ifaworanhan keji-si-keji lati pg_stat_activity, ṣugbọn awọn aaye pataki wa:

1. Gbogbo alaye ti a kojọpọ ti wa ni ipamọ nikan ni Ramu, ati iye iranti ti o jẹ ni ofin nipasẹ nọmba awọn igbasilẹ ti o fipamọ kẹhin.
2. Aaye queryid ti wa ni afikun - ibeere kanna lati pg_stat_statements itẹsiwaju (fifi sori ẹrọ nilo).
3. Aaye top_level_query ti wa ni afikun - ọrọ ti ibeere lati eyiti a ti pe ibeere lọwọlọwọ (ti o ba nlo pl/pgsql)

Akojọ kikun ti awọn aaye pg_active_session_history:

      iwe | Iru ------------------+----------------------------------- ash_time | timestamp pẹlu agbegbe aago datid | oid datename | ọrọ pid | odidi useyssid | oid username | ọrọ application_name | ọrọ client_addr | ọrọ client_hostname | ọrọ client_port | odidi backend_start | timestamp pẹlu agbegbe aago xact_start | timestamp pẹlu aago agbegbe query_start | timestamp pẹlu agbegbe aago state_change | timestamp pẹlu aago agbegbe wait_event_type | ọrọ wait_iṣẹlẹ | ọrọ ipinle | ọrọ backend_xid | xid backend_xmin | xid top_level_query | ibeere ọrọ | ọrọ queryid | bigint backend_type | ọrọ                     

Ko si package ti o ṣetan fun fifi sori ẹrọ sibẹsibẹ. O daba lati ṣe igbasilẹ awọn orisun ati pejọ ile-ikawe funrararẹ. O nilo akọkọ lati fi idii “idagbasoke” sori olupin rẹ ki o ṣeto ọna si pg_config ni oniyipada PATH. A gba:

cd pgsentinel/src
ṣe
ṣe fi sori ẹrọ

Fi paramita kun postgres.conf:

shared_preload_libraries = 'pg_stat_statements, pgsentinel'
track_activity_query_size = 2048
pg_stat_statements.track = gbogbo

# nọmba awọn igbasilẹ aipẹ ti o waye ni iranti
pgsentinel_ash.max_entries = 10000

Tun PostgreSQL bẹrẹ ki o ṣẹda itẹsiwaju:

ṣẹda pgsentinel itẹsiwaju;

Alaye ti a kojọpọ gba wa laaye lati dahun awọn ibeere bii:

• Awọn akoko idaduro wo ni o lo akoko pupọ julọ lori?
• Awọn akoko wo ni o ṣiṣẹ julọ?
• Awọn ibeere wo ni o ṣiṣẹ julọ?

O le, nitorinaa, gba awọn idahun si awọn ibeere wọnyi ni lilo awọn ibeere SQL, ṣugbọn o rọrun diẹ sii lati wo eyi ni oju lori aworan kan nipa titọkasi awọn aaye arin ti iwulo pẹlu asin naa. O le ṣe eyi pẹlu eto ọfẹ kan PASH-Oluwo (o le ṣe igbasilẹ awọn alakomeji ti o gba ni apakan tu).

Nigbati o ba bẹrẹ, PASH-Viewer (ti o bẹrẹ lati ẹya 0.4.0) ṣayẹwo fun wiwa pg_active_session_history wiwo ati pe ti o ba wa, o gbe gbogbo itan-akọọlẹ ti o ṣajọpọ lati ọdọ rẹ ati tẹsiwaju lati ka data titun ti nwọle, n ṣe imudojuiwọn iwọn ni gbogbo iṣẹju-aaya 15.

orisun: www.habr.com