Bucardo Server Down

  Yup betul. Pagi ini saya mendapati server bucardo saya mati. Hardware error. Saya coba restart server, selalu terhenti dengan pesan kesalahan ada pada perangkat keras. Sedikit panik sih, tetapi harus tetap tenang.  Saya hitung pilihan yang saya punya.

  • 1 Mesin DB slave
  • Tidak ada backup database bucardo
  • Server DB Master dan Server DB Slave masih berfungsi.

Pilihan yang ada adalah install bucardo di mesin DB Slave, dan register ulang semua tabel yang akan di replikasi. Seharusnya proses registrasi ulang ini dapat di hindari, tetapi karena saya tidak punya backup database bucardo, jadi harus registrasi ulang. Kalau ini berjalan, saya harus ikutkan database bucardo dalam skema backup saya.

Ada sedikit kekhawatiran dalam hati, jangan jangan bucardo nya tidak mau replikasi karena saya install database bucardo yang baru. Dalam hati berharap semoga bucardo nya tetap dapat melakukan replikasi.

So, setelah install ulang bucardo di mesin DB Slave, registrasi ulang, maka sekarang saatnya untuk start bucardo. Sambil berharap harap cemas, saya start bucardo dan tail -f log.bucardo.

Ternyata berjalan normal, tanpa kurang suatu apapun. L E G A.

Bagaimana dengan teman teman, ada memiliki pengalaman seperti ini ?

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 ?