αα·ααααα - ααααααααΈα‘αΆααΆαα - αααα»ααααα·αα
αα·αααααα’αΆα
ααααΎα’αααΈααΆαα (α) R. Diesel α
Epigraphs α
α¬ααΏααααΆαα’αααΈααΌαα ααα»αααα’ααααααααααααααΌαααααΆααα·ααααααααααΌαα
αα
αΆαααΈα’ααΈαααΆαααααΆαααααααααααα·ααΈααααααΆααα
αα»ααααααΆ
αααααααΆααα’ααααααΌαααΆαααααΆααααααΌαα ααΆαααααα½αααΊα αααααα αααααΆααααΊααααΆααααααΆαααα·αααααΆαααααα½αααααα’ααααα·ααααααα»αααααα
ααΆαααα·αααααααΆαααΆααΆα αα αααα»ααααααΈα’ααααααααααΆαααααααα»α ααΉααα·αααΆαααΆααα·αααααΆαααα’α·α αα·αααααΉαααααΌαααααΆααΆα αα·αααααααΈααααααΆαααααΎαααααα αααααΆαααα·αα’αΆα ααααΎααααΆαα "AS IS" ααΆαααα
ααΈαα½ααααααΆαααααα·ααΆααααααααααααΆααα
ααΈααΈα αααααΆαααααΆααα»ααααα½α ααΆαα½αααΉαααΌαααααΆαααα·αααααααααα’αα·αα·αααα·αααααΆααα
ααΌα αααα ααΆααααααα·α αα·αααΆααα·αααααΆαααα»αααααααααΌαα αααα»αααα»ααααααααααΉαααααΌαααΆααααααα±αααα αααα»αα’αααααα
αααα ααααΆαα αααα’ααΆααααααααααααΉαααΈαα ααααΎααααααααα·αααααΆααααα αααα ααΎ GitHub α¬αααα ααααΆαα·ααααααα αααααααΆααΉααααα αΆαα
ααΆαα
αΆααααααΎαααααΏα -
α’αααΈαααααΆαααΎαα‘αΎαααΆαααααααα
αααα»αααααααααααΌαα
αααα»α - "
α ααα»α’αααΈααΆαααΆαααα»αααααΌαααΆαααΆααα’ααααα?
ααΆααΆααααααΎαααΆαα, ααΆααααΌα, ααΎααααΈαα»αα±ααααααα
αααα½αα’ααα, α
αα
αΆααααααααα»αααΏααααα»αααΆαα
αΌααα·ααααααα
ααΈααΈα ααΎααααΈαααα
αααΆααααααααααΌαα’αααΈαααααΆααααααα αααααΆαααααα½ααααα»ααα½α
αα
α αΎα ααααααααααα»αα
αΆααααααΎααααα
αααα α αΎαααααα
αααααααΆα
ααααα‘ααα
ααΆααΆααααααΎαααΆαα α αΎαααααΆαααααα»α - ααααΆααααα ααΆα’αΆα ααΆααααααααααααααΆααααααΆααααΆαα αα·ααα½ααα·αα±αααααααΎααααα‘αΎααα·α αα·ααα·αα±αααααααΌααα»ααα½α α αα αααα»αααΆααααααααααα, ααααΎα±αααααααΎαα‘αΎαααααααααααα’ααα (αα·αααα Khabrovsky) α αααααΆααα’αααΈαααααΆαααααααααα»ααα αααα»ααα·ααααααααααΊαααα·αα ααΏαα αααααΊααααααααααα·αα α αΎαβααΆαβααβααααβαααα·αβαα βααΆβααΆαβαα·αβααΊβααΆβαααα αΆβαα αα αααααβαα»αααααΆαβαα½α βαα βα αΎαα
ααΌα ααααααΌαα αΆααααααΎαααΊα α ...
ααΆααααααΎααααα αΆα
ααΆαα
PostgreSQL(10.5) ααΆααααα»αα
αααα»α (OLTP+DSS) ααΆααααα»αααααα·αααααααα
ααααΆα αααα αααααα»αααα AWS α
αα·αααΆαααΆααααα½ααα·αα·αααααΌαααααΆααα·αααααααα ααΆααααα½ααα·αα·αααα αααααΆαα
ααΆααααααααααααΌαααΆααααα αΆαααΆα§ααααα AWS αααααααΆααααα»αααΆαααααααα
ααΆαααααααααα·α
αα½α
αααα»αα
ααΆααααΌαααΆαααΆαααΆαα
αααα½ααα·αα·αααααααΎαααΆα αα·αααααΆαααΆαααααΌαααααΆααα·αααααα ααααααα αα·αααΆαααααααΆαααααΌαααΎααααΈαααααΎαααααα·αααααΆααααα½αααΌαααααΆααα·αααααααααααα
ααΆαααααΆαααααΈα α¬ααΆααα·ααΆααααααααααααΆα
ααΎααααΈα αΆααααααΎα α αΌαααΎαααααΆααΆααα·ααΆααααααΎααααααΆαααααααααΆααααα αΆααΈα ααα»α αααα·αααααΆαααααΆααα·ααΆαααααααααααα’ααααααααααα αα·ααααα αΆαααααΆαααα·ααααα α αΎαα’αα»ααααΆαα±ααα’ααααααααααααααΆααΆααα αααα»ααααααΈαα»ααααα·α αααααααΆαααΆαα½αααΉαα’ααααααααααα αα·αααΆαααΆααααα ααααΆααααααααααα
αααααΎαααΈ 1 - "ααααΎααΆαααΆααααααΌαααΆα"
ααΎαβαα»αβα’αααΈβαααααβαααΆαβααΌα
βααΆβαααα ααααα·αααΎα’αα·αα·αααα·ααααα
α·αααααΉαα’αααΈαα½ααα
αααα»ααα»αααΆα ααααΎαααΆαααααΌαααααΆααα·αααααα α¬αααααα·ααΈααα ααΆααααΉαααΌαααααΉαααααα·ααααα DBA ααΆααααα’ααΈααα α¬ααααααααΎαα§ααααααα·α ααα»αα
αααα»ααααα’αααααα»αααα
αα·ααααα ααΆαααα½αααΆαααΌαααααΉααα½α ααΉααααααΈαααα αΆ ααααααααααααααΆα α¬αα·αααΆαααααα αΆ ααααααααΉαααΆα’αααΈαααΉααααααααΆαααααααα½αα―α α αΎααααΆαααΆααααα α’αααΈαααΉαααααΌαααααααα
α
αααααα»ααααααΆααααααα
ααααααΈ αα·αααααΌααΆαα ααααΆαααΆα αα·αααααΆαααΆαααααααΈ αα·αααααΌααΆααα
1. ααααΆαα’αααΈααααααααΎααααΈααααΎ
2. αααααααΆαα±ααΆαα
αααααα
αα·αααααααα
3. αααααααΆα
αααΎααααα’αααα’αΆα
α
αααΆαααΆαααΆαααααα
α
α·αααααααα’αααα
ααααΆαααΆα αα·αααααΆαααΆαα
1. αα·αααΌααα·αααΆαα α’αα·αα·ααααΉααα·αα’αααΈααααΉαααΆαααααΆαααΆ αα·ααα»αααα·αααααααα
αααα»ααα·αααααααα α αΎααα½ααααα½αα―ααααααααααΌααααα½αααΆ α ααα»α’αααΈααΆαααΆαααα»ααααααααΆααα±αααα½ααα? αααα·ααΆαααΊαααααααΌα
ααααΆ - αααα½ααααα½ααααααΊαα
ααααααα’αα·αα·αααα»αααααΆααα αΎααααααΈααΆα α αΎαα§ααααααααααααΊαααα ααΆβααΆβααΆαβαααααααααα
2. ααΆαα’αα·αααααααα·αααααααΊααΌαααα
3. ααΆαααααΆααααα»αααΆαααΆααααα
ααααααΆααα·αααΆααααα»α
αααααΎαααΈ 2 - "ααΆαααΆαα½α tambourines ααΆαααα·αααΆαααααααααΎα"
αααΆαααα 1- α ααα»α’αααΈααΆαααΆααΎαααααΌαααΆααααααααααααα½ααα·αα·ααα ααΎαααΉαααα½αααααΎααΆααα’ααα ααΎαααΎαααααΎαααΆααααα½αααααααααααααα
ααΆαααα
ααΆαα»αααααα·αααααα αα·ααα·αααααΆαααΆααααα ααΎααααααααααααααααα ααΆαααα’αααΈααααααααΆααα
αααα»αααΆααΆα αα·ααΆααααααΈ αα·αααΆααΆαααΆαααΆααααααα ααΆαααααα ααΎαααΆαααααΆα αα ααΆααΆα αααΆαααΆααα αααααααααα’αΆα α¬αα·αααΌαα
αααΆααα ααΏαααααΆαα - αααααΉαααΆααααα
αααΎαα
αααΆαααα 2αααααΎααααααααΆα - ααααΎαααΆαααΆααα·ααΆαααα’αααΈαααΆααα’αααααα
αααΆαααα 3- ααΎααααα»ααααα
αα―αααΆαααΆααααΆαααα½α ααΎαα α
α―αααΆαααααααααΆαααα - "ααΎααΎαααααΆααααΌαααααΆααα·αααααααααααααααΆ" α
αααΆαααα 4- α’αα·αα·αααααααΎαααΎαααΆαα’ααα
αΆαααααααααΆα αα αα·ααα½αααααα αααα·ααααα»αααΆαααΏααΆααααααααααααααααααΌαααααα - α₯α‘αΌααααα’αααΈαααΉαααααΎαααΆααααααΆααααΎαααΆααααααα α αΎα ααΆααααα½α αα·αααααΆαααΆαααΊα
αΆαα ααΆαααααααΆαα½αααααΆαα α·ααααααααα»αααααα½αααα ααΆααααααααααααααααΆααααΆαα·αααααααααααΎααααα»αααααΎααΆααααΆαααααΆαα ααΆααααα»αα’αα·ααααΆα
αααΆαααα 5- ααααΎααα αΆαααΈ 1 αααααααααΆαααααΆααα
ααααααΈ αα·αααααΌααΆαα ααααΆαααΆα αα·αααααΆαααΆαααααααΈ αα·αααααΌααΆααα
1. ααΈαα·αααααα’αααααααααααα αα·ααα·αααααααΊααΆαααα α’αΆα
ααααααααΆαααΆα αα·ααααααααα
ααααααααααΆαα α’αααΈβαααααβαααΆαβααΊβααΆβααΆαβααααΆααβααα’αΎαβα’αααβααΆααβααααΆβααΊβααΆβααΆαβααααβα
2. ααΈαα·αααααα’αα·αα·αααααα·αα’αΆαααααααα - ααΆαααααααααααΆααααΆα’αααααααΌαα’αααααααααααα·α
α αΎαα’αααΈαααΉαααααΎαααΆαα ααΆαα·αααΆαααααααααΎαα‘αΎααα ααα’ - αα·ααααααααααΊα’αα»αααα·ααααααα»αααΈαα·ααααααΆαα - α’αααααΉαααΆαααααΆαα
ααααΆαααΆα αα·αααααΆαααΆαα
1. αα·αααΌααα·αααΆαα ααΉαααΆαα’ααααααααααααΆααααααααααΆααααααΆααααααααΉαααααΎααΌα
ααααΆ ααα»ααααααΆαααααααααααΆαααααα·α
α α αΎαβααααα·αβααΎβααααααβααΊβααΌα
βααααΆ α ααα»βα’αααΈβααΆαβααΆβααααΌαβαααβααααααα ααααααααααααΉαααΆααα
αααααΆαααΆαααααα½ααααα feeder α
2. ααΆαα½αα±αααα»αα αααααααΆαααααΆαα’αααααααα·α
αα½α
αα½αα±αααα»αααα»ααααΆα
3. ααΌα
αα
αααα»ααααααα»α - αα·αααΆαααΆαα’αα·ααααααααα ααα»αααααααααΆαααα·ααααα αα»ααα·ααααα·ααΊααΆ αα·αααΌα
αααααΎαααΈαα½ααα αα
ααΈαααα’αααααααΌααααααΎα IDB ααΆαα·α
αα
α α αΎαααΆααααΌαααΆααααααααΆα αααα’αΆα
α
αααΆααααααΆααααΆαααααααααααααα»αααααΆααΈαααααΆααααααα’αααα αααααβα’αααβαα·αβα’αΆα
βααΎαβααβαααα½αβα―αβααΆα α’αααβααΆααβααααΆβαααααβααΈβα’αααα
αααααΎαααΈ 3- αα·αα αΆαααΆα ααααααΎαααααα α’αααααααΌααα·αααΆαααα·αα
αα·αααααααααΈαααα»αα αα»ααααααααααααΆαααΈα αααΆααΆαα½αααααΆααααααα
ααααΆ (α’αΌ αααααααΆαααα»αααΏαααααααΆααααααΊαααΊααααα»αααααααααααΈ 90)α αααααααΎαααααααααααα½ααα·αα·αααααααααααΎα ααααΆααααα α»α αα·αααααΎαααΆα α αΎαααΆααΌαα
ααΆααΆαααααΌαα’ααααααααααα (αααΆαα αα
ααΆαααααααΆααα’ααααααααΎααααααα½ααα)α
ααααααΈ αα·αααααΌααΆαα ααααΆαααΆα αα·αααααΆαααΆαααααααΈ αα·αααααΌααΆααα
1. αα·αα
αΆαααΆα
αααααααααΆααααααααΆαααααΎαα’αααΈαααααΆααααααΎααα½α
α αΎαα ααα αΎαααααΎα
2. αααααααααααα½ααα·αα·ααααα·αααααΌαααΆαααααααααααα»αααααααΈααααΎαα α αΎααα·αααΆααααΆααΆαααααααααα
3. αααααααααααα½ααα·αα·αααααΆαααΆαααΆααααααΆαααααααααααΆαα
αααααααΆαααααααααα
ααααΆαααΆα αα·αααααΆαααΆαα
1. αα·αααααβαααα»αβααααΈβαααβαα·αβαααβααΆβαα·αααααβααβααΊβααααΆααβααβααΆβα’αααβααααΎβααααΆααβααα·αβααβααααβα’αααβαααβα¬βα’αααβααααΎβααα»αααααα
2. α’αα·αα·ααααααΌαααααΏααΆααααΎαααααΌαααΆααα·ααααααααααΆααΌαα
ααΆαααα·αα
ααααα α αΎαααΆαααα·ααα½α α αΎαααΆααΌαα
ααα·ααΆαααααΆααααααΆαααααΌαααΆαα’αα»αααα αΎαααΉααα·αααααΆααααααΌαααα αααααΆααααα’αααααααΌααααα
ααααααΆαααΆα
ααααα‘αα ααααααα
ααΆααααααααααΆαααααΆααααααααααααΆααααΆαααα½αα ααΆαααααα ααααΌαα’αααααααΌααααααααΆαα ααα α αΎαααααααααααα α αΎαα’αα·αα·ααααΊααααα αααααΊααΆααααααΆαααααΈαα·ααααα
α’αααΈαααααααΌαααααΎ, Chernyshevsky? αααα½αααααα’αααααΊααΆαααααααααααΆααααΆααα (ααΆαα½α)
αα
αααα»αααααΈαα·αααααα αα·αααααΆαααΆααα
αα
α»αααααα α’αααα’αΆα
ααααΎαα»αααααΆααααα·α
ααααα½α
- ααααααααΎααααααααααααα½ααα·αα·αααααααΆαααααα½αααααααΎαα
ααΆααΆααα·αααΆαα αα·ααααααΆαααααααααα αααα»ααααααααααααααΆααα αααααΊααααΆααααα αα·ααα½αα±αααααα ααα»αααααααΆαα αα
ααΆααααααααΎα±ααααΆααΆααααααΆααααα½ααααααΆαααααα½αα’ααα αα·ααααααΌαααααααΆαααααααααΎααααΈαααααααΆαα§ααααααα·α ααα»ααααΆαα’αα»ααααα ααΎααααΈβαα»αβα±ααβααΎαβαααα½αβα―αβαααα·αβαααα»αβααααΆαααΆαβαα½α - "αα
βααΈααα αααα»αβαα·αβααΉαβααααααβααΆ ααβααΎαβααα αααα»αβαα·αβααΉαβα’αααΈβαα"α
ααΎα’αααΈαα ααΆαα»ααααααααα·αα·ααα»ααα·ααααα·αααααααΎαααα:
αααα»α:
1. ααΆαα½αα±ααα
αΆααα’αΆααααααα αααΆαβα αα
βααΆααβααβαα½αβα±ααβα
αΆααβα’αΆααααααβααΆαβ "ααααα½αβα―αααΆαβαα·αααααα ααααααβα
αααααβααΆααΆαα
2. ααΆαααααααΊααΆααααΆαααααΈ αα·αααΆαα’αα·ααααααααααΈα ααααα
αααα’ααΆαααα·αααΌααα·αααΆααααΉααααααα±ααααααααααααΈαα·αααααΌααΆαααααααααΉαααα½αααΆαα
αα»ααα·ααααα·:
1. ααααΌαααααΎααΆαα ααααΎααΆαα
αααΎαα
2. α’αααααΉαααααΌαααααααα±ααααΆααααααΆααααΌαα’αααααα αα·ααααααααααααααααΆαααΆααα’ααα
3. α’αααΈαα½αααΉαααααΌαααααααααααααααΆααααα½αααααααααΆααααααΆαααα·ααααα - αααααααΆ - ααααΌαααΆααααααααααααα
4. α’αΆααααααααα»ααα·ααα·ααααααΆαα
α·ααααααα»αα - ααΆαααααα ααααΆαααΌα
ααΆ "αα·ααααααΆααααα»α αα·ααααααΆαααααα ααα»ααααααΆααααααΌα
ααααα·αααααΆαα" α’αΆα
ααΉαααααααΆα
ααα
α’αααααΆαα·αααααα»αα’αααΈαα½ααα·αααΉαααααΆαααααΆαα
ααΌα
αααα ααΆααααααΆαα
αΆααααααΎαα
αααα·αααΌαα - αααααααΆααα
(ααΌαααΆααααααααα
ααααΈα’ααααα Β«
ααΆαααααααα
- ααΌαααααΆααα·ααααααααααα ααααΌαααΆαααα‘αΎαααΆαα½αααααααααααα PostgreSQL αααααααΆα βpg_stat_statementsβ α
- αα αααα»αααααΆαααα·αααααααααα½ααα·αα·ααα ααΎααααααΎααααα»αααΆααΆαααααΆααααααΎααααΈαααααΆαα»ααααααααα· pg_stat_statements αα ααααΆααααΆαααααΌα αα·αααΎααααΈααααααα ααΆαααααααααααααα αα·αααΆααααα½ααα·αα·αααααΆαααα’ααΆααα
- αα ααΎαααΆαααΈααααα½ααα·αα·ααα ααΎααααααΎααααα»αααααααααΈα bash αα½αααΆαααααααα·ααΈαααααΆαααααααΎαα§ααααααα·α ααα»αα αααα»ααααααααααααα»αααα
ααΆααΆαααααΆαααα
ααΎααααΈα
αΆααααααΎαααΆαα½α ERD αααααΆαααααααααΆααααααΆααααααααΆααα α’αααΈαααααΆαααΎαα‘αΎααα
ααΈαααα
ααα
ααΆααα·αααααΆααααααααααΆααΆαα
α»α - αααΆαααΈα, α
ααα»α
αααααΆαααα
α§ααΆα ααα
ααΌαααααΆααα·αααααα - αααααΎαααΌαααααΆααα·αααααα
pg_stat_history - ααΆααΆααααααααα·ααΆααααααααααΆαααααααΆαα»αααΌαααααααααα’αΆαααααααα·αααααΆα pg_stat_statements ααααΌαααααΆααα·ααααααααααα
metric_αααααΆαα»αααα - αα
ααΆαα»ααααααααΌα
ααΆααααΆαα’αα»αααα
metric_config - ααΆαααααααα
ααΆαααααααααααααααα»αααα
ααΆα - ααααααααΆααααΆαααααααΆααααααΎααααααα»αααααΌαααΆααααα½ααα·αα·ααα
metric_alert_history - αααααααα·ααααΆααααααΆαα’αααΈααΆαα’αα»αααα
log_query - ααΆααΆαααααΆαααααααααΆαααααααΆαα»ααααααααααΆαααααΆααααα
ααααΈα―αααΆααααααα ααα» PostgreSQL αααααΆαααΆαααααΈ AWS
ααΌαααααΆα - αααΆαααΆαααααααααααααααααααααΎααΆααΌαααααΆα
ααα»αααα·ααααα½ααα·αα·ααα - ααΆαααααααα
ααΆαααααααααααααααααααΆαααα·αα·αααααΎαααααΆαααΆαααααΌαααααΆααα·αααααα
checkpoint_alert_history - αααααααα·αααααΆαααααΆααααα½ααα·αα·αααααααΆαααΆαααΌαααααΆααα·αααααααααααα
pg_stat_db_queries - ααΆααΆαααααΆααααααααααΎααααα
αααααα ααα»βαααααααΆα - ααΆααΆαααααΆαααααααααα ααα»αααααααΆα
trap_oid - ααΆααΆαααααΆααααααααααα
ααΆααααααααα’ααααΆαα
ααααΆααααΆαααΈ 1 - αααααΌααααα·αα·ααααα·ααααα·ααΆα αα·αααα½αααΆααααΆαααΆααα
ααΆααΆααα½αααααΌαααΆαααααΎααΎααααΈαααααΆαα»αααααααΆααααα·αα·α pg_stat_history
αα
ααΆααααααααααΆααΆα pg_stat_history
ααΆααΆα "public.pg_stat_history" αα½α | αααααα | α’ααααααααα --------------------+-------------------------+---- -------------------------------- id | α ααα½αααα | αα·αααα null default nextval('pg_stat_history_id_seq'::regclass) snapshot_timestamp | ααααΆαααααααΆαααααααΆαααααααααααααΆ | database_id | α ααα½αααα | dbid | α’ααΈα | userid | α’ααΈα | αααα½α | αα | αααα½α | α’ααααα | ααΆαα α ααΌαααααα | αα | αααααααΆααα»α | ααΆαααΆααααΆαααααααα | min_time | ααΆαααΆααααΆαααααααα | max_time | ααΆαααΆααααΆαααααααα | mean_time | ααΆαααΆααααΆαααααααα | stddev_time | ααΆαααΆααααΆαααααααα | αα½α | αα | shared_blks_hit | αα | shared_blks_read | αα | shared_blks_dirtied | αα | shared_blks_written | αα | local_blks_hit | αα | local_blks_read | αα | local_blks_dirtied | αα | local_blks_written | αα | temp_blks_read | αα | temp_blks_ααααα | αα | blk_read_time | ααΆαααΆααααΆαααααααα | blk_write_time | ααΆαααΆααααΆαααααααα | baseline_id | α ααα½αααα | αα·αα·ααααα "pg_stat_history_pkey" PRIMARY KEY, btree (id) "database_idx" btree (database_id) "queryid_idx" btree (queryid) "snapshot_timestamp_idx" btree (snapshot_timestamp) Foreign_base"KeyData" ) ααΌαααααΆααα·ααααααααα(id ) αα ααΎααΆααα»α CASCADE
ααΌα αααα’αααα’αΆα ααΎαααΎα ααΆααΆαααααΆααααααΆαα·αααααααα·αααααΆααα½αααα»αααααα pg_stat_statements αα αααα»αααΌαααααΆααα·ααααααααααα α
ααΆαααααΎααααΆααααΆααΆααααααΊααΆααααααΆααα
pg_stat_history ααΉαααααΆαα±αααααα·αα·αααααααααΆαααααα·ααααα·αααα½ααααααΆααααααααΈαα½ααα αα
ααΎαααααααΈαα½αααααααΆααααΈαααααααΆααΆααααα·αα· pg_stat_statements αααααα‘αΎααα·αααΆαα½α pg_stat_statements_reset().
α
αααΆα: αααα·αα·ααααΌαααΆααααααΌααααααΆααααααΎαααααΆαααααααααΎαααΈ 1 αα·ααΆααΈα
αααα»ααααα
αΌαααΆααΆα pg_stat_history
--pg_stat_history.sql
CREATE OR REPLACE FUNCTION pg_stat_history( ) RETURNS boolean AS $$
DECLARE
endpoint_rec record ;
database_rec record ;
pg_stat_snapshot record ;
current_snapshot_timestamp timestamp without time zone;
BEGIN
current_snapshot_timestamp = date_trunc('minute',now());
FOR endpoint_rec IN SELECT * FROM endpoint
LOOP
FOR database_rec IN SELECT * FROM database WHERE endpoint_id = endpoint_rec.id
LOOP
RAISE NOTICE 'NEW SHAPSHOT IS CREATING';
--Connect to the target DB
EXECUTE 'SELECT dblink_connect(''LINK1'',''host='||endpoint_rec.host||' dbname='||database_rec.name||' user=USER password=PASSWORD '')';
RAISE NOTICE 'host % and dbname % ',endpoint_rec.host,database_rec.name;
RAISE NOTICE 'Creating snapshot of pg_stat_statements for database %',database_rec.name;
SELECT
*
INTO
pg_stat_snapshot
FROM dblink('LINK1',
'SELECT
dbid , SUM(calls),SUM(total_time),SUM(rows) ,SUM(shared_blks_hit) ,SUM(shared_blks_read) ,SUM(shared_blks_dirtied) ,SUM(shared_blks_written) ,
SUM(local_blks_hit) , SUM(local_blks_read) , SUM(local_blks_dirtied) , SUM(local_blks_written) , SUM(temp_blks_read) , SUM(temp_blks_written) , SUM(blk_read_time) , SUM(blk_write_time)
FROM pg_stat_statements WHERE dbid=(SELECT oid from pg_database where datname=current_database() )
GROUP BY dbid
'
)
AS t
( dbid oid , calls bigint ,
total_time double precision ,
rows bigint , shared_blks_hit bigint , shared_blks_read bigint ,shared_blks_dirtied bigint ,shared_blks_written bigint ,
local_blks_hit bigint ,local_blks_read bigint , local_blks_dirtied bigint ,local_blks_written bigint ,
temp_blks_read bigint ,temp_blks_written bigint ,
blk_read_time double precision , blk_write_time double precision
);
INSERT INTO pg_stat_history
(
snapshot_timestamp ,database_id ,
dbid , calls ,total_time ,
rows ,shared_blks_hit ,shared_blks_read ,shared_blks_dirtied ,shared_blks_written ,local_blks_hit ,
local_blks_read,local_blks_dirtied,local_blks_written,temp_blks_read,temp_blks_written,
blk_read_time, blk_write_time
)
VALUES
(
current_snapshot_timestamp ,
database_rec.id ,
pg_stat_snapshot.dbid ,pg_stat_snapshot.calls,
pg_stat_snapshot.total_time,
pg_stat_snapshot.rows ,pg_stat_snapshot.shared_blks_hit ,pg_stat_snapshot.shared_blks_read ,pg_stat_snapshot.shared_blks_dirtied ,pg_stat_snapshot.shared_blks_written ,
pg_stat_snapshot.local_blks_hit , pg_stat_snapshot.local_blks_read , pg_stat_snapshot.local_blks_dirtied , pg_stat_snapshot.local_blks_written ,
pg_stat_snapshot.temp_blks_read , pg_stat_snapshot.temp_blks_written , pg_stat_snapshot.blk_read_time , pg_stat_snapshot.blk_write_time
);
RAISE NOTICE 'Creating snapshot of pg_stat_statements for queries with min_time more than 1000ms';
FOR pg_stat_snapshot IN
--All queries with max_time greater than 1000 ms
SELECT
*
FROM dblink('LINK1',
'SELECT
dbid , userid ,queryid,query,calls,total_time,min_time ,max_time,mean_time, stddev_time ,rows ,shared_blks_hit ,
shared_blks_read ,shared_blks_dirtied ,shared_blks_written ,
local_blks_hit , local_blks_read , local_blks_dirtied ,
local_blks_written , temp_blks_read , temp_blks_written , blk_read_time ,
blk_write_time
FROM pg_stat_statements
WHERE dbid=(SELECT oid from pg_database where datname=current_database() AND min_time >= 1000 )
'
)
AS t
( dbid oid , userid oid , queryid bigint ,query text , calls bigint ,
total_time double precision ,min_time double precision ,max_time double precision , mean_time double precision , stddev_time double precision ,
rows bigint , shared_blks_hit bigint , shared_blks_read bigint ,shared_blks_dirtied bigint ,shared_blks_written bigint ,
local_blks_hit bigint ,local_blks_read bigint , local_blks_dirtied bigint ,local_blks_written bigint ,
temp_blks_read bigint ,temp_blks_written bigint ,
blk_read_time double precision , blk_write_time double precision
)
LOOP
INSERT INTO pg_stat_history
(
snapshot_timestamp ,database_id ,
dbid ,userid , queryid , query , calls ,total_time ,min_time ,max_time ,mean_time ,stddev_time ,
rows ,shared_blks_hit ,shared_blks_read ,shared_blks_dirtied ,shared_blks_written ,local_blks_hit ,
local_blks_read,local_blks_dirtied,local_blks_written,temp_blks_read,temp_blks_written,
blk_read_time, blk_write_time
)
VALUES
(
current_snapshot_timestamp ,
database_rec.id ,
pg_stat_snapshot.dbid ,pg_stat_snapshot.userid ,pg_stat_snapshot.queryid,pg_stat_snapshot.query,pg_stat_snapshot.calls,
pg_stat_snapshot.total_time,pg_stat_snapshot.min_time ,pg_stat_snapshot.max_time,pg_stat_snapshot.mean_time, pg_stat_snapshot.stddev_time ,
pg_stat_snapshot.rows ,pg_stat_snapshot.shared_blks_hit ,pg_stat_snapshot.shared_blks_read ,pg_stat_snapshot.shared_blks_dirtied ,pg_stat_snapshot.shared_blks_written ,
pg_stat_snapshot.local_blks_hit , pg_stat_snapshot.local_blks_read , pg_stat_snapshot.local_blks_dirtied , pg_stat_snapshot.local_blks_written ,
pg_stat_snapshot.temp_blks_read , pg_stat_snapshot.temp_blks_written , pg_stat_snapshot.blk_read_time , pg_stat_snapshot.blk_write_time
);
END LOOP;
PERFORM dblink_disconnect('LINK1');
END LOOP ;--FOR database_rec IN SELECT * FROM database WHERE endpoint_id = endpoint_rec.id
END LOOP;
RETURN TRUE;
END
$$ LANGUAGE plpgsql;
ααΆαααααααααααΆααααΈααααααααΆααααΆαααα½ααα αααα»αααΆααΆα pg_stat_history ααΎαβααΉαβααΆαβαααα»αβααΌαααΆαβααβααΆαα·ααΆβααβααΆααΆα pg_stat_statements ααΌαααααΆααα·ααααααααααα α
ααΆαααΆαααΆααααα·αααααΆαα
αααααααΎαααα½αααΆαααα α’αααα’αΆα ααα½αααΆααααΆαααΆααααααααΆααααααααα αα·ααα½αα±ααα αΆααα’αΆααααααα
αα·ααααααβααα»αβαααα»αβααααααβααααα
ααααΎ
SELECT
database_id ,
SUM(calls) AS calls ,SUM(total_time) AS total_time ,
SUM(rows) AS rows , SUM(shared_blks_hit) AS shared_blks_hit,
SUM(shared_blks_read) AS shared_blks_read ,
SUM(shared_blks_dirtied) AS shared_blks_dirtied,
SUM(shared_blks_written) AS shared_blks_written ,
SUM(local_blks_hit) AS local_blks_hit ,
SUM(local_blks_read) AS local_blks_read ,
SUM(local_blks_dirtied) AS local_blks_dirtied ,
SUM(local_blks_written) AS local_blks_written,
SUM(temp_blks_read) AS temp_blks_read,
SUM(temp_blks_written) temp_blks_written ,
SUM(blk_read_time) AS blk_read_time ,
SUM(blk_write_time) AS blk_write_time
FROM
pg_stat_history
WHERE
queryid IS NULL AND
database_id = DATABASE_ID AND
snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT
GROUP BY database_id ;
D.B. αααααααΆ
to_char(α αααααααα '1 αα·ααααΈαα·ααΆααΈ' * pg_total_stat_history_rec.total_time, 'HH24:MI:SS.MS')
αααααααΆ I/O
to_char(α αααααααα '1 αα·ααααΈαα·ααΆααΈ' * ( pg_total_stat_history_rec.blk_read_time + pg_total_stat_history_rec.blk_write_time ), 'HH24:MI:SS.MS')
TOP10 SQL ααΆα total_time
ααααΎ
SELECT
queryid ,
SUM(calls) AS calls ,
SUM(total_time) AS total_time
FROM
pg_stat_history
WHERE
queryid IS NOT NULL AND
database_id = DATABASE_ID AND
snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT
GROUP BY queryid
ORDER BY 3 DESC
LIMIT 10
---------------------------------------------------------------- ------------------------------------ | TOP10 SQL ααααααααααΆααααα·ααααα·ααα»α | #| αααα½α | ααΆαα α ααΌαααααα | α α %| αααααααΆααα»α (ms) | dbtime % ++----+-----------+-----------+----------+-------- -----------------+---------- | α‘| 1| α’| .821760255|2:00001:00(03 ms.)| α₯.α€α’ | α’| 23.141| α’| .203141.681|5.42:2:4152624390(2 ms.)| α₯.α‘α§ | α£| 00001| α€| .00|03:13.929:193929.215(5.17 ms.)| α£.α€α€ | α€| α¦α₯α₯α§α’α©α’α§α£| α‘| .3|1484454471:4:00001(00 ms.)| α£.α’α₯ | α₯| 02| α‘| .09.129|129129.057:3.44:4( 655729273 ms.)| 1 | α¦| 00000| α€| .00|02:01.869:121869.981(3.25 ms.)| .5 | α§| 2460318461| α‘| .1|00000:00:01(33.113 ms.)| .α‘α¦ | α¨| α£α¦α€α€α§α¨α α’α¨α¦| α‘| .93113.835|2.48:6:2194493487(4 ms.)| .α α£
TOP10 SQL ααΆααααααααΆ I/O ααα»α
ααααΎ
SELECT
queryid ,
SUM(calls) AS calls ,
SUM(blk_read_time + blk_write_time) AS io_time
FROM
pg_stat_history
WHERE
queryid IS NOT NULL AND
database_id = DATABASE_ID AND
snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT
GROUP BY queryid
ORDER BY 3 DESC
LIMIT 10
---------------------------------------------------------------- --------------------------------------- | TOP10 SQL ααα TOTAL I/O TIME | #| αααα½α | ααΆαα α ααΌαααααα | α α %| αααααααΆ I/O (ms)|db I/O time % ++----+----------+----------+-------- ----------+--------------------------------+---------- --| α‘| 1| α’| .4152624390|2:00001:00(08 ms.)| α£α‘ αα·αα»ααΆ | α’| 31.616| α’| .511616.592|31.06:2:821760255(2 ms.)| α£α .α§α¨ | α£| α¦α₯α₯α§α’α©α’α§α£| α‘| .00001|00:08:27.099( 507099.036 ms.)| 30.78 | α€| 3| α‘| .655729273|1:00000:00(05 ms.)| α‘α€.α©α£ | α₯| 02.209| α€| .302209.137|18.35:4:2460318461(1 ms.)| 00000 | α¦| 00| α€| .04|05.981:245981.117:14.93(5 ms.)| α‘.α‘α | α§| 1484454471| α‘| .4|00001:00:00(39.144 ms.)| 39144.221 | α¨| α£α¦α€α€α§α¨α α’α¨α¦| α‘| .2.38|6:2194493487:4(00001 ms.)| .α α£
TOP10 SQL ααΆααααααααΆα’αα·ααααΆααααΆαααααα·ααααα·
ααααΎ
SELECT
id AS snapshotid ,
queryid ,
snapshot_timestamp ,
max_time
FROM
pg_stat_history
WHERE
queryid IS NOT NULL AND
database_id = DATABASE_ID AND
snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT
ORDER BY 4 DESC
LIMIT 10
---------------------------------------------------------------- ------------------------------------ | TOP10 SQL ααααααααααΆααααα·ααααα·α’αα·ααααΆ | #| ααΌααα | snapshotID| αααα½α | max_time (ms) +----+--------------------+-----------------+-------- ---------------------------------------------------- | α‘| α α€/α α₯/α’α α‘α© αααα α α‘:α α£| α€α‘α¦α©| α¦α₯α₯α§α’α©α’α§α£| 1:05.04.2019:01(03 ms.) | α’| 4169/655729273/00 02:01.869| α€α‘α₯α£| 121869.981| 2:04.04.2019:17(00 ms.) | α£| 4153/821760255/00 01:41.570| α€α‘α€α¦| 101570.841| 3:04.04.2019:16(00 ms.) | α€| 4146/821760255/00 01:41.570| α€α‘α€α€| 101570.841| 4:04.04.2019:16( 00 ms.) | α₯| 4144/4152624390/00 01:36.964| α€α‘α₯α‘| 96964.607| 5:04.04.2019:17( 00 ms.) | α¦| 4151/4152624390/00 01:36.964 | α€α‘α¨α¨| 96964.607| 6:05.04.2019:10( 00 ms.) | α§| 4188/1484454471/00 01:33.452| α€α‘α₯α | 93452.150| 7:04.04.2019:17( 00 ms.) | α¨| 4150/2460318461/00 01:33.113| 93113.835| 8| 04.04.2019:15:00(4140 ms.) | α©| 1484454471/00/00 11.892:11892.302| α€α‘α€α₯| 9| 04.04.2019:16:00(4145 ms.) | α‘α | 1484454471/00/00 11.892:11892.302| α€α‘α₯α’| 10| 04.04.2019:17:00(4152 ms.)
TOP10 SQL ααα SHARED buffer read/write
ααααΎ
SELECT
id AS snapshotid ,
queryid ,
snapshot_timestamp ,
shared_blks_read ,
shared_blks_written
FROM
pg_stat_history
WHERE
queryid IS NOT NULL AND
database_id = DATABASE_ID AND
snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT AND
( shared_blks_read > 0 OR shared_blks_written > 0 )
ORDER BY 4 DESC , 5 DESC
LIMIT 10
---------------------------------------------------------------- ------------------------------------ | TOP10 SQL αααααΆαα ααααααα buffer α’αΆα/ααααα | #| ααΌααα | snapshotID| αααα½α | αααα»ααααααΆαα ααααααα α’αΆα| αααα»ααααααΆαα αααααααααααα ----+------------------+-----------------+---------- ------------------------+--------------------- | α‘| 1/04.04.2019/17 00:4153| α€α‘α₯α£| 821760255| α§α©α§α£α α¨| 797308 | α’| 0/2/04.04.2019 16:00| α€α‘α€α¦| 4146| α§α©α§α£α α¨| 821760255 | α£| α α€/α α₯/α’α α‘α© αααα α α‘:α α£| α€α‘α¦α©| α¦α₯α₯α§α’α©α’α§α£| α§α©α§α‘α₯α¨| 797308 | α€| 0/3/05.04.2019 01:03| α€α‘α€α€| 4169| 655729273| 797158 | α₯| 0/4/04.04.2019 16:00| α€α‘α₯α‘| 4144| 4152624390| 756514 | α¦| 0/5/04.04.2019 17:00| α€α‘α₯α | 4151| 4152624390| 756514 | α§| 0/6/04.04.2019 17:00| α€α‘α₯α₯| α£α¦α€α€α§α¨α α’α¨α¦| α₯α’α©α§α£| 4150 | α¨| α α€/α α₯/α’α α‘α© αααα α α‘:α α£| α€α‘α¦α¨| 2460318461| 734117| 0 | α©| 7/04.04.2019/17 00:4155| α€α‘α€α‘| 3644780286| 52973| 0 | α‘α | 8/05.04.2019/01 03:4168| α€α‘α€α§| 1053044345| 52818| 0 ------------------------------------------------ ------------------------------------------------
α’ααΈααααΌααααΆαααααΆαα ααα αΆααααα½αααααααααααΆααααα·ααααα·α’αα·ααααΆ
ααααΎ
SELECT
MIN(max_time) AS hist_min ,
MAX(max_time) AS hist_max ,
(( MAX(max_time) - MIN(min_time) ) / hist_columns ) as hist_width
FROM
pg_stat_history
WHERE
queryid IS NOT NULL AND
database_id = DATABASE_ID AND
snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT ;
SELECT
SUM(calls) AS calls
FROM
pg_stat_history
WHERE
queryid IS NOT NULL AND
database_id =DATABASE_ID AND
snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT AND
( max_time >= hist_current_min AND max_time < hist_current_max ) ;
|---------------------------------------------------------------- ---------------------------------------- | MAX_TIME HISTOGRAM | ααΆαα α ααα»αα 33851920 | MIN TIME : 00:00:01.063 | MAX TIME : 00:02:01.869 --------------------------------------------- ---------------------------- | ααααααα’αααααααΆ| ααααααα’αα·ααααΆ| ααΆαα α ααΌαααααα ---------------------------------------------------- ---------------------+---------- | 00:00:01.063(1063.830 ms.) | 00:00:13.144(13144.445 ms.) | α© | 9:00:00(13.144 ms.) | 13144.445:00:00(25.225 ms.) | 25225.060 | 0:00:00(25.225 ms.) | 25225.060:00:00(37.305 ms.) | 37305.675 | 0:00:00(37.305 ms.) | 37305.675:00:00(49.386 ms.) | 49386.290 | 0:00:00(49.386 ms.) | 49386.290:00:01( 01.466 ms.) | 61466.906 | 0:00:01( 01.466 ms.) | 61466.906:00:01(13.547 ms.) | 73547.521 | 0:00:01(13.547 ms.) | 73547.521:00:01(25.628 ms.) | 85628.136 | 0:00:01(25.628 ms.) | 85628.136:00:01(37.708 ms.) | α€ | 97708.751:4:00(01 ms.) | 37.708:97708.751:00(01 ms.) | α’ | 49.789:109789.366:2(00 ms.) | 01:49.789:109789.366(00 ms.) | 02
ααΌααα TOP10 ααΆααααα½ααααα»ααα½ααα·ααΆααΈ
ααααΎ
--pg_qps.sql
--Calculate Query Per Second
CREATE OR REPLACE FUNCTION pg_qps( pg_stat_history_id integer ) RETURNS double precision AS $$
DECLARE
pg_stat_history_rec record ;
prev_pg_stat_history_id integer ;
prev_pg_stat_history_rec record;
total_seconds double precision ;
result double precision;
BEGIN
result = 0 ;
SELECT *
INTO pg_stat_history_rec
FROM
pg_stat_history
WHERE id = pg_stat_history_id ;
IF pg_stat_history_rec.snapshot_timestamp IS NULL
THEN
RAISE EXCEPTION 'ERROR - Not found pg_stat_history for id = %',pg_stat_history_id;
END IF ;
--RAISE NOTICE 'pg_stat_history_id = % , snapshot_timestamp = %', pg_stat_history_id ,
pg_stat_history_rec.snapshot_timestamp ;
SELECT
MAX(id)
INTO
prev_pg_stat_history_id
FROM
pg_stat_history
WHERE
database_id = pg_stat_history_rec.database_id AND
queryid IS NULL AND
id < pg_stat_history_rec.id ;
IF prev_pg_stat_history_id IS NULL
THEN
RAISE NOTICE 'Not found previous pg_stat_history shapshot for id = %',pg_stat_history_id;
RETURN NULL ;
END IF;
SELECT *
INTO prev_pg_stat_history_rec
FROM
pg_stat_history
WHERE id = prev_pg_stat_history_id ;
--RAISE NOTICE 'prev_pg_stat_history_id = % , prev_snapshot_timestamp = %', prev_pg_stat_history_id , prev_pg_stat_history_rec.snapshot_timestamp ;
total_seconds = extract(epoch from ( pg_stat_history_rec.snapshot_timestamp - prev_pg_stat_history_rec.snapshot_timestamp ));
--RAISE NOTICE 'total_seconds = % ', total_seconds ;
--RAISE NOTICE 'calls = % ', pg_stat_history_rec.calls ;
IF total_seconds > 0
THEN
result = pg_stat_history_rec.calls / total_seconds ;
ELSE
result = 0 ;
END IF;
RETURN result ;
END
$$ LANGUAGE plpgsql;
SELECT
id ,
snapshot_timestamp ,
calls ,
total_time ,
( select pg_qps( id )) AS QPS ,
blk_read_time ,
blk_write_time
FROM
pg_stat_history
WHERE
queryid IS NULL AND
database_id = DATABASE_ID AND
snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT AND
( select pg_qps( id )) IS NOT NULL
ORDER BY 5 DESC
LIMIT 10
|---------------------------------------------------------------- ---------------------------------------- | ααΌααα TOP10 ααααααααΆαααααα QueryPerSeconds --------------------------------------------- ---------------------------------------------------------- --------------------------------------------- | #| ααΌααα | snapshotID| ααΆαα α ααΌαααααα | dbtime ααα»α| QPS | αααααααΆ I/O | αααααααΆ I/O % +-----+------------------+-----------------+-------- ----+-------------------------------------+---------- ------------------------------------+----------- | α‘| α α€/α α€/α’α α‘α© αααα α’α :α α€| α€α‘α¦α‘| 1| 04.04.2019:20:04( 4161 ms.)| α‘α₯α§α£.α£α©α¦| 5758631:00:06(30.513 ms.)| .α£α§α¦ | α’| 390513.926/1573.396/00 00:01.470| α€α‘α€α©| 1470.110| 376:2:04.04.2019(17 ms.)| 00| 4149:3529197:00(11 ms.)| 48.830 | α£| 708830.618/980.332/00 12:47.834| α€α‘α€α£| α£α₯α’α₯α£α¦α | 767834.052:108.324:3( 04.04.2019 ms.)| 16| 00:4143:3525360( 00 ms.)| 10 | α€| α α€/α α€/α’α α‘α© α’α‘:α α£| α€α‘α¦α£| α’α§α¨α‘α₯α£α¦| 13.492:613492.351:979.267(00 ms.)| 08| 41.396:521396.555:84.988(4 ms.)| .α‘α£α€ | α₯| α α€/α α€/α’α α‘α© αααα α‘α©:α α£| α€α‘α₯α©| α’α¨α©α α£α¦α’| 04.04.2019:21:03(4163 ms.)| 2781536| 00:03:06.470(186470.979 ms.)| .785.745 | α¦| 00/00/00.249 249.865:134| α€α‘α£α§| 5| 04.04.2019:19:03(4159 ms.)| α¦α¦α₯.α©α’α€| 2890362:00:03(16.784 ms.)| .196784.755 | α§| 776.979/00/00 01.441:1441.386| α€α‘α£α©| 732| 6:04.04.2019:14(00 ms.)| α¦α¦α₯.α‘α‘α¦| 4137:2397326:00(04 ms.)| α€.α‘α’α¦ | α¨| 43.033/283033.854/665.924 00:00 | α€α‘α£α₯| 00.024| 24.505:009:7(04.04.2019 ms.)| α¦α₯α©.α‘α§α©| 15:00:4139(2394416 ms.)| .α α’α€ | α©| α α€/α α₯/α’α α‘α© αααα α α‘:α α£| α€α‘α¦α§| α€α£α¨α§α‘α©α‘| 00:04:51.435(291435.010 ms.)| α¦α α©.α£α£α’| 665.116:00:00( 12.025 ms.)| 12025.895 | α‘α | α α€/α α€/α’α α‘α© αααα α‘α¨:α α‘| α€α‘α₯α§| α‘α‘α€α₯α₯α©α¦| 4.126:8:04.04.2019(13 ms.)| 00| 4135:2373043:00(04 ms.)| α‘.α¦α¦α¦
αααααααα·ααααα·ααααα·ααααααΆαα½α QueryPerSeconds αα·α I/O Time
ααααΎ
SELECT
id ,
snapshot_timestamp ,
calls ,
total_time ,
( select pg_qps( id )) AS QPS ,
blk_read_time ,
blk_write_time
FROM
pg_stat_history
WHERE
queryid IS NULL AND
database_id = DATABASE_ID AND
snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT
ORDER BY 2
|----------------------------------------------------------------------------------------------- | HOURLY EXECUTION HISTORY WITH QueryPerSeconds and I/O Time ----------------------------------------------------------------------------------------------------------------------------------------------- | QUERY PER SECOND HISTORY | #| snapshot| snapshotID| calls| total dbtime| QPS| I/O time| I/O time % +-----+------------------+-----------+-----------+----------------------------------+-----------+----------------------------------+----------- | 1| 04.04.2019 11:00| 4131| 3747| 00:00:00.835( 835.374 ms.)| 1.041| 00:00:00.000( .000 ms.)| .000 | 2| 04.04.2019 12:00| 4133| 1002722| 00:01:52.419( 112419.376 ms.)| 278.534| 00:00:00.149( 149.105 ms.)| .133 | 3| 04.04.2019 13:00| 4135| 2373043| 00:04:26.791( 266791.988 ms.)| 659.179| 00:00:00.064( 64.261 ms.)| .024 | 4| 04.04.2019 14:00| 4137| 2397326| 00:04:43.033( 283033.854 ms.)| 665.924| 00:00:00.024( 24.505 ms.)| .009 | 5| 04.04.2019 15:00| 4139| 2394416| 00:04:51.435( 291435.010 ms.)| 665.116| 00:00:12.025( 12025.895 ms.)| 4.126 | 6| 04.04.2019 16:00| 4143| 3525360| 00:10:13.492( 613492.351 ms.)| 979.267| 00:08:41.396( 521396.555 ms.)| 84.988 | 7| 04.04.2019 17:00| 4149| 3529197| 00:11:48.830( 708830.618 ms.)| 980.332| 00:12:47.834( 767834.052 ms.)| 108.324 | 8| 04.04.2019 18:01| 4157| 1145596| 00:01:19.217( 79217.372 ms.)| 313.004| 00:00:01.319( 1319.676 ms.)| 1.666 | 9| 04.04.2019 19:03| 4159| 2890362| 00:03:16.784( 196784.755 ms.)| 776.979| 00:00:01.441( 1441.386 ms.)| .732 | 10| 04.04.2019 20:04| 4161| 5758631| 00:06:30.513( 390513.926 ms.)| 1573.396| 00:00:01.470( 1470.110 ms.)| .376 | 11| 04.04.2019 21:03| 4163| 2781536| 00:03:06.470( 186470.979 ms.)| 785.745| 00:00:00.249( 249.865 ms.)| .134 | 12| 04.04.2019 23:03| 4165| 1443155| 00:01:34.467( 94467.539 ms.)| 200.438| 00:00:00.015( 15.287 ms.)| .016 | 13| 05.04.2019 01:03| 4167| 4387191| 00:06:51.380( 411380.293 ms.)| 609.332| 00:05:18.847( 318847.407 ms.)| 77.507 | 14| 05.04.2019 02:03| 4171| 189852| 00:00:10.989( 10989.899 ms.)| 52.737| 00:00:00.539( 539.110 ms.)| 4.906 | 15| 05.04.2019 03:01| 4173| 3627| 00:00:00.103( 103.000 ms.)| 1.042| 00:00:00.004( 4.131 ms.)| 4.010 | 16| 05.04.2019 04:00| 4175| 3627| 00:00:00.085( 85.235 ms.)| 1.025| 00:00:00.003( 3.811 ms.)| 4.471 | 17| 05.04.2019 05:00| 4177| 3747| 00:00:00.849( 849.454 ms.)| 1.041| 00:00:00.006( 6.124 ms.)| .721 | 18| 05.04.2019 06:00| 4179| 3747| 00:00:00.849( 849.561 ms.)| 1.041| 00:00:00.000( .051 ms.)| .006 | 19| 05.04.2019 07:00| 4181| 3747| 00:00:00.839( 839.416 ms.)| 1.041| 00:00:00.000( .062 ms.)| .007 | 20| 05.04.2019 08:00| 4183| 3747| 00:00:00.846( 846.382 ms.)| 1.041| 00:00:00.000( .007 ms.)| .001 | 21| 05.04.2019 09:00| 4185| 3747| 00:00:00.855( 855.426 ms.)| 1.041| 00:00:00.000( .065 ms.)| .008 | 22| 05.04.2019 10:00| 4187| 3797| 00:01:40.150( 100150.165 ms.)| 1.055| 00:00:21.845( 21845.217 ms.)| 21.812
α’αααααααααΆαααααΎαααΎα SQL ααΆααα’ααα
ααααΎ
SELECT
queryid ,
query
FROM
pg_stat_history
WHERE
queryid IS NOT NULL AND
database_id = DATABASE_ID AND
snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT
GROUP BY queryid , query
αααααα
ααΌα αααα’αααα’αΆα ααΎαααααααααααΆαααΆαααααααααα α’αααα’αΆα ααα½αααΆαααααααΆαααΆαααααααααααΆα αααΎαα’αααΈααααα»αααΆαααΆα αα·αααααΆαααΆαααααΌαααααΆααα·ααααααα
α αααΆααααααα·αααΎα’ααααα½ααα»ααααα½ααα αααα»ααααα½αααα ααΎαααΉαααα½αααΆααααααααα·αααααΆααααααΎααΆα ααααα‘αααα½α (ααΎααααΈαααααααα α αααΆαααΆααααααααΆααααααΎααΆα ααααα‘ααααααΌαααΆααα»αα αα)α
ααΌα
αααα αα·αααααααααα·αα·ααααΈααΈααΆαα’αα»αααααααα½αα’αΆα
ααααΆα αα·ααααααΌαααΆαα
ααααΆααααΆαααααΌα "ααΆααααααΌααα·αααααααααα·αα·" ααααΌαααΆααααα
ααα
α’αααα’αΆα
αααααα
ααααΆααααΆαααΈααΈα - "ααααααα
ααΆααααααααααααααααΆαα’αα»αααα" α
ααα»αααααααααΆααΏααα»αααααΆααΆαααααα»αα
αα
βααΆαβααΆβααααβαααβ¦
ααααα: www.habr.com