Info Computer & Technology

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_size)*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_size)*max_connections

Tags: Apache, Linux, mysql, server, tuning