PostgreSQL taariikhda fadhiga firfircoon - kordhinta pgsentinel cusub

Shirkad pgsentinel sii daayay kordhinta pgsentinel ee isla magaca (kaydka github), kaas oo ku dara pg_active_session_history view PostgreSQL - taariikhda fadhiyada firfircoon (oo la mid ah Oracle's v$active_session_history).

Asal ahaan, kuwani waa sawir-qaadis-labaad-labaad-labaad ee pg_stat_activity, laakiin waxaa jira dhibco muhiim ah:

1. Dhammaan macluumaadka la ururiyey waxa lagu kaydiyaa RAM oo keliya, iyo xaddiga xusuusta la isticmaalo waxaa lagu nidaamiyaa tirada diiwaannada kaydsan ee ugu dambeeya.
2. Goobta su'aalaha ayaa lagu daray - isla su'aal ka timid pg_stat_statements kordhinta (ku-rakici hore ayaa loo baahan yahay).
3. Meesha sare_level_query ayaa lagu daraa - qoraalka weydiinta laga soo wacay su'aasha hadda jirta (haddii la isticmaalayo pl/pgsql)

Liiska buuxa ee pg_active_session_history fields:

      Tiirka | Nooca -------------------+--------------------------------------waqtiga dambaska | timestamp leh aaga waqtiga datid | oid datename | qoraalka pid | integer Usesid | oid username | qoraalka codsiga_name | qoraalka macmiil_addr | qoraalka macmiilka_hostname | qoraalka macmiil_port | integer backend_start | timestamp leh aaga waqtiga xact_start | timestamp oo wata query_start | timestamp leh aaga wakhtiga gobolka_beddel | timestamp leh aagga waqtiga sugitaanka_event_type | qoraal sug_dhacdo | qoraalka gobolka | text backend_xid | xid backend_xmin | xid top_level_question | weydiin qoraal | text queryid | bigint backend_type | qoraal                     

Weli ma jirto xirmo diyaarsan oo la rakibay Waxaa lagu talinayaa inaad soo dejiso ilaha oo aad adigu ururiso maktabadda. Marka hore waxaad u baahan tahay inaad ku rakibto xirmada "devel" ee serverkaaga oo aad dejiso dariiqa pg_config ee doorsoomiyaha PATH. Waxaan aruurineynaa:

cd pgsentinel/src
dhigi
samee rakibo

Ku dar cabbirada postgres.conf:

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

# tirada diiwaanadii dhawaa ee lagu hayo xusuusta
pgsentinel_ash.max_entries = 10000

Dib u bilow PostgreSQL oo samee kordhin:

abuuro pgsentinel kordhin;

Macluumaadka la ururiyey ayaa noo ogolaanaya inaan ka jawaabno su'aalaha sida:

• Waa maxay fadhiyada sugitaanka ee aad wakhtiga ugu badan ku bixisay?
• Kulamadee ayaa ahaa kuwa ugu firfircoon?
• Waa maxay codsiyadii ugu firfircoonaa?

Waxaad, dabcan, u heli kartaa jawaabaha su'aalahan adigoo isticmaalaya weydiimaha SQL, laakiin way ku habboon tahay inaad tan muuqaal ahaan ku aragto garaafka adigoo muujinaya muddada u dhexeeya xiisaha jiirka. Taas waxaad ku samayn kartaa barnaamij bilaash ah PASH-Daawade (waxaad kala soo bixi kartaa binary-ga la ururiyay ee qaybta War).

Marka la bilaabayo, PASH-Daawade (laga bilaabo nooca 0.4.0) wuxuu hubiyaa joogitaanka pg_active_session_taariikheedka haddii uu jirona, wuxuu ka soo shubayaa dhammaan taariikhda la ururiyay oo wuxuu sii wadaa inuu akhriyo xogta cusub ee soo socota, isagoo cusbooneysiinaya garaafka 15 ilbiriqsi kasta.

Source: www.habr.com