Skip to content

postgrespro pg_stat_wait patch

postgrespro pg_stat_wait patch published on Комментариев к записи postgrespro pg_stat_wait patch нет

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 !

Primary Sidebar

Яндекс.Метрика