Meningkatkan Performa Database MySQL

[gilbert]

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.
Referensi tuning dari Linux Magazine bisa didownload disini.



Referensi :

1. MySQL Amazing Tips to Improve, Tuning and Increase MySQL Performance
2. http://dev.mysql.com/books/hpmysql-excerpts/ch06.html
3. http://dev.mysql.com/doc/refman/5.0/en/server-parameters.html
4. http://www.linux-mag.com/index.php?option=com_content&task=view&id=911&Itemid=2035

[papiku]

Sejak PHP 5 dirilis, muncul extension baru sebagai API ke RDBMS MySQL. Extension baru ini dinamakan MySQLI atau MySQL Improved. MySQLI memperkenalkan cara baru dalam berinteraksi dengan database server MySQL melalui PHP (dan konon lebih cepat dan lebih secure, walaupun gw sendiri belum pernah melakukan benchmark).
Yang paling keren menurut gw adalah model OOP baru dari MySQLI ini. Jadi di MySQLI kita bisa menggunakan 2 style coding, OOP ataupun prosedural. Model OOP-nya asyyiik banget dan lengkap.
Untuk nginstall extension MySQLI di Linux cukup gampang, cari aja paket-paket binary yang udah disediain ma distro masing-masing atau kalo kita biasa compile sendiri maka kita harus nambahin flag :

--with-mysqli=shared,/usr/local/mysql/bin/mysql-config

pada command configure kita
Kalo di Windows lebih gampang lagi, cukup tambahin baris :

extension=php_mysqli.dll

pada deretan module-module extension yang laennya di file php.ini. Trus restart deh Web Server-nya.
Langsung aja kita liat contoh implementasi extension MySQLI di source code PHP kita, begini nih caranye :

<?php // buat constant define('DB_HOST', 'localhost');define('DB_USER', 'dicarve');define('DB_PASS', 'dicarvepass');define('DB_NAME', 'dicarvedb');define('DB_PORT', '3306'); // cek dulu apa extensionnya dah aktif pa belom?if (!extension_loaded('mysqli')) {    echo 'Extension MySQLI belum terinstall dengan benar. check php.ini';    exit();} // buat objek mysqli$mysqli_obj = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME, DB_PORT); /* cek apakah koneksi berhasil dilakukan */if (mysqli_connect_errno()) {    echo 'Koneksi gagal dilakukan dengan alasan : '.mysqli_connect_error();    exit();} else {    echo 'Koneksi berhasil dilakukan'."\n";    echo 'Versi Server MySQL : '.$mysqli_obj->server_info."\n";}  /* tutup koneksi ke db server */$mysqli_obj->close();?>

Ya ampuunn, ternyata gampang bener yah!! Weiittsss nanti dulu, itu baru koneksi-nya aja gimana untuk ngirim query ke database server-nya? ini sedikit contoh query SELECT yang menghasilkan resultset (atau recordset, whatever lahh) :

<?php // include file source code koneksi database sebelumnya// yang misalnya udah kita kasih nama 'mysqli.inc.php';require 'mysqli.inc.php'; // untuk melakukan query kita menggunakan // metode 'query' milik kelas mysqli// hasil dari pemanggilan metode ini nantinya akan// menghasilkan sebuah objek baru milik kelas mysqli_result$result_obj = $mysqli->query("SELECT nama FROM tabel_dicarve"); // cek apakah query berhasil dilakukanif ($result_obj) {    // gunakan properti publik 'num_rows' milik kelas mysqli_result    // untuk mengetahui jumlah baris hasil query    echo 'Ditemukan '.$result_obj->num_rows.' baris data';     echo 'Data-data yang ditemukan adalah sbb : ';     // gunakan metode 'fetch_row' untuk mengambil data baris demi baris dari resultset    while ($data = $result_obj->fetch_row()) {        echo $data[0].''."\n";    }     // bebaskan memory resultset    $result_obj->close();} $mysqli_obj->close();?>

Lohh?? Cuman segitu doank toh MySQLI? Tau gitu mah gw mendingan pake extension MySQL biasa aja dechh (cappeee dehh!!). Ya Enggak Lah!!! ada beberapa fitur baru yang menarik di MySQLI selaen koneksi dan query sederhana, misalnya meng-execute query SQL secara multiple dengan langsung dalam satu statement (bukan dengan pemanggilan metode 'query' berulang-ulang). Coba liat contoh berikut ini deh :

<?php // include file source code koneksi database sebelumnya// yang misalnya udah kita kasih nama 'mysqli.inc.php';require 'mysqli.inc.php'; // setiap query SQL kita pisahkan dengan semi-colon atawa titik koma$queries  = "SELECT gmd FROM tabel_gmd;";$queries .= "SELECT nama FROM tabel_dicarve;";$queries .= "SELECT judul FROM tabel_biblio"; // gunakan metode 'multi_query'// untuk meng-execute string query SQL multiple secara simultan$multi_query_obj = $mysqli_obj->multi_query($queries); /* execute multi query */if ($multi_query_obj) {    // loop masing-masing result-nya    while (true) {        // ambil result set multiple query dengan metode 'store_result'        if ($result = $mysqli_obj->store_result()) {            while ($row = $result->fetch_row()) {                echo $row[0]."\n";            }             // bebasin memory resultset            $result->close();        }                 // apakah masih ada resultset lagi?        // kalau udah abis kita stop loop-nya        if (!$mysqli_obj->more_results()) {            break;        } else {            // kita buat pemisah antar resultset dengan garis horizontal HTML <hr>            echo "<hr>\n";        }    }} /* tutup koneksi ke server */$mysqli_obj->close();?>

Nah itu dia sekilas penggunaan extension MySQLI di PHP, masih banyak fitur lain yang saya ga bisa jelasin satu persatu di sini (takutnya blog ini malah isinya MySQLI melulu). Buat programmer PHP yang demen pake model OOP, MySQLI cocok banget untuk digunain sebagai bagian dari kode-kode mereka.

SELAMAT MENCOBA!!

[nandar]

The InnoDB storage engine is enabled by default. If you don't want to use InnoDB tables, you can add the skip-innodb option to your MySQL option file.
Note

InnoDB provides MySQL with a transaction-safe (ACID compliant) storage engine that has commit, rollback, and crash recovery capabilities. However, it cannot do so if the underlying operating system or hardware does not work as advertised. Many operating systems or disk subsystems may delay or reorder write operations to improve performance. On some operating systems, the very system call that should wait until all unwritten data for a file has been flushed — fsync() — might actually return before the data has been flushed to stable storage. Because of this, an operating system crash or a power outage may destroy recently committed data, or in the worst case, even corrupt the database because of write operations having been reordered. If data integrity is important to you, you should perform some “pull-the-plug” tests before using anything in production. On Mac OS X 10.3 and up, InnoDB uses a special fcntl() file flush method. Under Linux, it is advisable to disable the write-back cache.

On ATAPI hard disks, a command such hdparm -W0 /dev/hda may work to disable the write-back cache. Beware that some drives or disk controllers may be unable to disable the write-back cache.
Two important disk-based resources managed by the InnoDB storage engine are its tablespace data files and its log files.
Note

Sumber: http://dev.mysql.com/doc/refman/5.0/...iguration.html

[dany]

Untuk memperoleh hasil maksimal dari query terhadap MySQL sebaiknya memperhatikan setting variable pada file /etc/my.cnf
Berikut contoh setting terhadap mesin AMD 2Ghz memory 256MB
[client]
port = 3306
socket = /var/lib/mysql/mysql.sock
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
# maksimal koneksi yang diperbolehkan terhadap database
# perhatikan juga setting terhadap httpd.conf yang berkaitan dengan setting
# jumlah klien yang diperbolehkan mengakses
max_connections = 100
skip-locking
skip-innodb
# bila menggunakan versi 4.1x ke atas, terdapat fitur cache terhadap query
# tetapi bila table terlalu cepat berubah, sebaiknya ditiadakan
# karena justru akan membuat lambat query itu sendiri.
query_cache_type= 1
query_cache_limit= 1M
query_cache_size= 32M
interactive_timeout=100
wait_timeout=15
connect_timeout=10
table_cache=128
thread_cache=32
key_buffer=32M
[mysqldump]
quick
max_allowed_packet=16M
[mysql]
no-auto-rehash

[gilbert]

Pengalaman pribadi optimasi mysql untuk penggunaan resource server yg lumayan besar dengan mysql versi 4.x.x

1. instal mysql
download rpm mysql di http://www.mysql.com

ini contoh dari my.cnf yg sekarang berjalan di server DELL 2950, xeon dengan 2 GB's ram, MySQL dan web, jadi semua semua resource memori tidak terpakai oleh MySQL
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-locking
skip-innodb
query_cache_limit=1M
query_cache_size=32M
query_cache_type=1
max_connections=900
interactive_timeout=100
wait_timeout=100
connect_timeout=10
thread_cache_size=128
#key_buffer=16M
key_buffer=200M
join_buffer=1M
max_allowed_packet=16M
table_cache=1536
sort_buffer_size=1M
read_buffer_size=1M
read_rnd_buffer_size=1M
max_connect_errors=10
# Try number of CPU's*2 for thread_concurrency
thread_concurrency=4
myisam_sort_buffer_size=64M
#log-bin
server-id=1Query caching was added as of MySQL version 4, the fo

Server dengan memory 512:
thread_cache_size=50
key_buffer=40M
table_cache=384
sort_buffer_size=768K
read_buffer_size=512K
read_rnd_buffer_size=512K
thread_concurrency=2

servers dengan 1 GB ram:
thread_cache_size=80
key_buffer=150M
table_cache=512
sort_buffer_size=1M
read_buffer_size=1M
read_rnd_buffer_size=768K
thread_concurrency=2

Semoga berguna untuk optimasi alokasi memory di servers

[gilbert]

ni dalam platform ape? kalu red hat 8.0 dan mysql 4.0.12 camni

bukak fail my.cnf yang leh dijumpai dalam /etc/my.cnf
kalau takde locate my-larger.cnf yang biasa dijumpai dalam /root/mysql-4.0.12/support-files/my-large.cnf
em...pastu ko wat camni #cp my-large-cnf /etc/my.cnf---->fail ni akan dicopy dalam folder /etc
ok pastu vi my.cnf dan ko akan nampak benda ni
# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
set-variable = key_buffer=256M
set-variable = max_allowed_packet=1M
set-variable = table_cache=256
set-variable = sort_buffer=1M
set-variable = record_buffer=1M
set-variable = myisam_sort_buffer_size=64M
set-variable = thread_cache=8
# Try number of CPU's*2 for thread_concurrency
set-variable = thread_concurrency=8
log-bin----------------------------------------------------->ini adalah log file untuk mysql
server-id = 1


ok..kalau dalam fail my.cnf tu takde log-bin ko tambahkan aje.....taip log-bin.ini adalah default log file atau dalam mysql4.0.12 dipanggil binary log ...kalau sebagai default nama fail dia adalah berakhir dengan bin..contoh localhost-bin.002..ini adalah fail binary log untuk mysql 4.0.12 yang mengandungi semua proses update,delete dan select dalam mysql...nak baca fail ni guna mysqbinlog..kalau takde guna which mysqlbinlog....mesti ade dalam red hat 8.0..

ok tu je rase..o lupa ...fail log-bin boleh juga disimpan dalam fail my.cnf sebagai log-bin=fail.extension.

harap membantu anda

[quin2007]

port = 3306
socket = /tmp/mysql.sock
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
log-error = /data/error.txt
user=root
skip-locking
max_connections = 3000
table_cache = 1024
max_allowed_packet = 1M
sort_buffer_size = 64K
thread_cache = 8
thread_concurrency = 16
query_cache_size = 0M
query_cache_type = 0
default-storage-engine = innodb
transaction_isolation = REPEATABLE-READ
tmp_table_size = 1M
innodb_data_file_path = ibdata1:100M:autoextend
innodb_buffer_pool_size = 5500M
innodb_additional_mem_pool_size = 20M
innodb_log_file_size =1900M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit =1
innodb_lock_wait_timeout = 300
innodb_max_dirty_pages_pct = 90
innodb_thread_concurrency =16

[aljufri]

Sambungan dari tulisan saya sebelumnya tentang tuning MySQL, saya akan menjelaskan sedikit tentang tuning yang dapat kita lakukan dengan menggunakan file /etc/my.cnf. File my.cnf adalah file yang biasanya terletak di bawah direktori /etc/. File ini digunakan untuk mengeset tuning yang kita inginkan sesuai dengan kebutuhan dan kemampuan MySQL kita. Ada hal-hal yang apabila kita perhatikan maka akan membuat kemampuan atau kecepatan MySQL kita semakin meningkat. Tentunya tidak semua server mempunyai my.cnf yang sama persis, kecuali kalau konten atau query dari database adalah sama persis, maka my.cnf dapat kita setting dengan isi yang sama.

Misalnya adalah setting my.cnf seperti di bawah ini,

[mysqld]
socket=/path/to/mysql.sock
datadir=/var/lib/mysql
skip-locking
skip-innodb
# MySQL 4.x has query caching available.
# Enable it for vast improvement and it may be all you need to tweak.
query_cache_type=1
query_cache_limit=1M
query_cache_size=32M
# Reduced to 200 as memory will not be enough for 500 connections.
# memory=key_buffer+(sort_buffer_size+read_buffer_si ze)*max_connections
# which is now: 64 + (1 + 1) * 200 = 464 MB
# max_connections = approx. MaxClients setting in httpd.conf file
# Default set to 100.
max_connections=200
#interactive_timeout=180
interactive_timeout=100
#wait_timeout=180
#wait_timeout=100
# Reduced wait_timeout to prevent idle clients holding connections.
#wait_timeout=30
wait_timeout=15
connect_timeout=10
# max_connect_errors is set to 10 by default
#max_connect_errors=10
#table_cache=256
#table_cache=1024
# Checked opened tables and adjusted accordingly after running for a while.
table_cache=512
# Reduced it to 32 to prevent memory hogging. Also, see notes below.
thread_cache=32
# key_buffer=258M
# Reduced it by checking current size of *.MYI files, see notes below.
key_buffer=128M
# Commented out the buffer sizes and keeping the default.
# sort_buffer_size=2M by default.
#sort_buffer_size=1M
# read_buffer_size=128K by default.
#read_buffer_size=1M
# 1Mb of read_rnd_buffer_size for 1GB RAM — see notes below.
# read_rnd_buffer_size=256K by default.
#read_rnd_buffer_size=1M
# myisam_sort_buffer_size used for ALTER, OPTIMIZE, REPAIR TABLE commands.
# myisam_sort_buffer_size=8M by default.
#myisam_sort_buffer_size=64M
# thread_concurrency = 2 * (no. of CPU)
thread_concurrency=2
# log slow queries is a must. Many queries that take more than 2 seconds.
# If so, then your tables need enhancement.
log_slow_queries=/var/log/mysqld.slow.log
long_query_time=2
[mysql.server]
user=mysql
basedir=/var/lib
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
open_files_limit=8192
[mysqldump]
quick
max_allowed_packet=16M
[isamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M
[myisamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M
[mysqlhotcopy]
interactive-timeout
[client]
socket=/path/to/mysql.sock

Mari kita perhatikan option-option seperti di bawah ini,
max_connections
max_connections adalah maksimum sambungan yang dapat dilakukan dalam waktu sekaligus, kalau website anda sangat sibuk, semakin besar tentunya akan semakin bagus, akan tetapi sudah pasti bahwa karena resource server akan semakin banyak dipakai maka CPU atau memory anda akan semakin termakan oleh proses mysql.
Karena itu, nilai max_connections dapat anda sinkronkan dengan besar memory anda dan akses maksimum Apache anda, di MaxClient. Tidak ada artinya anda perbesar akan tetapi MaxClient di Apache anda sangat kecil, misalnya hanya sekitar 50 sambungan saja.
Misalnya adalah seperti ini.

max_connections=200

query_cache_size
Apabila MySQL anda sibuk dan selalu mengulang-ulang query yang sama, maka settting query_cache_size anda perlu anda perhatikan. query_cache_size akan menyimpan query yang berulang, sehingga akan mempercepat kerja MySQL anda untuk query yang sama. Karena query yang sebelumnya disimpan dalam memory cache.
key_buffer_size
Data base menggunakan key index. Karena itu apabila index ini tersimpan dalam satu memori secara utuh, maka sudah tentu proses MySQL anda akan semakin cepat mendapatkan hasil dari query yang diinginkan. Secara ideal semua index akan bagus tersimpan dalam memory ini, akan tetapi anda perlu juga melihat kemampuan kapasitas memory anda.
table_cache
Apabila MySQL anda banyak menggunakan table query, maka besar memori ini juga perlu anda perhatikan. Karena akan mempercepat proses query yang berhubungan dengan table. Default memory ini adalah 64Kb.
sort_buffer
Apakah MySQL anda memiliki query atau operasi myisamchk atau sort ? Maka option ini sangat penting untuk anda perhatikan. Dengan memasang buffer yang besar tentunya anda akan melakukan sorting secara besar juga. Nah sudah pasti sorting anda akan semakin cepat selesai.
read_rnd_buffer_size
read_rnd_buffer_size digunakan setelah kita melakukan sorting. Yaitu untuk memproses row dari query sorting yang telah kita lakukan. Apabila query anda sering memakai ORDER BY, maka option ini perlu anda perhatikan.
Anda dapat menghitung besar buffer ini dengan logika 1 MB adalah 1KB, jadi apabila memory server anda adalah 1GB, maka anda dapat mengesetnya sebesar 1MB.
thread_cache
Apabila server anda sibuk dengan banyaknya query dan memerlukan respon yang sangat cepat, maka thread_cache adalah option yang perlu anda perhatikan. Karena itu semakin besar nilai ini, akan semakin tinggi load CPU anda.
tmp_table_size
tmp_table_size akan memberikan kesempatan kepada MySQL untuk menyimpan table ke dalam harddisk yang dianggap sebagai memory. Sudah tentu tmp_table_size akan memperlambat kecepatan MySQL. Apabila anda mempunyai memory yang minim dan kecepatan MySQL tidak begitu diperhitungkan, barangkali ini adalah option yang anda kehendaki.
Bagaimana cara menghitung memory yang diperlukan oleh MySQL dengan mudah ? Anda dapat menggunakan rumus sederhana berikut ini.

memory=key_buffer+(sort_buffer_size+read_buffer_si ze)*max_connections