§®§à§ß§Ú§ä§à§â§Ú§ß§Ô postgresql §Ú §Þ§Ú§â§Ñ §Ó§à§Ü§â§å§Ô §ß§Ö§Ô§à. §±§Ñ§Ó§Ö§Ý §´§â§å§ç§Ñ§ß§à§Ó okmeter.io
1 like904 views
§¬§Ñ§â§ä§Ú§ß§Ü§Ú §ã §Õ§à§Ü§Ý§Ñ§Õ§Ñ §ß§Ñ Pg day 2015 §®§à§ß§Ú§ä§à§â§Ú§ß§Ô §Ù§Ñ§á§â§à§ã§à§Ó §Ó §Ò§Ñ§Ù§å §Õ§Ñ§ß§ß§í§ç postgresql ¡ª §Ó§â§Ö§Þ§Ö§ß§Ñ §à§ä§Ó§Ö§ä§Ñ §Ü§Ó§Ö§â§Ö§Û, cpu disk io usage §Ü§Ñ§Ø§Õ§à§Ô§à §à§ä§Õ§Ö§Ý§î§ß§à§Ô§à select §Ù§Ñ§á§â§à§ã§Ñ §Ú §Õ§â§å§Ô§Ú§ç query. §°§Ò§Ù§à§â contrib §â§Ñ§ã§ê§Ú§â§Ö§ß§Ú§ñ §Õ§Ý§ñ §á§à§ã§ä§Ô§â§Ö§ã ¡ª pg_stat_statements. §¡ §ä§Ñ§Ü §Ø§Ö §Õ§â§å§Ô§Ú§Ö §ã§Ú§ã§ä§Ö§Þ§ß§í§Ö view postgres ¡ª pg_stat_activity, pg_stat_user_tables, bgwriter, io_user_tables, replication §Ú §Þ§à§ß§Ú§ä§à§â§Ú§ß§Ô §Ó§ã§Ö§Ô§à §ï§ä§à§Ô§à)
![# SELECT query, calls, total_time, FROM pg_stat_statements ORDER
BY total_time DESC LIMIT 1;
-[ RECORD 1 ]----------------------------------------------------
query | UPDATE pgbench_branches SET bbalance = bbalance + ?
WHERE bid = ?;
calls | 3001
total_time | 9609.00100000002
query Text of a representative statement
calls Number of times executed
total_time Total time spent in the statement
pg_stat_statements
okmeter.io](https://image.slidesharecdn.com/pgday-15postgresql-160209175033/85/postgresql-okmeter-io-8-320.jpg)
More Related Content
Viewers also liked (11)
Similar to §®§à§ß§Ú§ä§à§â§Ú§ß§Ô postgresql §Ú §Þ§Ú§â§Ñ §Ó§à§Ü§â§å§Ô §ß§Ö§Ô§à. §±§Ñ§Ó§Ö§Ý §´§â§å§ç§Ñ§ß§à§Ó okmeter.io (20)
§®§à§ß§Ú§ä§à§â§Ú§ß§Ô postgresql §Ú §Þ§Ú§â§Ñ §Ó§à§Ü§â§å§Ô §ß§Ö§Ô§à. §±§Ñ§Ó§Ö§Ý §´§â§å§ç§Ñ§ß§à§Ó okmeter.io
- 1. §®§à§ß§Ú§ä§à§â§Ú§ß§Ô PG §Ú §Þ§Ú§â §Ó§à§Ü§â§å§Ô §±§Ñ§Ó§Ö§Ý §´§â§å§ç§Ñ§ß§à§Ó, okmeter.io
- 2. §° §é§×§Þ §ï§ä§à ¡ñ OLTP vs OLAP ¡ñ §á§à§Ý§î§Ù§à§Ó§Ñ§ä§Ö§Ý§î§ã§Ü§Ú§Ö vs §Þ§Ñ§ê§Ú§ß§ß§í§Ö §Ù§Ñ§á§â§à§ã§í ¡ñ §ß§à§â§Þ§Ñ§Ý§î§ß§Ñ§ñ §ã§Ú§ä§å§Ñ§è§Ú§ñ vs §á§â§à§Ò§Ý§Ö§Þ§í aka ¡°§Ü§Ñ§Ü §Ò§í§Ý§à vs §Ü§Ñ§Ü §ã§ä§Ñ§Ý§à¡± okmeter.io
- 4. §®§à§ß§Ú§ä§à§â§Ú§ß§Ô ?§ß§Ñ§Ò§Ý§ð§Õ§Ö§ß§Ú§Ö §Ù§Ñ §ã§à§ã§ä§à§ñ§ß§Ú§Ö§Þ §à§Ò§ì§Ö§Ü§ä§Ñ §Õ§Ý§ñ §à§á§â§Ö§Õ§Ö§Ý§Ö§ß§Ú§ñ §Þ§à§Þ§Ö§ß§ä§Ñ §á§Ö§â§Ö§ç§à§Õ§Ñ §Ó §á§â§Ö§Õ§Ö§Ý§î§ß§à§Ö §ã§à§ã§ä§à§ñ§ß§Ú§Ö? 1. §à§Ò§ß§Ñ§â§å§Ø§Ú§ä§î §á§â§à§Ò§Ý§Ö§Þ§í §Ú §à§á§à§Ó§Ö§ã§ä§Ú§ä§î 2. §ß§Ñ §ã§Ü§à§Ý§î§Ü§à §Ó§ã§Ö §á§Ý§à§ç§à? 3. §Ü§Ñ§Ü§Ú§Ö §ã§Ö§â§Ó§Ú§ã§í? §Ó §é§Ö§Þ §á§â§Ú§é§Ú§ß§Ñ? 4. ¡ §é§Ú§ß§Ú§Þ ¡ 5. §å§Ò§Ö§Õ§Ú§ä§î§ã§ñ §é§ä§à §Ó§ã§× §ã§ß§à§Ó§Ñ §°§¬ okmeter.io
- 5. §®§à§ß§Ú§ä§à§â§Ú§ß§Ô §Õ§Ý§ñ §¢§¥ ¡ñ §¯§Ö§ä §Ù§Ñ§á§â§à§ã§à§Ó ¡ª §ß§Ö§ä §á§â§à§Ò§Ý§Ö§Þ ¡ñ §©§Ñ§á§â§à§ã§í * §Õ§Ñ§ß§ß§í§Ö ¡ñ §©§Ñ§á§â§à§ã§í vs §Õ§à§ã§ä§å§á§ß§í§Ö §Þ§à§ë§ß§à§ã§ä§Ú §±§â§à§Ò§Ý§Ö§Þ§í: ¡ñ §Ò§Ý§à§Ü§Ú§â§à§Ó§Ü§Ú §Ú §Ü§à§ß§Ü§å§â§Ö§ß§è§Ú§ñ §Ù§Ñ§á§â§à§ã§à§Ó ¡ñ §à§ä§ã§å§ä§ã§ä§Ó§å§ð§ë§Ú§Ö §Ú§ß§Õ§Ö§Ü§ã§í ¡ñ §ß§Ö§ï§æ§æ§Ö§Ü§ä§Ú§Ó§ß§í§Ö §Ù§Ñ§á§â§à§ã§í ¡ñ §á§â§à§Ò§Ý§Ö§Þ§í §å§á§â§Ñ§Ó§Ý§Ö§ß§Ú§ñ §á§Ñ§Þ§ñ§ä§î§ð okmeter.io
- 6. §®§à§ß§Ú§ä§à§â§Ú§ß§Ô §Õ§Ý§ñ §¢§¥ 1. §à§Ò§ß§Ñ§â§å§Ø§Ú§ä§î §á§â§à§Ò§Ý§Ö§Þ§í = §ä§à§â§Þ§à§Ù§Ú§ä §Ý§Ú? = §ä§à§â§Þ§à§Ù§ñ§ä §Ý§Ú §Ù§Ñ§á§â§à§ã§í? pg_stat_statements okmeter.io
- 7. bgwriter replication database user_tables /indexes io_user_tabl /indexes §®§à§ß§Ú§ä§à§â§Ú§ß§Ô §Õ§Ý§ñ §¢§¥ 1. §à§Ò§ß§Ñ§â§å§Ø§Ú§ä§î §á§â§à§Ò§Ý§Ö§Þ§í = §ä§à§â§Þ§à§Ù§Ú§ä §Ý§Ú? = §ä§à§â§Þ§à§Ù§ñ§ä §Ý§Ú §Ù§Ñ§á§â§à§ã§í? pg_stat* okmeter.io
- 8. # SELECT query, calls, total_time, FROM pg_stat_statements ORDER BY total_time DESC LIMIT 1; -[ RECORD 1 ]---------------------------------------------------- query | UPDATE pgbench_branches SET bbalance = bbalance + ? WHERE bid = ?; calls | 3001 total_time | 9609.00100000002 query Text of a representative statement calls Number of times executed total_time Total time spent in the statement pg_stat_statements okmeter.io
- 10. §á§â§à§Ú§Ù§Ó§à§Õ§ß§Ñ§ñ current_q_time = rate(total_time) pg_stat_statements total_time okmeter.io
- 11. §¯§Ñ§á§â§Ú§Þ§Ö§â current_q_time avg_q_time = rate(total_time) / rate(calls) okmeter.io
- 12. §±§â§à§Ò§Ý§Ö§Þ§í §ã pg_s_s ¡ñ §Ô§à§ß§Ü§Ñ pg_stat_statements_reset() ¡ñ §ß§à§â§Þ§Ñ§Ý§Ú§Ù§Ñ§è§Ú§ñ a as b a as c y IN (?, ?) y IN (?, ?, ?) select a, b select b, a ¡ ¡ñ §â§Ñ§Ù§ß§í§Ö §á§Ý§Ñ§ß§í §à§Õ§ß§à§Ô§à §Ù§Ñ§á§â§à§ã§Ñ pg_stat_plans? ¡ñ §ä§à§Ý§î§Ü§à total_time :( ¡ñ §ä§â§Ö§Ò§å§Ö§ä §Ó§í§á§à§Ý§ß§Ö§ß§Ú§ñ §Ù§Ñ§á§â§à§ã§Ñ §Ó §¢§¥ okmeter.io
- 13. §²§Ö§Ñ§Ý§î§ß§Ñ§ñ §Ø§Ú§Ù§ß§î max(rate(total_time)) top(10, rate(total_time) mean(rate(total_time)) min(rate(total_time)) okmeter.io
- 17. okmeter.io
- 18. §®§à§ß§Ú§ä§à§â§Ú§ß§Ô §Ú§Ù§ß§å§ä§â§Ú §Ò§Ñ§Ù§í §ï§ä§à §ß§Ö §Ó§ã§× okmeter.io
- 20. ?§³§ß§Ñ§â§å§Ø§Ú? ¡ª §â§Ö§ã§å§â§ã§í tcp closewait iowait swap disk.write swap swap rate process count okmeter.io
- 22. §¤§Õ§Ö §ï§ä§à §Ö§ã§ä§î? Zabbix! ? ¡ª pg_monz, libzbxpgsql Nagios / Munin / Cacti ¡ §¯§¦§´ pg_stat_statements :( §¹§ä§à §Ö§ë§Ö? cli tools, §Õ§â§å§Ô§Ú§Ö pg extentions §ã§Þ wiki.postgresql.org/wiki/Monitoring okmeter.io
- 23. §¤§Õ§Ö §ï§ä§à §Ö§ã§ä§î? FOSS: OpenPgMonitoring (OPM), PoWA SaaS: New relic + EnterpriseDB No pg_stat_statements Datadog No pg_stat_statements VividCortex ! pganalyze ! OKmeter (§ï§ä§à §Þ§í :) ! okmeter.io
- 24. VividCortex
- 25. PgAnalyze
- 28. okmeter.io
- 30. okmeter.io
- 32. okmeter.io
- 33. okmeter.io
- 34. okmeter.io
- 35. okmeter.io
- 36. okmeter.io
- 37. okmeter.io
- 39. tcp okmeter.io
- 40. §®§à§ß§Ú§ä§à§â§Ú§ß§Ô PG §Ú §Þ§Ú§â §Ó§à§Ü§â§å§Ô §±§Ñ§Ó§Ö§Ý §´§â§å§ç§Ñ§ß§à§Ó, okmeter.io