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 ?

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 ?