usefull materials
github postgrespro
Waits monitoring on mail list of postgresql.org
pgconf2015 slides
Packages
ubuntu:
apt-get install gcc libreadline-dev zlib1g-dev libxml2-dev libldap2-dev python-dev libssl-dev build-essential libreadline-dev zlib1g-dev flex bison libxml2-dev libxslt-dev libssl-dev libperl-dev
fedora:
yum install -y gcc bison-devel flex bison readline-devel zlib-devel openssl-devel wget perl-ExtUtils-MakeMaker perl-ExtUtils-Embed readline-devel zlib-devel libxml2-devel openldap-devel python-devel openssl-devel
Install from source
mkdir /postgres/src -p cd /postgres/src wget https://github.com/postgrespro/postgres/archive/waits_monitoring_94.zip unzip waits_monitoring_94.zip cd postgres-waits_monitoring_94/ ./configure --with-ldap --with-perl --with-python --with-openssl --with-libxml --prefix=/usr/pgsql-pro --exec-prefix=/usr/pgsql-pro make make install /postgres/src/postgres-waits_monitoring_94/contrib/pg_stat_wait make make install
groupadd postgres -g 26 useradd --uid 26 --gid postgres --groups postgres -d /postgres -s /bin/bash -c "PostgreSQL Software Owner" postgres
enable waits monitoring
add to postgresql.conf
#History GUC parameters: shared_preload_libraries = 'pg_stat_wait.so' #for background worker that will be sample waits. pg_stat_wait.history = on #/off - turn on/off history recording pg_stat_wait.history_size = 1000 #how many records keep in history pg_stat_wait.history_period = 100 # period in millseconds between the sampling waits_monitoring=on
### alter system set waits_monitoring=on;
postgres=# select * from pg_stat_wait_current; pid | sample_ts | class_id | class_name | event_id | event_name | wait_time | p1 | p2 | p3 | p4 | p5 -------+------------------------------+----------+------------+----------+------------+------------+------+-------+-------+----+-------- 19213 | 2015-07-21 05:54:41.86929-04 | 3 | Storage | 0 | READ | 2971593 | 1663 | 13056 | 12817 | 0 | 6 19061 | 2015-07-21 05:54:41.86929-04 | 3 | Storage | 0 | READ | 622 | 1663 | 13056 | 12810 | 0 | 44 19054 | 2015-07-21 05:54:41.86929-04 | 3 | Storage | 1 | WRITE | 47717 | 1663 | 16418 | 16431 | 0 | 165053 19056 | 2015-07-21 05:54:41.86929-04 | 4 | Latch | 0 | Latch | 15808 | 0 | 0 | 0 | 0 | 0 19059 | 2015-07-21 05:54:41.86929-04 | 4 | Latch | 0 | Latch | 3179 | 0 | 0 | 0 | 0 | 0 19057 | 2015-07-21 05:54:41.86929-04 | 4 | Latch | 0 | Latch | 2971267 | 0 | 0 | 0 | 0 | 0 19055 | 2015-07-21 05:54:41.86929-04 | 4 | Latch | 0 | Latch | 157947 | 0 | 0 | 0 | 0 | 0 19206 | 2015-07-21 05:54:41.86929-04 | 5 | Network | 0 | READ | 544 | 0 | 0 | 0 | 0 | 0 19204 | 2015-07-21 05:54:41.86929-04 | 5 | Network | 0 | READ | 1958 | 0 | 0 | 0 | 0 | 0 19209 | 2015-07-21 05:54:41.86929-04 | 5 | Network | 0 | READ | 380 | 0 | 0 | 0 | 0 | 0 19062 | 2015-07-21 05:54:41.86929-04 | 5 | Network | 0 | READ | 2726490525 | 0 | 0 | 0 | 0 | 0 19201 | 2015-07-21 05:54:41.86929-04 | 5 | Network | 0 | READ | 704 | 0 | 0 | 0 | 0 | 0 19203 | 2015-07-21 05:54:41.86929-04 | 5 | Network | 0 | READ | 2002 | 0 | 0 | 0 | 0 | 0 19208 | 2015-07-21 05:54:41.86929-04 | 5 | Network | 0 | READ | 1625 | 0 | 0 | 0 | 0 | 0 19210 | 2015-07-21 05:54:41.86929-04 | 5 | Network | 0 | READ | 999 | 0 | 0 | 0 | 0 | 0 19205 | 2015-07-21 05:54:41.86929-04 | 5 | Network | 0 | READ | 248 | 0 | 0 | 0 | 0 | 0 19202 | 2015-07-21 05:54:41.86929-04 | 5 | Network | 0 | READ | 648 | 0 | 0 | 0 | 0 | 0 19207 | 2015-07-21 05:54:41.86929-04 | 5 | Network | 0 | READ | 718 | 0 | 0 | 0 | 0 | 0
ps: Many thanks to Vladimir Borodin for help !