MYSQL

Tuning dan Optimasi Konfigurasi Server MySQL

シックス , MYSQL
0
106

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.


0 Comments

×