Desain Table untuk replikasi

  Hi, sudah lama blog ini tidak saya update, tentu saja alasan paling utama adalah kesibukan. Klise sekali ya, jadi ingat para blogger yang lain, selalu memakai alasan yang sama, sibuk.

Baiklah, topik kita kali ini adalah desain tabel yang akan di pergunakan untuk replikasi. Replikasi data ditempat kita adalah Master to Master, artinya setiap lokasi dapat melakukan update data dan masing masing lokasi akan melakukan sinkronisasi dengan lokasi lainnya. Desain tabel ini berdasarkan pengalaman yang kita miliki selama ini, yaitu :

  • Primary Key
    • Setiap record yang tercatat harus memiliki primary key, yang dapat dipergunakan untuk identifikasi unik record tersebut. Tingkat ke-unik-annya harus mempertimbangkan kode lokasi dan kode perusahaan.
    • Dengan pertimbangan di atas, pada saat replikasi, kita yakin bahwasannya data setiap record itu adalah unik, tidak akan mungkin tercampur / bertabrakan dengan data dari lokasi lainnya.
    • Contoh primary key nya :
      • A1   : A untuk kode lokasi, 1 untuk no urut data
      • XA1 : X untuk kode perusahaan, A untuk kode lokasi, 1 untuk no urut data.
  • Tanggal Pembuatan
    • Kita harus mencatat juga kapan record tersebut di catat di dalam tabel. Umumnya kita kombinasikan dengan constraint DEFAULT.
    • Contoh untuk Postgresql dan Oracle :
      • created_date timestamp default NOW()
      • created_by numeric(8)
  • Tanggal Perubahan
    • Kita juga harus mencatat kapan record tersebut berubah/diubah di dalam tabel. Pencatatannya dilakukan pada saat kita menjalankan perintah UPDATE atau REPLACE.
    • Contoh untuk Postgresql dan Oracle :
      • modified_date timestamp
      • modified_by numeric(8)

ID yang unik adalah suatu keharusan, sementara untuk tanggal Pembuatan dan perubahan sebagai data pendukung terhadap status record tersebut. Tentusaja data pendukungnya dapat bertambah sesuai kebutuhan, tetapi kolom kolom yang diatas itu adalah keharusan di tempat kita. Jadi standard DDL untuk tabel yang akan di replikasi seperti berikut :

<!-- Center Pane -->
Create Table Master_tbl
( id varchar(16),
  ...
  ...
  created_date timestamp default NOW(),
  created_by numeric(8),
  modified_date timestamp,
  modified_by numeric(8),
  contraint master_tbl_pk primary key (id)
);

Bagaimana dengan teman teman ?

Common Creative Image untuk blog ini

Creative Commons (CC) License adalah salah satu dari beberapa lisensi kepemilikan untuk umum. Lisensi CC dipergunakan manakala pemilik / pencipta ingin memberikan hak untuk membagikan, menggunakan dan bahkan perubahan di atas karya mereka. Lisensi CC memberikan kebebasan kepada pemilik / pencipta dan melindungi mereka yang menggunakan atau menyebarkan karya mereka, sehingga tidak perlu khawatir atas pelanggaran hak cipta.

Saya sendiri ingin sekali menambahkan beberapa image dengan lisensi CC ini, misal logo postgresql, mysql dan sebagainya. Lisensinya harus lisensi CC. Hingga saat ini saya belum menemukannya, jikalau ada teman teman yang mengetahuinya mohon informasinya. Dari google images banyak juga hasil image dengan Lisensi CC, tetapi belum ada yang sesuai dengan yang saya butuhkan.

Nulls hanya untuk tipe data berbasis character dan timestamp

  Bruse momjian adalah salah satu orang penting di dunia PostgreSQL, salah satu pendiri The PostgreSQL Global Development Group, dan dia sudah bekerja dengan PostgreSQL sejak tahun 1996. Beliau juga menulis buku yang berjudul “PostgreSQL: Introduction and Concepts“.

Menurut beliau, nulls adalah nothing atau pointer yang tidak menunjuk kemana mana. Untuk itipe data character, nulls dapat ditulis dengan spasis kosong atau zero length, sedangkan untuk numeric dan timestamp, nulls harus di tulis dengan apa ? 0, -1 atau yang lain ? timestamp dengan 01/01/01 ?

Berikut adalah file presentasi beliau mengenai Nulls, silahkan diambil disini. Presentasi ini telah di presentasikan dalam ajang pgCon 2013. Bruce juga menjelaskan apa, mengapa dan bagaimana pemakaian nulls yang benar. Benar benar penjelasan yang menarik untuk disimak. Sayangnya tidak ada penjelasan mengenai performance database apabila kita menggunakan nulls, jadi kita tidak tahu apa efek dari pemakaian nulls terhadap performance database kita, lebih cepat atau lebih lambat.

Berdasarkan presentasi tersebut, saya dapat mengambil kesimpulan bahwa nulls sebaiknya hanya dipergunakan untuk tipe data yang berbasis character, semisal tipe data character, text, atau variable character. Untuk tipe data berbasis numeric dan timestamp lebih baik menghindari nulls.

Untuk tipe data numeric, saya memiliki trik untuk menghindari nulls yakni dengan selalu menambahkan integrity NOT NULL DEFAULT 0. Jadi jika saya melakukan insert terhadap tabel tersebut dan tidak mencantumkan suatu value, maka secara otomatis database akan menambahkan angka 0.

Sementara untuk tipe data berbasis timestamp, saya memiliki kesulitan, karena dalam beberapa kasus kolom tersebut tidak dapat dikenakan integrity NOT NULL, sehingga nulls tetap diperkanankan. Integrity DEFAULT juga tidak dapat dikenakan karena saya tidak dapat menentukan tanggal berapa yang di pergunakan sebagai nilai default. Contoh, setiap tabel saya memiliki kolom MODIFIED_DATE, dan kolom ini hanya berisikan data pada saat terjadi perintah UPDATE. Sehingga pada saat dia berisikan nulls,saya dapat mengetahui bahwa memang belum pernah terjadi perintah UPDATE pada baris data tersebut. Seandainya saya menentukan default tanggalnya adalah 17/08/1945 dan menerapkan integrity NOT NULL DEFAULT TO_DATE(’17/08/1945′,’DD/MM/YYY’), maka akan muncul permasalahan pada penampilan data, karena secara default nilai kolom tersebut adalah ’17/08/1945′ padahal saya ingin melihat kolom tersebut dengan kosong untuk menyatakan tidak ada proses UPDATE.

Jadi, untuk saat ini, nulls akan saya terapkan untuk tipe data berbasis charcter dan timestamp. Bagaimana dengan teman teman ?

Replikasi melambat setelah tabel reorganize dengan CTAS

  Teman teman pasti tahu kalau saya memakai Bucardo untuk replikasi. Nah kejadian terakhir yang saya alami dengan bucardo berhubungan dengan tabel reorganisasi. Begini ceritanya.

Saya memiliki 3 server yang saya pergunakan untuk master to master replication dan hanya salah satu server saja yang di pergunakan sebagai server transaksi utama, lainnya untuk keperluan backup dan baca data. Nah saat saya melakukan analisa terhadap server untuk keperluan backup dan data ini, saya menemukan beberapa tabel yang ukurannya 3x lebih besar dari asalnya di server transaksi utama. Saya cek apakah mungkin tabelnya ter-fragmentasi atau tidak. Ternyata benar ada fragmentasi disana.

Akhirnya tabel yang ter-fragmentasi itu saya betulkan dengan metode CTAS. Berhasil. Salah satu tabel tersebut setiap malamnya melakukan update data sekitar 150k records. Jadi kalau dengan bucardo akan menjadi 300K records. Saya berkeyakinan bahwa semuanya akan lancar.

Esoknya, hari ke 1, sewaktu saya memonitor ZABBIX, ini adalah tools di linux untuk melihat kondisi server, saya mendapati bahwa dua server dimana saya melakukan CTAS kemarin cpu-load-nya sangat tinggi. Itu selesai setelah 6 Jam process, baru load nya turun drastis. Langsung saja saya curiga, mungkin itu terjadi karena tabel yang kemarin saya CTAS. Kemudian saya coba matikan bucardo-nya dan berharap esok hari zabbix akan memberikan laporan yang lebih baik.

Esoknya, hari ke 2, pagi pagi langsung saya lihat kondisi Zabbix, ternyata benar, load selama 12 jam terakhir rendah sekali. Jadi saya berkesimpulan bahwa load yang tinggi kemarin itu karena Bucardonya. Saya remove tabel tabel yang kemarin saya CTAS dari daftar tabel yang di replikasi di bucardo. Kemudian bucardo saya start lagi untuk menyelesaikan semua data data yang belum di replikasi. Dalam 3 jam ke depan Zabbix menunjukan kalau 2 server tersebut sibuk, karena menyelesaikan transaksi bucardo. Setelah itu rendah kembali, normal seperti sedia kala.,

Nah, saya browsing mencari jawaban mengenai hal ini, tetapi masih belum juga ketemu jawabannya. Mengapa setelah tabel tersebut di CTAS, process bucardo di  tabel tersebut jadi lebih lambat. Apakah ada teman teman yang memiliki pengalaman seperti ini ? mohon info nya.

Bucardo : Perintah Truncate Table Ter-Replikasi

  Hari ini saya mendapatkan pelajaran berharga dari bucardo. Saya secara tidak sengaja, maksudnya aktivitas yang saya kerjakan akan ter-replikasi ke server yang lain.

Jadi ceritanya, saya akan mendaftarkan tabel baru ke dalam replikasi bucardo. Tabel ini tabel aktif, dan transaksi masih sedang berlangsung. Rencana saya, setelah saya daftarkan tabel tersebut di bucardo, saya akan sinkronisasi datanya terlebih dahulu, baru kemudian mengaktifkan bucardonya. Sebelum melakukan sinkronisasi saya harus memastikan tabel di server yang lain kosong, saya pakai perintah truncate, dan ini adalah kesalahan saya, karena ternyata perintah truncate termasuk perintah SQL yang akan ter-replikasi juga.

Berikut kronologis  nya :

  • Server A: Tambahkan tabel yang baru ke dalam replikasi bucardo
  • Server A: Validate sync-nya
  • Server B: kosongkan tabel yang baru di server B pakai perintah truncate
  • Server A: copy data dari server A ke server B
  • Server A: start bucardo

Nah, begitu bucardo start, terjadilah replikasi dari server B ke server A, perintah truncate tersebut, sehingga data di server A terhapus juga. Berikut ini adalah urutan yang benar  :

  • Server B: kosongkan tabel yang baru di server B pakai perintah truncate
  • Server A: copy data dari server A ke server B
  • Server A: Tambahkan tabel yang baru ke dalam replikasi bucardo
  • Server A: Validate sync-nya
  • Server A: start bucardo

Dijamin, truncate tidak akan ikut ter-replikasi.

Apa itu DataWarehouse ?

  Menurut Wikipedia, Datawarehouse adalah

In computing, a data warehouse or enterprise data warehouse (DWDWH, or EDW) is a database used for reporting and data analysis. It is a central repository of data which is created by integrating data from one or more disparate sources. Data warehouses store current as well as historical data and are used for creating trending reports for senior management reporting such as annual and quarterly comparisons.

The data stored in the warehouse are uploaded from the operational systems (such as marketing, sales etc., shown in the figure to the right). The data may pass through an operational data store for additional operations before they are used in the DW for reporting.

Dari informasi diatas, maka datawarehouse adalah suatu database yang dipergunakan untuk keperluan membuat laporan dan analisa data. Database ini berperan sebagai pusat data dari berbagai sumber data, semisal dari 2 atau 3 database yang lainnya.  Datawarehouse menyimpan data saat ini dan data history yang dipergunakan untuk membuat report report perbandingan antara suatu waktu atau area dengan yang lainnya. Jadi orientasi dari datawarehouse adalah analisa data.

Contoh jenis analisa di dalam datawarehouse :

  • Data Penjualan berdasarkan :
    • Area, Tahun, Jenis Produk, dll
  • Data Kemampuan Karyawan berdasarkan :
    • Skill, Umur, Test, dll
  • Data Pembelian berdasarkan :
    • Tahun, Supplier, Produk, OnTime, dll

Data yang disimpan di dalam database datawarehouse itu berasal dari berbagai database berjenis OLTP. OLTP adalah database yang dipergunakan untuk keperluan penyimpanan data transactional. Jadi data data OLTP harus di upload ke dalam database datawarehouse, proses upload data ini dikenal dengan istilah ETL ( Extract, Transform, Load). Penjelasannya sebagai berikut :

  • Extract
    • Ekstrak data dari sumber data yang lain, dari database OLTP atau dari file Excell, atau dari file Log dan sebagainya.
  • Transform
    • Transformasi data agar sesuai dengan kebutuhan kita, termasuk urutan, perhitungan atau penjabaran.
  • Load
    • Loading ke dalam database tujuan kita, database datawarehouse.

Database Datawarehouse harus didesain dan di rancang untuk menangani kegiatan Analisa Data. Desain databasenya memiliki karakteristik sebagai berikut :

  • De-Normalisasi
    • Tabel tabel yang dimiliki selalu dalam bentuk tabel de-normalisasi atau 90% atau lebih dari tabel yang dimilikinya dalam bentuk de-normalisasi
  • Database Blok berukuran besar, 8 kilobyte , 16 kilobyte atau 32 kilobyte
    • Setiap blok penyimpanan di database umumnya memakai besaran lebih dari 8kb
    • Ukuran blok yang besar ini akan mempercepat proses pencarian keseluruhan data di dalam tabel
  • Data diatur berdasarkan subyeknya
    • Subyek ini yang menentukan data didalam datawarehouse, contohnya data penjualan, pembelian dan lain lain.
  • Integrasi
    • Data yang berasal dari berbagai sumber data tentu memiliki beberapa perbedaan, misal nama kolom atau tipe data. Perbedaan perbedaan ini harus di atasi, agar dapat di buat laporan dengan format yang valid dan kosisten.
  • Non-Volatile
    • Artinya, sekali data itu masuk ke datawarehouse, data tidak boleh di ubah. Mengapa ? karena fungsi datawarehouse adalah untuk mengetahui apa yang terjadi.
  • Time Variant
    • Data yang disimpan di dalam datawarehouse di kelompokkan berdasarkan waktunya, mingguan, bulanan atau tahunan.

Dengan karakteristik seperti itu, maka database Datawarehouse haruslah dapat menyimpan data dalam jumlah yang besar, gigabye atau terabyte. Pengambilan data dalam jumlah besar juga harus dapat dilakukan, tidak harus cepat, tapi harus dapat dilakukan. Misal untuk membuat suatu analisa perlu melakukan query terhadap database yang menghasilkan 1 Trilyun record, yang kemudian di summarize menjadi 100 record.

Kecepatan bukan hal yang utama disini, penyelesaian suatu analisa itu yang paling utama.

RedHat akan mengganti MySQL dengan MariaDB

  Collin Charles melaporkan dari blognya bahwa Red Hat Enterprise Linux 7 akan melepas database MySQL dan menggantinya dengan database MariaDB. Pengumuman ini dikeluarkan pada #rhsummit yang sedang berlangsung di xxx.

Sejak MySQL di akuisisi oleh ORACLE pada tahun 2010, para pengembang MySQL memisahkan diri dan membuat versi terbuka dan mandri yang di beri nama MariaDB. MariaDB masih kompatible dengan MySQL, sehingga jikalau applikasi kita beralih dari database MySQL ke MariaDB dapat langsung berjalan.

Kabar ini tentu saja akan menggembirakan para pengguna MariaDB dan membantu para pemakai MySQL yang risau setelah pengakuisisian MySQL oleh Oracle. Termasuk saya tentunya, bagaimana dengan anda ?

Apa itu Database OLTP ?

  OLTP adalah singkatan dari On-line Transaction Processing. Menurut Wikipedia,

Online transaction processing, or OLTP, is a class of information systems that facilitate and manage transaction-oriented applications, typically for data entry and retrieval transaction processing. The term is somewhat ambiguous; some understand a “transaction” in the context of computer or database transactions, while others (such as the Transaction Processing Performance Council) define it in terms of business or commercial transactions.[1] OLTP has also been used to refer to processing in which the system responds immediately to user requests. An automatic teller machine (ATM) for a bank is an example of a commercial transaction processing application.

Jadi, OLTP adalah sistem informasi yang berbasis transaksi. Transaksi dalam konteks Applikasi OLTP adalah satu proses transaksi atau satu cyle, ada awal ada akhir. Proses transaksi di dalam applikasi sendiri bervariasi jenisnya,  antara lain :

  • Proses penyimpanan 1 form, contoh 1 Purchase Order, 1 Sales Order, 1 Material Incoming dan lain lain
  • Proses End of Month, contoh proses akhir bulan Accounting, akhir bulan Banking, dan lain lain
Semua jenis transaksi tersebut diatas itulah yang disebut Proses Transaksi atau transaction processing.  Sedang yang dimaksud dengan Transaksi Database adalah proses menyimpan sebuah record dalam suatu tabel. Sehingga dengan penjelasan ini, 1 Transaksi Applikasi dapat berisikan ribuan Transaksi Database.
Database OLTP harus didesain dan di rancang untuk menangani kegiatan Transaksi Applikasi. Desain databasenya memiliki karakteristik sebagai berikut :
  • Normalisasi
    • Tabel tabel yang dimiliki selalu dalam bentuk tabel normalisasi atau 90% atau lebih dari tabel yang dimilikinya dalam bentuk normalisasi
  • Database Blok berukuran kecil, 2 kilobyte atau 4 kilobyte
    • Setiap blok penyimpanan di database umumnya memakai besaran 2kb atau 4kb
    • Ukuran blok yang kecil ini akan mempercepat proses pencarian data yang spesifik
  • Transaction Control
    • Selalu menggunakan transaction contol, yakni begin transaction, end transaction, commit dan rollback.
    • Kontrol ini dipergunakan untuk memastikan 1 Transaksi Applikasi berjalan semestinya hingga akhir dengan benar.

Dengan karakteristik seperti itu, maka Transaksi Applikasi di database OLTP haruslah transaksi yang memiliki proses penyimpanan dengan cepat. Kecepatannya harus kurang dari 60 detik. Lebih cepat lebih baik. Misal, 1 Transaksi Applikasi, menyimpan 1 Form Purchase Order, haruslah kurang dari 60 detik. Jadi semua Transaksi Applikasi jenis OLTP ini harus cepat.

Bagaimana dengan Proses Analisa ? Proses analisa umumnya melibatkan banyak tabel yang harus di join. Semakin banyak tabel yang di join akan mempengaruhi kecepatan pemrosesan data. Proses Analisa tetap dapat di lakukan dengan menggunakan database OLTP, tetapi ada keterbatasan di dalamnya, karena normalisasi, atau karena blok database. Seberapa besar batasan yang dapat diterima itu harus ditentukan. Jikalah Proses Analisa cukup tinggi pengunaannya, lebih baik proses analisa dilakukan di DATAWAREHOUSE.

Bucardo : Mengatur object dalam sync

  Sekarang  bucardo sudah berjalan dengan lancar. Kadang kala kita perlu mengatur object postgresql di dalam bucardo sync. Object ini adalah table dan sequence. Kedua object ini dapat kita tambahkan atau kita hapus.

Contoh yang saya miliki adalah sebagai berikut :

  • Table A, ingin saya replikasi dari db1 ke db2, tetapi tidak untuk db3.
  • Sequence B, ingin saya replikasi dari db1 ke db3, tetapi tidak untuk db2.

Berikut langkah langkah untuk menambah suatu object ke dalam sync :

  • stop bucardo
  • bucardo add table schema.nama_table herd=my_herd
  • bucardo add sequence schema.nama_sequence herd=my_herd
  • bucardo validate sync my_sync
  • start bucardo

Dan untuk menghapus, langkah langkah nya sebagai berikut :

  • stop bucardo
  • bucardo remove table schema.nama_table
  • bucardo remove sequence schema.nama_sequence
  • bucardo validate sync my_sync
  • start bucardo

Jika setelah menghapus suatu object, kita menemukan pesan errod di dalam file log, 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”

artinya ada object postgresql yang secara fisik sudah tidak ada di postgresql tetapi masih terdaftar di dalam sistem bucardo. Mengapa ini dapat terjadi ? bucardo yang kita pakai bukan versi stabil, masih versi beta atau alpha. Tenang saja, kita bisa memperbaikinya kan.

Untuk membetulkannya:

  • bucardo stop
  • login ke db1
  • cek melalui perintah
    • select * from pg_class where oid = 265672
  • jika tidak ada hasilnya, berarti object memang tidak ada.
  • delete from bucardo.bucardo_delta_targets where tablename = 265672
  • if no result…. ulang step ke db yang lainnya  ..dan seterusnya
  • bucardo start

Bagaimana pengalaman anda dengan bucardo ? mohon infonya.

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 ?