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 data Base backend.

All Scritps is 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 Passwo Rd.

Note:you may need to modify pg_hba.conf file to allow the scripts to connect to the server. 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.

Package

PYTHON-2.7.TAR.BZ2 psycopg2-2.6.1.tar.gz psutil-3.2.2.tar.gz

Installing 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

Add/usr/local/python2.7/bin/to Linux environment variables

[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/

Delete the Python file in/usr/bin and create a connection for 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.

>>>

Troubleshoot problems with the Python upgrade that Yum can't use

#修改yum文件

Vi/usr/bin/yum

The file header is

#！ /usr/bin/python

Change to the following content

#！ /usr/bin/python2.6

Installing 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

Installing 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

Installing 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

Installing 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 (Delta 0), reused 0 (Delta 0), pack-reused 22

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

[Email protected] opt]# CD postgresql-perf-tools/

Start PostgreSQL

[Email protected] bin]$./pg_ctl-d. /data/start

Server starting

Generate small amounts of data

[Email protected] bin]$/pgbench-i-F 100-s 142-h localhost-p 5432-u postgres postgres

Process Pgbench Test

[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