Thursday, May 28, 2009

MySQL Partition Table

Secara definisi partisi table adalah memecah tabel menjadi beberapa bagian/segmen, tujuan dari menggunakan partisi adalah untuk mempercepat proses query.

Misalkan kita memilik data sebanyak 10 juta record, bila pada table konvensional maka data sebanyak itu akan di simpan dalam satu segmen, tetapi bila menggunakan partisi maka data sebanyak itu akan di pecah-pecah ke banyak partisi berdasarkan sebuah kondisi, misalkan berdasarkan tanggal, maka ketika melakukan query hanya men-scan segmen dimana data itu berada, tidak 10 juta record itu di scan, sehingga proses query menjadilebih cepat.

Feature partisi tabel ini ada hadir dari MySQL versi 5.1, sedangkan MySQL yg gw pake saat eksperimen adalah MySQL versi 5.1.31 dengan sistem operasi yang gw pake adalah Ubuntu 9.04 Jaunty Jackalope


Tapi sebelum-nya melakukan partisi tabel cek lebih dahulu apa databasenya suport partisi atau tidak, caranya seperti di bawah ini

setelah masuk ke console MySQL ketikan perintah dibawah ini:
SHOW VARIABLES LIKE '%partition%';

+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| have_partitioning | YES   +
+-------------------+-------+
1 row in set (0.06 sec)

Partisi tabel terdiri dari beberapa tipe, antara lain

1. Partisi Range, adalah membuat partisi berdasarkan sebuah range, misalkan berdasarkan tanggal, sebuah nilai, contoh nya seperti di bawah ini
CREATE TABLE tbl_penjualan (
id INT NOT NULL,
nama VARCHAR(30),
tgl_transaksi DATE NOT NULL DEFAULT '1970-01-01'
)
PARTITION BY RANGE ( YEAR(tgl_transaksi) ) (
PARTITION p0 VALUES LESS THAN (1990),
PARTITION p1 VALUES LESS THAN (1995),
PARTITION p2 VALUES LESS THAN (2000),
PARTITION p3 VALUES LESS THAN MAXVALUE
);

contoh di atas membuat partisi berdasarkan tahun transaksi, jadi transaksi yg dari tahun 1990 akan masuk ke partisi p0, kemudian dari tahun 1995 akan masuk ke partisi p1, kemudian yang dari tahun 2000 akan masuk ke partisi p2 dan selebihnya akan masuk ke partisi p3.

contoh lengkapnya bisa diliat di: http://dev.mysql.com/doc/refman/5.1/en/partitioning-range.html

2. Partisi List, adalah membuat partisi berdasarkan kondisi dari sebuah nilai yang nilai nya cocok dengan yang sudah di tentukan, contohnya seperti dibawah ini
CREATE TABLE tbl_pegawai (
kode_pegawai  VARCHAR(30),
kode_golongan INT,
nama_lengkap  VARCHAR(255)
)
PARTITION BY LIST(kode_golongan) (
PARTITION p0 VALUES IN (1, 2, 3),
PARTITION p1 VALUES IN (4, 5, 6)
);

contoh diatas membuat partisi berdasarkan golongan pegawai, jadi pegawai dengan golongan 1,2 dan 3 akan masuk ke partisi p0 sedangkan karyawan dengangolongan 4,5 dan 6 akan masuk ke partisi p1

contoh lengkapnya bisa diliat di http://dev.mysql.com/doc/refman/5.1/en/partitioning-list.html

3. Partisi Hash, adalah membuat partisi berdasarkan sebuah kolom yg sudah ditentukan dengan pembagian segmen nya diatur oleh MySQL, contohnya seperti dibawah ini
CREATE TABLE tbl_pegawai (
kode_pegawai  VARCHAR(30),
kode_golongan INT,
nama_lengkap  VARCHAR(255)
)
PARTITION BY HASH( kode_pegawai )
PARTITIONS 4;

contoh diatas membuat partisi berdasarkan kode pegawai, jadi data pegawai akan di pecah menjadi empat partisi, dan yg mengatur sebuah kode pegawai masuk ke partisi yang mana langsung di atur otomatis oleh MySQL

contoh lengkapnya bisa diliat di http://dev.mysql.com/doc/refman/5.1/en/partitioning-hash.html


4. Partisi Key, adalah membuat partisi berdasarkan sebuah key dari tabel, contoh nya seperti dibawah ini
CREATE TABLE tbl_nama (
id INT NOT NULL PRIMARY KEY,
nama_lengkap VARCHAR(255)
)
PARTITION BY KEY()
PARTITIONS 2;

Contoh diatas membuat partisi berdasarkan primary key yang bernama id, jadi data dari tbl_nama akan di pecah menjadi 2 partisi, dan yang mengatur sebuah data masuk
kepartisi yang mana, diatur otomatis oleh MySQL

contoh lengkapnya bisa diliat di http://dev.mysql.com/doc/refman/5.1/en/partitioning-key.html




Berikut ini gw akan membuat sebuah contoh dan men-share hasil banchmark gw yang bikin secara *ecek-ecek* aja..... :D


misalkan saya mempunyai tabel seperti dibawah ini..
CREATE TABLE anggota (
username VARCHAR(16) NOT NULL,
namalengkap VARCHAR(25) NOT NULL,
tanggal DATE NOT NULL,
PRIMARY KEY(username)
)
PARTITION BY KEY(username)
PARTITIONS 6;

dari table diatas kita ingin melihat apakah tabel anggota mempunyai 6 partisi, dan sebuah data berada di partisi yang mana...

1. untuk melihat partition dari tabel, bisa dengan cara di bawah ini
explain partitions select * from anggota;
+----+-------------+---------+-------------------+------+---------------+------+---------+------+-------+-------+
| id | select_type | table   | partitions        | type | possible_keys | key  | key_len | ref  | rows  | Extra |
+----+-------------+---------+-------------------+------+---------------+------+---------+------+-------+-------+
|  1 | SIMPLE      | anggota | p3,p4,p5 | ALL  | NULL          | NULL | NULL    | NULL | 99570 |       |
+----+-------------+---------+-------------------+------+---------------+------+---------+------+-------+-------+
1 row in set (0.00 sec)

2. untuk melihat sebuah data berada di partisi yang mana, bisa dengan cara dibawah ini
explain partitions select * from anggota where username='50000' ;
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table   | partitions| type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | anggota |p4        | const | PRIMARY       | PRIMARY | 18      | const |    1 |       |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)


Selanjutnya saya akan membuat benchmark antara tabel yang tidak di partisi, dengan tabel yang di partisi dengan KEY, dan tabel yang di partisi dengan RANGE, di bawah ini adalah struktur tabel nya, jumlah data untuk ekperimen ini sebanyak 100.000 record.

1. tabel tanpa partisi
CREATE TABLE anggota 
username INT NOT NULL,
namalengkap VARCHAR(25) NOT NULL,
tanggal DATE NOT NULL,
PRIMARY KEY(username)

2. tabel dengan partisi KEY
CREATE TABLE anggota2 (
username INT NOT NULL,
namalengkap VARCHAR(25) NOT NULL,
tanggal DATE NOT NULL,
PRIMARY KEY(username)
)
PARTITION BY KEY(username)
PARTITIONS 6;

3. partisi dengan partisi RANGE
CREATE TABLE anggota3 (
username INT NOT NULL,
namalengkap VARCHAR(25) NOT NULL,
tanggal DATE NOT NULL,
PRIMARY KEY(username)
)
PARTITION BY RANGE(username)
(
PARTITION P0 VALUES LESS THAN (20000),
PARTITION P1 VALUES LESS THAN (40000),
PARTITION P2 VALUES LESS THAN (60000),
PARTITION P3 VALUES LESS THAN (80000),
PARTITION P4 VALUES LESS THAN (100001)
)

pengukurannya dengan query sederhana saja dan yang menjadi ukuran nya adalah waktu yang di butuhkan dalam eksekusi query tersebut, dibawah ini adalah hasilnya:

1. yg tidak menggunakan partisi
Select count(*) from anggota where username > 25000 and username < 85000
+----------+
| count(*) |
+----------+
|    59999 |
+----------+
1 row in set (0.15 sec)

2. yg menggunakan partisi KEY
Select count(*) from anggota2 where username > 25000 and username < 85000 
+----------+
| count(*) |
+----------+
|    59999 |
+----------+
1 row in set (0.20 sec)

3. yg menggunakan partisi RANGE
Select count(*) from anggota3 where username > 25000 and username < 85000
+----------+
| count(*) |
+----------+
|    59999 |
+----------+
1 row in set (0.07 sec)

Ternyata yang terlama adalah tabel yang menggunkan partisi KEY (0.20 sec) dan yang tercepat adalah tabel yang menggunakan partisi RANGE (0.07).

Jadi silakan simpulkan sendiri saja....
dan mana yang akan digunakan/dipakai tergantung kasus yang di hadapinya....
*ngak ada yg lebih baik dan juga ngak ada yg salah*

11 comments:

  1. maksih, ilmu baru buat saya.. :)

    ReplyDelete
  2. klo merubah tabel yg sudah ada gimana ya? sy coba pke SCRIPT "ALTER TABLE xxxx PARTITION BY RANGE (to_days(xxxx))
    (PARTITION P0 VALUES LESS THAN (to_days('2010-01-01')),
    PARTITION P1 VALUES LESS THAN (to_days('2011-01-01')),
    PARTIION VALUES LESS THAN MAXVALUE);" Ko ga bisa ya? Kadang ada keterangan "harus mengikutsertakan primary key disetiap partisi" ntu gimana ya? Mohon Pencerahannya,... Tq.

    ReplyDelete
  3. Dalam proses partisi tabel memerlukan primary key, tetapi apabila tabel tidak memilik primary maka secara default yang di jadikan primary key adalah field yang di jadikan key dalam partion.

    Apabila tabel telah memiliki primary key tetapi yang di jadikan field key dalam membuat partisi adalah bukan field tersebut maka akan terjadi error seperti diatas.

    Untuk menangani error diatas maka berikan atribute primary key pada field yang dijadikan key untuk partisi.

    contoh :

    alter table sales add primary key(tgl_penjualan);

    ReplyDelete
  4. apa benar mysql partition ini tidak boleh ada foreign key di tabel? bagaimana caranya agar partition bisa diterapkan di tabel yg ada foreign key nya?

    ReplyDelete
    Replies
    1. Iya betul ngak support foreing key, jadi alternatif untuk update data ke tabel yang di partisi dapat menggunakan trigger dan store procedure.

      Delete
  5. Mas Dendie,
    jikalau ada suatu table di set index di suatu kolom tertentu, dan table dengan data yg sama di partisi.
    nah secara performance kira2 mana yg lebih cepat ya?

    ReplyDelete
    Replies
    1. index dan partisinya di kombinasikan, partisi tabel ini akan terasa apabila data-data sudah sangat-sangat banyak.

      Delete
    2. Mas Dendie,
      kalau misalnya data dalam suatu table melebihi 2 juta record, manakan yang performanya lebih bagus?
      apakah index atau partisi table
      atau gabungan antara index dan partisi tabel?
      Apakah nantinya ada pengaruh pada saat insert ke dalam tabel tersebut?

      Terima kasih

      Delete