PGWatch2 ile PostgreSQL Monitoring

Veri tabanı izleme, yüksek performanslı ve sürdürülebilir bir sisteme sahip olmak için veri tabanı performansı ve kaynaklarının izlenmesidir. Veri tabanları kurum ve kuruluşların en kritik iş süreçlerinin merkezinde yer almaktadır, bu sebeple oluşabilecek sorunların hızlı ve doğru çözülmesi için analizler yapmak gerekmektedir.

Pgwatch2; Cybertec tarafından geliştirilmiştir. Veri tabanı sunucusu üzerinde belirli metrikler ile topladığı verileri grafana ile görselleştirerek veri tabanı durumunu canlı izlemeye olanak tanıyan bir araçtır.

Kuruluma başlamadan önce sunuculara ait IP bilgileri aşağıdaki gibidir.

Monitoring Sunucusunda Yapılacak İşlemler

Pgwatch2 kurulumunu docker image üzerinden gerçekleştireceğimiz için ilk olarak docker kurulumunu yapıyoruz.

PostgreSQL Veri Tabanı Sunucusu:192.168.203.137
Pgwatch2 Monitoring Sunucusu:192.168.203.139

[root@monitor~] yum update 
[root@monitor~] yum install docker 
[root@monitor~] systemctl enable docker
[root@monitor~] systemctl start docker

Pgwatch2 , 3 farklı servisin birbiriyle entegre çalışmasıyla oluşan mimaridir. Veri tabanı bağlantısını gerçekleştirdiğimiz Pgwatch2 bağlantı paneli 8080 portu üzerinde hizmet vermektedir. Bu panelde veri tabanı sunucusu tanımlama, pgwatch2 logları ve metrik ayarlama gibi işlemler yapılmaktadır.

Veri tabanı sunucusu üzerinde metrikler sayesinde toplanan veriler Grafana uygulaması ile görselleştirilerek canlı olarak veri tabanının izlenmesine olanak tanımaktadır. Grafana uygulaması 3000 portunda hizmet vermektedir.

Pgwatch2 tarafından toplanan metrik verileri local bir PostgreSQL veri tabanında saklanmaktadır. Local’de çalışan ve sadece metrik verilerini tutan PostgreSQL servisi ise 5432 portunda hizmet vermektedir.

Docker kurulumunu tamamladıktan sonra pgwatch2 imajını sunucuya çekerek ayağa kaldırıyoruz.

[root@monitor~] docker run -d --restart=unless-stopped --name pw2 \
  -p 3000:3000 -p 8080:8080 -p 127.0.0.1:5432:5432 \
  -e PW2_TESTDB=true \
  cybertec/pgwatch2-postgres:latest

docker ps komutuyla konteyner durumunu görüntüleyebiliriz.

[root@monitor~] docker ps        
CONTAINER ID        IMAGE                               COMMAND                  CREATED             STATUS              PORTS                                                                                        NAMES
89262f81a0d5        cybertec/pgwatch2-postgres:latest   "/pgwatch2/docker-..."   5 seconds ago       Up 4 seconds        8080/tcp, 9187/tcp, 0.0.0.0:3000->3000/tcp, 0.0.0.0:5432->5432/tcp, 0.0.0.0:8080->8080/tcp   pgwatch2

Konteynerın ayağa kalktığını check ettikten sonra, tarayıcı üzerinden 3000 portuna ilerlediğimizde grafana uygulaması üzerinde local’de çalışan veri tabanının test veri tabanı olarak bağlandığını görüyoruz.

Veri Tabanı Sunucusu Hazırlıkları

İlk olarak pgwatch2’nin sunucu üzerinde metriklerle bilgi toplayabilmesi için pg_hba.conf dosyası üzerine monitor sunucusunun bilgilerini giriyoruz

[root@postgres~] su - postgres 
[-bash-4.2$] vi 12/data/pg_hba.conf 
TYPE  DATABASE        USER            ADDRESS                 METHOD 
host  all             all             192.168.203.139/32      md5

Temel gereksinim olarak, Pgwatch’nin sunucunuza bağlanmak ve metrikleri almak için bir kullanıcıya (superuser önerilmez) ihtiyacı vardır. Bu sebeple pgwatch2 isminde bir veri tabanı kullanıcısı oluşturuyoruz.

[root @ postgres ~] su - postgres 
[-bash-4.2 $] psql
postgres = # CREATE ROLE pgwatch2 WITH LOGIN PASSWORD '123';

Pgwatch2 kullanıcısının veri tabanı üzerinde metrik toplayabilmesi için bazı ayrıcalıklara ihtiyaç vardır. pgwatch2 kullanıcısına pg_monitor ile birlikte ihtiyacı olan ayrıcalıkları veriyoruz.

postgres=# ALTER ROLE pgwatch2 CONNECTION LIMIT 3; 
postgres=# GRANT pg_monitor TO pgwatch2; 
postgres=# GRANT CONNECT ON DATABASE postgres TO pgwatch2; 
postgres=# GRANT USAGE ON SCHEMA public TO pgwatch2; 
postgres=# GRANT EXECUTE ON FUNCTION pg_stat_file(text) to pgwatch2;

PostgreSQL’de performans sorunlarını tespit etmek amacıyla kullanılan pg_stat_statements extensionunu aktif ederek SQL sorgu istatistiklerini izlemek için ön hazırlığımızı yapıyoruz. Bunun için ilk olarak kurulu değilse postgresql12-contrib paketini kuruyoruz.

[root@postgres~] yum install postgresql12-contrib

Pg_stat_statements’ı aktif edebilmek için postgresql.conf dosyası içerisinde yer alan shared_preload_libraries ve track_io_timing parametrelerinde değişiklik yapmamız gerekmektedir. Burada dikkat edilmesi gereken durum ise shared_preload_libraries parametresi üzerinde yapılan değişikliklerin aktif olması için servisin restart edilmesi gerekmektedir.

[root@postgres~] su - postgres 
[-bash-4.2$] psql 
postgres= ALTER SYSTEM SET shared_preload_libraries TO "pg_stat_statements"; 
postgres= ALTER SYSTEM SET track_io_timing TO on; 
postgres= SELECT pg_reload_conf();
[root@postgres~] systemctl restart postgresql-12.service

Servisi yeniden başlattıktan sonra, izlenecek olan veri tabanında postgres kullanıcısıyla pg_stat_statements için extension oluşturuyoruz.

[root@postgres~] su - postgres 
[-bash-4.2$] psql -c "CREATE EXTENSION IF NOT EXISTS pg_stat_statements"

Pgwatch2’nin sistem kaynakları ve kaynakların kullanımı hakkında anlık olarak veri toplayabilmesi için psutil ve plpython kurmamız gerekmektedir.

[root@postgres~] yum install postgresql12-plpython3
 [root@postgres~] yum installl  epel-release 
[root@postgres~] yum install python3-psutil

Kurulumu tamamladıktan sonra plpython3 için extension oluşturuyoruz.

[root@postgres~] su - postgres 
[-bash-4.2$]psql -c "CREATE EXTENSION plpython3u"

Pgwatch2 ile Veri Tabanı Sunucusu Bağlantısının Kurulması

Yukarıda yaptığımız işlemlerde Grafana ile Pgwatch2 veri tabanı bağlantısını sağladıktan sonra veri tabanı sunucumuzda bir takım hazırlıklar yapmıştık. Şimdi ise veri tabanı sunucumuz ile pgwatch arasındaki bağlantıyı sağlayacağız. Bunun için tarayıcıdan http://192.168.203.139:8080/ adresine gidiyoruz ve bizi veri tabanı bağlantısını sağlayacağımız veri tabanı paneli karşılamaktadır.

Buradaki textboxlara bağlantı adreslerimizi ve metrik konfigürasyonlarını aşağıdaki gibi girerek veri tabanı bağlantısını sağlıyoruz.

Unique Name= postgres 
DB Host= 192.168.203.137 
DB Portu=5432 
DB dbname=postgres 
DB User=pgwatch2 
DB Password=123 
Preset metrics config=FULL
Standby preset config=Minimal 
Statement timeout [seconds= 5
Master mode only?=True

Veri Tabanı bağlantısını sağladıktan sonra kontrol etmek için http://192.168.203.139:3000/ adresine yani grafanaya gidiyoruz.

Ekran görüntüsünden de anlaşılabileceği gibi veri tabanımız posgres ismiyle tanımlanmıştır. Şimdi veri tabanı sunucusu üzerinde daha fazla veri toplayabilmek için bazı metrikleri aktif etmemiz gerekmektedir. Pgwatch2 docker konteynırı içerisine girerek bu metrikleri veri tabanı sunucusu üzerinde çalıştıracak şekilde aktif ediyoruz.

[root@monitor~]docker exec -it pgwatch2 bash 
[root@68816ad648d8:/#] su - postgres 
[postgres@68816ad648d8:~$]export PGUSER=postgres 
[postgres@68816ad648d8:~$]psql -f /pgwatch2/metrics /00_helpers/get_stat_activity/9.2/metric.sql -h 192.168.203.137 -d postgres 
[postgres@68816ad648d8:~$]psql -f /pgwatch2/metrics/00_helpers/get_stat_replication/9.2/metric.sql -h 192.168.203.137 -d postgres 
[postgres@68816ad648d8:~$] psql -f /pgwatch2/metrics/00_helpers/get_wal_size/10/metric.sql -h 192.168.203.137 -d postgres 
[postgres@68816ad648d8:~$]psql -f /pgwatch2/metrics/ 00_helpers/get_sequences/10/metric.sql -h 192.168.203.137 -d postgres

get_stat_statements() metriğini çalıştırırken aşağıdaki gibi bir hata ile karşılaşıyoruz.

[postgres@68816ad648d8:~$]psql -f /pgwatch2/metrics/00_helpers/get_stat_statements/9.4/metric.sql -h 192.168.203.137 -d postgres
Kullanıcı postgres parolası:
BEGIN
psql:/pgwatch2/metrics/00_helpers/get_stat_statements 9.4/metric.sql:17: NOT: "pg_stat_statements" uzantısı zaten mevcut, atılıyor
CREATE EXTENSION
CREATE FUNCTION
GRANT
COMMENT
psql:/pgwatch2/metrics/00_helpers/get_stat_statements/9.4/metric.sql:55: NOT: search_path = "$user ", public
psql:/pgwatch2/metrics/00_helpers/get_stat_statements/9.4/metric.sql:55: HATA: get_stat_statements() SECURITY DEFINER helper, tüm kullanıcıların nesneler oluşturabileceği search_path üzerindeki tüm şemalar güvenli olmadığından oluşturulmaz -
güvenliği sıkılaştırmak için 'REVOKE CREATE ON SCHEMA $my_schema FROM public' komutunu çalıştırın veya kontrolü devre dışı bırakmak için DO bloğunu yorumlayın
CONTEXT: inline_code_block PL/pgSQL fonksiyonu, 15. satır, RAISE içinde
ROLLBACK

Bu hatadan kurtulmak için tüm kullanıcılardan(sahip ve superuser kullanıcıları hariç) public şemasında CREATE ayrıcalığını iptal ediyoruz.

[root@postgres~] su - postgres 
[postgres@postgres~] psql 
postgres=# REVOKE CREATE ON SCHEMA public FROM public;

get_state_statements() metriğini tekrar çalıştırdığımız da get_stat_statements fonksiyonunun oluşturulduğunu, fakat public şemada bu fonksiyona izin vermemiz gerektiğini görüyoruz. İzin vermek için aşağıdaki adımı uyguluyoruz.

[root@postgres~] su - postgres 
[postgres@postgres~] psql 
postgres=# ALTER FUNCTION get_stat_statements() search_path TO public;

get_state_statements() metriğini tekrar çalıştırıyoruz.

[postgres@68816ad648d8:~$]psql -f /pgwatch2/metrics/00_helpers/get_stat_statements/9.4/metric.sql -h 192.168.203.137

Veri tabanı sistem kaynaklarını analiz edebilmek için monitor sunucusunda cpu, mem, disk, disk-i/o ve load average metriklerini çağırıyoruz.

[postgres@68816ad648d8:~$] psql -f /pgwatch2/metrics/00_helpers/get_psutil_cpu/9.1/metric.sql -h 192.168.203.137 postgres 
[postgres@68816ad648d8:~$] psql -f /pgwatch2/metrics/00_helpers_mem_ /metric.sql -h 192.168.203.137 postgres 
[postgres@68816ad648d8:~$] psql -f /pgwatch2/metrics/00_helpers/get_psutil_disk/9.1/metric.sql -h 192.168.203.137 postgres 
[postgres@688116ad648d8:~$] psql -f/pgwatch2/metrics/00_helpers/get_psutil_disk_io_total/9.1/metric.sql -h 192.168.203.137 postgres 
[postgres@68816ad648d8:~$] psql -f /pgwatch2/metrics/00_helpers/get_load_average/9.1/metric.sql -h 192.168.203.137 postgres

Kurulum adımlarımızı tamamlamış olduk.

Grafana Dashobard

Şimdi grafana üzerinde bize sunulan özelliklerin bir kaçına göz atalım.

Veri tabanına genel bir bakış yapmak için DB Overview paneli 5 saniyelik yenileme ve 1 saniyelik ortalamalar ile izlenebilir.

Sistem Kaynaklarının kullanımı ve kaynak planlaması için ise System Stats bize canlı veriler sunmaktadır.

Eğer veri tabanı sunucunuzda iyi bir log yapılandırması yapılmış ise En çok kullanılan sorgular, ortalama çalışma süresi uzun olan sorgular ve sorguların ortalama çalışma süresi gibi istatistikleri Stat Statements Top panelinde görüntüleyebilirsiniz.

Index büyüklükleri, tablo büyüklükleri yönetimsel grafikleri Biggest Relations Treemap panelinden görüntüleyebilirsiniz.

Tablo büyüklük istatistiklerini Tables Top panelinde görüntüleyebilirsiniz. Farklı zaman dilimleri arasında tabloların büyüme oranını analiz etme de kullanabilirsiniz.

Pgwatch2 daha bir çok özelliğe sahiptir. Tablo durumları, tablo detayları, Checkpointer/Bgwriter/Block IO istatistikleri, Veri tabanı lag durumları, Index’ler hakkında bilgiler ve bunların dışında bir veri tabanını izlemek için gerekli bir çok özelliği içinde barındırmaktadır.