Sekedar Copy Paste Untuk Berbagi: Juli 2013

Minggu, 21 Juli 2013

Meningkatkan Performa Database MySQL

Langkah berikut bisa digunakan untuk meningkatkan kecepatan akses terhadap database MySQL, terutama jika akses dilakukan dari komputer dengan sistem non Linux.
1. Memperbaiki DNS setting dan memasukkan ip address / nama host client yang mengakses mysql kedalam file /etc/hosts di server mysql.
Proses edit file /etc/hosts ini bisa dilakukan menggunakan webmin ataupun diedit langsung. Berdasarkan ujicoba langsung, tips ini meningkatkan kecepatan koneksi hingga 10X
2. Memperbaiki / meningkatkan setting konfigurasi MySQL pada file my.cnf.
Secara default, setting mysql menggunakan setting normal dengan memori kecil.
# This is for a system with little memory (32M – 64M) where MySQL plays
# an important part, or systems up to 128M where MySQL is used together with
# other programs (such as a web server)
Dengan memori yang ada pada server (misalnya sebesar 1 GB), kemampuan MySQL dapat lebih ditingkatkan dengan menaikkan key_buffer_Size (untuk model tabel MYISAM) dan innodb_buffer_pool_size (untuk tipe table Inno-db) dan table_cache. Jangan lupa, angka yang diisikan adalah angka dalam bentuk umum memory (kelipatan dari 2ⁿ), misalnya 128 MB, 256 MB, 512 MB dll.
Dalam beberapa proses instalasi, biasanya ada juga file konfigurasi untuk mysql yang dijalankan pada komputer dengan memori besar. Nama file konfigurasinya : my-large.cnf. Pelajari file konfigurasi tersebut agar bisa diujicoba pada file konfigurasi my.cnf.
Sebelum melakukan perubahan setting, backup terlebih dahulu konfigurasi yang lama, untuk berjaga-jaga jika setting yang baru justru memunculkan masalah. Berdasarkan pengalaman, setting yang tidak tepat-terlalu tinggi misalnya-membuat MySQL tidak bisa dijalankan.
Jangan lupa, perubahan konfigurasi MySQL sebaiknya dilakukan dalam posisi MySQL off sehingga setelah selesai dire-konfigurasi bisa langsung ditest.
3. Meningkatkan kapasitas memori server
Memori yang lebih tinggi akan membuat server leluasa dalam melayani trafik yang tinggi. Jika memori mencukupi, server tidak akan menggunakan memori cadangan dari swap file yang biasanya berimplikasi pada kecepatan akses data.
4. Pada aplikasi, hindari query SELECT yang tidak spesifik seperti SELECT * …
Pastikan memilih kolom yang akan diambil dan sedapat mungkin hindari model Select *
5. Gunakan indeks pada key table yang sering digunakan. Misalnya pada tabel karyawan yang menggunakan NIK (Nomor Induk Karyawan) sebagai primary key, indeks table menggunakan key ini. Jika ada proses query yang sering menggunakan nama field lainnya, buat indeks juga untuk field tersebut.
sumber http://opensuse.or.id/panduan/server-setup/database-server/meningkatkan-performa-database-mysql/

Sabtu, 13 Juli 2013

Membandingkan Stored Procedure Antar 2 Database di MySQL

Pernah coding secara massive di pemrograman internal DBMS makae stored procedures/functions?

Bekerja dalam tim?

Punya beberapa database yang dipakae dalam setiap fase development? misal :

db_ini_dev : dikonsumsi oleh para coder-coder cakep kita
db_ini_test : dikonsumsi oleh para tester-tester cantik kita
db_ini : dikonsumsi ama client-client baik kita

Atau pernah bekerja dalam tim yang tersebar di beberapa tempat yang terpisah, dimana masing-masing punya local database untuk kegiatan coding masing-masing?

db_ini_dev_bandung : dikonsumsi oleh para coder-coder cakep kita di Buah Batu Regency
db_ini_dev_solok : dikonsumsi oleh para coder-coder tampan kita di Perumnas Koto Baru Solok
db_ini_test : di-host di sebuah server public dengan spesifikasi medium di sebuah gedung di Kuningan, Jakarta
db_ini : di host di server public yang sama dengan db_ini_test

Ketika small-release udah digulirkan, mau-ngga-mau setiap perubahan struktur database (tabel/routines/trigger/view) harus di-broadcast ke database-database kita tersebut.

Puyeng meng-analisis diferensial?

Silakan coba trik gw untuk membandingkan stored procedures antara dua database di bawah ini. (Owya, gw pakae MySQL 5 -- dan trik ini hanya bisa jalan di MySQL 5 ke atas)

Ini script gw untuk "mendeteksi" apakah ada SP yang beda definisi (atau SP baru) dari dua database yang identik. Silakan donlot di sini :http://qvezst.googlepages.com/xp_routines_compare.zip

Pertama-tama yang harus dilakukan adalah dengan meng-execute ntu script ke database mysql. Yupe! Database mysql (ini merupakan database yang 'pasti ada' di setiap engine MySQL Server -- yang isinya adalah informasi user, informasi schema, de-el-el). Klo sukses ke-attach, ntar di database mysql loe bakal nambah 2 stored procedure yang namanya xp_execute dan xp_routines_compare.

xp_execute adalah routine yg gw bikin untuk memudahkan pemanggilan PREPARED STATEMENTS di dalam stored routines, daripada tiap kali harus nulis PREPARE ... EXECUTE ... DEALLOCATE -- mending tinggal panggil call xp_execute("select blablabla");

dan xp_routines_compare, adalah inti dari artikel ini -- memiliki dua parameter input yaitu SOURCE DATABASE NAME dan TARGET DATABASE NAME. Untuk meng-compare dua database, loe masukin aja nama-nama database yg mo loe bandingin tersebut dalam format string (alias loe kasi kutip satu ato kutip dua). Kalo bingung, coba loe liat sampel pemanggilan ntu procedure di bawah ini :

mysql>call mysql.xp_routines_compare('ekomit_dbrisma','ekomit_dbrisma_test');

+------------------------------------+--------------------------------------+

| routine_yg_berbeda | letak_perbedaan |

+------------------------------------+--------------------------------------+

| sf_psb_get_nama_sekolah | routines baru di db : ekomit_dbrisma |

| sf_report_get_count | routines baru di db : ekomit_dbrisma |

| sf_statistik_getcount | routines baru di db : ekomit_dbrisma |

| sp_disdik_kategori_laporan | routines baru di db : ekomit_dbrisma |

| sp_disdik_lihat_laporan | routines baru di db : ekomit_dbrisma |

| sp_psb_cluster_daftar_sekolah_save | PARAMETER BODY |

| sp_psb_sekolah_luar_cluster_combo | BODY |

| sp_psb_sekolah_prs_view | PARAMETER BODY |

| sp_psb_sekolah_prs_view1record | routines baru di db : ekomit_dbrisma |

| sp_stats_agama | routines baru di db : ekomit_dbrisma |

| sp_thnajaran_kini_dan_sebelumnya | routines baru di db : ekomit_dbrisma |

| xf_properdate | BODY |

+------------------------------------+--------------------------------------+

12 rows in set

Yak! akan terlihat di mana letak perbedaan routines di dalam dua database tersebut yang akan terdeteksi beda dari ntu routines ada diPARAMETER, di BODY, atau beda di RETURN (khusus stored functions). Dan klo ada routine baru, akan langsung di-kasih-tau ntu routines baru ada di database mana..

Gimana-gimana? Keren kan?

Oh iya, -- mencoba obyektif -- gw juga pengen beritaukan LIMITASIdalam pengimplementasian xp_routines_compare() gw ini, yaitu :

xp ini hanya bisa di call ama user yang punya GRANT PRIVILEGES sekelas root -- atau minim punya privileges SELECT ke database mysql.
ngga bs nge-compare dari 2 databases yang BERBEDA ENGINE -- karena database yang beda engine artinya beda databasemysql nya.. Ntar lah kapan-kapan gw coba bikin versi cross-engine-nya (manfaatin fitur mysql replikasi, tentunya) -- tp gw gag janji dalam waktu dekat. Maklum, lagi banyak Pe-eR yg musti digarap nieh. hihihi...

Tunggu kelanjutan kisah ini...

From : http://rizky.prihanto.web.id/2008/12/membandingkan-stored-procedures-antar-2.html

Aplikasi Sederhana Penggunaan MySQL Procedure dengan PHP

1 Votes

Pukul 23:55 WIB @kamar kos yang sempit.

Mengingat waktu mencoba mendalami penggunaan procedure pada Data base MySQL setelah berhasil, rasanya ingin membagikan kepada semua orang..hehehe

ada beberapa alasan kenapa digunakan Stored Procedure (SP) pada database alih-alih menuliskannya pada codingan secara langsung. Pastinya semua metode memiliki 2 buah sisi, yaitu keuntungan dan kerugian. berikut sedikit ulasannya.

Keuntungan :

SP meningkatkan performa aplikasi, sekali dibuat SP dikompile dan disimpan pada database, ia berjalan lebih cepat dari pada uncompiled SQL command yang dikirimkan dari aplikasi.
Karena disimpan didatabase, maka akan mengurangi trafic antara aplikasi dan Database, karena tidak mengirimkan berkali-kali uncompiled SQL dari aplikasi.
Karena menggunakan SQL yang hampir tidak berubah banyak pola penulisan coding jika menggunakan DB yang berbeda (Oracle, MsSQL server, dll) jika nanti berniat migrasi database, sehinggap bisa dibilang SP portable syntaks
Karena SP disimpan di sisi server, sehingga ia lebih aman, karena, administrator dapat memberikan akses siapa saja yang boleh atau tidak boleh mengakses SP.

Kerugian :

Karena SP di proses pada server, sehingga SP akan berpengaruh pada kinerja server baik memory maupun processor.
karena SP menggunakan declarative SQL, sehingga sulit menuliskan procedure dengan bisnis logic yang kompleks seperti language program lainnya seperti : Java, C+, C#, dll
Kita tidak bisa men-debug SP. iya sih bisa aja di debug (seperti pesan error yang ditampilkan jika terjadi error seperti yang saya buat dibawah, namun rasanya itu masih belum cukup baik).
Menuliskan dan memaintain SP butuh kemampuan khusus yang mana tidak semua developer memilikinya.

Script PHP :

1. Buat File input.php

<?php
//ini untuk koneksi database, setingan bisa berbeda, tergantun setingan MySQL anda, jika
//database anda tidak memakai password, maka nilai variable $pass dikosongkan saja
$database = "test";
$user = "root";
$pass = "xyz";
$host = "localhost";
$sambung = mysql_connect($host,$user,$pass);
mysql_select_db($database,$sambung) or die ("gagal");
// akhir koneksi database
if($_GET['act']=="simpan") //simpan data
{
    if($simpan = mysql_query("CALL simpan_input_user($_POST[id], '$_POST[nama]','$_POST[alamat]');"))
    {
        echo "berhasil";    
    }
    else
    {
        echo  mysql_errno($sambung).": ".mysql_error($sambung)."\n";
        echo"<script>alert('Proses simpan gagal'); windows.history.back();</script>";
    }
    
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Test Input menggunakan procedure</title>
</head>
<body>
<form action="index.php?act=simpan" method="post">
<table cellpadding="2" cellspacing="2" border="0">
<tr>
    <td>No ID</td>
    <td><input type="text" name="id" /></td>
</tr>
<tr>
    <td>Nama :</td>
    <td><input type="text" name="nama" /></td>
</tr>
<tr>
    <td>Alamat :</td>
    <td><input type="text" name="alamat" /></td>
</tr>
  
<tr>
    <td colspan="2"><input type="submit" value="SIMPAN" /><input type="reset" value="RESET" /></td>
</tr>
</table>
</form>
<br />
<?PHP
include "tampil.php";
?>
</body>
</html>

Tampilannya kurang lebih akan seperti ini :

Penjelasan Script :

Line 4 – 10 : merupakan setingan koneksi ke database, hal ini bisa berbeda dengan setingan MySQL pada database anda, jika anda mengkuti langkah-langkah pada server side dibawah nanti, saya berani bertaruh segelas susu (hehe biar sehat) paling yang berbeda nanti hanya pada setingan password
line 11 – 23 : merupakan proses send-request ke server, jadi cara kerjanya adalah : ketika form action HTML pada baris 32 – 51 dilakukan (SUBMIT), maka form akan mengirimkan data-data form ke dirinya sendiri (re-load), dengan menambahkan parameter “act” pada URL, ketika ditemukan browser menemukan parameter “act” tersebut, maka proses pada line 11 – 23 dilakukan, jika tidak, maka proses ini akan di lewati (skip).
line 13 : memanggil SP simpan_input_user dengan mengirimkan paramater-parameter yang dikirimkan dari form HTML.

2. Buat file tampilan.php

<?php
//setingan koneksi ke database
$database = "test";
$user = "root";
$pass = "xyz";
$host = "localhost";
$sambung = mysql_connect($host,$user,$pass);
mysql_select_db($database,$sambung) or die ("gagal");
//akhir setingan koneksi kedatabase
if(!empty($_GET['cari']))
{
    if($cari = mysql_query("CALL show_data_user('$_GET[cari]');"))
    {
        /* do nothing */    
    }
    else
    {
        echo  mysql_errno($sambung).": ".mysql_error($sambung)."\n";
        echo"<script>alert('Error'); windows.history.back();</script>";
    }
}
else
{
    $cari = mysql_query("CALL show_data_user(NULL)");
}
  
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>tampil data user</title>
</head>
  
<body>
<form method="GET" action="tampil.php?act=show">
<input type="text" name="cari" />&nbsp;<input type="submit" value="Cari" />
</form>
<?php
            while($hasil = mysql_fetch_array($cari))
            {
                echo"id : $hasil[no_id]<br />".
                    "nama : $hasil[nama]<br />".
                    "alamat : $hasil[alamat]<br />".
                    "<hr />";
            }
        
    
?>
  
</body>
</html>

Tampilan kira-kira akan seperti ini :

untuk saat ini tampilan belum menampilkan data apa-apa, karena belum terdapat data pada database.

Penjelasa Script :

Line 11 – 25 : karena form menggunakan method GET, maka browser akan men-check adakah paramater “cari” pada URL yang mengindikasikan tombol Search diklik (line10). jika terdapat parameter “cari” maka line 12 – 15 yang akan aktif, dimana nilai balikan query ini (jika ada) akan ditampilkan ke user oleh proses line 40 – 46. Sebaliknya jika tidak terdapat parameter “cari” pada URL, maka proses 22 – 25 yang akan berjalan, dimana form akan memanggil semua data pada tabel user dan hasil (jika ada) akan di tampilkan ke user oleh proses line 40 – 46.
Line 16 – 20 akan menampilkan pesan error kepada user, jika terjadi suatu masalah dalam menampilkan data.
Memanggil SP show_data_user dengan mengirimkan parameter yang dibutuhkan dari form pencarian.

Script Server Side :

1. Buat sebuah database pada MySQL anda dan beri nama database tersebut “test” (tanpa tanda petik)

2. Buat Database User

CREATE TABLE `user` (
  `no_id` int(10) NOT NULL,
  `nama` varchar(50) DEFAULT NULL,
  `alamat` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`no_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

3. Buat Procedure Simpan Input Data User

DELIMITER $$
USE `test`$$
DROP PROCEDURE IF EXISTS `simpan_input_user`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `simpan_input_user`(IN id INT(10),IN nama VARCHAR(50), IN alamat VARCHAR(100))
BEGIN
    INSERT INTO USER (no_id, nama, alamat) VALUES (id, nama, alamat);
    END$$
DELIMITER ;

3. Buat Procedure untuk menampilkan Data User

DELIMITER $$
USE `test`$$
DROP PROCEDURE IF EXISTS `show_data_user`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `show_data_user`(IN q_cari VARCHAR(10))
BEGIN    
    IF q_cari IS NULL THEN
        SELECT*FROM USER;
    ELSE 
        SELECT*FROM USER WHERE user.no_id=q_cari;
    END IF;    
    END$$
DELIMITER ;

yah kira-kira seginilah yang baru saya bisa bagikan ke anda, semoga ilmu yang sedikit ini bisa bermanfaat bagi kalian. Silahkan dicoba, dicopy codingnya juga gapapa..

salam.
sumber:http://bungjoehanz.wordpress.com/2011/06/03/aplikasi-sederhana-penggunaan-mysql-procedure-dengan-php/

Dynamic CrossTab di MySQL

Submitted by nurhidayat on Thu, 07/05/2012 - 15:19

Membuat report adalah salah satu pekerjaan yang cukup rumit, lebih lagi jika harus membuat report crosstab dengan jumlah kolom yang dinamis dijamin pusing tujuh keliling. Mencoba menjawab sejumlah pertanyaan tentang crosstab dinamis yang muncul di Facebook grup PHP Indonesia dan MySQL Indonesia, akhirnya saya penasaran juga....

Misalkan kita punya data di tabel stok seperti ini:

1

2

3

4

5

6

7

KodeGudang KodeProduk Stok

Gd1        Produk1       5

Gd1        Produk2      10

Gd1        Produk3      15

Gd2        Produk1      20

Gd2        Produk2      25

Gd3        Produk3      30

Namun kita perlu membuat laporan seperti ini

1

2

3

4

KodeProduk Gd1 Gd2 Gd3 

Produk1      5  20   0 

Produk2     10  25   0 

Produk3     15   0  30

Kolom Gd1 sampai Gd3 sifatnya dinamis, artinya KodeGudang bisa jadi tidak hanya 3, bisa lebih banyak, bisa juga lebih sedikit.

Solusinya adalah memanfaatkan stored procedure di MySQL, dan aplikasi PHP kita cukup terima data mateng hasil perhitungan stored procedure bersangkutan, dan Alhamdulillah berhasil menemukan solusi seperti di bawah ini. Silakan dipelajari :D

D:\xampp\mysql\bin>mysql -u root

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 7

Server version: 5.5.16 MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test;

Database changed

mysql> select * from mydata;

+-------+------------+-------+

| kode  | tanggal    | nilai |

+-------+------------+-------+

| CN235 | 2012-01-02 |   100 |

| CN235 | 2012-01-05 |   120 |

| CN235 | 2012-01-12 |   140 |

| CN235 | 2012-01-11 |   150 |

| CN236 | 2012-01-11 |   200 |

| CN235 | 2012-01-15 |   160 |

| CN235 | 2012-01-17 |   170 |

| CN235 | 2012-01-16 |   180 |

| CN236 | 2012-01-14 |   200 |

| CN236 | 2012-01-12 |   400 |

| CN236 | 2012-01-15 |   120 |

| CN236 | 2012-01-17 |   400 |

| CN236 | 2012-01-18 |   600 |

| CN236 | 2012-01-05 |   800 |

+-------+------------+-------+

14 rows in set (0.00 sec)

mysql> DELIMITER $$

mysql> DROP PROCEDURE IF EXISTS `test`.`mysp`$$

Query OK, 0 rows affected (0.00 sec)

mysql> CREATE PROCEDURE `test`.`mysp`()

    -> BEGIN

    ->  -- declare variables

    ->  declare prepsql varchar(1000);

    ->  declare no_more_rows boolean default false;

    ->  declare tanggal_val varchar(20);

    ->  declare tanggal_cur cursor for

    ->          select distinct tanggal

    ->          from mydata order by tanggal;

    ->  declare continue handler for not found

    ->          set no_more_rows = true;

    ->

    ->  -- start generating sql

    ->  set prepsql = 'select kode';

    ->  open tanggal_cur;

    ->  the_loop: loop

    ->      -- fetch data

    ->      fetch tanggal_cur

    ->      into tanggal_val;

    ->      -- break out loop

    ->      if no_more_rows then

    ->              close tanggal_cur;

    ->              leave the_loop;

    ->      end if;

    ->      -- display

    ->      set prepsql = concat(prepsql,'\n, sum(case when tanggal=\'',tanggal_val,'\' then ifnull(nilai,0) else 0 end) "',tanggal_val,'"');

    ->  end loop the_loop;

    ->  set prepsql = concat(prepsql,'\nfrom mydata group by kode');

    ->

    ->  -- executing generated query

    ->  set @mysql = prepsql;

    ->  prepare stmt from @mysql;

    ->  execute stmt;

    ->

    ->  -- clean up

    ->  drop prepare stmt;

    -> END$$

Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> call mysp;

+-------+------------+------------+------------+------------+------------+------------+------------+------------+------------+

| kode  | 2012-01-02 | 2012-01-05 | 2012-01-11 | 2012-01-12 | 2012-01-14 | 2012-01-15 | 2012-01-16 | 2012-01-17 | 2012-01-18 |

+-------+------------+------------+------------+------------+------------+------------+------------+------------+------------+

| CN235 |        100 |        120 |        150 |        140 |          0 |        160 |        180 |        170 |          0 |

| CN236 |          0 |        800 |        200 |        400 |        200 |        120 |          0 |        400 |        600 |

+-------+------------+------------+------------+------------+------------+------------+------------+------------+------------+

2 rows in set (0.00 sec)

Query OK, 0 rows affected (0.04 sec)

mysql>

Source code juga bisa dilihat di sini

Secara umum alur dari stored procedure di atas adalah:

query ke tabel data untuk mendapatkan kolom crosstab
susun sql statement berdasarkan hasil query tersebut
siapkan sql menggunakan perintah prepare
jalankan query

Dengan cara di atas, aplikasi hanya perlu memanggil stored procedure dan langsung mendapatkan hasil report-nya dalam bentuk "matang" tanpa perlu proses perhitungan lagi.

Selamat mencoba... :)

- See more at: http://pojokprogrammer.net/content/dynamic-crosstab-di-mysql#sthash.EQsMbHrZ.dpuf