Konfigurasi server digunakan untuk mengatur server secara umum, tidak spesifik ke pengaturan tertentu seperti pengaturan storage engine tertentu. Ketika server dinyalakan maka konfigurasi yang ada pada file konfigurasi akan disimpan pada variable sistem, untuk mengakses variabel ini kita dapat menggunakan perintah:
SHOW VARIABLES LIKE 'nama_variable'Atau menggunakan wilcard tanda % untuk mencari nama variabel yang mengandung kata tertentu. Untuk menentukan nilai variabel, terkadang kita perlu mengetahui status / statistik bagian tertentu dari operasional server, status ini disimpan pada variabel status, untuk mengaksesnya, kita gunakan perintah:
SHOW STATUS LIKE 'nama_status'Kita juga dapat menggunakan wilcard tanda % untuk mencari status variabel yang mengandung kata tertentu.
Berikut ini konfigurasi server yang penting untuk kita ketahui:
1. max_allowed_packet
Koneksi MySQL didesain untuk mengirim perintah SQL standar, tidak terlalu panjang, sehingga perintah SQL dapat dikirim sekaligus dalam satu paket (packet) dalam istilah MySQL atau chunk dalam istilah network, selanjutnya Server mengalokasikan memory (RAM) untuk menyimpan paket ini, besarnya alokasi ini ditentukan oleh konfigurasi max_allowed_packet. Besarnya perintah SQL ini ditentukan oleh panjang perintah SQL itu sendiri yang rata-rata 1 byte per karakter, untuk mudahnya kita dapat menyimpan perintah SQL tersebut ke sebuah file, misal file .txt kemudian lihat ukuran file tersebut, besarnya ukuran file = besarnya perintah SQL yang kita kirim. Pada MySQL, nilai default konfigurasi ini adalah 8Mb:
mysql> SHOW VARIABLES LIKE "%max_allowed_packet%";+------------------------------ +-------------+ | Variable_name | Value | +------------------------------+-------------+ | max_allowed_packet | 8388608 | | mysqlx_max_allowed_packet | 67108864 | | slave_max_allowed_packet | 1073741824 | +-------------------------------+-------------+ 3 rows in set (0.10 sec)
Sedangkan pada MariaDB, nilai defaultnya adalah 1Mb. Nilai default ini umumnya cukup untuk query select, sepanjang apapun query select tersebut, biasanya yang bermasalah ketika menjalankan statement INSERT (dibahas di bab tersendiri).
2. sort_buffer_size
Sort buffer adalah space yang dialokasikan pada memory (per client basis) untuk setiap eksekusi query yang memerlukan sorting data (yaitu ORDER BY dan GROUP BY). Ketika melakukan operasi sorting data maka server akan
Kita juga dapat menggunakan wilcard tanda % untuk mencari status variabel
yang di sort hanya sedikit sekali, tentu hal ini menyebabkan pemborosan space memory, Misal: apakah kita akan mengalokasikan 16Mb memory hanya untuk melakukan sorting 10 baris data? Tentu tidak bukan? Singkatnya nilai sort buffer tinggi berisiko pada pemborosan space, sehingga keep it small. Lantas berapa nilai yang pas? Idealnya beri nilai seminimal mungkin, misal 256Kb atau kurang, baru kemudian pada query yang membutuhkan space sort buffer besar, naikkan nilai sort buffer ini tepat sebelum menjalankan query tersebut dan tepat setelah query tersebut dijalankan, kembalikan nilai ini ke nilai defaultnya, misal:
SET @@session.sort_buffer_size := ;
-- Execute the query...
SET @@session.sort_buffer_size := DEFAULT;
Jika space tidak cukup, maka sorting dilakukan di hardisk yang tentu saja lebih lambat dibanding jika dilakukan di memory, namun dengan teknik diatas, nilai sort buffer dapat kita sesuaikan sehingga proses sorting tetap dapat dilakukan di memory.
Note: Jika ingin merubah nilai default dari sort buffer, maka sebaiknya tidak lebih dari 2M, karena malah akan menurunkan performa query.
Mungkin anda bertanya tanya, bagaimana kita mengetahui bahwa query kita perlu tambahan sort buffer? Jawabannya adalah tidak ada angka pasti, salah satu cara yang kita lakukan adalah melihat status dari sort_merge_passes
MariaDB [(none)]> SHOW STATUS LIKE "%Sort_merge_passes%"; +-----------------------+---------+ | Variable_name | Value | +-----------------------+---------+ | Sort_merge_passes | 0 | +-----------------------+---------+ 1 row in set (0.001 sec)
Jika nilai status ini tinggi, maka pertanda kita perlu menaikkan nilai sort buffer.
mengalokasikan space sebesar nilai pada konfigurasi ini, meskipun data yang di sort hanya sedikit sekali, tentu hal ini menyebabkan pemborosan space memory, Misal: apakah kita akan mengalokasikan 16Mb memory hanya untuk melakukan sorting 10 baris data? Tentu tidak bukan?
Singkatnya nilai sort buffer tinggi berisiko pada pemborosan space, sehingga keep it small. Lantas berapa nilai yang pas? Idealnya beri nilai seminimal mungkin, misal 256Kb atau kurang, baru kemudian pada query yang membutuhkan space sort buffer besar, naikkan nilai sort buffer ini tepat sebelum menjalankan query tersebut dan tepat setelah query tersebut dijalankan, kembalikan nilai ini ke nilai defaultnya, misal:
SET @@session.sort_buffer_size := ;
-- Execute the query...
SET @@session.sort_buffer_size := DEFAULT;
Jika space tidak cukup, maka sorting dilakukan di hardisk yang tentu saja lebih lambat dibanding jika dilakukan di memory, namun dengan teknik diatas, nilai sort buffer dapat kita sesuaikan sehingga proses sorting tetap dapat dilakukan di memory.
Note: Jika ingin merubah nilai default dari sort buffer, maka sebaiknya tidak lebih dari 2M, karena malah akan menurunkan performa query
Mungkin anda bertanya tanya, bagaimana kita mengetahui bahwa query kita perlu tambahan sort buffer? Jawabannya adalah tidak ada angka pasti, salah satu cara yang kita lakukan adalah melihat status dari sort_merge_passes
MariaDB [(none)]> SHOW STATUS LIKE "%Sort_merge_passes%";
++---------+ | Variable_name | Value | +---------------------+------------------------------+ | Sort_merge_passes | 0 | ++------------------------------+ 1 row in set (0.001 sec)
Jika nilai status ini tinggi, maka pertanda kita perlu menaikkan nilai sort buffer.
mengalokasikan space sebesar nilai pada konfigurasi ini, meskipun data
MariaDB [(none)]> SHOW VARIABLES LIKE "%sort_buffer_size%";
+------------------+---------| Variable_name | Value |-----------++------------------+---------| aria_sort_buffer_size | 268434432| | innodb_sort_buffer_size | 1048576 | | myisam_sort_buffer_size | 8388608 | | sort_buffer_size | 524288 | +-----------++------------------+--------4 rows in set (0.129 sec)-----------+
Pada contoh diatas, terlihat bahwa nilai sort buffer adalah 512Kb. Pada MySQL, nilai defaultnya adalah 256Kb
mysql> SHOW VARIABLES LIKE "%sort_buffer_size%";
+------------------+--------------------+ | Variable_name | Value | +------------------+---------+ | innodb_sort_buffer_size | 1048576 | | myisam_sort_buffer_size | 8388608 | | sort_buffer_size | 262144 | +-----------------------------+---------+ 3 rows in set (0.01 sec)-----------
NOTE: tidak selamanya perinsip “semakin besar semakin baik” itu baik, untuk konfigurasi ini, berlaku sebaliknya, semakin kecil semakin baik.
3. read_buffer_size
read_buffer_size mengatur besarnya space memory yang diperlukan ketika server melakukan scanning data pada sebuah tabel secara squensial (berurutan). Sama seperti sort buffer size, konfigurasi ini digunakan hanya jika diperlukan (melakukan table scan) dan semakin kecil nilai konfigurasi, maka semakin baik. Nilai konfigurasi bisa dimulai dari nilai 128K atau 256Kb, jika perlu nilai besar, pastikan nilainya tidak lebih dari 2M.
Pada percobaan yang ada, melakukan scanning tabel dengan jumlah data 4jt row maupun tabel dengan sedikit data, misal 400rb row, nilai read buffer optimal yang didapatkan adalah 128K, untuk nilai lebih dari itu, misal 256K, 512K, atau 2M waktu eksekusi query menjadi lebih lambat, sedangkan untuk
Nilai kurang dari 128K, Misal 64K atau 16K waktu eksekusi query sedikit lebih lambat namun masih lebih cepat dibanding nilai diatas 128K.
Nilai default untuk konfigurasi ini adalah:
MariaDB [(none)]> SHOW VARIABLES LIKE "%read_buffer_size%";
+-----------+-----------------------+ | Variable_name | Value | +-----------+------------+ | read_buffer_size | 262144 | +----------------------+------------+ 1 row in set (0.003 sec)-----------
Dari contoh diatas terlihat bahwa pada MariaDB nilai default read buffer adalah 256Kb, bagaimana dengan MySQL?
mysql> SHOW VARIABLES LIKE "read_buffer_size";
+-----------+------------| Variable_name | Value |-----------++-----------+------------| read_buffer_size | 131072 |-----------++-----------+------------1 row in set (0.04 sec)-----------+
Terlihat bahwa pada MySQL nilai default read buffer adalah 128K, lebih optimal dibanding MariaDB.
4. read_rnd_buffer_size
read_rnd_buffer_size sama seperti read_buffer_size, bedanya konfigurasi ini digunakan ketika membaca data tabel yang sudah diurutakan (sorted row) sedangkan pada reda_buffer_size data dibaca berurutan sesuai dengan urutan di hardisk. Konfigurasi ini berpengaruh signifikan ketika terdapat operasi ORDER BY yang melibatkan data dalam jumlah besar.
Seperti read_buffer_size, beri nilai read rnd ini sekecil mungkin baru ketika diperlukan (misal ada ORDER BY dengan jumlah data besar) ubah nilai buffer rnd melalui query SET ...
5. join_buffer_size
Join buffer ini digunakan ketika terjadi join antar tabel tanpa menggunakan index, pada query yang kompleks bisa saja terjadi banyak join buffer. Join buffer ini dapat dengan mudah dihindari dengan menambahkan index pada kolom yang di joinkan, menambahkan index ini jauh lebih baik dibanding memperbesar nilai join buffer.
Secara default nilai untuk konfigurasi ini adalah 256K baik pada MariaDB maupun MySQL
mysql> SHOW VARIABLES LIKE "%join_buffer_size%";
mysql> SHOW VARIABLES LIKE "%join_buffer_size%";
+-----------+------------| Variable_name | Value |-----------++-----------+------------| join_buffer_size | 262144 |-----------++-----------+------------1 row in set (0.17 sec)-----------+
6. net_buffer_length
net buffer adalah nilai awal (alokasi space awal) dari setiap koneksi buffer maupun result buffer, nilai ini dapat berubah maksimal sebesar max_allowed_packet, setelah query selesai dieksekusi, koneksi buffer kembali mengecil ke nilai net buffer, dengan demikian biasanya nilai ini secara default sudah kecil, MariaDB 8Kb, sedangakan MySQL 16Kb.
7. table_open_cache
Konfigurasi ini dapat meningkatkan performa dengan menyimpan tabel yang sedang digunakan (tabel yang sedang dibaca/ditulis oleh MySQL melalui file handler) ke dalam memory, setiap tabel yang akan digunakan akan disimpan ke dalam cache, jika sudah tidak digunakan maka tabel akan dihapus dari cache
jika cache penuh, data di dalam cache dapat dihapus menggunakan perintah FLUSH TABLES
Konfigurasi table_open_cache akan mengatur banyaknya tabel yang dapat disimpan pada cache, nilai ini biasanya tinggi sehingga dapat menampung banyak table. Nilai ideal biasanya dihitung dengan mengalikan
max_connection dengan jumlah tabel database, namun jika jumlah tabel banyak, nilai konfigurasi akan sangat tinggi dan tidak mungkin untuk dilakukan.
Pada MariaDB, nilai default untuk konfigurasi ini adalah 2000
MariaDB [(none)]> SHOW VARIABLES LIKE "%table_open_cache%";
+-----------+------------| Variable_name | Value |-----------++-----------+------------| table_open_cache | 2000 |-----------++-----------+------------1 row in set (0.003 sec)-----------+
Sedangkan pada MySQL, nilainya lebih besar yaitu 4000
mysql> SHOW VARIABLES LIKE "%table_open_cache%";
+------------------+------------| Variable_name | Value |-----------++-----------+-------------------| table_open_cache | 4000 | | table_open_cache_instances | 16 |-----------++-----------+-------------------2 rows in set (0.46 sec)-----------+
Sedangkan untuk nilai max_connection, baik pada MariaDB maupun MySQL nilainya sama, yaitu 151
MariaDB [(none)]> SHOW VARIABLES LIKE "%max_connection%";
+-----------+------------| Variable_name | Value |-----------++-----------+------------| extra_max_connections| 1 | | max_connections | 151 |-----------++-----------+------------2 rows in set (0.001 sec)-----------+
Untuk mengetahui tabel yang diakses tidak melalui cache, kita dapat menggunakan statistik opened tables, sebagai contoh:
mysql> SHOW GLOBAL STATUS LIKE "%opened_tables%";
+-----------+------------| Variable_name | Value |---------++-----------+------------| Opened_tables | 109 |---------++-----------+------------2 rows in set (0.02 sec)---------+
Atau kita dapat melihat tabel yang sedang dibuka melalui statistik open (tanpa ed) table sebagai berikut:
mysql> SHOW GLOBAL STATUS LIKE "%open_tables%";
+-----------+------------| Variable_name | Value |-----------++-----------+------------| Com_show_open_tables | 0 | | Open_tables | 102 |-----------++-----------+------------2 rows in set (0.03 sec)-----------+
Selanjutnya kita cek jika nilai opened table bertambah banyak dalam waktu singkat (terutama ketika server sibuk), maka pertanda kita perlu menaikkan nilai table_open_size. Penghitungan opened table ini melibatkan banyak hal, seperti temporary table yang akan menaikkan nilai opened table meski cache belum penuh, sehingga tidak perlu khawatir dengan semaki besarnya nilai konfigurasi ini.
Meskipun table cache sangat bermanfaat, kita tidak perlu memberi nilai tinggi, karena MySQL perlu melakukan pengecekan pada cache, sehingga jika nilai table cache besar, maka proses pengecekan bisa menjadi lambat, nilai optimum sebaiknya tidak lebih dari 10.000 sedangkan untuk nilai awal, yang ideal adalah 10x atau 20x max_connection.
