Archive for the ‘ Database ’ Category

PHP MySQL Compare/Benchmark, PDO vs Mysql vs Mysqli OOP vs Mysqli Procedural

Saat ini saya akan develop aplikasi yang mature dan dapat diandalkan, sehingga saya mencari tahu cara yang terbaik untuk melakukan koneksi dan query terhadap Database Mysql. Saat ini yang tersedia (bawaan) dari PHP ada 3, yaitu koneksi mysql biasa (biasa disebut Raw Mysql, contoh mysql_connect()), PDO (PHP Data Object) dan Mysqli (OOP dan Procedural). Disamping ketiganya masih banyak lagi aplikasi 3rd party yang dapat digunakan untuk melakukan koneksi ke database Mysql seperti Doctrine (ORM) dkk.

Setelah mencari tahu di internet, kebanyakan dari hasil Benchmark Raw Mysql yang menang. Sehingga saya juga tertarik untuk melakukan perbandingan mana yang paling cepat. Cara yang saya gunakan disini adalah, PHP melakukan koneksi dan query menggunakan looping tanpa menampilkan data hasil query ke halaman website. Mungkin kedepannya saya juga akan melakukan komparasi dengan cara lain, yaitu berapa waktu yg dibutuhkan untuk melakukan query pada data yang besar.

Oke langsung saja saya jabarkan scriptnya disini.

Listing Code mysql_compare.php

<?
/* File name : mysql_compare.php
 * Author : ofidz
 * Email : offiedz@majezty
 * Web : mocopat.wordpress.com
 */
?>
<html>
 <style>
 input {
 font-family: courier;
 text-align: right;
 }
 </style>
 <body style="font-family: courier;">

<table>
 <tr><td align="right">Running time : </td><td><input id="running" value='0' size="10"></td><td align="center">detik</td><td align="center">qry/dtk</td></tr>
 <tr style="background-color: bisque;"><td align="right">PDO Process : </td><td><input id="pdo" value='0' size="10"></td><td><input id="pdt"></td><td><input id="avgpdo" value='0' size="7"></td></tr>
 <tr style="background-color:lightcyan ;"><td align="right">Raw Process : </td><td><input id="raw" value='0' size="10"></td><td><input id="rdt"></td><td><input id="avgraw" value='0' size="7"></td></tr>
 <tr style='background-color: lightgreen;'><td align="right">Mysqli OOP Process : </td><td><input id="sqli" value='0' size="10"></td><td><input id="sqlit"></td><td><input id="avgsqli" value='0' size="7"></td></tr>
 <tr style='background-color: lavender;'><td align="right">Mysqli Proc Process : </td><td><input id="sqlip" value='0' size="10"></td><td><input id="sqlipt"></td><td><input id="avgsqlip" value='0' size="7"></td></tr>
 </table>
 <script>
 var milisec = 0
 var seconds = 0
 document.getElementById('running').value='0'
 function display(){
 if (milisec >= 9){
 milisec = 0
 seconds += 1
 }
 else
 milisec += 1
 document.getElementById('running').value=seconds+"."+milisec+" detik"
 t = setTimeout("display()",100)
 }
 display()
 </script>
 <?
 set_time_limit(3600);
 $data = 1000;
 $server = "localhost";
 $user = "root";
 $pass = "";
 $db = "nwind";
 $sql = "SELECT * FROM order_details ";
 # Function

function xtime($start) {
 return microtime(true) - $start;
 }

# Begin Test PDO

function pdo_test($server, $db, $user, $pass, $data, $sql) {

$start = microtime(true);
 for ($i = 1; $i <= $data; ++$i) {

try {
 $conn = new PDO("mysql:host=$server;dbname=$db", $user, $pass);
 } catch (PDOException $e) {
 echo 'Connection failed: ' . $e->getMessage() . "\n";
 }

$conn->query($sql);
 echo "<script>document.getElementById('pdo').value='$i/$data';</script>";
 echo "<script>document.getElementById('pdt').value='" . xtime($start) . "';</script>";
 echo "<script>document.getElementById('avgpdo').value='" . number_format($i / xtime($start), 2, ',', '') . "';</script>";
 $conn = null;
 }
 echo "<tr style='background: bisque;'><td align='right'>PDO time : </td><td>" . xtime($start) . "</td></tr>";
 echo "<br/>";
 }

# Begin mysql_* Test

function mysql_test($server, $db, $user, $pass, $data, $sql) {
 $start = microtime(true);

for ($i = 1; $i <= $data; ++$i) {
 $conn = mysql_connect($server, $user, $pass);
 mysql_select_db($db);
 mysql_query($sql);
 echo "<script>document.getElementById('raw').value='$i/$data';</script>";
 echo "<script>document.getElementById('rdt').value='" . xtime($start) . "';</script>";
 echo "<script>document.getElementById('avgraw').value='" . number_format($i / xtime($start), 2, ',', '') . "';</script>";
 mysql_close($conn);
 }

echo "<tr style='background-color:lightcyan ;'><td align='right'>Raw time : </td><td>" . xtime($start) . "</td></tr>";
 }

# Mysqli OOP test

function mysqli_oop_test($server, $db, $user, $pass, $data, $sql) {
 $start = microtime(true);
 for ($i = 1; $i <= $data; ++$i) {
 $mysqli = new mysqli($server, $user, $pass, $db);
 $mysqli->query($sql);
 echo "<script>document.getElementById('sqli').value='$i/$data';</script>";
 echo "<script>document.getElementById('sqlit').value='" . xtime($start) . "';</script>";
 echo "<script>document.getElementById('avgsqli').value='" . number_format($i / xtime($start), 2, ',', '') . "';</script>";
 $mysqli->close();
 }
 echo "<tr style='background: lightgreen;'><td align='right'>Mysqli OOP time : </td><td>" . xtime($start) . "</td></tr>";
 }

# Mysqli Procedural Test

function mysqli_proc_test($server, $db, $user, $pass, $data, $sql) {
 $start = microtime(true);
 for ($i = 1; $i <= $data; ++$i) {
 $mysqli = mysqli_connect($server, $user, $pass, $db);
 mysqli_query($mysqli, $sql);
 echo "<script>document.getElementById('sqlip').value='$i/$data';</script>";
 echo "<script>document.getElementById('sqlipt').value='" . xtime($start) . "';</script>";
 echo "<script>document.getElementById('avgsqlip').value='" . number_format($i / xtime($start), 2, ',', '') . "';</script>";
 mysqli_close($mysqli);
 }
 echo "<tr style='background: lavender;'><td align='right'>Mysqli Proc time : </td><td>" . xtime($start) . "</td></tr>";
 }

 # Jumlah baris data
 function jml_row($server,$user,$pass,$db,$sql){
 $conn = mysql_connect($server,$user,$pass);
 mysql_selectdb($db);
 $xSql = mysql_query($sql);
 return mysql_num_rows($xSql);
 mysql_close($conn);
 }
 ?>
 <table>
 <tr><td align='right'><b>Result :</b></td><td><?= $data . " x ". jml_row($server, $user, $pass, $db, $sql)." row(s) execution"; ?></td></tr>
 <?
 pdo_test($server, $db, $user, $pass, $data, $sql);
 mysql_test($server, $db, $user, $pass, $data, $sql);
 mysqli_oop_test($server, $db, $user, $pass, $data, $sql);
 mysqli_proc_test($server, $db, $user, $pass, $data, $sql);
 ?>
 </table>
 <script>
 clearTimeout(t);
 </script>
 </body>
</html>

Pada script diatas, masing-masing driver menggunakan function, supaya kita lebih mudah dalam menempatkan driver yang lebih dulu akan dijalankan. Jumlah query yang akan dijalankan juga dapat disetting dengan mengganti nilai pada variabel $data. Berikut contoh hasil benchmark yang saya lakukan.

Software dan Hardware yang saya gunakan pada saat benchmark.

  • OS : Windows 7
  • Laptop Acer 4752G
  • Prosesor : Intel Core i3-2330
  • RAM : Kingston 2GB
  • Hardisk : Seagate 500GB
  • VGA NVIDIA GeForce GT 520M 1GB
  • Browser : Chrome 17
  • MySQL :  5.0.8
  • PHP : 5.3.8

Jika anda pernah melakukan benchmark juga, silahkan share disini. Saya juga minta pendapat anda mengenai script yang saya pakai untuk benchmark, mungkin kurang fair atau bagaimana, silahkan diluruskan jika kurang fair. Terima kasih.

Backup dan Restore Database MySQL lewat Shell/Konsole

Sekedar sharing, beberapa waktu lalu saya dibuat bingung dengan command mysqlimport. Ternyata command ini tidak di peruntukkan restore database, tapi digunakan untuk append data dari database backup ke database asli. Sehingga jika ingin melakukan restore, kita bisa menggunakan command mysql.

Backup Database:

mysqldump -u user_name -ppassword_user namadb /path/nama_db_backup.sql

Atau jika ingin langsung di compress:

mysqldump -u user_name -ppassword_user namadb | gzip > /path/nama_db.gz

Restore Database:

mysql -u user_name -p nama_db < /path/nama_db_backup.sql

Ganti user_name, password, nama_db dan path sesuai dengan parameter anda.

Script Auto Backup Database MySQL pada cPanel

Kemarin sempat browsing cara backup database MySQL lewat Cron Jobs yang tersedia pada linux dan disediakan juga di cPanel. Ternyata langkah yang harus dilakukan tidak terlalu sulit. Bisa langsung menggunakan perintah yang langsung di tulis di Cron Jobs atau dengan cara membuat script. Script yang saya buat disini menggunakan Shell/Bash.

Oke langsung saja.

    1. Terlebih dahulu sebuah buatlah folder backup pada user yg bersangkutan
    2. Kemudian buatlah script untuk backup database MySQL. Disini saya tuliskan script lengkap yang telah saya buat dan nanti akan saya jelaskan satu persatu.
#!/bin/sh
tanggal=`date '+%Y%m%d_%H%M%S'`;
path="/home/user/backup";
find $path/ -mtime +2 -type f -exec rm -rf {} \;
sleep 30;
mysqldump -u userdb -ppassdb namadb | gzip > $path/namadb_$tanggal.gz
    1. Simpan script tersebut dan berilah nama backup.sh
    2. Upload dan letakkan file tersebut pada direktori /bin pada cPanel.
    3. Ubah permission nya menjadi 755.
    4. Masuk ke Cron Jobs, pada menu Add New Cron Job, kemudian setting waktu yang diinginkan untuk melakukan backup.
    5. Pada form Command, masukkan perintah
sh /home/user/bin/backup.sh
  1. Klik tombol “Add New Cron Job”.
  2. Untuk mencoba apakah script yang kita buat berjalan atau tidak, sebaiknya setting waktu waktu eksekusi di Cron Jobs tadi menjadi “Every Minute”, nanti jika script nya sudah berjalan dengan lancar baru ubah lagi sesuai dengan yang diinginkan.

Penjelasan Script

tanggal=`date '+%Y%m%d_%H%M%S'`;

Adalah variabel yang mengambil tanggal pada waktu script di eksekusi, sehingga nama database akan selalu berbeda, sesuai dengan tanggal dan jam backup.

path="/home/user/backup";

Folder dimana database hasil backup akan di simpan.

find $path/ -mtime +2 -type f -exec rm -rf {} \;

Script diatas berfungsi untuk mencari file di dalam folder $path (/home/user/backup) yang mempunyai umur lebih dari 2 hari dari sekarang. Jika file yang dimaksud ditemukan, kemudian file tersebut di delete. Silahkan ubah +2 sesuai dengan kebutuhan anda.

sleep 30;

Digunakan untuk menunggu waktu delete dan waktu backup, supaya tidak berbarengan. 30 adalah dalam detik. Mungkin script ini tidak diperlukan jika database yang didelete kecil atau sedikit. Tapi jika database yang akan didelete besar dan banyak, sebaiknya script ini ditambahkan supaya tidak terlalu memberatkan server.

mysqldump -u userdb -ppassdb namadb | gzip > $path/namadb_$tanggal.gz

mysqldump adalah perintah untuk melakukan backup pada database MySQL. Gantilah userdb, passdb dan namadb sesuai dengan user, password dan database anda. gzip digunakan untuk melakukan kompresi database. Anda juga diperbolehkan untu menggunakan jenis kompresi lain seperti zip, rar dll.

Semoga bermanfaat.