Mbiri ya gawo la PostgreSQL yogwira ntchito - kukulitsa kwatsopano kwa pgsentinel

Kampaniyo pgsentinel adatulutsa pgsentinel yowonjezera ya dzina lomwelo (github chosungira), zomwe zimawonjezera pg_active_session_history view ku PostgreSQL - mbiri ya magawo omwe akugwira ntchito (mofanana ndi Oracle's v$active_session_history).

Kwenikweni, izi ndizithunzi zachiwiri ndi chiwiri kuchokera pg_stat_activity, koma pali mfundo zofunika:

1. Zidziwitso zonse zomwe zasonkhanitsidwa zimasungidwa mu RAM yokha, ndipo kuchuluka kwa kukumbukira kumayendetsedwa ndi kuchuluka kwa zosungidwa zomaliza.
2. Gawo la queryid liwonjezedwa - funso lomwelo kuchokera ku pg_stat_statements extension (kukhazikitsanso kofunika).
3. The top_level_query field yawonjezedwa - mawu afunso omwe funso lapano likutchedwa (ngati mukugwiritsa ntchito pl/pgsql)

Mndandanda wathunthu wamagawo a pg_active_session_history:

      Chigawo | Type ------------------+------------------------- ash_time | timestamp yokhala ndi zone ya nthawi datid | oid datename | mawu pid | integer usesyid | oid usename | text application_name | text client_addr | text client_hostname | text client_port | integer backend_start | timestamp yokhala ndi zone ya nthawi xact_start | timestamp yokhala ndi zone ya nthawi query_start | timestamp yokhala ndi zone ya nthawi state_change | sitepe yanthawi yokhala ndi zone wait_event_type | text wait_chochitika | mawu akuti | text backend_xid | xid backend_xmin | xid pamwamba_level_query | funso lolemba | text queryid | bigint backend_type | mawu                     

Palibe phukusi lokonzekera lokonzekera panobe. Iwo amati kukopera magwero ndi kusonkhanitsa laibulale nokha. Choyamba muyenera kukhazikitsa phukusi la "devel" la seva yanu ndikukhazikitsa njira yopita ku pg_config mu PATH variable. Timasonkhanitsa:

cd pgsentinel/src
kupanga
pangani kukhazikitsa

Onjezani magawo ku postgres.conf:

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

# ziwerengero zaposachedwa zomwe zasungidwa
pgsentinel_ash.max_entries = 10000

Yambitsaninso PostgreSQL ndikupanga chowonjezera:

pangani pgsentinel yowonjezera;

Zomwe zasonkhanitsidwa zimatithandiza kuyankha mafunso monga:

• Ndi magawo ati odikirira omwe mudakhala nawo nthawi zambiri?
• Ndi magawo ati omwe anali achangu kwambiri?
• Kodi ndi zopempha ziti zomwe zinali zogwira mtima kwambiri?

Mukhoza, ndithudi, kupeza mayankho a mafunsowa pogwiritsa ntchito mafunso a SQL, koma ndizosavuta kuwona izi pazithunzi powonetsa nthawi yachidwi ndi mbewa. Mutha kuchita izi ndi pulogalamu yaulere PASH-Wowonera (mutha kutsitsa ma binaries omwe asonkhanitsidwa mgawoli Mabuku).

Poyambira, PASH-Viewer (kuyambira ku 0.4.0) imayang'ana kupezeka kwa pg_active_session_history view ndipo ngati ilipo, imanyamula mbiri yonse yomwe yasonkhanitsidwa kuchokera pamenepo ndikupitiriza kuwerenga deta yatsopano yomwe ikubwera, kukonzanso graph masekondi 15 aliwonse.

Source: www.habr.com