Bucardo : Menambah Server ke dalam Replikasi Master To Master

  Beberapa waktu yang lalu, saya memutuskan untuk menambahkan 1 server lagi kedalam replikasi master to master yang saya pergunakan. Saya memakai Bucardo untuk menangani replikasi master to master. Performancenya cukup memuaskan saya. Nah saat ini saya merasa perlu untuk menambah 1 server lagi untuk membagi beban kerja di server.

Pada awalnya tujuan memakai 2 server hanya untuk keperluan berbagi beban server dan backup di dalam 1 lokasi gedung, ternyata diperjalanannya untuk lokasi kota yang lain perlu juga di bagi beban servernya dan backup jika sewaktu waktu jaringan antar kota terputus, sehingga tidak akan mengganggu transaksi lokal. Untuk instalasi awal replikasi master to master dengan bucardo dapat anda baca disini.

Informasi :

  • db1 memiliki ip address 192.16.0.1
  • db2 memiliki ip address 192.16.0.2
  • db1 dan db2 berada dalam 1 gedung
  • db3 memiliki ip address 192.16.1.3
  • db3 berada di kota yang berbeda

Nah berikut adalah langkah langkah untuk menambahkan Server Database db3  ke dalam replikasi :

  • Login ke Server Bucardo dan stop replikasinya.
    • bucardo stop
  • Login ke Server Applikasi dan stop semua applikasi yang terkoneksi ke Server Database
  • Login ke Server Database ke 1 , backup dan kirim Server Database ke 3
    • pg_dump -h 127.0.0.1 -U postgres my_database > my_database.dmp
    • scp my_database.dmp root@192.16.1.3:/root
  • Login ke Server Database ke 3, buat database baru, dan restore database backupnya
    • psql -h 127.0.0.1 -U postgres -c “create database my_database; ” template1
    • psql -h 127.0.0.1 -U postgres -d my_database < my_database.dmp
  • Login ke Server Bucardo, daftarkan Server Database ke 3, dan syncronize ulang
    • bucardo add db db3 dbname=eis dbhost=192.16.1.3 dbuser=postgres dbpass=password
    • bucardo remove sync db_sync
    • bucardo remove dbgroup db_group
    • bucardo add dbgroup db1:source db2:source db3:source
    • bucardo add sync db_sync herd=db_herd dbs=db_group
    • bucardo start
  • Kemudian check log.bucardo, kita akan menemukan pesan kesalahan seperti ini :
    • “(23143) [Mon Dec 3 13:18:24 2012] VAC Warning! VAC was killed at line 6486: DBD::Pg::st pg_result failed: ERROR: relation “bucardo.delta_265672? does not exist”
    • Tenang, pesan kesalahan ini terjadi karena kita melakukan restore database beserta object object bucardo dari server asal.
    • Setelah kita restore database dan kemudian bucardo kita start, maka bucardo akan melakukan proses sinkronisasi objectnya. Karena kita melakukan restore, tentu saja object id atau OID dari object table di server yang baru akan berbeda dengan object id dari server yang lama. Oleh karena itu, bucardo akan mendaftarkan ulang object table di server yang baru, sehingga kita harus menghapus object id yang lama secara manual.
    • Mari kita periksa table bucardo_delta_targets, disinilah object table di daftarkan.
      • select * from bucardo.bucardo_delta_targets;
    • Kita akan mendapati, daftar table dengan perbedaan di tanggal registrasi, di kolom cdate. Yang perlu kita lakukan adalah menghapus semua table yang memiliki tanggal cdate tidak sama dengan saat ini atau saat prosess restore.
      • delete from bucardo.bucardo_delta_targets where to_char(cdate,’dd/mm/yyyy’) <> to_char(now(),’dd/mm/yyyy’);
    • Setelah itu periksa kembali file log.bucardo, seharusnya pesan kesalahan sudah tidak muncul lagi.
  • Done

Nah, sekarang kita memiliki 3 server di dalam replikasi master to master kita, db1 dan db2 dalam 1 gedung, db3 berada di kota yang lain. Dengan cara ini, apabila terjadi putusnya jaringan antar kota, maka user di kota yang lain tetap dapat menjalankan applikasi. Dan pada saat jaringan tersambung kembali maka bucardo akan melakukan sinkronisasi data kembali. Bagaimana pendapat anda ?

Defrag database PostgreSQL

  Artikel ini di peruntukkan pengguna Database PostgreSQL yang memutuskan untuk melakukan defrag terhadap database anda.

Berikut adalah langkah langkah untuk melakukan defrag di database PostgreSQL :

  • Cek tabel tabel dan index index yang memiliki fragmentasi dengan menjalankan skrip berikut ini dan melihat nilai rasio dan wastebyte dari tabel bloat anda.
    • Nilai rasio itu menentukan seberapa besar fragmentasi yang terjadi di dalam tabel atau index anda.
    • Semakin besar rasio semakin besar fragmentasi yang terjadi.
    • Wastebyte adalah kondisi dimana blok data tidak dapat dipergunakan untuk menyimpan data.
  • Apabila anda mendapati rasio > 1.5 lakukan VACUUM ANALYZE.
    • VACUUM ini akan merubah wastebyte yang tidak dapat dipergunakan untuk transaksi menjadi aktif dan dapat dipergunakan lagi.
    • ANALYZE akan melakukan pembuatan statistik untuk tabel atau index tersebut.
    • Dengan melakukan VACUUM ANALYZE ini, maka wastebyte yang ada akan di aktifkan kembali, walaupun posisinya berada ditengah tengah blok data yang ada isinya.
  • Apabila anda mendapati rasio > 5 lakukan VACUUM FULL, REINDEX TABLE dan ANALYZE
    • VACUUM FULL ini akan mengaktifkan kembali wastebyte yang ada dan mengisinya dengan blok data dari bagian akhir dari tabel atau index. Sehingga bagian yang ter-fragmentasi itu akan terisi dengan data, kemudian sisa free space yang ada akan dikembalikan ke Sistem Operasi, sehingga tabel tidak akan menyimpan free-space lagi.
    • VACUUM FULL ini akan mendefrag tabel, tetapi tidak mengurutkan datanya sesuai dengan primary key tabel itu.
    • Kemudian lakukan REINDEX terhadap tabel ini, untuk membuat ulang index-indexnya.
    • Kemudian lakukan ANALYZE untuk melakukan pembuatan statistik untuk tabel dan indexnya.
    • Lakukan proses ini pada saat koneksi user sedikit, karena postgresql akan melakukan lock terhadap tabel secara keseluruhan. Untuk versi 9.0 keatas proses vacuum sangat cepat.
  • Apabila anda mendapati rasio > 10 lakukan dengan metode CTAS.
    • Untuk proses ini, pastikan jangan ada user atau applikasi yang terkoneksi ke database.
    • Proses ini akan melakukan membuat tabel baru dengan data yang telah terurut berdasarkan primary key atau unique indexnya.
    • Jangan lupa untuk melakukan ANALYZE di akhir proses untuk membuat statistik terhadap tabel dan indexnya.
  • Solusi yang lain adalah menggunakan extensi pg_reorg.
    • Solusi ini berbasis metode CTAS dan saya belum pernah mencobanya.

Demikian cara saya untuk melakukan defrag database PostgreSQL.

Bagaimana dengan anda ?

Postgresql : Mencari table bloated atau ter-fragmentasi

  Postgresql mempunya istilah untuk fragmentasi yaitu bloat. Bloat ini muncul terhadap dua object postgresql yaitu tabel dan index. Artikel ini akan menjelaskan bagaimana cara kita mencari tabel yang ter-fragmentasi. Apabila anda menemukannya, maka tabel tersebut harus di defrag.

Untuk mencari tabel dan index mana saja yang memiliki Bloat, kita dapat menggunakan script yang telah disediakan oleh postgreSQL yang dapat dilihat disini. Berikut saya copy paste dari halaman wiki PostgreSQL:

SELECT current_database(), schemaname, tablename,
       /*reltuples::bigint, relpages::bigint, otta,*/
       ROUND( CASE WHEN otta=0
                   THEN 0.0 ELSE sml.relpages/otta::numeric
                   END,1) AS tbloat,
       CASE WHEN relpages < otta THEN 0 
                ELSE bs*(sml.relpages-otta)::bigint END AS wastedbytes,
       iname, /*ituples::bigint, ipages::bigint, iotta,*/
       ROUND(CASE WHEN iotta=0 OR ipages=0 
                   THEN 0.0 ELSE ipages/iotta::numeric 
                   END,1) AS ibloat,
       CASE WHEN ipages < iotta THEN 0 
               ELSE bs*(ipages-iotta) END AS wastedibytes
FROM 
( SELECT schemaname, tablename, cc.reltuples, cc.relpages, bs,
             CEIL((cc.reltuples*((datahdr+ma- 
                    (CASE WHEN datahdr%ma=0 THEN ma 
                             ELSE datahdr%ma END))+nullhdr2+4)
                     )/(bs-20::float)) AS otta,
             COALESCE(c2.relname,'?') AS iname, 
             COALESCE(c2.reltuples,0) AS ituples, 
             COALESCE(c2.relpages,0) AS ipages,
             COALESCE(CEIL((c2.reltuples*(datahdr-12)
                           )/(bs-20::float)),0) AS iotta 
                           -- very rough approximation, assumes all cols
  FROM 
  (  SELECT ma,bs,schemaname,tablename,
               ( datawidth+(hdr+ma-
                 (case when hdr%ma=0 THEN ma ELSE hdr%ma END))
               )::numeric AS datahdr,
               ( maxfracsum*(nullhdr+ma-
                 (case when nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))
               ) AS nullhdr2
    FROM 
    (  SELECT schemaname, tablename, hdr, ma, bs,
                  SUM((1-null_frac)*avg_width) AS datawidth,
                  MAX(null_frac) AS maxfracsum,
                  hdr+( SELECT 1+count(*)/8
                           FROM pg_stats s2
                           WHERE null_frac<>0 
                               AND s2.schemaname = s.schemaname 
                               AND s2.tablename = s.tablename
                  ) AS nullhdr
       FROM pg_stats s, 
       ( SELECT ( SELECT current_setting('block_size')::numeric) AS bs,
                    CASE WHEN substring(v,12,3) IN ('8.0','8.1','8.2') 
                    THEN 27 ELSE 23 
                    END AS hdr,
                    CASE WHEN v ~ 'mingw32' THEN 8 ELSE 4 END AS ma
         FROM ( SELECT version() AS v) AS foo
      ) AS constants
      GROUP BY 1,2,3,4,5
    ) AS foo
  ) AS rs
  JOIN pg_class cc ON cc.relname = rs.tablename
  JOIN pg_namespace nn
    ON ( cc.relnamespace = nn.oid 
            AND nn.nspname = rs.schemaname 
            AND nn.nspname <> 'information_schema' )
  LEFT JOIN pg_index i ON indrelid = cc.oid
  LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid
) AS sml
ORDER BY wastedbytes DESC

Yang perlu diperhatikan dari skrip ini adalah :

  • Skrip ini diperuntukkan sebagai informasi penunjang, untuk memastikan keakuratannya harus di cek dengan modul contrib pgstattuple atau pg_freespacemap.
  • Kolom tbloat adalah rasio fragmentasi di table antara besarnya table dan free space yang dimilikinya.
  • Kolom wastedbytes adalah besarnya free space yang dimiliki tabel tersebut dalam satuan byte yang tidak dapat dipergunakan.
  • Kolom ibloat adalah rasio fragmentasi di index antara besarnya index dan free space yang dimilikinya
  • Kolom watedibytes adalah besarnya free space yang dimiliki index tersebut dalam satuan byte yang tidak dapat dipergunakan.

Apabila skrip ini dijalankan, maka akan kita dapati tabel dan index yang memiliki bloat. Perhatikan kolom kolom rasio-nya dan gabungkan dengan kolom wastebyte-nya. Perhatikan pola antar tabel. Rasio 1 pada tabel A, nilai wastebyte nya dapat berbeda dengan tabel B untuk rasio yang sama.

Lantas bagaimana menentukan mana tabel yang perlu di defrag ? Jawabannya adalah kenalilah karakteristik database anda.

Database saya memiliki beberapa tabel yang karakteristiknya berbeda. Ada tabel tabel yang sering kali memakai proses update secara perlahan lahan. Ada juga tabel yang lebih memakai metode proses delete-insert daripada update. Ada juga yang lebih banyak proses insertnya daripada proses updatenya. Ada juga tabel yang cepat sekali pertumbuhan datanya ada yang lambat.

Nah, bagaimana cara saya menentukan tabel yang perlu defrag adalah dengan membatasi ratio tbloat dan ibloat > 1.5 atau yang memiliki wastebyte > 3Mb. Dengan syarat seperti itu saya akan mendapati 5-6 tabel yang perlu di defrag setiap minggu nya. Saya tinggal melakukan defrag.

Bagaimana dengan anda ?

Defrag Database dengan metode CTAS

  Artikel sebelumnya menjelaskan perlu tidaknya kita melakukan defrag database dan untuk artikel berikut akan menjelaskan bagaimana melakukan defrag Database secara manual. Cara ini saya pelajari waktu masih menggunakan Oracle 7.4 dan cukup efektif untuk menjaga kesehatan database. Cara ini juga dapat di pergunakan di database yang lain seperti PostgreSQL, MySQL, MS-SQL atau RDBMS lainnya. CTAS adalah singkatan dari Create Table As Select.

Berikut adalah langkah langkah untuk melakukan defrag tabel dengan memakai perintah database Oracle / PostgreSQL :

  • Tentukan tabel yang menurut anda perlu di defrag.
    • Setiap database memiliki cara bagaimana menentukan tabel yang perlu di defrag.
    • PostgreSQL dan MySQL menggunakan script sementara MS-SQL dan Oracle memiliki dynamic view untuk mendeteksinya.
  • Cek primary key dari tabel tersebut, atau jika tidak memilikinya pakailah unique index. Cek urutan kolomnya.
  • Cek trigger dari tabel tersebut dan siapkan script untuk create ulang.
  • Buat tabel baru dengan perintah :

sql> create table new_table as select * from old_table order by col_1, col_2, col_3 ;

    • Disini kita membuat tabel yang baru dan data yang ada kita urutkan sesuai dengan urutan kolom di primary key yang dimiliki tabel tersebut
    • Dengan cara ini kita sudah membuat tabel dengan blok data di filesystem terurut mulai dari awal record hingga akhir record
  • Drop tabel yang lama beserta semua object yang mengikutinya dengan perintah :

sql> drop table old_table cascade;

  • create table baru dengan perintah :

sql> create table old_table as select * from new_table;

    • di MS-SQL dan My-SQL dapat menggunakan perintah select * into old_table from new_table;
    • di PostgreSQL dapat menggunakan perintah alter table new_table rename to old_table;
  • Recreate ulang semua constraint dan index nya, dengan perintah :

sql> alter table old_table add constraint old_table_pk primary key ….. ;

sql> alter table old_table add constraint old_table_ux unique …..;

sql> create index old_table_ix on old_table ( … );

  • Analyze table dan index nya untuk membuat data statistic terhadap table dan index yang baru buat ulang ini

sql> analyze table old_table;

sql> analyze index old_table_pk;

sql> analyze table old_table_ux;

  • Buat ulang trigger dari tabel yang lama.
  • Drop table yang baru

sql> drop table new_table;

  • Done.
Seperti yang kita lihat bersama, teknik yang dipergunakan adalah menggunakan create table as select. Teknik ini dapat dipergunakan untuk semua RDBMS.
Hal hal yang perlu diperhatikan pada saat menggunakan teknik ini adalah :
  • Applikasi atau user tidak diperkenankan mengakses tabel yang akan di defrag.
  • Jikalau perlu matikan dulu akses ke database server.
  • Jangan lupa untuk selalu mengakhiri prosedur ini dengan membuat statistik terhadap tabel yang baru dibuat. Hal ini akan membantu database dalam mengeksekusi perintah query.

Bagaimana pendapat anda ?

Perlukah Database di Defrag ?

  Untuk membuat Database agar selalu berjalan dengan lancar merupakan salah satu tugas dari Database Administrator (DBA), terutama di area perawatan database. Performance Database dapat melambat seiring berjalannya waktu, dan dapat disebabkan oleh berbagai hal mulai dari keterbatasan memory, media penyimpanan sampai pada penambahan jumlah user.

Salah satu hal yang perlu di perhatikan oleh DBA adalah data fragmentation. Data fragmentation adalah kondisi dimana data tersimpan di dalam blok data yang tidak berurutan tetapi tersebar di berbagai tempat. Bagaimana data tersebut disimpan bergantung pada filesystem dari sistem operasi yang dipergunakan.

Apakah anda pernah memakai software defragmentation ? Software ini sangat populer dikalangan para pengguna sistem operasi Windows. Saya sering menggunakan software ini dikala masih menggunakan sistem operasi Windows 95 dan XP. Software ini akan mengatur, mengkelompokkan, dan mengurutkan kembali file file yang ada di dalam media penyimpanan. Hasilnya performa sistem operasi menjadi lebih baik dan lebih cepat dibandingkan sebelum di lakukan defrag.

Hal yang sama berlaku juga untuk Database. Mengapa ? karena cara database menyimpan data bergantung pada filesystem yang dipergunakannya. Sehingga jika file dapat ter-fragmentasi tentu Database juga dapat ter-fragmentasi.

Apakah semua Database akan ter-fragmentasi ? tentu saja, seiring berjalannya waktu dan pemakaian database tersebut. Semakin banyak operasi insert, delete, update terhadap suatu tabel di dalam database, semakin besar kemungkinan tabel dan indexnya mengalami fragmentasi. Ini adalah hal yang normal, wajar saja, dan pasti akan dialami. Sehingga diperlukan proses untuk melakukan defragmentation terhadap database.

Secara umum, database yang telah didefrag memiliki kecepatan baca atau query atau operasi select akan lebih baik. Tools yang dipergunakan umumnya berupa script database. Ada juga software buatan pihak ketiga yang khusus melakukan defrag terhadap database.

Bagaimana dengan anda, sudahkah anda melakukan defrag terhadap database anda ?

PostgreSQL 9.2 Release

  Kemarin, komunitas PostgreSQL menyambut peluncuran versi terbaru dari database PostgreSQL, yaitu versi 9.2. Menurut press release yang dikeluarkan pengembang, versi yang terbaru ini memberikan peningkatan pada replikasi dan performance yang cukup menjanjikan.

Adapun fitur fitur unggulan untuk versi 9.2 ini adalah :

  • Tipe data baru yakni Range dan JSON
  • Linier Scalability to 64 cores.
  • Index Only Scans
  • Pengurangan konsumsi daya CPU
  • Peningkatan kemampuan replikasi dan performance
  • Extension pg_stat_statement
  • dan beberapa lagi dapat dibaca disini.
Dari fitur fitur diatas, saya tunggu adalah Index Only Scans (IOS) dan pg_stat_statement.
Index Only Scan adalah fitur untuk mencari data hanya di area index saja, tanpa perlu melihat ke real data di tabel. Sebelum versi 9.2, PostgreSQL akan mencari data di index terlebih dahulu, kemudian memastikannya dengan cara melihat ke real data di tabel. Dengan fitur IOS proses memastikan ke real data di table digantikan dengan visibility map.  Visibility Map ini disimpan didalam index, sehingga pada saat mencari data di index, PostgreSQL akan melihat nilai Visibility Map, jika bernilai “all visible” maka proses pencarian hanya akan dilakukan di dalam index, tanpa perlu melihat ke real-data di tabel. Mekanisme inilah yang dikenal sebagai Index Only Scan. Tentu saja ada kaidah kaidah pemakaian yang harus diikuti pada saat memakai IOS ini.
Pg_stat_statement adalah fitur untuk memonitor query melalui dynamic system view. Jadi proses memonitor query dapat dilakukan melalui view. Kita dapat melihat berapa kali query di jalankan dan berapa lama proses eksekusinya. Idenya adalah untuk memberikan kita timbal balik yang cepat terhadap query yang “panas” dan memakan banyak sumber daya sistem. Sebelumnya kita sudah mengenal pgfouine atau pgbadger yang dapat dipergunakan untuk mencari query query yang “panas” tersebut. Dengan adanya dynamic system view ini maka kita menganalisa dengan lebih capat tanpa perlu menjalankan log database.
Bagaimana dengan anda, fitur mana yang anda nantikan ?

Drop rubyrep object secara manual di PostgreSQL

  Selama saya melakukan test terhadap rubyrep, ada kejadian dimana saya harus menghapus object object milik rubyrep secara manual. Menggunakan perintah rubyrep uninstall gagal, karena tidak dapat berkomunikasi dengan salah satu servernya. Alhasil dengan terpaksa harus melakukan pembersihan secara manual.

Semua object object milik rubyrep, secara default memiliki nama depan yang diawali dengan awalan “rr_“. Object yang terinstal adalah trigger dan table.

Berikut langkah langkah melakukan drop rubyrep secara manual :

  • Login ke database postgresql dimana tabel tabel rubyrep terinstall
  • Dapatkan daftar trigger trigger yang dipergunakan rubyrep
SELECT 'DROP FUNCTION ' || ns.nspname || '.' || proname || '('
       || oidvectortypes(proargtypes) || ') CASCADE;'
FROM pg_proc 
INNER JOIN pg_namespace ns ON (pg_proc.pronamespace = ns.oid)
WHERE ns.nspname = 'public'  order by proname;
  • Copy Paste perintah drop function yang dimulai dengan awalan “rr_”  tersebut untuk menjalankan perintah drop tersebut.
  • Dapatkan daftar tabel tabel yang di pergunakan rubyrep
select 'DROP TABLE '||tablename||' CASCADE; ' 
from pg_tables 
where tablename ilike 'rr_%';
  • Copy Paste perintah drop tabel yang dimulai dengan awalan “rr_” tersebut untuk menjalankan perintah drop tersebut.
  • Done.
Nah, dengan cara ini maka semua tabel dan trigger milik rubyrep sudah hilang dari sistem. Silahkan mencoba.

Bucardo memakai parameter max_stack_depth

  Pagi ini saya menemukan error di dalam file log.bucardo yang menyatakan bahwa saya harus menaikkan paramter max_stack_depth di file konfigurasi postgresql.conf. Berikut potongan pesan errornya :

(9926) [Thu Sep  6 08:32:45 2012] KID Warning! Aborting due to exception for public.my_big_table:? Error was DBD::Pg::db do failed: ERROR:  stack depth limit exceeded\nHINT:  Increase the configuration parameter "max_stack_depth" (currently 2048kB), after ensuring the platform's stack depth limit is adequate. at /usr/local/share/perl/5.14.2/Bucardo.pm line 8131.

...

” Wah ini parameter apa ya ?” pikir saya. Terus terang saya belum pernah melihat secara mendetail fungsi dari parameter ini.

Menurut dokumentasi postgresql, parameter Max_Stack_Depth ini berfungsi untuk menaikkan kemampuan server dalam Execution Stack. Misalnya dalam pemrosesan fungsi fungsi rekursif seperti sub-query dan klausa IN. PostgreSQL akan memanggil parameter ini pada saat memproses fungsi fungsi rekursif itu saja, jadi tidak setiap saat. Nilai ideal dari parameter ini harusnya sama dengan nilai batasan ukuran stack di kernel linux, yang bisa diperoleh dengan menjalankan perintah ulimit -s.

$> ulimit -s

8192

Jika di komputer saya akan menghasilkan 8192. Nilai ulimit ini dalam kilobyte, ini artinya kernel saya memiliki batasan ukuran stack 8192 Kb atau 8M. Tetapi saya tidak akan menggunakan 8M ini, saya akan pakai setengahnya saja, 4M. Jikalau kita memaksa menaikkan nilai parameter ini diatas kemampuan kernel, 8M, maka sistem dapat menjadi lumpuh.

Setelah itu barulah saya merubah file konfigurasi postgresql.conf dan ubah parameter max_stack_depth.

$> vim /etc/postgresql/9.1/main/postgresql.conf 
...
max_stack_depth = 4M
...

Setelah itu server  postgresql di restart dan bucardo di start kembali. Problem selesai.

Monitor Query yang sedang berjalan di PostgreSQL

  Setiap hari saya menyempatkan diri untuk memonitor kondisi database PostgreSQL.  Salah satu rutinitasnya adalah  memonitor Query apa saja yang sedang berjalan di database. Apakah ada query yang prosesnya lama dan di luar kebiasaan ? jikalau ada, maka query tersebut harus diperiksa untuk melihat apa yang menyebabkannya menjadi berjalan lambat. Saya memakai script kecil untuk melihat proses query apa saja yang sedang berjalan di postgresql. Berikut scriptnya :

watch -n 0.5 "psql -h 127.0.0.1 -U postgres -p 5433 -d my_db
-c 'SELECT datname,procpid,current_query FROM pg_stat_activity;' |
grep -v IDLE | grep -v 'SELECT datname,procpid,current_query FROM pg_stat_activity;' |
grep -v ^\( | grep -v 'datname | procpid ' |
grep -v -- '-----------+---------+-' "

Script ini saya letakkan di server database di linux, dan saya jalankan setiap kali ingin melakukan monitoring. Untuk keluar cukup pakai Ctrl-X atau Ctrl-Z. Hasilnya kira kira akan seperti ini :

----------+---------+-------------------------------------------------------------
 my_db      |    9162 | select * from my_bigtable where ...

Bagaimana cara saya memonitornya ? Saya cukup menjalankan script ini dan memperhatikan pergerakan perintah query. Script ini akan refresh setiap 0.5 detik, cukup untuk melihat pergerakan query yang muncul dan menghilang. Apabila saya melihat ada query yang tidak segera menghilang setelah sekian lama maka itulah saat untuk melakukan tuning.

Berdasarkan contoh diatas, saya query terhadap salah satu tabel yang memiliki jumlah record yang sangat banyak. ID Process di linux yang terlihat adalah 9162 sebelum kemudian menghilang. Terlihat bahwasannya querynya terpotong atau terlalu panjang untuk muncul di layar. Bagaimana cara untuk mendapatkan querynya ? anda harus melihat id process di linux, kemudian cek di postgresql untuk melihat perintah query lengkapnya, dengan memakai perintah sebagai berikut :

SELECT datname,usename,procpid,client_addr,waiting,query_start,current_query
FROM pg_stat_activity
where procpid = 9162;

Setelah dapat querynya, silahkan melakukan tuning terhadap query tersebut. Bagaimana dengan anda ? apakah ada cara yang lain ? mohon infonya.

Instalasi Bucardo, master to master replikasi untuk database PostgreSQL

  Bucardo adalah software replikasi master to master untuk database Postgresql. Bucardo yang saya pergunakan adalah bucardo versi 5 beta, tepatnya versi 4.99.5 dan dapat di download dihalaman ini.  Sampai saat ini kecepatan replikasi yang dapat saya peroleh adalah > 200 record per detik untuk replikasi antar kota. Hasil ini dapat berbeda untuk jenis applikasi yang anda gunakan.

Konfigurasi yang saya miliki :

Berikut langkah langkah yang dilakukan untuk instalasi bucardo :

  • Backup database di server 1 dan extract di server 2, dengan ini maka kedua database tersebut memiliki struktur dan data yang identik.
  • Update package ubuntu dengan menambahkan beberapa package yang dibutuhkan

$> apt-get install make libdbi1 libdbi-perl libdbix-safe-perl libdbix-simple-perl libdbd-pg-perl libboolean-perl postgresql-contrib postgresql-plperl-9.1

  • Check nilai ulimit di kernel linux. Nilai ulimit ini akan di pergunakan untuk menentukan nilai parameter max_stack_depth di postgresql.

$> ulimit -s

8192

  • Berdasarkan infomasi diatas menghasilkan nilai 8192 Kb. Artinya saya dapat menaikan parameter max_stack_depth hingga 8M. Tetapi kita akan pakai setengahnya saja. Parameter max_stack_depth ini dipergunakan untuk memperbesar kemampuan query yang menggunakan klausa IN. Kemudian update file konfigurasi postgresqlnya.

$> vim /etc/postgresql/9.1/main/postgresql.conf

max_stack_depth = 4M

  • Install language plperlu di postgresql

$> psql -h 127.0.0.1 -U postgres
SQL> CREATE LANGUAGE PLPERLU;
SQL> CREATE LANGUAGE PLPGSQL;

  • Download Bucardo versi 5 Beta disini.
  • ekstrak file hasil download tersebut.
  • Kemudian lakukan kompilasi .

$> cd bucardo-4.99.5
$> perl Makefile.PL
$> make
$> sudo make install

  • Install Bucardo, dan ubah setting koneksinya seperti berikut

$> bucardo install

Current connection settings:
1. Host: 127.0.0.1
2. Port: 5432
3. User: postgres
4. Database: postgres
5. PID directory: /var/run/postgresql
Enter a number to change it, P to proceed, or Q to quit: P

$>

  • Check instalasi bucardo

$> bucardo –version

  • Tambahkan database ke bucardo

$> bucardo add db db1 dbname=mydatabase dbhost=192.16.0.1 dbuser=postgres dbpass=password
$> bucardo add db db2 dbname=mydatabase dbhost=192.16.0.2 dbuser=postgres dbpass=password

  • Daftarkan semua table dan sequence ke bucardo. Herd dapat dianalogikan sebagai kumpulan object database

$> bucardo add table all tables herd=db_herd
$> bucardo add sequence all sequences herd=db_herd

  • Daftarkan database yang ada didalam bucardo ke dalam database group

$> bucardo add dbgroup db_group db1:source db2:source

  • Sinkronisasi ke dua database tersebut untuk install triggernya dan start replikasi

$> bucardo add sync db_sync herd=db_herd dbs=db_group
$> bucardo start

  • Setelah start anda dapat melakukan testing insert, delete, update dari masing masing sisi server.
  • Perlu diingat yang di replikasikan adalah perintah DML yakni insert, update, delete.
  • Done.
Silahkan mencoba.