PostgreSQL的監控三(zabbix)

五柳-先生發表於2015-11-13
SQL

3  共通的監控工具

前面介紹的PostgreSQL監控工具都偏向於效能分析，沒有告警功能。而且它們只是針對PostgreSQL的監視，有時需要監控整個業務相關的系統，這時候就要考慮通用的監控工具了。Linux下比較適合監控資料庫的常用的工具有Nagios和Zabbix。Zabbix更容易使用，現在看上去也更被多數人看好，所以本文只介紹Zabbix監控PostgreSQL的方法。

3.1 Zabbix簡介



Zabbix是一個all in one高度整合的企業級監控解決方案。由一箇中心的Zabbix Server和若干可能安裝有Zabbix Agent被監控裝置構成，主要特性可概括為以下幾點
資料採集
 支援agent和agent less(SNMP, IPMI, HTTP，FTP...)
 支援基於JMX對java應用的監視
 可靈活定製agent
資料儲存
 資料庫為PostgreSQL,Mysql,Oracle，SQLite或DB2
 可配置歷史和趨勢資料的儲存時間
 內建舊清理程式防止資料膨脹
報警
 可定製報警閾值
 靈活設定報警方式，郵件，SMS，指令碼
 支援報警升級
 報警訊息可使用巨集變數定製
視覺化
 可定製的資料圖形
 儀表盤
 地圖
 所有配置都通過GUI編輯
大規模部署
 支援模板
 自動發現主機和監控專案
 通過Zabbix Proxy實現分散式部署
其他
 Zabbix API
 認證和訪問控制
 IT資產收集

zabbix要想監視PostgreSQL這種應用型的物件，一般使用zabbix agent。zabbix agent有2種工作方式。
方式1：被動代理
由Zabbix Server(或Proxy)主動查詢資料（如CPU負載），作為響應Zabbix Agent返回查詢結果。這也是最簡單最常用的方式。

方式2：主動代理
Zabbix Agent先從Zabbix Server獲取需要主動報告的監控專案一覽，然後定期傳送新值到Zabbix Server。主動代理可以用於處理時間比較長的監控項，比如log 。

也可以使用Zabbix Trapper
方式3：Trapper
由Zabbix Agent主動報告資料。被監控端可呼叫zabbix_send命令或直接利用Socket傳送資料到Zabbix Server。通過Trapper可以只在狀態變更時進行報告。

另外還有把PostgreSQL狀態通過SNMP代理髮布的方案（http://pgsnmpd.projects.pgfoundry.org/），估計用的不多，本文不涉及。

詳細參考:
https://www.zabbix.com/documentation/2.4/manual/concepts/agent
https://www.zabbix.com/documentation/2.4/manual/appendix/items/activepassive
https://www.zabbix.com/documentation/2.4/manual/config/items/itemtypes/trapper


Zabbix沒有內建對PostgreSQL的監控項，所以如果要監控PostgreSQL需要做一些監控項的配置或定製，下面會介紹幾種方法。

3.2 方法1：直接擴充UserParameter

自己修改zabbix_agentd.conf的配置檔案，在Zabbix agent上增加PostgreSQL相關的監控項，使用psql發SQL的方式獲取PostgreSQL的效能資料。

例
zabbix_agentd.conf

  1. #Get the PostgreSQL version
  2. UserParameter=psql.version,psql --version|head -n1
  3. #Get the total number of Server Processes that are active
  4. UserParameter=psql.server_processes,psql -t -c "select sum(numbackends) from pg_stat_database"
  5. #Get the total number of commited transactions
  6. UserParameter=psql.tx_commited,psql -t -c "select sum(xact_commit) from pg_stat_database"
  7. #Get the total number of rolled back transactions
  8. UserParameter=psql.tx_rolledback,psql -t -c "select sum(xact_rollback) from pg_stat_database"

參照
https://www.zabbix.com/wiki/howto/monitor/db/postgresql

3.3 方法2：擴充UserParameter呼叫PostgreSQL監視指令碼

和方法1類似，但方法1中定義的UserParameter太多，為方便起見，可以把監控PostgreSQL的SQL命令做成一個單獨的指令碼。

比如參照下面的例子
https://www.zabbix.com/forum/showthread.php?t=8009

zabbix_agentd.conf：

  1. UserParameter=postgresql[*],/opt/zabbix/bin/zapost $1 $2

zapost：

  1. #
  2. # Name: zapost
  3. #
  4. # Checks PostgreSQL activity.
  5. #
  6. # Author: bashman
  7. #
  8. # Version: 1.0
  9. #

  11. zapostver="1.0"
  12. rval=0
  13. sql=""

  15. case $1 in

  17. #'summary')
  18. # sql="select a.datname, pg_size_pretty(pg_database_size(a.datid)) as size, cast(blks_hit/(blks_read+blks_hit+0.000001)*100.0 as numeric(5,2)) as cache, cast(xact_commit/(xact_rollback+xact_commit+0.000001)*100.0 as numeric(5,2)) as success from pg_stat_database a order by a.datname"
  19. # ;;

  21. #'size')
  22.         #comprobar aqui los parametros
  23. # shift
  24. # sql="select pg_database_size('$1') as size"
  25. # ;;

  27. #'version')
  28. # sql='select version()'
  29. # ;;

  31. 'totalsize')
  32.         sql="select sum(pg_database_size(datid)) as total_size from pg_stat_database"
  33.         ;;

  35. 'db_cache')
  36.         # comprueba los parametros
  37.         if [ ! -z $2 ]; then
  38.         shift
  39.             sql="select cast(blks_hit/(blks_read+blks_hit+0.000001)*100.0 as numeric(5,2)) as cache from pg_stat_database where datname = '$1'"
  40.     fi
  41.         ;;

  43. 'db_success')
  44.         # comprueba los parametros
  45.     if [ ! -z $2 ]; then
  46.         shift
  47.                sql="select cast(xact_commit/(xact_rollback+xact_commit+0.000001)*100.0 as numeric(5,2)) as success from pg_stat_database where datname = '$1'"
  48.     fi
  49.     ;;

  51. 'server_processes')
  52.     sql="select sum(numbackends) from pg_stat_database"
  53.     ;;

  55. 'tx_commited')
  56.     sql="select sum(xact_commit) from pg_stat_database"
  57.     ;;

  59. 'tx_rolledback')
  60.     sql="select sum(xact_rollback) from pg_stat_database"
  61.     ;;

  63. 'db_size')
  64.     # comprueba los parametros
  65.         if [ ! -z $2 ]; then
  66.         shift
  67.         sql="select pg_database_size('$1')" #as size"
  68.     fi
  69.     ;;

  71. 'db_connections')
  72.         # comprueba los parametros
  73.         if [ ! -z $2 ]; then
  74.         shift
  75.             sql="select numbackends from pg_stat_database where datname = '$1'"
  76.     fi
  77.     ;;

  79. 'db_returned')
  80.     # comprueba los parametros
  81.         if [ ! -z $2 ]; then
  82.         shift
  83.         sql="select tup_returned from pg_stat_database where datname = '$1'"
  84.     fi
  85.     ;;

  87. 'db_fetched')
  88.         # comprueba los parametros
  89.         if [ ! -z $2 ]; then
  90.         shift
  91.             sql="select tup_fetched from pg_stat_database where datname = '$1'"
  92.     fi
  93.     ;;

  95. 'db_inserted')
  96.     # comprueba los parametros
  97.         if [ ! -z $2 ]; then
  98.             shift
  99.             sql="select tup_inserted from pg_stat_database where datname = '$1'"
  100.     fi
  101.         ;;

  103. 'db_updated')
  104.     # comprueba los parametros
  105.         if [ ! -z $2 ]; then
  106.             shift
  107.             sql="select tup_updated from pg_stat_database where datname = '$1'"
  108.     fi
  109.         ;;

  111. 'db_deleted')
  112.     # comprueba los parametros
  113.         if [ ! -z $2 ]; then
  114.             shift
  115.             sql="select tup_deleted from pg_stat_database where datname = '$1'"
  116.     fi
  117.         ;;

  119. 'db_commited')
  120.     # comprueba los parametros
  121.         if [ ! -z $2 ]; then
  122.             shift
  123.         sql="select xact_commit from pg_stat_database where datname = '$1'"
  124.     fi
  125.     ;;

  127. 'db_rolled')
  128.     # comprueba los parametros
  129.     if [ ! -z $2 ]; then
  130.             shift
  131.         sql="select xact_rollback from pg_stat_database where datname = '$1'"
  132.     fi
  133.     ;;

  135. 'version')
  136.     sql="version"
  137.     ;;

  139. 'zapostver')
  140.         echo "$zapostver"
  141.     exit $rval
  142.         ;;

  144. *)
  145.         echo "zapost version: $zapostver"
  146.         echo "usage:"
  147.     echo " $0 totalsize -- Check the total databases size."
  148.     echo " $0 db_cache <dbname> -- Check the database cache hit ratio (percentage)."
  149.     echo " $0 db_success <dbname> -- Check the database success rate (percentage)."
  150.     echo " $0 server_processes -- Check the total number of Server Processes that are active."
  151.     echo " $0 tx_commited -- Check the total number of commited transactions."
  152.     echo " $0 tx_rolledback -- Check the total number of rolled back transactions."
  153.     echo " $0 db_size <dbname> -- Check the size of a Database (in bytes)."
  154.     echo " $0 db_connections <dbname> -- Check the number of active connections for a specified database."    
  155.     echo " $0 db_returned <dbname> -- Check the number of tuples returned for a specified database."
  156.     echo " $0 db_fetched <dbname> -- Check the number of tuples fetched for a specified database."
  157.     echo " $0 db_inserted <dbname> -- Check the number of tuples inserted for a specified database."
  158.     echo " $0 db_updated <dbname> -- Check the number of tuples updated for a specified database."
  159.     echo " $0 db_deleted <dbname> -- Check the number of tuples deleted for a specified database."
  160.     echo " $0 db_commited <dbname> -- Check the number of commited back transactions for a specified database."
  161.     echo " $0 db_rolled <dbname> -- Check the number of rolled back transactions for a specified database."
  162.     echo " $0 version -- The PostgreSQL version."
  163.     echo " $0 zapostver -- Version of this script."
  164.         exit $rval
  165.         ;;
  166. esac

  168. if [ "$sql" != "" ]; then
  169.     if [ "$sql" == "version" ]; then
  170.         psql --version|head -n1
  171.         rval=$?
  172.     else
  173.         psql -t -c "$sql"
  174.         rval=$?
  175.     fi
  176. fi

  178. if [ "$rval" -ne 0 ]; then
  179.       echo "ZBX_NOTSUPPORTED

3.4 方法3：使用Postbix或DBforBIX外掛

和前面提到的2種方法相比，Postbix外掛的功能更全面，它包含了PostgreSQL相關的監控項和圖形的Zabbix模板。Postbix以一個的後臺java deamon執行，這個deamon通過jdbc查詢遠端被監控資料庫的狀態然後以trap agent的方式傳送到Zabbix Server。
Postbix來自http://www.smartmarmot.com/，除了Postbix該公司還有Orabbix,MySQLBix，這些工具的內部架構和使用方法基本相同，唯一的區別就是支援的被監控資料庫不同。所以smartmarmot又推出了整合這幾種資料庫監控能力的DBforBIX。DBforBIX的內部結構和使用方法和Postbix基本相同，下面介紹一下DBforBIX的簡單的使用例子。

1）下載dbforbix
http://www.smartmarmot.com/product/dbforbix/dbforbix-download/

2）在Zabbix Server上安裝dbforbix

  1. [root@zabbix ~]# mkdir /opt/dbforbix
  2. [root@zabbix ~]# cd /opt/dbforbix
  3. [root@zabbix dbforbix]# unzip /root/dbforbix-0.6.1.zip
  4. [root@zabbix dbforbix]# cp /opt/dbforbix/init.d/dbforbix /etc/init.d/dbforbix
  5. [root@zabbix dbforbix]# chmod +x /etc/init.d/dbforbix
  6. [root@zabbix dbforbix]# chmod +x /opt/dbforbix/run.sh
  7. [root@zabbix dbforbix]# chkconfig dbforbix on

3)匯入dbforix的模板到Zabbix伺服器
點選Zabbix GUI畫面的"Configuration->Templates->Import"把下面的模板檔案匯入Zabbix伺服器。
/opt/dbforbix/template/template_postgresql.xml


4)在被監控PostgreSQL例項上建立DBforBIX使用的賬號並賦予許可權

  1.   CREATE USER zabbix WITH PASSWORD 'passw0rd';
  2.   GRANT SELECT ON pg_stat_activity to zabbix;
  3.   GRANT SELECT ON pg_stat_activity to zabbix;
  4.   GRANT SELECT ON pg_database to zabbix;
  5.   GRANT SELECT ON pg_authid to zabbix;
  6.   GRANT SELECT ON pg_stat_bgwriter to zabbix;
  7.   GRANT SELECT ON pg_locks to zabbix;
  8.   GRANT SELECT ON pg_stat_database to zabbix
5)修改config.props
通過拷貝config.props.sample生成config.props, 然後修改config.props設定Zabbix Server的IP和埠號，設定被監控PostgreSQL資料庫的訪問賬號

  1. [root@zabbix dbforbix]# cp /opt/dbforbix/conf/config.props.sample /opt/dbforbix/conf/config.props
  2. [root@zabbix dbforbix]# vi /opt/dbforbix/conf/config.props
  3. ZabbixServerList=ZabbixServer
  4. ZabbixServer.Address=IP_ADDRESS_OF_ZABBIX_SERVER
  5. ZabbixServer.Port=PORT_OF_ZABBIX_SERVER
  6. ...
  7. DBforBIX.PidFile=./logs/dbforbix.pid
  8. ...
  9. DatabaseList=PGSQLDB2
  10. PGSQLDB2.Url=jdbc:postgresql://host:port/database
  11. PGSQLDB2.User=zabbix
  12. PGSQLDB2.Password=passw0rd

注意：/opt/dbforbix/init.d/dbforbix有個Bug，本來應該從config.props中讀取pid檔名的，結果寫死了是dbforbix.pid，config.props.sample中的預設值又是orabix.pid。

6)修改pgsqlquery.props
通過拷貝pgsqlquery.props.sample生成pgsqlquery.props。pgsqlquery.props中定義了監控專案及對應的查詢SQL，可以編輯QueryList對監控項做篩選。

  1. [root@zabbix dbforbix]# cp /opt/dbforbix/conf/pgsqlquery.props.sample /opt/dbforbix/conf/pgsqlquery.props

看一下pgsqlquery.props.sample包含的內容
pgsqlquery.props.sample:
  1. QueryList=activeconn,tupfetched,tupinserted,tupupdated,tupdeleted,xactcommit,xactrollback,exclusivelock,accessexclusivelock,accesssharelock,rowsharelock,rowexclusivelock,shareupdateexclusivelock,sharerowexclusivelock,checkpoints_timed,checkpoints_req,buffers_checkpoint,buffers_clean,maxwritten_clean,buffers_backend,buffers_alloc    

  3. #statistic of database
  4. activeconn.Query=select sum(numbackends) from pg_stat_database
  5. tupreturned.Query=select sum(tup_returned) from pg_stat_database
  6. tupfetched.Query=select sum(tup_fetched) from pg_stat_database
  7. tupinserted.Query=select sum(tup_inserted) from pg_stat_database
  8. tupupdated.Query=select sum(tup_updated) from pg_stat_database
  9. tupdeleted.Query=select sum(tup_deleted) from pg_stat_database
  10. xactcommit.Query=SELECT sum(xact_commit) FROM pg_stat_database
  11. xactrollback.Query=SELECT sum(xact_rollback) FROM pg_stat_database

  13. #locks
  14. exclusivelock.Query=SELECT count(*) FROM pg_locks where mode='ExclusiveLock'
  15. accessexclusivelock.Query=SELECT count(*) FROM pg_locks where mode='AccessExclusiveLock'
  16. accesssharelock.Query=SELECT count(*) FROM pg_locks where mode='AccessShareLock'
  17. rowsharelock.Query=SELECT count(*) FROM pg_locks where mode='RowShareLock'
  18. rowexclusivelock.Query=SELECT count(*) FROM pg_locks where mode='RowExclusiveLock'
  19. shareupdateexclusivelock.Query=SELECT count(*) FROM pg_locks where mode='ShareUpdateExclusiveLock'
  20. sharerowexclusivelock.Query=SELECT count(*) FROM pg_locks where mode='ShareRowExclusiveLock'

  22. checkpoints_timed.Query=select checkpoints_timed from pg_stat_bgwriter
  23. checkpoints_req.Query=select checkpoints_req from pg_stat_bgwriter
  24. buffers_checkpoint.Query=select buffers_checkpoint from pg_stat_bgwriter
  25. buffers_clean.Query=select buffers_clean from pg_stat_bgwriter
  26. maxwritten_clean.Query=select maxwritten_clean from pg_stat_bgwriter
  27. buffers_backend.Query=select buffers_backend from pg_stat_bgwriter
  28. buffers_alloc.Query=select buffers_alloc from pg_stat_bgwriter

7）啟動dbforbix deamon

  1. [root@zabbix dbforbix]# /etc/init.d/dbforbix start

8) 在Zabbix Server上建立Host
點選Zabbix GUI畫面的"Configuration->Hosts->Create Host"為被監控資料庫建立一個專門的Host。"Host name"設定為“PGSQLDB2”( 必須和config.props的DatabaseList中的名稱一致，這裡是“PGSQLDB2”)。並且把Host“PGSQLDB2”連結到前面匯入的模板"Template_PostgeSQL"。




9) 檢查資料是否已被收集
點選Zabbix GUI畫面的"Monitoring->Last data"檢查資料是否已被收集。


參考
http://www.smartmarmot.com/wiki/index.php/DBforBIX

3.5 方法4：使用pg_monz模板

pg_monz是一套可以監控PostgreSQL的zabbix模板，通過定製的agent UserParameter監控PostgreSQL資料庫，並且利用Zabbix的發現機制可以自動發現和監視資料庫和表。
pg_monz由下面幾個檔案組成
檔案 說明
pg_monz_template.xml
 模版定義檔案
userparameter_pgsql.conf
 提供PostgreSQL監控專案的使用者引數定義
find_dbname.sh
 PG資料庫的自動發現指令碼
find_dbname_table.sh
 PG資料表的自動發現指令碼



  

  

要了解pg_monz支援哪些監控專案，看一下userparameter_pgsql.conf就可以了
userparameter_pgsql.conf:

點選(此處)摺疊或開啟

  1. # PostgreSQL user parameter

  3. #
  4. # Server specific examples
  5. #
  6. # Get the total number of commited transactions
  7. UserParameter=psql.tx_commited[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select sum(xact_commit) from pg_stat_database"
  8. # Get the total number of rolled back transactions
  9. UserParameter=psql.tx_rolledback[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select sum(xact_rollback) from pg_stat_database"
  10. # Max Connections
  11. UserParameter=psql.server_maxcon[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "show max_connections"
  12. # PostgreSQL is running
  13. UserParameter=psql.running[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select 1" > /dev/null 2>&1 ; echo $?

  15. # Added by SRA OSS
  16. # Get number of checkpoint count (by checkpoint_timeout)
  17. UserParameter=psql.checkpoints_timed[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select checkpoints_timed from pg_stat_bgwriter"
  18. # Get number of checkpoint count (by checkpoint_segments)
  19. UserParameter=psql.checkpoints_req[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select checkpoints_req from pg_stat_bgwriter"
  20. # Get the total number of connections
  21. UserParameter=psql.server_connections[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select count(*) from pg_stat_activity;"
  22. # Get the total number of active (on processing SQL) connections
  23. UserParameter=psql.active_connections[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select count(state) from pg_stat_activity where state = 'active'"
  24. # Get the total number of idle connections
  25. UserParameter=psql.idle_connections[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select count(state) from pg_stat_activity where state = 'idle'"
  26. # Get the total number of idle in transaction connections
  27. UserParameter=psql.idle_tx_connections[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select count(state) from pg_stat_activity where state = 'idle in transaction'"
  28. # Get the total number of lock-waiting connections
  29. UserParameter=psql.locks_waiting[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select count(*) from pg_stat_activity where waiting = 't'"

  31. # Get buffer information
  32. UserParameter=psql.buffers_checkpoint[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select buffers_checkpoint from pg_stat_bgwriter"
  33. UserParameter=psql.buffers_clean[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select buffers_clean from pg_stat_bgwriter"
  34. UserParameter=psql.maxwritten_clean[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select maxwritten_clean from pg_stat_bgwriter"
  35. UserParameter=psql.buffers_backend[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select buffers_backend from pg_stat_bgwriter"
  36. UserParameter=psql.buffers_backend_fsync[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select buffers_backend_fsync from pg_stat_bgwriter"
  37. UserParameter=psql.buffers_alloc[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select buffers_alloc from pg_stat_bgwriter"

  39. # Get number of slow queries
  40. UserParameter=psql.slow_queries[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select count(*) from pg_stat_activity where state = 'active' and now() - query_start > '$5 sec'::interval"
  41. UserParameter=psql.slow_select_queries[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select count(*) from pg_stat_activity where state = 'active' and now() - query_start > '$5 sec'::interval and query ilike 'select%'"
  42. UserParameter=psql.slow_dml_queries[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select count(*) from pg_stat_activity where state = 'active' and now() - query_start > '$5 sec'::interval and query ~* '^(insert|update|delete)'"

  44. #
  45. # Database specific examples
  46. #
  47. # Get the size of a Database (in bytes)
  48. UserParameter=psql.db_size[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select pg_database_size('$5')"
  49. # Get number of active connections for a specified database
  50. UserParameter=psql.db_connections[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select numbackends from pg_stat_database where datname = '$5'"
  51. # Get number of tuples returned for a specified database
  52. UserParameter=psql.db_returned[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select tup_returned from pg_stat_database where datname = '$5'"
  53. # Get number of tuples fetched for a specified database
  54. UserParameter=psql.db_fetched[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select tup_fetched from pg_stat_database where datname = '$5'"
  55. # Get number of tuples inserted for a specified database
  56. UserParameter=psql.db_inserted[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select tup_inserted from pg_stat_database where datname = '$5'"
  57. # Get number of tuples updated for a specified database
  58. UserParameter=psql.db_updated[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select tup_updated from pg_stat_database where datname = '$5'"
  59. # Get number of tuples deleted for a specified database
  60. UserParameter=psql.db_deleted[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select tup_deleted from pg_stat_database where datname = '$5'"
  61. # Get number of commited/rolled back transactions for a specified database
  62. UserParameter=psql.db_tx_commited[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select xact_commit from pg_stat_database where datname = '$5'"
  63. UserParameter=psql.db_tx_rolledback[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select xact_rollback from pg_stat_database where datname = '$5'"

  65. # Cache Hit Ratio
  66. UserParameter=psql.cachehit_ratio[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "SELECT round(blks_hit*100/(blks_hit+blks_read), 2) AS cache_hit_ratio FROM pg_stat_database WHERE datname = '$5' and blks_read > 0 union all select 0.00 AS cache_hit_ratio order by cache_hit_ratio desc limit 1"

  68. # Added by SRA OSS
  69. # Get number of temp files
  70. UserParameter=psql.db_temp_files[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select temp_files from pg_stat_database where datname = '$5'"
  71. # Get temp file size (in bytes)
  72. UserParameter=psql.db_temp_bytes[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select temp_bytes from pg_stat_database where datname = '$5'"
  73. # Get percentage of dead tuples of all tables for a specified database
  74. UserParameter=psql.db_dead_tup_ratio[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select round(sum(n_dead_tup)*100/sum(n_live_tup+n_dead_tup), 2) as dead_tup_ratio from pg_stat_all_tables where n_live_tup > 0"
  75. # Get number of deadlocks for a specified database (9.2 or later)
  76. UserParameter=psql.db_deadlocks[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select deadlocks from pg_stat_database where datname = '$5'"


  79. #
  80. # Table specific examples
  81. #
  82. # Get table cache hit ratio of a specific table
  83. UserParameter=psql.table_cachehit_ratio[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select round(heap_blks_hit*100/(heap_blks_hit+heap_blks_read), 2) as cache_hit_ratio from pg_statio_user_tables where schemaname = '$5' and relname = '$6' and heap_blks_read > 0 union all select 0.00 as cache_hit_ratio order by cache_hit_ratio desc limit 1"
  84. # Get number of sequencial scan of a specific table
  85. UserParameter=psql.table_seq_scan[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select seq_scan from pg_stat_user_tables where schemaname = '$5' and relname = '$6'"
  86. # Get number of index scan of a specific table
  87. UserParameter=psql.table_idx_scan[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select coalesce(idx_scan,0) from pg_stat_user_tables where schemaname = '$5' and relname = '$6'"
  88. # Get number of vacuum count of a specific table
  89. UserParameter=psql.table_vacuum_count[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select vacuum_count from pg_stat_user_tables where schemaname = '$5' and relname = '$6'"
  90. # Get number of analyze count of a specific table
  91. UserParameter=psql.table_analyze_count[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select analyze_count from pg_stat_user_tables where schemaname = '$5' and relname = '$6'"
  92. # Get number of autovacuum count of a specific table
  93. UserParameter=psql.table_autovacuum_count[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select autovacuum_count from pg_stat_user_tables where schemaname = '$5' and relname = '$6'"
  94. # Get number of autoanalyze count of a specific table
  95. UserParameter=psql.table_autoanalyze_count[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select autoanalyze_count from pg_stat_user_tables where schemaname = '$5' and relname = '$6'"

  97. # Get number of tuples of a specific table
  98. UserParameter=psql.table_n_tup_ins[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select n_tup_ins from pg_stat_user_tables where schemaname = '$5' and relname = '$6'"
  99. UserParameter=psql.table_n_tup_upd[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select n_tup_upd from pg_stat_user_tables where schemaname = '$5' and relname = '$6'"
  100. UserParameter=psql.table_n_tup_del[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select n_tup_del from pg_stat_user_tables where schemaname = '$5' and relname = '$6'"
  101. UserParameter=psql.table_seq_tup_read[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select seq_tup_read from pg_stat_user_tables where schemaname = '$5' and relname = '$6'"
  102. UserParameter=psql.table_idx_tup_fetch[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select coalesce(idx_tup_fetch,0) from pg_stat_user_tables where schemaname = '$5' and relname = '$6'"
  103. UserParameter=psql.table_n_tup_hot_upd[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select n_tup_hot_upd from pg_stat_user_tables where schemaname = '$5' and relname = '$6'"
  104. UserParameter=psql.table_n_live_tup[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select n_live_tup from pg_stat_user_tables where schemaname = '$5' and relname = '$6'"
  105. UserParameter=psql.table_n_dead_tup[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select n_dead_tup from pg_stat_user_tables where schemaname = '$5' and relname = '$6'"


  108. #
  109. # Discovery Rule
  110. #
  111. # Database Discovery
  112. UserParameter=db.list.discovery[*],$5/find_dbname.sh $1 $2 $3 $4
  113. UserParameter=db_table.list.discovery[*],$5/find_dbname_table.sh $1 $2 $3 $4



使用例：
1）下載pg_monz
https://github.com/pg-monz/pg_monz/releases

2）安裝pg_monz

  1. [root@zabbix ~]# tar xfz pg_monz-1.0.tar.gz
  2. [root@zabbix ~]# cd pg_monz-1.0/pg_monz
  3. [root@zabbix pg_monz]# cp find_dbname.sh find_dbname_table.sh /usr/local/bin/
  4. [root@zabbix pg_monz]# cp userparameter_pgsql.conf /etc/zabbix/zabbix_agentd.d/
  5. [root@zabbix pg_monz]# chmod +x /usr/local/bin/find_dbname.sh
  6. [root@zabbix pg_monz]# chmod +x /usr/local/bin/find_dbname_table.sh
  7. [root@zabbix pg_monz]# /etc/init.d/zabbix-agent restart

3）匯入模板到Zabbix伺服器
點選Zabbix GUI畫面的"Configuration->Templates->Import"把模板檔案pg_monz_template.xml匯入Zabbix伺服器。

4）設定模板中的巨集
點選Zabbix GUI畫面的"Configuration->Templates"，再點選其中的"PostgreSQL Check"模板，然後點選"Marcos" Tab設定必要巨集引數（尤其是連線相關的引數）。


5)在Zabbix Server上建立Host
點選Zabbix GUI畫面的"Configuration->Hosts->Create Host"為被監控資料庫所在主機建立一個Host，如果該主機的Host已存在也可使用已有Host。這個Host要設定Zabbix Agent，並且把該Host連結到前面匯入的模板"PostgeSQL Check"。





6) 檢查資料是否已被收集
點選Zabbix GUI畫面的"Monitoring->Last data"檢查資料是否已被收集。



參考
http://pg-monz.github.io/pg_monz/index-en.html

3.6 小結

以上的方法1和方法2都需要自己再進行定製，而DBforBIX和pg_monz已經比較成熟了。pg_monz和DBforBIX相比更簡單，可監控的PostgreSQL專案也更多，還可以自動發現庫和表；DBforBIX的優勢則在於支援監控多種常用的資料庫以及可以使用jdbc連線池。綜合而言如果不需要監控多種資料庫個人傾向於pg_monz。

轉載: http://blog.chinaunix.net/uid-20726500-id-4513716.html

相關文章