PostgreSQL Performance Monitoring Tools

標籤：

PostgreSQL Performance Monitoring Tools

https://github.com/CloudServer/postgresql-perf-tools

This package includes three useful scripts aimed to help to pinpoint performance issues on systems with PostgreSQL as database backend.

All scritps are written in Python. Requirements:

• Python 2.6+
• psycopg2 module (http://initd.org/psycopg/)

All scripts require a connect to PostgreSQL database. They take traditional set of DB credentials: host address, port (5432 by default), database name, database user and password.

Note: you may need to modify pg_hba.conf file to allow the scripts to connect to the server. Please refer to official documentaion:http://www.postgresql.org/docs/9.3/static/auth-pg-hba-conf.html

For detailed list of command line options use --help / -h option.

軟體包

Python-2.7.tar.bz2   psycopg2-2.6.1.tar.gz   psutil-3.2.2.tar.gz

安裝 python2.7

[[email protected] soft_bak]# tar jxvf Python-2.7.tar.bz2

[[email protected] soft_bak]# cd Python-2.7

[[email protected] Python-2.7]# ./configure --prefix=/usr/local/python2.7/

[[email protected] Python-2.7]# make

[[email protected] Python-2.7]# make install

添加/usr/local/python2.7/bin/到Linux的環境變數中

[[email protected] bin]# echo $PATH

/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin

[[email protected] bin]# PATH="$PATH":/usr/local/python2.7/bin/

[[email protected] bin]# echo $PATH

/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin:/usr/local/python2.7/bin/

刪除/usr/bin中的python檔案，為python2.7建立串連

[[email protected] bin]# cd /usr/bin/

[[email protected] bin]# rm -rf python

[[email protected] bin]# ln -s /usr/local/python2.7/bin/python ./python

[[email protected] bin]# python

Python 2.7 (r27:82500, Jan 14 2016, 16:13:36)

[GCC 4.4.7 20120313 (Red Hat 4.4.7-4)] on linux2

Type "help", "copyright", "credits" or "license" for more information.

>>> 

解決Python升級後yum不能用的問題

#修改yum檔案

vi /usr/bin/yum

將檔案頭部的

#！/usr/bin/python

改為如下內容

#！/usr/bin/python2.6

安裝psutil

[[email protected] soft_bak]# tar zxvf psutil-3.2.2.tar.gz

[[email protected] PGMonitor]# cd psutil-3.2.2

[[email protected] psutil-3.2.2]# python setup.py install

安裝psycopg2

[[email protected] soft_bak]# tar zxvf psycopg2-2.6.1.tar.gz

[[email protected] PGMonitor]# cd psycopg2-2.6.1

[root[email protected] psycopg2-2.6.1]# python setup.py build_ext --pg-config /usr/local/pg945/bin/pg_config  build

[[email protected] psycopg2-2.6.1]# python setup.py build_ext --pg-config /usr/local/pg945/bin/pg_config  install

安裝setuptools

[[email protected] soft_bak]# tar zxvf setuptools-19.2.tar.gz

[[email protected] setuptools-19.2]# cd setuptools-19.2

[[email protected] setuptools-19.2]# python setup.py install

安裝postgresql-perf-tools

[[email protected] opt]# git clone https://github.com/CloudServer/postgresql-perf-tools

Initialized empty Git repository in /opt/postgresql-perf-tools/.git/

remote: Counting objects: 22, done.

remote: Total 22 (delta 0), reused 0 (delta 0), pack-reused 22

Unpacking objects: 100% (22/22), done.

[[email protected] opt]# cd postgresql-perf-tools/

啟動PostgreSQL

[[email protected] bin]$ ./pg_ctl -D ../data/ start

server starting

產生少量資料

[[email protected] bin]$ ./pgbench -i -F 100 -s 142 -h localhost -p 5432 -U postgres postgres

進程pgbench測試

 [[email protected] bin]$ ./pgbench -h localhost -p 5432 -c 10 -T 600

[[email protected] postgresql-perf-tools]# ./pg-top.py  --db-host=localhost

 

PostgreSQL Performance Monitoring Tools